12. Building Multiple Scenarios Part 1

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

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.

Lesson Notes

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

Keyboard shortcuts

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

Transcript

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% or greater, our target return of five times our original investment will not be met. Before sharing these insights with the senior management team, we decide to speak to Supercam's head of business development. While he's 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 will 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 will 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 allowed scenario. It can also be hard to remember which scenario our numbers are currently showing.

The ideal solution would have three new columns, each holding a set of variable assumptions and a 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 + IC 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 dropdown list. So I'll write "Select Scenario", and in the adjacent cell, I'll 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.