What comes to mind when you hear the words ‘Monte Carlo’. If you are like me then you are probably having visions of European holidays, the Monaco Grand Prix, sunshine, beaches, and an opulent casino on of the French Riviera…..….. nice but that’s not what I am referring to here – Monte Carlo is also a method that is used to determine probability using random numbers to generate scenarios. Not quite as exciting as the former, I agree, but interesting and useful in the right context. In this post I will show how the Monte Carlo method can add value to improve a project budget estimation. Its actual application is not limited to budgeting, for example, it could be used to predict queuing times for an event based on the number of open entrance gates etc.
Okay let’s get started, say you are asked to provide a 2020 FY budget estimate +/- 20% for a project; you do your research, and come to the following conclusions. You estimate the project will take three years to complete, with material costs for the project costing $7.5m and with the addition of project labour costs bring the total cost to $15m.
In compiling your estimate you have taken into consideration the following three variables that may impact the final cost. Firstly, between now and purchasing the materials for the project, the material costs could change. You think that with some clever negotiation you might be able to reduce those by up to 10%, but you acknowledge there is also a risk of a price increase of 15% due to production costs, etc. Secondly, for the project labour costs you do not see much chance to reduce them, perhaps 1% at the most, conversely given labour shortages rates could increase by up to 15%. Lastly, there is the time to complete the project, your most optimistic view is that could deliver three months earlier than expected, which would save on labour costs. You also think there is a risk that it could run over by up to nine months, due to political uncertainties, which would obviously increase the labour costs.
If we assume these three variables only change in increments of 1% or by working days to complete, then how many possible different project budget scenarios could we generate by changing the variables in the ranges that we specified earlier? By my calculations, it’s 115,362.
Now one may choose to simply calculate the worst scenario and the best scenario to help assess where to set the upper and lower limits for the estimate, but this may yield a variance of greater than +/- 20% and gives no indication of probability. Another option is to use the Monte Carlo method to determine the project cost and suitable budget ranges. The Monte Carlo model that I have built in Excel uses the original project costs and will effectively roll the dice 5,000 times on your estimate, each time making random changes to the three variables within the ranges specified. So one pass of Monte Carlo (it takes seconds to run) effectively calculates 4.3% of the 115,362 possibilities.
Here are the results. Of the 5,000 simulations, the median cost for the project comes to $15.6m. The maximum cost is $17.2m and the minimum is $14.2m. There is a 50% chance that costs will be within +/- 3% of the median cost and an 80% chance that costs are within +/- 5% of the median figure. I ran a second simulation (another 5,000) and the difference to the median was only a few thousand dollars and there was no change in the min and max values.
The accuracy of your results will, of course, depend on the data you use in the model and the number and range of variables, but one should see from this example how it works. If you want to explore Monte Carlo further there is plenty of information on the internet showing you how to build your own models in Excel, or there are people out there selling prebuilt Excel models. If cost is not an issue then you might explore programs such as @Risk or Crystal Ball which have Monte Carlo capabilities.
The 2nd value in the frequency chart is the number of occurrences in the range. E.g. 14,500, 98 = 98 estimates between 14,000 and 14,499.