SmartDrill Home

   
   Search the SmartDrill site

 
 

Risk Analysis Using Monte Carlo Simulation




Here we present a simple hypothetical budgeting problem for a business start-up to demonstrate the key elements of Monte Carlo simulation. This table shows the setup:

Cost/Budget Item

Input Values

Min.
 Allowed

10% Likely

Expected Values

90% Likely

Max.
Allowed

  Administrative

$70,000        

$65,000      

$67,709    

$71,667         

$76,097    

$80,000      

  Cost of Sales

$125,000        

$120,000      

$122,709    

$126,667         

$131,097    

$135,000      

  Personnel

$335,000        

$325,000      

$329,950    

$336,667         

$343,826    

$350,000      

  Professional Fees

$15,000        

$12,000      

$13,533    

$15,667         

$17,984    

$20,000      

  Sales & Marketing

$50,000        

$45,000      

$47,216    

$50,000         

$52,744    

$55,000      

  Technology

$20,000        

$17,000      

$18,533    

$20,667         

$22,984    

$25,000      



The random-variable probability distribution we have chosen for each of the input budget elements is trangular:

Small triangular distribution

This seems like a reasonable distribution to choose because it's peak represents the initial input values for the budget element variables, that we feel initially are the most likely values; and then it tails off in a linear fashion as we move left toward the "Minimum Allowed Values" and right toward the "Maximum Allowed Values" we initially chose to bracket the range of uncertainty around the initial input values. This triangular distribution results in the "Expected Values," the "Values 10% Likely" and the "Values 90% Likely" shown in the table above.

Selecting probability distributions for the input variables in a risk analysis can be tricky. If valid, reliable historical data are available, these should be used as a guide (but it might require doing a bit of research). Otherwise, a combination of common sense and intuition usually works well. Careful inspection of the table above reveals that for most of the input variables, we chose maximum allowed values that are farther above the initial input values than the minimum allowed values are below the initial input values. This is a somewhat conservative approach that helps us to avoid or minimize bad surprises later on due to under-budgeting.

This approach is shown graphically below for each individual budget input variable:

 

Administrative Budget 

 

Cost of Sales Budget

 

Personnel Budget

 

Professional Fees Budget

 

Sales and Marketing Budget 

 

Technology Budget

 

Based on the above distributions, we can now generate a Sensitivity Report that shows us the relative influence of each input variable on the Total Budget output variable:

Total Budget Sensitivity Report

In the graph above, the horizontal line represents the Total Budget number expressed as the sum of the initial budget input variables. A vertical line represents the output range between an input variable's 10% likely and 90% likely values. Inputs with long vertical lines have the most effect on the output variable (Total Budget) Below is a tabular Total Budget sensitivity report:

Total Budget Sensitivity Table

Again we can see that the Personnel Budget has by far the largest influence on the Total Budget (48%), and the Technology Budget has the smallest influence (5%).

Now we run 1,000 Monte Carlo simulation trials and examine the results:

Minimum result:  $600,651                    $615,000 Initial Total Budget value    
Maximum result: $649,455                    20.6% of results are equal or lower    
Expected value: $621,314               79.4% of results are equal or greater    
Std Deviation:     $7,484          

Here is a table showing the percent of simulation values falling below various Total Budget estimates:

  5% <= $609,295
  10% <= $612,067
  15% <= $613,616
  20% <= $614,847
  25% <= $615,996
  30% <= $617,038
  35% <= $618,179
  40% <= $619,176
  45% <= $620,050
  50% <= $621,175 (Simulated expected value = $621,314)
  55% <= $621,942
  60% <= $622,992
  65% <= $623,871
  70% <= $624,964
  75% <= $626,117
  80% <= $627,575
  85% <= $629,159
  90% <= $631,155
  95% <= $634,251
  100% <= $649,455

If Total Budget is $615,000:

  • 20.6% of results are equal or lower
  • 79.4% of results are equal or greater

Here is a histogram of Total Budget simulated values:

Total Budget Histogram 

Here is the cumulative Total Budget frequency chart:

Total Budget Cumulative Frequency Chart

And here is the tabular version of these distributions:

Monte Carlo simulation frequencies



Here is a summary of the simulations in terms of the amount of change occurring from start to finish for estimated Total Budget values and the standard deviation of the estimates:

Monte Carlo simulation budget values


We usually stop simulating when the % change drops below 1.0; otherwise we continue. This simulation exercise stopped after 1,000 runs. In our case we could have stopped after 500 runs, but by going longer we were able to reduce the standard deviation slightly.

And finally, here is a calculation of the 95% statistical confidence interval around our simulated expected mean of $321,314:

Expected Total Budget Confidence Interval

Thus, based on our sample of 1,000 runs, if we were to repeat the simulation many more times using a different random number seed for each simulation, then we would expect 95% of those samples to result in an Expected Total Budget value between $620,849.99 and $621,778.77.


Conclusion

Based on the results of the simulation, we have learned a lot, and we can be more confident in our budget planning. First of all, we learned that we would have only about a 20.6% chance of achieving our initially specified Total Budget of $615,000. If we want to have at least a 50/50 chance of achieving our budget, we should set the budget level at about $621,314. If we want at least a 75% chance of achieving our budget, we should set the budget level at about $626,117. And a budget of about $631,155 would give us an even more comfortable 90% chance of success.

Back to Top

Back to the Risk Analysis page  

· Marketing Analytics 
· Market Research
· Operations Research
· Risk/Decision Analysis
· Project Management

         

SSL certification seal from Comodo