In this post, we give an extended account of an example use case of our application, Scenarios: modelling the labour costs and budget of an urban construction task. (This is our second extended example use case; we have previously discussed using Scenarios for accounting and management.) Along the way, we'll provide example Excel models that you can download and run yourself (if you have Scenarios). To download Scenarios, join our alpha program here.

Sensitivity Analysis

Consider the following hypothetical situation. You’re an executive at a construction company that has been contracted to build a 15-floor building, charging a price of $10 million. Fixed costs (such as equipment and materials) account for $5 million of this price, leaving $5 million in earnings before accounting for labour costs.

However, accounting for labour costs isn’t so simple. As a seasoned professional in the construction industry, you know that what was written in the budget is often not identical to what actually plays out. In planning the budget, you used the figure provided to you by the construction manager. The construction manager told Accounting to assume that your 100 laborers, each paid $55,000 per year and working eight-hour days, would complete each floor in 11 days, completing the building within 24 weeks.

More specifically, he assumed it takes 8,000 total man-hours of work to complete each floor, that can be distributed among over a number of workers, plus one day of setup time for each floor. By this assumption, your budget specifies $2,493,132 in labour costs, leading to $2,506,868 in earnings before tax, depreciation, and amortization (EBITDA). Was the construction manager’s assumption correct? Your profit margin hinges on it.

Being wrong about the amount of labour necessary to complete the building will cost your company much more than just the direct cost of the labour. By the terms of the contract, your company must finish this building within 30 weeks, or you will face a penalty: for every week over the contractual completion time, you will pay a penalty of $150,000, eating into your profit margin.

These assumptions, and the resulting budget, are specified in the following spreadsheet model (click to download):

The initial problem the above presents to planners and decision-makers is one called sensitivity analysis. How sensitive is the profit margin of this job to the assumption about how long each floor will take? If for whatever reason each floor takes 15 days instead of 11 days, does this destroy the profit margin? What if, with each floor completed, the pace of construction increases, so subsequent floors are completed quickly – and what if it slows down? Both are feasible, depending on the type of project.

The pre-eminent tool for attacking these questions is Monte Carlo simulation. We, the modellers, augment the above budget by specifying certain quantities as unknowns, as assumptions. This allows for the possibility that the assumptions the construction manager made may not be precisely correct. In this case, the unknowns are a) the number of man-hours it takes to complete a floor, b) the floor-to-floor change in man-hours, and c) the variability in how long it takes to complete each floor are all unknown. Using Scenarios, changing the previous model into a probabilistic model is straightforward: simply replace the cells that are to become random with distributions. Click the below image to download our model:

This model is augmented with three new assumptions. First, we assume that the man-hours it takes to complete one floor is normally distributed, with an expected value of 8000 hours and a standard deviation of 1000 hours. Essentially, this means that the modeller expects that the value is 8,000 hours, but is allowing for the possibility that it could be as low as 5,000 hours and as high as 11,000 hours.

Our second assumption is related to the pace at which construction proceeds. We allow for the possibility that construction slows down or speeds up with each floor that is completed. We think it most likely that construction does not slow down or speed up, but we allow for the possibility that it does slow down or speed up by possibly as much as 900 man-hours per floor in either direction. We allow for this possibility because we do not know whether or not it will slow down or speed up, and because if it does slow down, as we’ll see, this is very bad for our profit margin.

Finally, in the bottom-right formula shown in the picture above, we make our third assumption. We assume that the variability in construction time for each floor is also uncertain – that while most floors are expected to take around 8,000 man-hours to complete, it is unknown how wide a spread of floor completion times is likely to occur within one building. A high variability would indicate that floor completion times are very unpredictable, while a low variability would indicate that if the first floor takes 8,500 hours, the rest are likely to take a very similar time as well.

(Note: These assumptions make use of only a few of the probability distributions packaged within Scenarios – the normal distribution and the gamma/Erlang distribution. For a complete list of the distributions included in Scenarios, see the Scenarios documentation. For more information on how to interpret these assumptions and understand the analysis performed by Scenarios, see here and here.)

By using Scenarios to generate 100,000 scenarios per the model above, we can analyze the sensitivity of the profit margin to the particular assumptions we've made. The following is a report of the results of those simulations:

The graph on the left shows the EBITDA generated in each of the 100,000 simulations run by Scenarios. The graph on the right is simply a compressed version of the graph on the left, indicating only whether the EBITDA came out positive in each scenario.

So in the clear majority of the simulations run (about 98%), the EBITDA is greater than zero. In the remaining 2% of scenarios, it was either the case that each floor took significantly longer than the expert projected, or that there was a significant trend upwards in floor completion times, resulting in the upper floors taking far longer to complete than the lower floors. If 2% is a level of risk that you and your company can tolerate, sensitivity analysis has made your decision – you should agree to the offer and go ahead with the project.

Incorporating Actuals

