Prototipo no Funcional

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 I can use to vary more than two inputs in a sensitivity analysis?

Suppose you want to create the following three scenarios related to the net present value (NPV) of a car, using the example in Chapter 16, “The Auditing Tool.”

Year 1 sales Annual sales growth Year 1 sales price

Best case $20,000 20% $10.00

Most likely case $10,000 10% $7.50

Worst case $5,000 2% $5.00

For each scenario, you want to look at the firm’s NPV and each year’s after-tax profit. The work is in the file NPVauditscenario.xlsx. Figure 19-1 shows the worksheet model (contained in the Original Model worksheet), and Figure 19-2 shows the scenario report (contained in the Scenario Summary worksheet).

FIGURE 19-1 The data on which the scenarios are based.

FIGURE 19-2 The scenario summary report.

To begin defining the best-case scenario, display the Data tab, and then click Scenario Manager on the What-If Analysis menu in the Data Tools group. Then click the Add button, and fill in the Add Scenario dialog box as shown in Figure 19-3.

Chapter 19 Using the Scenario Manager for Sensitivity Analysis 145

FIGURE 19-3 Data inputs for the best-case scenario.

Enter a name for the scenario (Best), and select C2:C4 as the input cells containing the values that define the scenario. After you click OK in the Add Scenario dialog box, fill in the Scenario Values dialog box with the input values that define the best case, as shown in Figure 19-4.

FIGURE 19-4 Defining the input values for the best-case scenario.

By clicking Add in the Scenario Values dialog box, you can enter the data for the most-likely and worst-case scenarios. After entering data for all three scenarios, click OK in the Scenario Values dialog box. The Scenario Manager dialog box, shown in Figure 19-5, lists the scenarios I created. When you click Summary in the Scenario Manager dialog box, you can choose the result cells that will be displayed in scenario reports. Figure 19-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).

FIGURE 19-5 The Scenario Manager dialog box displays each scenario you define.

FIGURE 19-6 Use the Scenario Summary dialog box to select the result cells for the summary report.

Because the result cells come from more than one range, I 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 PivotTable option), click OK, and Excel cre-ates the beautiful Scenario Summary report pictured earlier in Figure 19-2. Notice that Excel includes a column, labeled Current Values, for the values that were originally placed in the worksheet. The worst case loses money (a loss of $13,345.75), whereas the best case is quite profitable (a profit of $226,892.67). Because the worst-case price is less than our variable cost, the worst case loses money in each year.

Remarks

■ The Scenario PivotTable Report option on the Scenario Summary dialog box presents the scenario results in a PivotTable format.

■ Suppose you select a scenario in the Scenario Manager dialog box and then click the Show button. The input cells’ values for the selected scenario then appear in the work-sheet, and Excel recalculates all formulas. This tool is great for presenting a “slide show”

of your scenarios.

Chapter 19 Using the Scenario Manager for Sensitivity Analysis 147

■ It’s hard to create a lot of scenarios with the Scenario Manager because you need to input each individual scenario’s values. Monte Carlo simulation (see Chapter 69,

“Introduction to Monte Carlo Simulation”) makes it easy to create many scenarios.

Using the Monte Carlo simulation method, you can find information such as the probability that the NPV of a project’s cash flows is nonnegative—an important mea-sure because it is the probability that the project adds value to the company.

■ Clicking the minus (–) sign in row 5 of the Scenario Summary report hides the Assumption cells and show only results. Clicking the plus (+) sign restores the full report.

■ Suppose you send a file to several people, and each adds his or her own scenarios.

After each person returns the file containing the scenarios to you, you can merge all the scenarios into one workbook by opening each person’s version of the workbook, clicking the Merge button in the Scenario Manager dialog box in the original work-book, and then selecting the workbooks containing the scenarios you want to merge.

Excel merges the selected scenarios in the original workbook.

Problems

1. Delete the best-case scenario and run another scenario report.

2. Add a scenario named High Price, in which Year 1 price equals $15 and the other two inputs remain at their most-likely values.

3. For the lemonade stand example in Chapter 17, “Sensitivity Analysis with Data Tables,”

use the Scenario Manager to display a report summarizing profit for the following scenarios:

Scenario Price Unit cost Fixed cost

High cost/high price $5.00 $1.00 $65,000.00

Medium cost/medium price $4.00 $0.75 $45,000.00

Low cost/low price $2.50 $0.40 $25,000.00

4. For the mortgage payment example in Chapter 17, use the Scenario Manager to create a report tabulating monthly payments for the following scenarios:

Scenario Amount borrowed Annual rate Number of monthly payments

Lowest payment $300,000 4% 360

Most-likely payment $400,000 6% 240

Highest payment $550,000 8% 180

149

Chapter 20

The COUNTIF, COUNTIFS, COUNT,