Sensitivity Analysis Example Answers All Your What If’s

sensitivity analysis exampleWhat if I told you that you never ever have to go to a fortune teller ever again? That’s right. Never. Believe it or not, more often than you’d think, when it comes to a lot of different situations, you can actually compute for the future. To find out what the future brings, especially in the economy, all you need to have is a handy tool called Sensitivity Analysis. One example is, say, while riding home from work in your car today, you wanted to save some money on gas  and tried to skip traffic by going the other route home? Except on this day, everyone had the same great idea and took the same route, which caused more traffic and gas used than if you had gone your regular way?

‘What if’ questions like these are what sensitivity analysis answers, so you know which road to take even before actually going. Just between you and me, I think this is a great way of avoiding any unpleasant surprises along the way! As a matter of fact, if you love knowing not just what is going on in the world these days in terms of the economy, but also why it is happening, you might like this course called Economics without Boundaries with David McWilliams. Packed with dynamic animation, ideas and information, the course will help you lift the lid on exactly how the economy works, making everything predictable to you.

‘What if’ questions are what sensitivity analysis answers such as: What is the impact on shipping and manufacturing if the oil price increases by 20% in the latter part of next year? If inflation is 8 per cent next year what profits can you anticipate if you continue with the same prices you currently have?  What profits can you expect if you open a new plant? If you were to hire more people in Marketing and Sales, what impact would this have on the company?  Sensitivity analysis is also defined as the method of changing cell values to see how the change affects the formula’s outcome on the work sheet. Here is a course called Micro and Macro Economics that teaches you the impact economic variable shave on the finance industry and market. It also deals with the decision making, behavior, structure and performance of the entire economy. As a matter of fact this course comes in really handy for investors.

Also known as what-if analysis, Sensitivity analysis is a technique of brainstorming that is used for determining how performance projected is affected by altered assumptions that create the performance projected. Often, this kind of analysis is used for comparing potential outcomes of various scenarios based on conditions that change. Often, this is used in business risk assessments but can also be used for other activities. For example, if you are making vacation plans for the family, you might be considering flying versus driving. Except, what if the air ticket becomes more expensive at the last minute? Or the gasoline prices of cars go up? These factors determine your final decision. Using sensitivity analysis will help you make a much better final decision.

Basically, the study of how a mathematical system or model’s output uncertainty is called Sensitivity Analysis.  In economics, managers and business owners are provided with various tools for measuring the activity of business. Sensitive analysis is one common tool, which reviews situations before making a determination how dependable variable will be affected by various values. Sensitivity analysis is also used by companies when using a finance model to find out how changes in net income are relative to operations change. A set net income target figure can be set by the company before determining how sensitive this amount I when the number of competitors and government regulations affect it.

The price of services and goods is a common sensitivity analysis. Under the theory of basic supply and demand, a price change affects the number of sold product. Goods are subjected to substitute or inferior products, which lead to high sensitivity because there are existing alternative products for use by consumers. Here is an article entitled Multiple If Statements in Excel Make Complex Conditions Easy to Deal With that shows you exactly just how powerful ‘if’ statements are in Excel and what functions you can use to extract or analyse values from a specific data set.

Doing a sensitivity analysis starts with a formula set or model for item measurement. In economics, it is common to do a customer survey since companies are able to determine how consumers will be reacting to business practices changes. For instance, surveys can gauge sensitivity that results from a product quality change.

What are the Benefits of What If Analysis?

There are several ways in which conducting a what-if framework is beneficial. Not only can you make more informed and better decisions by estimating, observing or changing assumptions of the results, you are also able to better predict your decisions’ outcome. For instance, if you have done an analysis of sensitivity before you decide to raise your prices, you will probably be making a less risky move than if you didn’t do an analysis. After all, you have already made a determination of how your business will be affected by the price increase. Aside from leading to faster decisions, a sensitivity analysis also provides you with a real glimpse of the future.

Common Sensitive Analysis Methods Use:

  1. Simulation and Modelling techniques used often for testing IT scenarios and computer systems.
  2. Techniques of brainstorming that involve identifying potential factors and activities that could affect these activities’ outcomes. This involves generating questions of ‘what if’ to determine how activities will be affected by various scenarios.
  3. Tools for managing scenarios such as the kind built into Excel by Microsoft.

Sensitivity Analysis Example # 1

Current MBA student Carla Lee makes the decision to spend her summer marketing and designing West Virginia bicycle maps. She finished designing four maps, which correspond to the four quadrants around Morgantown. Each map is different in topography 3-D relief, complexity, colors used and size. The maps are produced by a printer she has retained. Each map needs to be folded, cut and printed.

