Excel 4 Ur Business

Overview

• I’d like to create best, worst, and most-likely scenarios for the sales of an automobile by varying the values of year 1 sales, annual sales growth, and year 1 sales price. Data tables for sensitivity analysis allow me to vary only one or two inputs, so I can’t use a data table. Does Excel have a tool that allows me to vary more than two inputs in a sensitivity analysis?

The Scenario Manager enables you to perform sensitivity analysis by varyingas many as 32 input cells. With the Scenario Manager, you first define the set of input cells you want to vary. Next you name your scenario and enter for each scenario the value of each input cell. Finally, you select the output cells (also called result cells) that you want to track. The Scenario Manager then creates a beautiful report containing the inputs and the values of the output cells for each scenario.

I’d like to create best, worst, and most-likely scenarios for the sales of an automobile by varying the values of year 1 sales, annual sales growth, and year 1 sales price. Data tables for sensitivity analysis allow me to vary only one or two inputs, so I can’t use a data table. Does Excel have a tool that allows me to vary more than two inputs in a sensitivity analysis?

Suppose we want to create the following three scenarios related to the net present value (NPV) of a car, using the example in post The Auditing Tool.

For each scenario, we want to look at the firm’s NPV and each year’s aftertax-profit. Our work is in the file NPVAuditScenario.xls. The following figure shows the spreadsheet model (contained in the worksheet named Original Model), and the following figure shows the scenario report (contained in the worksheet Scenario Summary).

 

To begin defining the best-case scenario, I choose Tools, Scenarios and then click the Add button. Next I fill in the Add Scenario dialog box as shown in the following figure.

I enter a name for the scenario (Best) and select C2:C4 as the input cells whose values will define the scenario. After I click OK in the Add Scenario dialog box, I fill in the Scenario Values dialog box with the input values that define the best case, as shown in the following figure.

By clicking Add in the Scenario Values dialog box, I can enter the data for the most-likely and then the worst-case scenarios. After I’ve entered data for all three scenarios, I click OK in the Scenario Values dialog box. In the Scenario Manager dialog box, shown in the following figure, the scenarios I created are listed. When I click Summary in the Scenario Manager dialog box, I can choose the result cells that will be displayed in scenario reports. Figure 17-6 shows how I indicated in the Scenario Summary dialog box that I want the scenario summary report to track each year’s after-tax profit (cells B17:F17) as well as total NPV (cell B19).

Because the result cells come from more than one range, I’ve separated the ranges B17:F17 and B19 with a comma. (I could also have used the Ctrl key to select and enter multiple ranges.) After selecting Scenario Summary (instead of the Pivot Table option), I click OK, and Excel creates the beautiful Scenario Report pictured earlier in the following figure. Notice that Excel includes a column, labeled Current Values, for the values that were originally placed in the spreadsheet. The worst case loses money ($13,345.75), while the best case is quite profitable (a profit of $226,892.67). Because the worst-case price is less than our variable cost, in each year the worst case loses money.

Explore posts in the same categories: MS Excel Tune Up