Scenario Analysis for the Project Valuation [Financial Modeling]

Posted on July 26th, 2011 in Financial Modeling - 10 comments

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!

clip_image002

clip_image004

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

clip_image006

b. Two Variable Analysis

clip_image008

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)

clip_image010

clip_image012

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

clip_image014

clip_image016

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

clip_image018

 

clip_image020

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

clip_image022

clip_image024

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.
Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine

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

Written by paramdeep@gmail.com
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Scenario Analysis for the Project Valuation [Financial Modeling]”

  1. Ashish says:

    You could also use Spin Buttons from the Developer ribbon to create scenarios efficiently. Spin Buttons, when linked to cells, allow the target cell to be changed by a pre-decided amount (increased or decreased). Like you said, if the model is formula based and linked properly, this works as well.

  2. paramdeep@gmail.com says:

    @Ashish: That is right. But if you want to see all the scenarios at the same point of time, then the spin buttons might not be helpful.

  3. Pankaj says:

    I have used database functions to do scenario management. For my purpose I wanted average, count and standard deviation. Here is the link to the file that can dynamically change the scenario considering upto 7 dimensions. No macros.

    http://pankaj.dishapankaj.com/share

    Explore the file. Since I work in obesity space, the file is built to display weight loss for individuals/groups. the data in this file is simulated data based on averages and standard deviations for the groups. I am hoping to write a post regarding it soon. I am sure this can be applied to financial models as well or for that matter many other situations.

  4. Pankaj says:

    Its obvious but I forgot to mention the filename is 'USE_of_Excel_as_database20110622.xls'

  5. Peter Thomsen says:

    Long time ago I was teaching a course on financial modelling in Excel.

    As far as I remember the students had an assignment what-if with more than two variables. I think it was handled by using a list with the scenarioes and reference the scenarioes with the lookup function.
    That way you only need one value in order to change multiple values in your analysis.

    Peter Thomsen

  6. paramdeep@gmail.com says:

    @Peter: Yes, you can do that. But it would also show the impact of only 2 input variables on the output variable at one point of time. If I want to change more than 2 variables (simultaneously) and see the impact, the excel data tables cannot be used.

  7. Emma Tameside says:

    Multi-point modelling analysis is the bane of my life! I've never really got my head around the advanced functions of Excel to model these scenarios for me, so I always end up with multiple worksheets that all need updating when the fundamentals change! This article should help me immensely if I can find the time to really learn it all.
     
    This is what we are taught during our CFA training, but that kind of ends at using pivot tables 🙂
     
    Just one question, are there are readable formats for 3 or more data variables? I can only envision 2 variables vs. 1 constant at the momemtn in my mind.

  8. Alex says:

    Could not get the values you got by using Data Table. Could you please specify how you got all the different scenario values?

  9. Alex says:

    I got it. But why we use the value cell with formula?

Leave a Reply