Are you a Purchasing Psychic, or a Costings Clairvoyant?

Projecting future values can be an important part of your financial decision-making process. It’s often helpful to project more than one set of values to see how they affect your results. You can do this easily with "what-if" scenarios — sets of input values that Microsoft Office Excel 2003 can substitute automatically in your worksheet.

"What-if" scenarios can help you answer questions such as:

• How would changing my variable cost per unit affect my net profit?

• What would my estimated capital gain tax liability be if I sold certain investments?

• How would my loan payments change if I found a lower interest rate?

You can create and save different scenarios on a worksheet and then switch between scenarios to view different results.

Try it with a breakeven analysis

To do a breakeven analysis and see how changing the variable cost, fixed cost, unit sales, or price will affect your profit, you can define different values for these variables, and then switch between scenarios to compare the results.

The following illustration shows the current breakeven analysis for this example.

Breakeven analysis: Current scenario


Create scenarios
1. On the Tools menu in Excel, click Scenarios.
2. Click Add.
3. In the Scenario name box, type a name for the scenario.
4. In the Changing cells box, enter the references for the cells that you want to change, or hold down CTRL and click each cell.
In this example, to try a scenario of a lower price and projected higher unit sales, hold down CTRL, and then click cells B2 and B3.
Note To preserve the original values for the changing cells, create a scenario that uses the original cell values before you create scenarios that change the values.
5. Under Protection, select the options you want.
6. Click OK.
7. In the Scenario Values dialog box, type the values you want for the changing cells. For example, for $B$2 (price per unit), type 6.25. For $B$3 (unit sales), type 110000.
8. To create the scenario, click OK.
9. If you want to create more scenarios, click Add again, and then repeat the procedure. When you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box.

Display a scenario
1. On the Tools menu, click Scenarios.
2. Select the scenario you want to see, and then click Show.
The values for the scenario are automatically substituted in your worksheet.

Breakeven analysis: Higher price scenario


Create a scenario summary report or PivotTable report
1. On the Tools menu, click Scenarios.
2. Click Summary.
3. Click Scenario summary or Scenario PivotTable.
4. In the Result cells box, enter the references to the cells that contain values that are changed by the scenarios. In this case, enter $B$8 to see how the scenarios affect profit. Separate multiple references with commas.

Note You don't have to specify result cells to generate a scenario summary report, but you must enter them for a scenario PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.).
The scenario summary report displays the variables for each scenario and the result cells.

Scenario summary report


The PivotTable report displays the result cells for each scenario. You can use the drop-down arrows to choose the results you want to be displayed.

PivotTable report