Estimating capital project budgets can be a tricky business. There are many factors to include as inputs to the budget: vendor quotes, capital labor, permits, third- party inspections, expenditure data from previous projects, material escalation… the list goes on and on. Unfortunately, each input can itself be an estimate, and each input can present cost uncertainty risks. Because it is impossible to calculate a precise, infallible budget (owing to the nature of project risk and cost uncertainty), numerical analysis is a good option for arriving at a solution. In the case of predicting capital budgets, the Monte Carlo Simulation (MCS) is the numerical method of choice.
What is a MCS? In terms of project cost risk, the MCS is a tool for calculating the statistical likelihood of exceeding a base budget by a given value. The goal is to assign a statistically-derived dollar value to the various risks associated with the project. The MCS calculates budget values and likelihoods based on two key inputs for each budget line item: 1) the forecasted cost distribution for the expenditure, and 2) the chance of incurring the expenditure.
For example, Item A should cost between $70,000 and $85,000, and there is a 100% of incurring the cost; Item B should cost between $10,000 and $15,000, but there is only an 80% chance of incurring the cost. (Table 1).
Table 1: Example Monte Carlo Inputs
The MCS generates random numbers and applies them to the cost and likelihood-of-occurrence for each line item. The sum of all line items is a resulting budget scenario - also called an iteration. The first iteration may yield a budget of $93,561 because Item B occurs. The second iteration may yield a budget of $74,829 because Item B doesn’t occur.
Table 2: Example Monte Carlo
Using this random “roll of the dice” calculation method, the MCS generates thousands of budget iterations. A simple results chart then tabulates the iterations and calculates the budget contingency required to have X% certainty of staying within budget. MCS overall results typically read like this: for a $90,000 baseline budget, a contingency of $2,950 should be allocated to assure that the project doesn’t go overbudget – assuming management accepts an 80% likelihood of staying within budget. For a 90% likelihood of staying within budget, the MCS may calculate that $5,820 contingency is required.
At this point, many project managers and engineers will say, “OK, fine, I’ll try using a MCS to estimate my budget – where can I download one, and how much does it cost?” There certainly are commercially- available MCS software packages, and some are very affordable (less than $100 for a single license). The problem lies in using a black-box method to estimate a budget. Regardless of how poorly or how well a MCS package performs, it is likely that the end user doesn’t fully understand its internal workings. What probability distributions are used? Is the random number generator truly random? How many iterations are performed? For that matter, how many iterations are required?...
For those willing to remove the MCS from within its black box, the rewards are great. The only requirements for creating a MCS from scratch are: 1) a spreadsheet program, 2) familiarity with basic spreadsheet functions, and 3) following along with the example below. The end product will be a MCS spreadsheet that is user-friendly, documented, fully understood, and similar in function to commercially- available options. Still reading?... let’s begin!
Building Your Own MCS Tool
The most important aspect of understanding MCS mechanics is knowing how to generate meaningful cost probability distributions. There are many probability distributions that could be used to describe forecasted cost: normal distribution (standard bell curve), uniform distribution (see Table 2), log-normal distribution, beta distribution, etc. The problem lies in translating straightforward cost data into statistical distribution lingo. It takes a skilled statistician to, for example, analyze limited data and generate the α and β parameters of a beta distribution. Moreover – how does “α=2, β=5” translate to a $10,000,000 line item? The beta parameters have no clear meaning on the surface.
Fortunately, there exists a straightforward and easy-to-generate distribution that is well suited to MCSs: the triangular distribution (TriD). A TriD has three simple parameters: low limit a, high limit b, and likely value c. The TriD’s Probability Distribution Function (PDF – a function that describes the likelihood of a random variable having any given value) shows the characteristic triangular shape, with the “likely value” having the highest chance of occurring. The chance of occurring drops to 0% at the high and low values. (Figure 1).
The TriD is a good choice for MCSs for multiple reasons. Firstly, the parameters (a, b, c) used to define the distribution are easily superimposed onto a budget line item: Lowest possible cost, highest possible cost, and most likely cost, respectively. This aids in easy comprehension of the cost inputs. Additionally, the triangular shape of the distribution is versatile enough to approximate forecasted cost for a variety of possible line items. For example, “time & material” line items can place the Likely Cost centrally between the Low and High Costs (roughly approximating a bell curve) – this accounts for the risk of either less or more T&M cost. For lump-sum and not-to-exceed budget items, the Likely Cost can be set closer to (or equal to) the High Cost, indicating low likelihood of exceeding the line item cost. If using expired vendor quotations to forecast future projects, the Likely Cost can be set equal to the Low Cost, indicating likelihood of future cost being higher than current benchmark data.