Sign in or start a free trial to avail of this feature.
14. Analysing Our Scenarios
To analyse the differences between our scenarios, we can again use a Tornado chart. The chart will identify which variables contribute most to the differences between the optimstic/pessimistic scenarios and the base case.
Analysing our scenarios
- Scenarios with a large range of values correspond to high risk / high return investments
- Less risky investments will have a smaller range of values
- To find what's causing the differences between scenarios, we can use a tornado chart
Tornado charts for scenarios
1 Set selected scenario to Base
2 For each variable that is changed in a scenario, create a data table
3 The inputs to the table will be the optimistic/pesssimistic values for that variable
4 Subtract base case output value from the data table outputs
5 Plot these values as before
SHIFT + →: Select next cell
ALT + A , W, T: Create data table
CTRL + →: Move to the last cell in the data region
ALT + N , B: Create Bar chart
F4: Anchor cells
With our three scenarios now created, it's time to analyze our results.
On a PowerPoint slide, I have created a bar chart that shows the range of returns for the pessimistic, base and optimistic cases.
As you can see from the chart, there's a huge range of values between the pessimistic and optimistic cases.
In the pessimistic scenario, we will lose money on a risk-weighted basis and in the optimistic scenario, we'll make over 15 times our money.
For some investors such as venture capitalists, only projects with high risk and high reward interest them.
However, for more conservative investors and companies, the spread across optimistic and pessimistic scenarios would need to be a lot smaller.
Whenever you're building forward-looking models, a pessimistic and optimistic set of assumptions should be included.
In addition, both scenarios should have at least a 20% percent probability of occurring.
If you include an optimistic scenario with only a very small chance of occurring, you might give the client an overly positive view of the project.
By including a range of outcomes with three different scenarios, you provide a much better view of the project risk, moving your answer from a single point to a range.
However, before you present this chart to the client, it's important to understand what factors create the differences between the scenarios.
To find the primary causes of these differences, we can again use the tornado chart.
Back in my Excel sheet, I have created a similar table to the first tornado chart except this time I've replaced the plus or minus 10% percent values with optimistic and pessimistic.
To create a tornado chart based on scenarios, we simply set the Live scenario to Base case and then run each parameter separately for their pessimistic and optimistic input values.
Let's start with the market price.
So in the optimistic case, I'll find the market price value, which is going to be F15.
And the pessimistic value will be D15.
In this cell, I'll select my desired output which is return on investment.
I'll then select my cells and Alt + A, W, T to create a table.
My row input cell would be the live market price and I'll press OK.
And this gives me two values for the optimistic and pessimistic market price inputs.
Let's repeat this for variable cost.
So again, my input will be the optimistic variable cost, which will be cell F19.
And then my pessimistic value will be D19.
Again, I'll set my return on investment as the desired output and then I'll select the cells and run a data table to get my two desired outputs.
The row input will be the live unit variable cost and then I'll press OK.
And again, this gives me two desired output values.
Off camera, I'll repeat this for the three remaining variables and I'll add in my additional variable for Operating Life which wasn't included in the original tornado chart.
To create the data for our chart, we simply subtract the base case scenario from our 12 results.
And to save some time, I'll do this off camera.
We're now ready to create our chart.
So I'll select the data and Alt + N, B for a Bar chart.
As in the previous tornado lesson, I'll do some formatting to the chart off camera and put it in position next to the data.
From the chart, we see that four different parameters can individually have a big impact on the base case rate of return potentially pushing it close to or below five times target rate.
If our tornado chart had only one or two parameters with this impact, it might be more palatable for our client.
But given the multiple sources of significant risk, it's unlikely that a conservative company like SupraChem will take on this project.
Unfortunately, this chart only works when the selected scenario is the base case.
So I'm going to add a title to the chart that will instruct the user to switch to the base case if she wants to use the chart.
To change a chart title based on an input value, we need to link it to a cell.
So I'll select the cell above the chart title and here I'll write an IF statement.
For the IF statement, the logical test will be, if my selected scenario which is cell E6 is equal to Base.
And if it is, I'll insert a title, which will be tornado chart for three scenarios.
And otherwise, I'll return a message that says, select base scenario to use this chart.
I'll now link the chart title to this cell.
Currently this scenario is set at Base so the title appears correctly.
If I now go back up to cell E6, and switch to optimistic, the message on the chart should change, which it does and tells us to select the base case scenario to use this chart which is exactly what we want.