Scenario Manager for project finance

A good scenario manager allows you to look at a different ‘world’ in the model by changing just one cell. When combined with our 1-D Data Table approach the results of the Scenarios are shown without pressing any buttons!

Introduction to scenario management

You may have heard about a Scenario Manager being useful in modelling. You may even have created one. Either way this tutorial shows you how to build one similar to that in our public training courses. A good scenario manager allows you to look at a different ‘world’ in the model by changing just one cell. When combined with our 1-D Data Table approach the results of the Scenarios are shown without pressing any buttons!

Scenario analysis is the process of analyzing alternative outcomes and is best performed within the same version of the model.

In Project Finance in particular, an analyst might want to know what would happen to the key project ratios or project’s return if some of the key drivers were altered particularly in the downside / worse case scenarios such as:

• Construction is delayed by 6 months
• Production volume –10%
• Product Price path which is different from Base Case
• Operating expenditure +10%
• Base Interest Rate + 100 bp
• Combination of the above

So lets take a look at how can scenario analysis be incorporated into a typical Project Finance model. Our aim in this tutorial is to provide guidance in developing a practical scenario manager that allows us to switch between each of the identified cases. When each ‘case’ is selected we want the differences to the Base Case to flow through the entire model.

We have developed a workbook on how to create a Scenario Manager for project finance models. The screenshot below illustrates a typical Scenario Manager

Screenshot 1: Scenario Manager

Setting up Scenario Manager

Refer to the attached worksheet / Screenshot 1 for cell references.

• Cell B9: Begin with creating the title Assumption
• Col B: Include all the variables we would like to flex, e.g. Production, Price, OpEx, CapEx, Interest Rates
• F7:M7: Create list of scenario numbers from 1 to X running horizontally across the page
• F9:M9: Insert the names of the different scenarios, with number 1 being the Base Case
• F9:M9: You might want to have a number of “Spares” which can be used to later enter more scenarios if you so choose
• D7: Create a “switch” with a Data Validation list using all the numbers you have just created above
• The above “Switch” will allow us to switch / choose between particular scenario
• F8:M8 (optional): To make our “choice” clear we could create a flag to highlight the particular chosen scenario using conditional formatting
• In the heart of the table input the values for the Base Case and each respective scenario
• We could enter a value, percentage or even data validation (e.g. Price Path has a data validation of various prices such as Base Case, Bank Price, etc.)
• D9: Enter formula that will create a title that corresponds to the particular scenario that is chosen

=OFFSET(F9,0,$D$7-1)

Col D: Under the title in D9 we want to bring-in the data from the table using the formula below

=IF(OFFSET(F11,0,$D$7-1)<>"",OFFSET(F11,0,$D$7-1),F11)

The above formula tells us to pick up the Base Case unless the scenario chosen is not the Base Case

We now have created a Scenario Manager that allows us to enter a particular scenario of our choice by clicking cell D7.

Linking in Flex Cells

To allow us to see the variables we would like to flex flowing through the entire model, we have to link “Col D” into various sections of the model. This would be best illustrated through examples.

Example 1: Production Volume

How to link cell D11 in Scenario sheet?

• Cell D11 could be linked to cell F12 in Inputs sheet
• The Inputs sheet in Base Case will look like Screenshot 2
• If for example Scenario 2 is selected, the inputs sheet will look like Screenshot 3.
• Row 13 will flow through various calculations in the model

Screenshot 2: Inputs – Production Volume (Base Case)

Screenshot 3: Inputs –Production Volume (Scenario 2)

Example 2: Product Price

There are 2 cells in Scenario sheet: Cell D14 (Price Path) and Cell D15 (Flex). How to link these cells?

• Cell D14 (Price Path) in Scenario can be linked to cell C16 in Inputs sheet
• Cell C16 in Inputs will then be linked to F18:F21 in Inputs
• Enter formula in Row 22 which tells us to pick-up the selected Price Path
• Cell D15 (Flex) in Scenario can be linked to F23 in Inputs and follow the same logic as in Example 1
• The Inputs sheet in Base Case will look like Screenshot 4
• If for example Scenario 3 is selected, the inputs sheet will look like Screenshot 5.
• Row 24 will flow through various calculations in the model.

Screenshot 4: Inputs – Product Price (Base Case)

Screenshot 5: Inputs – Product Price (Scenario 3)

Scenario Data Table

The only thing that now remains is to get a better idea of how the change influences the output, in this case the, key project ratios. We want a clear, concise summary of what that scenario change actually meant. To do this, we will make use of a 1-D data table such as in Screenshot 6. The data table enables us to compare the results of a number of permutations to our original data together in the one table. How to create data table in Excel is covered in its own tutorial.

Screenshot 6: Scenario Data Table

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...