Simulation Tools for Creating More Robust Forecasting Models
By: Joseph G. Herlihy, Seth E. Webber, and Sean R. Ferguson, CPA, CCIFP
Construction financial managers (CFMs) know very well the balance between winning a bid and not leaving money on the table. Few are magicians in their ability to do this. However, the majority of financial managers need tools to improve the quality of project estimates and subsequent decisions in order to optimize profit.
While there are several powerful estimating tools available on the market, many still rely heavily on internally developed spreadsheet models to handle the bulk of the scenarios for estimating work. Spreadsheets are everywhere – the estimation process, project tracking, forecasting, budgeting, and preparing information for financial statements. But a large question remains – has the quality of the information produced really improved all that much? Unfortunately, spreadsheets have significant limitations and can contribute to analytical errors and oversights, leading to costly errors in judgment.
However, spreadsheets are wonderful tools for understanding what the key drivers are for any business. Most companies have developed their own spreadsheets to capture the factors they worry about – materials costs, labor rates, payment schedules, man-hours required, some hurdle rate or discount rate. The issue is that these spreadsheets still result in a single number rather than a range or more dimensional result. How do we capture the impact of weather delays, cost over-runs, fluctuations in materials costs, availability of specialized trades, and other factors in that single number? Some CFMs increase the hurdle rate or discount rate, others just reduce the final answer by some fudge factor or set up a discretionary reserve.
There are better ways
One relatively inexpensive approach can result in a better discussion on the issues facing your business – leading to improved profitability, better reports, fewer surprises, a better understanding of the financial impact of various delays, and most importantly, better use of your time. By understanding the uncertainty that is present in the key variables used in projections, a CFM can present a better picture of the risk and reward inherent in any project or the financial projections for your entire company.
There are Excel add-on software packages that take advantage of a construction financial manager’s current models that can greatly improve the model’s effectiveness and sophistication, allowing contractors to make better decisions about whether and how to bid a job. The software uses an analytical method, Monte Carlo Simulation (MCS), to model events or activities that are characterized by significant uncertainty in the variables (like bidding a large construction job).
Monte Carlo Simulation generates values for uncertain variables over and over, not unlike rolling dice (hence the name). When rolling a die, there are a known range of values (one through six), but any of the values could occur for any roll. That same principal is applied to the variables in a construction job—labor costs or weather events for example, which are dependent on other variables, but have a specific range of possible values.
Using software packages that use the Monte Carlo method can significantly enhance the value and power of existing analytical tools by easily isolating variable and correlating them with one another and providing sensitivity analysis to help focus on the most critical assumptions to the project. Monte Carlo Simulation can greatly improve the quality of information provided by CFMs and lead to more meaningful discussions about the risks and rewards facing a company. CFMs can vary materials costs, labor costs, construction timing, and other key project factors. MCS helps determine, based on your experience, what the range of any given factor is likely to be. Armed with this information, MCS performs numerous scenarios to determine the entire range of possible outcomes. Rather than a single point estimate, you now have meaningful, graphical output to understand the financial projections for the company. Instead of managing multiple spreadsheets with different scenarios, the entire universe of possibilities is built into one single scenario. The best part – all of this can be run in your existing spreadsheets.
MCS software is designed to work with spreadsheet software, usually Microsoft Excel, and automates the process of running hundreds to thousands of simulations by randomly changing the values in variable cells, based on parameters determined in the process of developing the projections.
The benefit to the CFM is clear – using existing tools and spreadsheets and the institutional knowledge of the company, financial projections can be greatly improved.
What are the real benefits for CFMs?
One simulation versus many scenarios.
Using software packages that use the Monte Carlo method can significantly enhance the value and power of existing analytical tools by easily isolating variable and correlating them with one another and providing sensitivity analysis to help focus on the most critical assumptions to the project. With the capability of modeling assumption ranges, there is no longer the need to build separate spreadsheets for each scenario. Instead, CFMs can build one model and spend their time understanding and challenging the fundamental decisions that drive their assumptions.
Appreciate full range of possibilities.
By defining variables that capture the full range of possibilities for each variable, CFMs can investigate, and gain much more insight, into the impact of specific changes to business conditions. Additionally, MCS software is designed to create a visual representation of the variation used to run the scenarios. These become valuable communication tools when CFMs are asked to explain their analysis and their underlying assumptions to others.
Focus on the most critical assumptions.
MCS leads to a sensitivity analysis that identifies the variables which have the most impact on the end result. This allows CFMs to spend their time wisely by focusing on those critical assumptions. It can also be used to focus the entire organization on the key variables that drive results, saving time and effort. It also serves as an important tool, allowing CFMs to demonstrate the critical factors and eliminating “conventional wisdom” or crowd-favorite metrics that do nothing to add value to the company.
Understand full range of outcomes.
Ultimately, the goal of Monte Carlos Simulation is to display the full range of possible results rather than a single point answer or multiple answers from a fairly random array of hand-picked scenarios. This allows management to make more informed decisions about the risks and rewards of different projects. For example, a CFM may find that the single-point estimate on a project is only slightly profitable. A full MCS analysis demonstrates that while the project is most likely to be slightly profitable, it has almost no chance of losing money and a very large upside if the project can be brought in ahead of schedule.
MCS can be helpful with risk mitigation. By determining the financial impact of a delay, management can use MCS to understand how much overtime they would be willing to pay for in order to get a project back on track and remain profitable.
The alternatives to MCS have some pretty significant drawbacks in terms of accuracy, time, and usefulness in making crucial decisions relative to profitability:
The single point estimate.
Often, the data that go into creating the number are imprecise, are themselves a point rather than a range, and/or are based on assumptions that are difficult to trace back or recreate. Many times, if the one number doesn’t line up with expectations the CFM is asked to go back into the assumptions and play around a little bit until the answer that feels better for management. Consequently, the reliance on one number can lead to decisions that overlook important variables or different possibilities and are often as much based on an overall feeling as on the results of data.
Variable Overload.
As the pendulum swings away from the single point estimate, it brings on another problem – managing a baffling array of scenarios. There is no end to creating scenarios-- increase material costs 10% and decrease labor 5%, or increase materials 5% and decrease labor 5%, what if there is late snow, what if… From all these scenarios, it is difficult to extract anything really useful – other than things could be very, very good or pretty awful, depending on the scenario chosen. Unfortunately, this analysis rarely leads to an understanding of how the key variables interact with each other or affect the company’s bottom line.
The Red Herring or What Happened Last Time Some of our projections rely too heavily on avoiding specific scenarios that have a limited chance of recurrence. Having been burned or blind-sided by a particular problem in the past on previous projects, we may weigh that event too heavily in current projections. This can cause missed opportunities as a result of too-conservative projections.
It is relatively easy and inexpensive to try Monte Carlo Simulation and determine if your organization can benefit. There are two major products on the market today are @Risk (www.palisade.com) and Crystal Ball (www.decisioneering.com). Both companies offer free trial downloads from their websites. We recommend that you take advantage of the free trial before deciding to invest in the software.
Monte Carlo Simulation is a tool, not a silver bullet. MCS modeling allows construction financial managers to take the initiative to capture the deep institutional knowledge that exists in all construction firms and use it to advantage in pricing jobs, forecasting revenues and expenses, even estimating labor requirements. It allows management to make more informed decisions knowing both context and risk and likely outcomes.
Berry, Dunn, McNeil & Parker is an independent accounting and management consulting firm headquartered in Northern New England. We do not partner with any software or hardware vendors. We are not interested in selling you any software, but would be happy to help your company start making better decisions that lead to more profitable work.