This is part three of a three part series on the use of Excel for financial analysis.  In part two, we saw how a data sensitivity table can give the user an understanding of how IRR results can be changed based on a range of inputs for key variables.  What the sensitivity table doesn't do so well is to give the user an understanding of what outcomes are more likely to occur. 

A great tool to address the uncertainty in forecasting is the Monte Carlo Simulation.   A Monte Carlo Simulation application allows the user to run thousands or financial forecast simulations by assigning key inputs a range of values.  In our example, we can determine the likelihood that the IRR of the proposed project will be greater than 15% based on a variety of possible first year revenue estimates, revenue growth assumptions and gross margin estimates.

To get started in a simulation, you need to find a Monte Carlo Simulator add on for Excel.  I am using a product called Lumenaut Monte sold by Lumenaut.  In addition to the product, a little understanding of statistics is useful.  I recommend a book by the name of "How to Measure Anything - Finding the Value of Intangibles in Business" by Douglass Hubbard as a great resource for understanding the statistics behind the model.

For each variable that may have multiple outcomes, it is useful to determine the upper and lower ranges of those variables.  For example, Year 1 revenue is assumed to be $250,000.  The user knows that a certain segment of his existing customer base will also buy the new line of business.  Based on an informal survey, he is confident that he has pre-existing orders for $150,000 of product and feels that with little or no effort he could also find another $50,000.  At the top end of his estimate, he is aware that the sales cycle time is rather long and anything above his initial estimate will likely come from customers that have already been made aware of the product but haven't yet committed.  In addition, the user is comfortable that the upper and lower limits are possible but less likely to be the end result than a result that falls in the middle of that range.  If we modeled this assumption as probability, the graph would look like the following:

 

If we continue the same exercise for upper and lower limits for Revenue Growth Rate and Gross Margin as illustrated below:

 

Once we are finished establishing the upper and lower limits for the various input variables, we identify the output result.  In this case the output result is the IRR number produced based on five years of financial performance.  We then set the model to run thousands of scenarios based on the defined input variables.  In this exercise, I set the model to run 10,000 scenarios.  The results of the model indicated that there is approximately a 21% likelihood that the results will be less than 15%.  A graphic illustration of the 10,000 scenarios is as follows:

 

The blue line represents the number of outcomes at the various IRR returns.  I inserted the red line to provide a visual understanding of the likelihood of the project returning less than 15% IRR.

Based on this scenario, the user may go back to the assumptions and try to refine them.  In our example, the user went back to review the Gross Margin estimates and Growth Rates.  In both cases, the upper and lower limits were tightened up as follows:

 

I reran the model with the new results.  The change to the Growth Rates and Gross Margin limits had a negative impact on the model.  The probability of achieving an IRR over 15% dropped from 79% to 74%.  The follow graph illustrates the point:

 

In addition to the increased risk of an insufficient IRR, the second model shows that the possible upper end of IRR outcomes has also compressed. 

As demonstrated above, a Monte Carlo model can help with the financial decision process.  While the model can influence the decision, it can also provide the user with some insight on what key assumptions need to be further researched.  In the above example, the user may want to spend some time and resources on a more definitive revenue growth assumption.  For example, he may want to use a reputable outside consultant to define the market opportunity and the likely growth prospects.  If the results of such a study confirmed that the growth was at the upper end of the assumptions, the risk of the project may be worth the cost of the research project.  I ran a final model that narrowed the growth assumptions to a range of 4% to 5% and I increased the cost of the project by $10,000 to reflect the resources needed for a more accurate revenue projection.  The results are as follows:

 

Based on the results, the additional costs needed to find a true growth rate are not justified.  The chance of having an IRR below 15% is now almost 40%.  In this case, the user would be better off making the decision with the data in hand.

If you would like a copy of the worksheets I used for this series on financial analysis using Excel, email me at mfoos@b2bcfo.com.

 

View Michael’s Personal Website