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 InputsThe 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.
Because the likelihood of occurrence may not be 100% for all line items, an additional check is performed to determine if the line item’s cost is included in the iteration total cost. The assigned iteration cost “CostI” of each line item, based on random occurrence variable y is given by Equations 3 and 4 (for 0 ≤ y ≤ 1):
In figure 2, each bar represents the number of data points randomly generated between the indicated x axis value and the next higher x axis value. For example, 51 data points fall between Cost I of $79,000 and $79,750. Only 2 data points fall between $84,250 and $85,000. It should be noted that Figure 2 does not take on a perfectly triangular shape – this is to be expected. Because the dataset is based on a random number generator, there is an extremely small likelihood of the triangle being perfectly formed. For Figure 2, 1,000 data points were used. For a sample size of only 100 data points, the triangle shape would be must less pronounced. For a sample size of 50,000 data points, the triangle would appear much more precisely formed.Now that the TriD is generated (and visually verified) for line item 1, the same methodology is used to create the TriDs for each line item. Using the F9 key to refresh the calculations will update the random numbers, and will show how variable the triangular shape can be. Note, however, that the peak will always be at or near the Likely Value assigned to each line item, and the trend will decrease to 0 at the Low and High limits.Let’s speculate that our MCS is being created for a vessel procurement project. A new agitated process vessel must be purchased and connected to an existing piping system, but there is a 20% chance that the agitator can be sourced internally from decommissioned vessel at another site. The MCS input is defined as:
The most relevant MCS result is calculated in the cost confidence table (See Table 4). The cost confidence table uses a simple formula to calculate the total budget required to guarantee an X% chance of staying within budget: PERCENTILEINC([dataset],[percentile]). This function references the range of 1,000 budget total data points as the [dataset], along with a desired confidence [percentile]. The formula’s result is the budget value that falls at the desired percentile. For example, at the 90 th percentile (90% of the budgets calculated fall below this budget), the total budget happens to be $102,677. Comparing this to the Base Cost of $97,000, a contingency of $5,677 should be allocated. It is also helpful to calculate each percentile’s contingency as a percentage of the Base Cost. The only subjective decision is to select the cost confidence percentile that is acceptable to management.Important Note: This MCS example was performed with 1,000 iterations for illustrative purposes. To increase statistical robustness of the MCS result, the MSC spreadsheet creator should consider using a very large number of iterations: targeting a minimum of 20,000. 50,000 iterations or more are desirable (but will result in a large filesize of the spreadsheet – not really a concern in this age of inexpensive storage).For the spreadsheet power-user, there is a method of transforming the standard TriD into a Modified TriD (TriMod) – similar to the TriGen function in the Excel @Risk add-on. The TriMod allows the user to specify a small chance of under- of over-running the budget line item. This is useful in cases where there is low confidence in the selected High and Low cost inputs. The TriMod calculations determine the new High and Low values that would be required to generate the desired percentages of area outside the original High and Low values. See Figure 4: The area of right-triangle θ represents the percentage of the TriMod triangle that falls outside of Low value a, and inside of new Low value m. The area of right- triangle Φ represents the percentage of the TriMod triangle that falls outside of High value b, and inside of new High value n.
References:
Kots, Samuel, and Johan Rene van Dorp. Beyond Beta – Other Continuous Families of Distributions with Bounded Support and Applications. Singapore: World Scientific Publishing Co, 2004. Print.
About the Author:
Keith Melchiors is currently a process engineer at Biogen Idec in Research Triangle Park, North Carolina. During the writing of this article, he was senior process engineer with Integrated Process Technologies in Cary, N.C. Keith earned his B.S. ChE from Purdue University in 2002, and has 10 years experience in biopharmaceutical process design and capital project execution.
Related article:
Quantifying Breakeven Point with Contingencies