However, decision-making does not stop once you’ve decided to go ahead with the project. In fact, Invrea’s insight is that the most important and difficult decisions are still ahead. If your team doesn’t correctly react to actual data about the construction project as it comes in – if your team doesn’t incorporate actuals into its decision-making processes – then you can lose big. Here’s what we mean by that.

Decision-making is not a static process. Frequently, business decisions need to be re-evaluated in the light of new data. In the case of your construction project, consider the position you’d be in if your company commenced the construction project, and the first four floors took 11.5 days, 11 days, 14.5 days, and 15.8 days respectively.

These results put you in a difficult position. Do these floor completion times represent a clear trend upwards, which will result in significant delays to completion? Or is this simply a result of unforeseen factors that made floors 3 and 4 take longer than they should have? If there is a clear trend upwards, then the resulting delays will probably make it necessary to double the size of your workforce in order to finish the project by the contractually-obligated time, so that you can still turn a profit. However, if these data points are just outliers and not indicative of a trend, then doubling the size of your workforce would be wasteful and would eat into your profit margin.

Specifically, the problem you must solve is that in building the model, we made two assumptions that are somewhat at odds with one another. We made one assumption about the floor-to-floor change in man-hours, stating that an upwards trend of this magnitude is perfectly possible. We made another assumption about variability in construction time per floor, which posits that outliers of this magnitude are perfectly plausible, and may be consistent with a constant floor completion time. We have a very small amount of data. How do you weigh these two assumptions against one another, in order to best explain the observed data, and then use the result to inform and possibly re-assess your decision? This is exactly the problem that the new technology of probabilistic programming, the core unique technology of Scenarios, was built to solve.

In Scenarios, there is a special construct for telling the model it must learn from new data in this way. We call data points that the model must learn from Actuals. Adding an actual data point to a model is as easy as right-clicking a cell and pressing 'Record actual':

By pressing 'Record' in this dialog box, the user tells Scenarios that they are only interested in viewing scenarios in which the fourth floor takes 15.8 days to complete. (This process can be repeated easily for the earlier three floors; or simply click the above image to download the resulting model.) Scenarios uses these actual data points in a way never before seen in a commercial application: to simultaneously verify and improve the assumptions that we, the modeller, entered. Scenarios then produces new forecasts and recommendations, based on the new data and the revised assumptions. (For an in-depth discussion about how Scenarios solves these problems, see here and here.)

So using Scenarios, we generate 500,000 more scenarios, this time taking into account the results of the first four floors. We see that our predictions have shifted heavily:

Taking into account the completion times of the first four floors, the odds that the EBITDA will be less than zero are over 35 percent. So, in order to guarantee that the company doesn’t lose money on this project, we must hire more workers. Scenarios has told us that in light of the new data, our initial assumptions about floor completion times were wrong, and revised them into more accurate assumptions.

You cannot get this kind of analysis without using Scenarios. If instead of pressing ‘Record actual’ you had simply typed the observed floor completion times into the corresponding cell, we would not have revised our initial assumptions, we would still be operating on a model that is wrong, and we would have more than a 35 percent chance of losing hundreds of thousands of dollars. To see this, download the below model and run Scenarios:

When instructed to learn from data, Scenarios uses this data to check whether its user-inputted assumptions are correct, improves those assumptions, and then uses those improved assumptions to build improved predictions about the future. In this case, it has learned that floors in reality take closer to 9,000 man-hours to build (rather than the 8,000 we had initially assumed), and that there is very likely a trend leading to higher floors taking more construction time.

To inspect what Scenarios has learned about the construction process, compare the following two histograms. The one on the left is our initial assumption about the floor-to-floor trend in man-hours, which is centred around zero, and the one on the right is the distribution that Scenarios has inferred to better represent the data that has actually occurred:

So these actuals - the floor completion times - have forced you to reconsider your assumptions moving forward. In reaction to this new data, you should change your decision - increase the size of the workforce, so that the project will be completed in 30 weeks after all. However, you don’t want to increase the size of your workforce too much, because this will waste money on labour costs. So how do you choose when to increase the size of your workforce, and by how much do you increase it?

Scenarios, using the above model, can also solve this problem. Simply change the number of labourers used in the model, retaining the results of the first four floors to train the model, and the predictions about the EBITDA will be updated. For example, suppose you’re considering doubling the size of your workforce when building floors 10 through 15. The following graph shows the probability that the EBITDA is positive, if you decide to increase your workforce per this scheme:

Doubling the workforce size, only on the latter floors, has significantly driven down the risk of going over-budget due to the penalty fees. Therefore, despite the increased labour costs, doubling the workforce size on floors 10 through 15 is a good idea.

The applications of Scenarios are by no means limited to construction. If you face a decision-making problem for which uncertainty is high and data is available, consider using Scenarios to inform your analysis. Neither decisions nor data is static, and in almost every situation, the most important data is the most recent data. Forecasts should change as soon as new data emerges; Scenarios can do this for you automatically. Join the alpha program here.