Sensitivity Analysis Using Excel

2783 WordsMay 12, 201212 Pages
Sensitivity Analysis Using Excel 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…show more content…
Or, you could use multiple data tables to illustrate the search. We conclude that we must sell at least 572 units to break even. Figure 2.8 Data Table Results for One-Variable, Net Cash Flow = $4300 E F 1 Units Sold Net Cash Flow 2 3 500 -$1,500 4 550 -$450 5 600 $600 6 650 $1,650 7 700 $2,700 8 750 $3,750 9 800 $4,800 10 850 $5,850 11 900 $6,900 12 13 G H I Units Sold Net Cash Flow 750 760 770 780 790 800 $3,750 $3,960 $4,170 $4,380 $4,590 $4,800 J K L Units Sold Net Cash Flow 770 771 772 773 774 775 776 777 778 779 780 $4,170 $4,191 $4,212 $4,233 $4,254 $4,275 $4,296 $4,317 $4,338 $4,359 $4,380 2.4 Two-Variable Data Table 25 You can use the same approach to find the threshold value of units sold (777) for net cash flow of $4,300, as shown in Figure 2.8. Line Chart or XY (Scatter) Chart The results of the Data Table command may be displayed using either a Line chart type or an XY (Scatter) chart type. Both types have a continuous numerical vertical axis. However, the Line

More about Sensitivity Analysis Using Excel

Open Document