Questions to Answer:

  1. For extra time of folding time and printing, how much is Carla willing to pay? How many hours are people willing to purchase at that price.
  2. What is the projected profit and quantities of production?
  3. If the 1 thousand limit is reduced to 900, how much more will she make and which map ought to be decreased?
  4. If she comes up with a 5th map, this would take 2 minutes for printing, 3 minutes for folding and 2 minutes for cutting. What is the least profit amount needed to think of getting this map produced? What effects will requiring another 1000 of these have?
  5. There is still an incomplete D marketing analysis, though it is known that per item there is a $2 profit within twenty five cents of the right values. To complete the analysis, it costs $500. Should she push through with getting it analysed?

Print   Fold   Cut

D             3        3       3

C             3        5       1

B             2         2      4

A             1         3      2

———————————

Avail 15,000 20,000 20,000

As noted in this table, there is a limited amount of time in the schedule.

Based on the projecting price of selling minus variable costs and printer costs comes out to about $2 for C and D and $1 for A and B. For you to have a display that is sufficiently nice, a minimum of a thousand of each type needs to be made.

Max A + B + 2 C + 2 D

Subject to:

8 1000 < D

7 1000 < C

6 1000 < B

5 1000 < A

4 20000 > 3A + 2B + 5C + 3D

3 20000 > 2A + 4B + C + 3D

2 15000 > A + 2B + 3C + 3D

End

Optimum LP is found at step 8

Objective Function Value 1) 10166.667

RowSurplus or SlackDual Prices
81833.3333740.000000
70.000000-0.333333
60.000000-0.333333
5500.0000000.000000
40.0000000.166667
33500.0000000.000000
20.0000000.500000

No. of Iterations: 8

VariableValueReduced Cost
D2833.33323520.000000
C1000.0000000.000000
B1000.0000000.000000
A1500.0000000.000000

Ranges where there is an unchanged basis:

Right Hand Side Ranges:

RowCurrent RHSIncrease AllowableDecrease Allowable
81000.0000001833.333374Infinity
71000.000000   500.0000001000.000000
61000.0000001750.0000001000.000000
51000.000000   500.000000Infinity
420000.0000007000.0000001000.000000
320000.000000Infinity3500.000000
215000.0000001000.0000003666.666748

 

Sensitivity Analysis Example # 2

A thousand automobiles need to be produced by Tucker, Inc. using their four plants of production. Due to technological advances and varying workforce, the plants have varied costs for the production of each car. At each plant, they also use different materials and labor amounts.

Here are some questions to be answered:

  1. What is the cost of production at the moment? What are the current quantities of production?
  2. If it cost only eight thousand dollars to produce at the 2nd plant, how much will the solution change? For what cost range is the solution valid for the 2nd plant?How much will the cost be for producing 1 vehicle? By producing one less, how much will you save?
  3. How much is it costing us to go on with the union contract? What value would reduce the car limit of 400 to 200 or to zero cars? What would be the cost of increasing by 200 cars? By 100?
  4. For an hour of labor, how much are we willing to pay?
  5. A new plant design is going on which will only use 4 units of raw materials and 1 unit of workers. What is the cost maximum it can have for us to consider its use?
  6. By how much does the 4th plant have to get its cost reduced in order to consider its use? By how much does it need its material usage reduced instead?
  7. How much does the first plant cost need to increase before we start producing at that location?
  8. To get one more unit how much is our raw material worth? What happens if we want additional material? How many units do we want to buy at this price?

This table summarizes the situation:

Plant     Material     Labor    Cost Per Thousand

1                3            2                   15

2                4             3                    10

3                5             4                      9

4                6             5                      7

The contract of labor specifies that a minimum of four hundred cars be produced at the third plant. There are four hundred material unites and three thousand, three hundred hours of material that can be used for allocation to the 4 plants.

Minimum 15 x1 + 10 x2 + 9 x3 + 7 x4

Subject to:

2 à 1000 = x1 + x2 + x3 + x4

3 à 400 <  x3

4 à 3300 > 2 x1 + 3 x2 + 4 x3 + 5 x4

5 à 4000 > 3 x1 + 4 x2 + 5 x3 + 6 x4

Optimum LP found at Step 3.

Objective Function Value

  1. 11,600.000
VariableValueReduced Cost
X40.0000007.000000
X3400.0000000.000000
X2200.0000000.000000
X1400.0000000.000000
ROWSURPLUS OR SLACKDUAL PRICES
50.0000005.000000
4300.0000000.000000
30.000000-4.000000
20.000000-30.000000

 

# Of Iterations: 3

Range in which there is an unchanged basis:

Right-hand Side Ranges

RowCurrent RHSAllowable increaseAllowable Decrease
54000.000000300.000000200.000000
43300.000000Infinity300.000000
3400.000000100.000000400.000000
21000.00000066.66664100.000000

 

OBJ Coefficient Ranges

VariableCurrent CoefficientAllowable IncreaseAllowable Decrease
X47.000000Infinity7.000000
X39.000000Infinity4.000000
X210.0000002.000000Infinity
X115.000000Infinity3.5000000

 

Hope this helps! For more of the same useful tools you can use in various situations, here is a course entitled Excel 203 Data Analyst Intermediate Training that helps you learn awesome data analysis with this awesome course of Excel.