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.
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!
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.
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 firstname.lastname@example.org
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.
Leave a Reply
|« Video Tutorial on Interactive Dashboard using Hyperlinks||Financial Modeling School is Open, Please Join Today! »|
10 Responses to “Scenario Analysis for the Project Valuation [Financial Modeling]”
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.
@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.
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.
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.
Its obvious but I forgot to mention the filename is 'USE_of_Excel_as_database20110622.xls'
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: 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.
[...] Scenario Analysis for a Project [...]
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.
Could not get the values you got by using Data Table. Could you please specify how you got all the different scenario values?
I got it. But why we use the value cell with formula?