Building a spreadsheet model for a business case, product profitability projection, etc. often entails estimating some input factors. The estimates are often a most likely value, an estimated average, or median. These are single-point estimates since you're only using one number (a single data point) for each input. Finally, you get an output number from the model, and decisions are based off that number.
The problem lies in what's not part of the model. What is the chance that the project doesn't meet the hurdle rate (positive net present value), or the product isn't profitable? Single-point estimates don't tell us much about what can go wrong, or what can go better than expected.
Consider the model below. We're trying to decide if we should invest $10 million in a new product. We can estimate the unit cost relatively well, but what about forecasting units sold? We could estimate 100 units in the first year, and sales increasing by 3 each successive year.
For a discount rate of 10%, we get a positive net present value (NPV) of $567,488. Now, what is the chance that we actually achieve a positive NPV? We don't really know at this point.
Going Beyond Single Point Estimates
Of course you may want to know what inputs have the greatest impact on model output. You could do a sensitivity analysis where each input is changed +/- a certain percentage and recording the change in output.
Another alternative is to plug in the worst case scenario inputs and check the output. This gives an idea of what could go wrong, but at what probability? What is the probability that all inputs are at their worst case level at the same time? Likely, there are no good answers to these questions.
Going Further: Random Variables
Instead of single point estimates, we could turn to simulation and incorporate randomness into the model. Each estimate is replaced with a random variable that is sampled repeatedly to generate thousands of outcomes.
How is randomness introduced into the model? We assume that each random variable follows a probability distribution. Then a random number between 0 and 1 is generated and we use this random number to get a sample value from the probability distribution.
In the model earlier, the sales forecast was questionable. We could replace unit sales for each year with a random variable. We could also use a triangular probability distribution that requires a three point estimate to define how the random variable is sampled.
Triangular distribution parameters are:
- Worst case estimate.
- Most likely case estimate.
- Best case estimate.
We'll use the single point estimates from earlier as the most likely estimates. For worst case, we'll assume 20 less unit sales than most likely. Best case is 10 more unit sales than most likely.
You can see that cells C5:G5 have been replaced with the Simulation Master function for the triangular distribution. Each time the worksheet is calculated, these cell functions generate a random number, and sample a unit sales number based on the probability distribution. More samples will be generated around the most likely values since that has the highest probability of occurrence.
Now we can simulate the model. Simulation involves sampling the random variables, calculating the spreadsheet, and recording the value of NPV. We'll do this for 25,000 iterations and the results are shown below.
To find the probability that NPV ≥ 0, the number of simulation iterations that resulted in negative NPV are counted and then divided by 25,000 iterations. From the results below, we see that we have a 79.7% chance of positive NPV.
Now we have an idea that NPV will be positive for most of the possible outcomes. Depending on your risk tolerance, a 79% chance of positive NPV may be acceptable, or it might give you pause. At least you have this information on which to decide.
If One Estimate is Not Good, How Can Three be Better?
You may be thinking, if one estimate is not good, how can three point estimates be any better? First, simulation will not cure the garbage in, garbage out problem of all models. Three point estimate values aren't necessarily more accurate than a single point estimate. What they do together is allow for a more realistic estimate of reality.
Single point estimate models are convenient to develop a single measure, such as NPV that can be used for decision making. The downside is risks are not exposed including the probabilities involved. Sensitivity and scenario analysis improve the quality of a single point model, but simulation of a stochastic model allows us to go even further and estimate probability of success or failure.
Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.