12. Building Multiple Scenarios Part 1
Multiple scenarios should always be considered when evaluating high-risk projects. In the next two lessons, we'll add optimistic and pessimistic scenarios to our output panel.
Why have multiple scenarios
- Forward-looking models should always have multiple scenarios
- They provide a range of potential outcomes rather than a single value
- A range of outcomes gives the client a better understanding of project risk
- Scenarios can vary as many inputs as you want
Option 1 - scenario manager
- Can only be edited and changed within the manager which is time-consuming
- Can't view the inputs in each scenario together when making edits
- Create useful summaries that show outputs for all three scenarios simultaneously
Option 2 - manually create scenarios in control panel
- Scenarios can be edited and selected in the control panel
- All scenario inputs can be seen when making edits
- Won't have a summary function without VBA macros
ALT + A , W, S: Open scenario manager
CTRL + Spacebar: Select full column
ALT + I , C: Insert column
F4: Repeat previous action
ALT + A , V, V: Open Data Validation
After updating our model with taxes, we now have a revised profit multiple of 6.6 times our original investment. Our updated tornado chart shows that this number is very sensitive to market price and variable cost.
If either of these variable assumptions are out by 10% percent or greater, our target return of 5 times our original investment will not be met. Before sharing these insights with the senior management team we decide to speak to SupraChem's Head of Business Development.
While he is intrigued by the tornado chart insights, he cautions against presenting to management until we've also shown some optimistic and pessimistic scenarios. They hate single point analysis he says and we'll probably focus on the pessimistic case first.
He passes us the following numbers for both pessimistic and optimistic scenarios and asks us to incorporate these into our model and then include them in our final presentation to management. Returning to our model, it's not immediately clear how we can incorporate these two additional sets of assumptions into our control panel.
Under WHAT-IF analysis, Excel allows us to create scenarios in the Scenario Manager. We simply click Add, give the scenario a name.
In this case, Base and then identify the cells that we'd like to change. I'll select the reference plant cost, success probability, commercial plant cost, market price and the unit variable cost. I'll then press OK. And Excel then asks us to enter values for each of these variables that would be associated with the Base scenario. While this is one way of creating scenarios in Excel, it can get quite tedious because we need to return to the scenario manager every time we want to change a variable or change the live scenario.
It can also be hard to remember which scenario or numbers are currently showing. The ideal solution would have three new columns each holding a set of variable assumptions under Live scenario which would be selected from the dropdown. So let's try to create this.
I'll start by inserting three columns to the left of my current assumptions. So I'll select the column, and then Alt + I, C and then F4 to repeat this action two more times. Off camera, I'll add in values for the optimistic, pessimistic and base case scenarios. Now let's create our drop-down list. So I'll write, select scenario and in So I'll write, select scenario and in the adjacent cell, I use data validation to create a list and the list will allow pessimistic, base or optimistic.
So in the source, we simply select, pessimistic, base and optimistic. And then we press OK and test our dropdown which is now working correctly. Now we just need to link our live cells to the selected scenario. To do this, I'll use a combination of the index and match functions in the next lesson.