What If Analysis Using Excel Scenarios

What if my side eBay bike business sold an extra 10%, what if I increased my price by $15 per bike, what if my costs increased by $5000 per year?

What-if analysis is commonly done in Excel by saving different versions of the same workbook, or having a different sheet for each scenario which then become difficult to compare.

But did you know you could do it all in the one workbook, on the one sheet and select from a list of scenarios and see your data automatically update, then create a summary comparison of each scenario?

Well, it’s true, unlike my eBay side business which is complete fabrication solely for the purpose of this tutorial 🙂

Excel Scenario Example

Let’s say for the time being that my eBay bike story is true and I’m in the process of preparing my 5 year forecast.

Here are my assumptions that I’ve used in various calculations in the above forecast.

But I’m wondering what the effect would be if my growth was higher, or my price per bike was higher, or my margin was higher.

I can use the Scenario Manager to save different sets of variables (in my case my assumptions will be the variables), and then toggle between them to see how they affect my budget.

I’ve set up 4 different scenarios using the Scenario Manager (all based on how the different levels of profit will affect my lifestyle…keep reading and you’ll see what I mean).

Download the Workbook

The workbook includes the examples used in this post and some notes so you can refer to it later.

How to Use Excel Scenarios

The Excel Scenario Manager is on the Data tab of the ribbon under What-if Analysis.

When you click the Scenario Manager the dialog box will open:

You can see I already have 4 scenarios (Still Hungry, Occasional Takeaway, Fine Dining and Happy Days). To add a new Scenario press the ‘Add’ button.

The Add Scenario dialog box opens.

1. Give your Scenario a name.
2. Select the cells that you want to alter. To select non-adjacent cells press CTRL+left mouse button.
3. Add a comment if you want and press OK

Now you can enter your Scenario values.

Note: if you want to keep your original scenario, set this up first by entering your values here. Then set up additional scenarios.

Once you’ve set up more than one scenario you can toggle between them by going back into the Scenario Manager and pressing the ‘Show’ button.

You can also create a summary of all scenarios by pressing the Summary button in the Scenario Manager.

You can then choose from a Scenario Summary or a PivotTable report.

The plain vanilla version of the Scenario summary looks like this:

And the Scenario PivotTable report looks like this:

Neither is great but with a little chocolate topping (read ‘improvement’) they can be better:

So now you don’t have to save a separate file or create a new sheet for each scenario, your workbooks are likely to be easier to manage and update, and less prone to errors.

Happy Days 🙂