Scenario Analysis for the Project Valuation [Financial Modeling]

Few months ago, we learned how to create a project valuation model in Excel as part of our series on Financial Modeling in Excel.

My Project Evaluation Model had a limitation!! In one of the personal comments that I received, the reader pointed out an important problem!

If you remember, when I had started writing on chandoo’s blog, I had written a series of blog posts on project evaluation. We had created this simple Integrated Financial Model in Excel to the model for analysis.

In any real life project the decision makers are not satisfied with a single point analysis. In any meaningful decision making process, the decision maker wants to see the impact of change in the assumption.

• What if – The assumption that I had made is not true?
• What if – The starting revenues are not USD 250 Mio, but instead are just 200 Mio (Pessimistic Scenario)? What if they become 300 Mio (Optimistic Scenario)?
• What if – To reduce risk, I make an initial investment of USD 300 Mio and the starting revenue is just USD 120 Mio? Would the project make sense?

Managers always demand a lot of data for analysis and analysts always find this a pain!! To remove (Rather reduce) the pain, Excel provides you its own analyst – Data Tables!

What are Data Tables?

Hui and Chandoo have been speaking a lot about the data tables. They are your best friends when it comes to doing the donkey work about changing the variables and noting the scenario results. One post that I love is here.

Just like Hui has pointed out, Data tables provide you a platform to vary two assumption variables (max) and see what your result variables are going to be!

Our reader pointed out the same question! If my revenues were not 300, what would happen to my output variable (IRR or NPV)? I told him exactly what Hui told you guys – Use Data Tables!

How to Use Data Tables in Your Project Evaluation Model?

Using Data tables in your models is pretty simple. I am listing the steps. [More on data tables]

0) Create a completely linked and correct model! (A must!). You can download one from here

1) Create the structure of the what-if analysis. If you want to vary one input variable, put it in row/ column, if you want to vary two, create a 2-D structure

a. One Variable Analysis

b. Two Variable Analysis

Remember to Use constants as the changing scenarios! Never link them as formulas

2) Once we have the structure in place, link the cell on top of a 1-D array and in the intersection of the 2-D array to the variable that you want to observe (In our case NPV value)

3) Once you have linked to the variable, select the complete table and go to data What if Analysis Data Tables

4) Since the data is in column format, in the column input cell, link to the initial constant expectation of revenue. If it is 2-D, link to both the column and row

5) That’s it – All the analysis is done!

6) So if my initial revenue was 200, I would be making a huge loss in the project. Similarly if the initial revenue is 250 but the growth remains curtailed to 5%, the project would still make a –ve NPV.

Beware!!

The basic assumption before starting the scenario analysis was complete linking and correctness of model. In one of the consulting assignments that we worked on, we got a model that was not correctly linked and the client wanted scenario analysis. That is not going to happen!

In the different scenarios make sure that you use constants as your changing inputs. For example, if the revenue is varying from 200 – 220 – 240 – 260, then you should put them as constants and NOT as 200, and then an increment of 20 on the initial value!

Limitations

Since MS Excel has a 2-D structure, so Excel Data Tables can vary 2 inputs at a time. If you want to see effect of more than 2 variables, then Data Tables would not work. You need to use scenario manager or Macros to overcome the limitation.

Each time you update your sheet, all the data tables get updated (Each cell). If your model is a large one, it can considerably slow down your sheet.

How do you generate scenarios in your models?

Do you generate scenarios for your models? I am sure you would be. If you are not, then you should!

If you are already doing it, then how do you do it? Do you use data tables/ macros/ scenario manager? Share your experience!

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.

Next Steps

Here we made the assumption that variables are known. What if the input variables were themselves flowing from some distribution, instead of deterministically known inputs! Then the scenarios could be large in number and you might be required to do an advanced technique like Monte Carlo Simulation! I do hope that you found the posts interesting and look forward to your comments and suggestions!

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.