# Sensitivity Analysis Using Excel

**Topics:**Cartesian coordinate system

**Pages:**9 (2783 words)

**Published:**May 12, 2012

2

The main goal of sensitivity analysis is to gain insight into which assumptions are critical, i.e., which assumptions affect choice. The process involves various ways of changing input values of the model to see the effect on the output value. In some decision situations you can use a single model to investigate several alternatives. In other cases, you may use a separate spreadsheet model for each alternative.

2.1 MANUAL WHAT-IF ANALYSIS

Using this approach, you enter values into cells C4:C6 and see what the effect is on net cash flow. For example, with the predetermined price of $29, you may think that Units Sold will be in the range between 500 and 900 units. Keeping other input assumptions at base case, the corresponding Net Cash Flows are $–1,500 and $6,900. When we vary a single input assumption, keeping all other input assumptions at their base case values, we say we are doing "one at a time" or "singlefactor" sensitivity analysis. Figure 2.1 Model Display A B 1 Controllable Input Unit Price 2 3 Uncontrollable Inputs Units Sold 4 Unit Variable Cost 5 Fixed Costs 6 7 Performance Measure Net Cash Flow 8 C $29 700 $8 $12,000 $2,700

2.2 THRESHOLD VALUES

You are usually interested in how the input assumptions eventually affect the recommended choice in the decision problem. In the professor's summer decision problem, the software alternative (uncertain payoff) is being compared with teaching MBAs (payoff $4,300) and taking a vacation (payoff $0). An example of a threshold is to ask "How many units must I sell to do better with software than with teaching?" In other words, in the software spreadsheet model, what must the value of Units Sold in cell C4 be so that the value of Net Cash Flow in cell C8 is equal to $4,300? You could find the answer by repeatedly trying different values in cell C4. Or, you could solve a single algebra equation involving one unknown. Or, you could let Excel find the answer.

22

Chapter 2 Sensitivity Analysis Using Excel

Goal Seek

To answer the question using an Excel feature, choose Tools | Goal Seek. In Excel 2007, choose Data | What-If Analysis | Goal Seek. In Excel terminology, you want to "Set cell" C8 "To value" 4300 "By changing cell" C4. Figure 2.2 shows the entries when you point to cells C8 and C4, in which case they appear in the range edit boxes as absolute references, indicated by the dollar signs. Figure 2.2 Goal Seek Dialog Box

Alternatively, you could type C8 and C4 into the edit boxes instead of pointing. When you click OK, Excel displays a Goal Seek Status message, as shown in Figure 2.3. If there is a complex or discontinuous relationship between the changing cell and the set cell, the Goal Seek Status message may say that it was not able to find a solution. Figure 2.3 Goal Seek Status

To dismiss the message, click OK, in which case Excel shows the results in the spreadsheet model. Figure 2.4 shows that you must sell at least 776 units to have higher cash flow with the software than teaching MBAs. Cell C4 is formatted to display integer values. The formula bar shows that the exact value is 776.190476190476, using Excel's precision of fifteen significant digits.

2.3 One-Variable Data Table

23

Figure 2.4 Units Sold Threshold for $4,300 Net Cash Flow

A B 1 Controllable Input Unit Price 2 3 Uncontrollable Inputs Units Sold 4 Unit Variable Cost 5 Fixed Costs 6 7 Performance Measure Net Cash Flow 8 C $29 776 $8 $12,000 $4,300

Breakeven Point

A special case of threshold analysis is the breakeven point, usually defined as the sales volume at which contribution to profit and overhead equals fixed cost. In the software model, the breakeven point is the value for Units Sold when Net Cash Flow is zero. Using Goal Seek (not shown here), the breakeven point is found to be 571. The professor must sell at least 571 units to have higher cash flow with the software than taking a vacation. You could also use...

Please join StudyMode to read the full document