5. Simulation with @RISK

There are various simulation softwares one can utilize.We use @RISK.That software can handle fairly complicated situations, and can be uploaded in Excel.The simulation essentially conducts a scenario analysis which is not restricted to 3 scenarios but instead analyzes thousands of possible outcomes.

Contrary to the preceding approach, simulation does not focus on individual confidence intervals but rather on the distribution of the relevant variables simultaneously.  From those distributions, we select values in a random way and include them in our computation of project value.  By repeating this procedure a large number of times, we get an interval of firm value. 

The first step is to define the distribution of our potential value drivers. For simplicity, we make the following assumptions:


Variables

Distributional assumption

Cost of sales

Uniformly distributed between 42% and 46% of sales

Equity beta

Normally distributed with mean 1.2 and standard deviation 0.1%

Credit spread

Uniformly distributed between 2% and 4%

Long-term EBIT margin

Uniformly distributed between 21% and 23%


To estimate the implied confidence interval of firm value, we proceed as follows. We randomly draw a number from each of the distributions above and plug it in the valuation model. Then we record the corresponding firm value. This procedure is repeated 10'000 times, so that, at the end of the analysis, we have 10'000 estimates of firm value.


After 10'000 iterations, we obtain the following NPV-distribution:

 


The histogram shows that the 95%-confidence interval of firm value lies between 50'826 and 64'728, as indicated by the two vertical lines.

One can easily adjust the output to determine the probability with which the value exceeds (or falls short of) a certain threshold. For example, we might want to know how likely it is that the value of the firm is larger than 55'000. This information could be relevant in an M&A situation, where the seller asks for a price of 55'000. According to our simulation, that probability is 69%:




One should always remember, however, that the more detailed the simulation, the more complicated and difficult to interpret it becomes.  The secret of a useful simulation is finding a delicate balance between formal correctness and simplicity.  Striking that balance is more art and experience than science.

We can use the simulation results also to identify the relevant value drivers, i.e., the variables that have the largest impact on firm value.These are the variables one should estimate more precisely, if possible.Moreover, these are the variables that management should monitor closely when implementing the project.

The following figure shows how firm is affected by a one standard deviation (!) change in one particular variable.For example, if we overestimate average equity beta by one standard deviation, the actual firm value will 3'615 lower than in the base case.The ranking of the sensitivities reported in the graph corresponds to the ranking we obtained with the sensitivity analysis.