Compare Budgets With Excel Scenarios

Should you spend extravagantly this Christmas, or go cheap, or spend somewhere in the middle?

You can use Excel Scenarios to store several versions of a budget, and compare the results. Let’s set up a worksheet where we can compare three scenarios for holiday spending.

Set Up the Worksheet

The first step is to set up the worksheet. Some of the cells will be the same in each Excel scenario, and other cells will change.

Note: There’s a limit of 32 changing cells in an Excel Scenario.

• On a worksheet named Budget, add headings, spending categories, and amounts, as shown in the screen shot below.
• Add a Total label, and a sum of the spending amounts.

Create the First Scenario

The first scenario is for Extravagant spending, which will contain the highest amounts.

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)

• In the Scenario Manager, click Add
• Type name for the Scenario. For this example, use High.
• Clear the Changing cells box
• With the cursor in the Changing cells box, click cell B4 on the worksheet. That’s one of the cells that will change in each scenario.
• Hold the Ctrl key, and select cells C6:C11. Do not include any of the category labels, or the total cells.
• (optional) Enter a comment that describes the scenario.
• Click OK to close the Edit Scenario box.

Add the Scenario Values

The Scenario Values dialog box opens, with a box for each changing cell. It automatically displays the current value in each changing cell. You could modify one or more of the existing values, or leave them as is.

We’ll make the Gifts – Family amount a bit higher, and leave the other values untouched.

• For item 5, change the value from 500 to 600.
• Click OK to return to the Scenario Manager. Notice that the value on the worksheet didn’t change – it still shows 500 as the amount for Gifts – Family.

Create Another Scenario

You can add more scenarios by changing the worksheet values, and following the steps that you used to build the first scenario.

Or, you can add an Excel scenario directly into the Scenario Manager.

1. In the Scenario Manager, click Add
2. Type name for the next scenario. For the second scenario, use Mid.
3. Leave the existing cells in Changing cells box
4. (optional) Enter a comment that describes the second scenario.
5. Click OK to close the Add Scenario box.
6. In the Scenario Values dialog box, enter the worksheet heading and values for the second scenario.
7. Click OK to return to the Scenario Manager.
8. Create the third scenario – Low – and enter the lowest amounts for that scenario.
9. Click Close, to return to the worksheet.

Show a Scenario

Once you have created the Excel Scenarios, you can show them.

On the worksheet, the original values for Extravagant scenario are showing.

To change to a different scenario, follow these steps:

1. On the Ribbon, click the Data tab.
2. Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
3. In the list of Scenarios, click on a Scenario name
4. Click the Show button, then click Close.

Show the Excel Scenario Summary

After you create the Excel Scenarios, you can view them in an Excel Scenario Summary. This lets you see the values and totals side-by-side, for an overall comparison.

Note: The Excel Scenario Summary does NOT update automatically if you change the scenario values. You can delete the old summary and create a new one.

To create a Scenario Summary:

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
• In the Scenario Manager, click Summary
• In the Scenario Summary dialog box, for Report type, select Scenario Summary
• Click in the Result cells box, and on the worksheet, click the Total calculation cell (C12).
• Click OK, to close the dialog box.

A Scenario Summary sheet is added to the workbook.

• To show or hide the details, click the + / – buttons at the left side and top of the worksheet

Improve the Scenario Summary

In the Scenario Summary shown above, the changing cells are shown as addresses.

If you name the value cells, the Scenario Summary will show those names, instead of the cell addresses.

You could probably change the colour scheme too, unless you’re a big fan of grey and purple!

P.S. There’s more information on Excel Scenario Summary settings, and programming examples, on my Contextures website.
___________________