There are many reasons you might be running through a list of possible scenarios. If you would like to transform your data into a visualization of a variety of possibilities, you might be interested in the What If Analysis feature in Excel.
Video
Here is an accompanying video for this blog post. I hope this is helpful!
A Fictional Applicable Situation
Think of a business considering a start date, weighing the cost of a delayed open. They have two possible amounts for how much they stand to lose per day if they don’t open: $250 loss per day and $500 loss per day. They also have three possible proposed open/start dates: 6/1/20, 7/1/20, and 8/1/20. The owner would like to know a variety of outcomes, given different possible start dates and different possible income loss per day to delay opening.
Of course, this is a vastly over simplified version of a business accounting model, but will hopefully serve to show you how this feature works.
Document Formulas
The two cells that will prompt changes in our What If Scenario are the Start Date, of which there are three possibilities (June, July, August), and Loss/Day, of which there are two possibilities ($250 and $500).
The total Expenses and Balance in reserve will change as the date and loss figures change.
Creating Scenarios
To create What If scenarios:
1. Go to the Data tab, and select the dropdown for What-If Analysis. Select Scenario Manager.
2. If you have not created any scenarios, this area will be blank. Press Add.
3. Name your scenario something that will help you identify it later. I identified mine with the elements that would be changing in each scenario: the start date and the loss per day.
4. Click into the blank space next to Changing Cells and select a cell that you would like to create a scenario for. In my case, I selected two cells (for date and loss per day) by holding down the Ctrl key and selecting each of them. Press OK.
5. For my first scenario, I wanted to see what would happen if B1 were the date 6/1/2020, and B3 were $500. I entered those figures accordingly, then pressed Add.
6. Create as many scenarios as you would like. I made six possible scenarios and for the different possible loss amounts and start dates.
7. Select a scenario and press show to run the What If Analysis.
There is also an option to generate a summary on a new sheet with all the possible scenarios. Press Summary to generate this.
Accessing Scenarios
You can access scenarios this way:
1. Go to the Data tab, What-If Analysis dropdown, and select Scenario Manager.
Each of these listed scenarios represents a potential start date and a different loss per day amount.
2. To examine a scenario, either double click on one of them, or select one and press Show.
3. The various figures change as you select different possible scenarios: a July start date, with $250 loss per day means the total expenses were $17,000 and remaining balance $33,000.
An August start date with $500 loss per day leaves a $500 remaining balance.
Extras: Macros and Charts
You can customize your What If scenario with charts and macros. Look at this setup below, incorporating a Donut Chart (like this one) to accompany the data, and some text boxes linked to macros to run the What If Scenarios.
Pretty handy for a presentation or for distributing to others without having to send instructions for running the What If scenarios.
Downloads
I made a couple versions of this document for you to examine that incorporate the What If Analysis:
Macro Enabled Version with Chart and Extras
Warning: you may choose to set up formulas a variety of ways, but the formulas in these documents were set up for forecasting, so are not intended to be access to far in the future. Long story short, you may get an error if you are opening these documents too many months from now; this is due to the formulas, not the What If feature.
Thoughts?
What do you think? Has this sparked any ideas? I hope this feature helps you out as you are making plans for different future possibilities.
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser