Projecting future values can be an important part of the financial decision-making process. It's often helpful to project more than one set of values to find out how they affect your results. You can do this easily with what-if scenarios — sets of input values that 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. You can also create a summary or PivotTable® list (PivotTable list: A Microsoft Office Web Component used to create an interactive list that summarizes and analyzes data, such as database records, from various sources. Users can view the list in a Web browser and change its layout.) with the results of all your scenarios.
Try it with a breakeven analysis
To do a breakeven analysis and find out 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
- On the Tools menu in Excel, click Scenarios.
- Click Add.
- In the Scenario name box, type a name for the scenario.
- 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.
- Under Protection, select the options you want, and then click OK.
- 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.
- To create the scenario, click OK.
- 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
- On the Tools menu, click Scenarios.
- Select the scenario you want to display, 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
- On the Tools menu, click Scenarios.
- Click Summary.
- Click Scenario summary or Scenario PivotTable.
- In the Result cells box, enter the references to the cells you want to display in your report — generally cells whose values are changed by the scenarios. In this case, enter B6:B8 to find out how the scenarios affect total costs, total revenue, and profit.
Note You don't have to specify result cells to generate a scenario summary report, but you must specify 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. In this example, even though total revenue is lower if you raise the price, profit is higher. But if you lower the price, total revenue is higher and profit is lower.
Scenario PivotTable report
You can try other scenarios and find out how they affect net profit. For example, what if the variable unit cost rises by 25 cents per unit, but price per unit stays the same? By using scenarios, you can create as many scenarios as you need to help plan your business strategy.