Use scenarios to forecast outcomes
Overview: Scenarios
The Scenarios Manager helps you examine different sets of variables to see how they affect an outcome. For example, create scenarios by using different monthly investments and interest rates to examine possibilities for your retirement fund. Or, as in this example, examine different operating expenses to see how they would change the monthly income of your business. Excel can take data (Monthly Budget tab) and create a summary report based on your scenarios (Scenario Summary tab). In addition, we've created an annotated summary report that highlights the differences in the scenarios (Formatted Summary tab). To see some formatting possibilities for presenting your monthly budget, click the Formatted Budget tab. To see detailed instructions about creating scenarios, click the Instructions tab. You might want to print the instructions to look at while you're examining the other tabs.
October Budget Revenues Labor Materials Production Travel Revenues total Expenses Salaries Commissions Rent Utilities Insurance Telephone Office Supplies Equipment & Materials Media Production Travel (Expenses) Expenses total Operating Income
$69,500.00 $1,125.00 $6,000.00 $2,100.00 $78,725.00 $50,600.00 $6,800.00 $1,200.00 $500.00 $700.00 $490.00 $400.00 $900.00 $5,000.00 $2,000.00 $68,590.00 $10,135.00
We created scenarios to examine possibilities for
Scenario Summary
Current Values: Changing Cells: Labor Materials Production Travel Result Cells: $69,500.00 $1,125.00 $6,000.00 $2,100.00 Current Budget $69,500.00 $1,125.00 $6,000.00 $2,100.00 Increase Labor $75,300.00 $1,125.00 $6,000.00 $2,100.00 $15,935.00 Increase Materials $69,500.00 $2,125.00 $6,000.00 $2,100.00 $11,135.00
$10,135.00 $10,135.00 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.
You can request a Scenario Summary report based on your
Increase Production Increase Travel $69,500.00 $1,125.00 $7,500.00 $2,100.00 $11,635.00 $69,500.00 $1,125.00 $6,000.00 $3,000.00 $11,035.00
You can request a
Summary report based on your
Scenario Summary
Current Budget Labor Materials Production Travel $69,500.00 $1,125.00 $6,000.00 $2,100.00 Increase Labor $75,300.00 $1,125.00 $6,000.00 $2,100.00
The values changed in each scenario are highlighted
Increase Materials $69,500.00 $2,125.00 $6,000.00 $2,100.00
Increase Production $69,500.00 $1,125.00 $7,500.00 $2,100.00
Increase Travel $69,500.00 $1,125.00 $6,000.00 $3,000.00
Operating Income
Net Change
$10,135.00
$0.00
$15,935.00
$5,800.00
$11,135.00
$1,000.00
$11,635.00
$1,500.00
$11,035.00
$900.00
The redundant Current
Calculation cells showing the net change
October Budget
REVENUES
Labor
The current budget is formatted for a presentation by using options on
$69,500.00 $1,125.00 $6,000.00 $2,100.00 Revenues total $78,725.00
Materials Production Travel
EXPENSES
Salaries Commissions Rent Utilities Insurance Telephone Office Supplies Equipment & Materials Media production Travel (Expenses) Expenses Total
OPERATING INCOME
$50,600.00 $6,800.00 $1,200.00 $500.00 $700.00 $490.00 $400.00 $900.00 $5,000.00 $2,000.00 $68,590.00 $10,135.00
Scenarios Sample
Page 7
Scenario Instructions
Scenario Manager helps you examine different sets of data to see how they affect an outcome, for example, to increase a business's monthly income. The outcome of each calculation is called a
Create scenarios
When creating scenarios, you might want to use current information to create the first one so that you’ll have a basis for comparison. Then create as many additional scenarios as you In our example, we started by creating a current monthly budget scenario and then we created scenarios by increasing each revenue component–labor, materials, production, and travel–to see the effect on the total operating income. 1. On the Monthly Budget worksheet, select cells B3 through B6, which are the cells that you will change to show different scenarios. 2. On the Tools menu, click Scenarios. 3. In the Scenario Manager dialog box, click Add. 4. In the Add Scenario dialog box, name the scenario, for example, Current budget, and click OK. 5. In the Scenarios Values dialog box, click OK. The Scenario Manager dialog box appears, with Current budget displayed in the Scenarios list. Repeat this process to add scenarios. For example, name the second scenario Increase Labor, and in the Scenario Values dialog box, change 69500 to 75300. The boxes are identified by the cell reference: $B$3 corresponds to B3, Labor, on the In our example, we created the following scenarios: • Increase Labor (increased value to 75300) • Increase Materials (increased value to 2125) (continued) (Create scenarios, continued) • Increase Production (increased value to 7500) • Increase Travel (increased value to 3000)
Scenarios Sample
Page 8
Each scenario appears in the Scenario Manager dialog box.
Scenarios are stored in your workbook, so they'll be there the next time you need them. You can look at the different scenarios to see how each one changes the monthly operating budget. • In the Scenario Manager dialog box, in the Scenarios list, click a scenario, such as Increase Labor, and then The worksheet is updated to show how changing the variable affects the outcome.
Compare scenarios
You can easily compare the alternatives by creating a scenario summary, which is shown on the Scenario Summary tab. 1. On the Monthly Budget worksheet, on the Tools menu, click Scenarios. 2. Click Summary.
Scenarios Sample
Page 9
3. Verify that Scenario summary is selected, and then click OK. Excel adds a new worksheet named Scenario Summary, which shows the results for each scenario. You can change both the data and the format of this table. For example, on the Formatted Summary tab, you can see that we’ve eliminated a column of data and added a row of calculation cells to show net change. We’ve highlighted the differences in the scenarios so that they can be Note: You can also create PivotTablereports from scenarios. See the PivotTable report sample for more information about
Format reports
On the Formatted Summary and the Formatted Budget tabs, you see examples of the ways you can use Excel to create presentation-quality reports. We applied formatting options, • For each of the rows, we added different types of orange cell borders. To add a border, first select the row. On the Formatting Palette, under Borders and Shading, select a border type. If you’re going to use several different border styles, as we have, it might be easier to draw them by hand. On the Formatting Palette, under Borders and Shading, click Draw by hand. Select the line color and line style you want from the Border Drawing toolbar, click Draw Border, and then draw the • Under Font on the Formatting Palette, we selected font and font colors so that the text would match the corporate standard typefaces used in other company