 Construction delays are one of the most common risks encountered in project finance or real estate finance, yet if you wanted to apply a scenario analysis to construction delays, you’d have to create a separate schedule for each case that changed the number of months involved. We’re going to show you a better way to model construction curves – also known as s-curves.

Construction cost curves usually consist of a hard coded set of values per period (usually on a monthly basis for precision). While it can be nice to input exact curves, modelling projects in this way severely limits the flexibility of creating a useful scenario analysis.

For today’s eCampus post, we’re going to present an alternative to the inputted s-curve: the Weibull distribution s-curve. Does that sound like a mouthful? Well, it does to us too, so we’ll be sure to break it down for you.

The Weibull distribution is commonly used in probability analyses for forecasting the survival of a project, probability of failure across various points, and wind speed analysis. We’re aiming for something more simplistic than that.

Standard S-Curve vs. Weibull S-Curve

Let’s assume a base case of 8 months of construction. As you can see above in the Checks, both the Inputted S-Curve, and the Weibull S-Curve return as TRUE. Excel’s WEIBULL.DIST function has 3 inputs, and we’re going to get to those in a second, but first, take a quick look at our Inputted S-Curve (blue bars on graph): The image below shows shows the % of construction per month, according to our assumptions. Now, what happens if we change the # of months of construction to 9? Unless we have 0% scheduled into the inputted S-Curve schedule (which we do not), the inputted construction curve is going to fall above or below 100% of the construction costs. In this case, it falls above. If the construction months is switched to 7, it falls below. Both of these cases turn the Inputted check to FALSE because the value does not add up to 100%.

No matter how what value you change the Months of Construction value to, the Weibull S-Curve stays at 100%. This is because we’ve set it up to adjust appropriately to the change in date value. The key to using Weibull distributions is to make sure that it generally follows the curve that your inputs do. Once you’ve established that, the Months of Construction value becomes something flexible that will update appropriately. This is especially important for time value of money calculations such as NPV or IRR analysis.

So how does the Weibull S-Curve work?

Excel’s WEIBULL.DIST function allows for two different formulas: the Cumulative Distribution Function (CDF), and the Probability Density Function (PDF). We are only concerned with the Probability Density Function.

WEIBULL.DIST takes 3 key inputs:

• x: the period
• beta: shape of the distribution (calculated and driven by the Gamma input and the Midpoint)
• alpha: the skew of the distribution

While beta can be hard-coded as an input, it’s a little more helpful to do a simple derivation of it from the midpoint of the construction period – this will allow us to base our curve “shaping” off of the months of construction and it adds flexibility to the model.

To kick the calculations off, we’ll start with the construction Midpoint. To calculate the Midpoint, all we’re going to do is take our # of months of construction, and divide it by 2.

Together with the Midpoint, the “Gamma” input drives the calculation of Beta, which is the WEIBULL.DIST function’s shape input. We leave this in blue in order to denote it as an input. Then we calculate Beta by dividing the Midpoint by the Gamma input.

As with most financial modelling, these words and names might be “esoteric”, but the math is simple. Let’s starting playing around with the inputs to see what sorts of shapes we come up with. We’ve created a little scenario changer using the INDEX function; the scenarios exhibit a few different ways to work with and create the the Weibull distribution s-curve. The blue text denotes inputs, and the black denotes a calculation – coloring inputs like this is one of the financial modelling “best practices” that you can do to provide clarity in the models.

By going through the Weibull scenarios, we can see the impact that various switches and levers have on the Weibull distribution. These are helpful as guides: when we have an idea of the curve we’d expect construction to follow, we can recreate it using the Weibull, and benefit from the flexibility it has over the timing inputs.

We run through each scenario in the video at the top of the page in order to give you a better idea of what inputs you need to plug in in order to create an appropriate curve for your project.

Now that we’ve done an overview of the Weibull, let’s set up an S-Curve with it. If you’re following along in the workbook, jump down to row 39, where we’ve performed the Weibull distribution calculation, and created the resulting curve.

One thing you might note immediately is that the actual distribution itself does not sum to 100%. This is okay, the actual Weibull distribution isn’t supposed to, but the S-Curve should every time.

Setting Up the Weibull Distribution

In order to set up the formula, we want to start in the column that holds the first period of construction: period 1 (row 26 in the workbook). The WEIBULL.DIST function takes inputs as follows: =WEIBULL.DIST(x, beta, alpha, cumulative). For our first period of construction, in cell G40, we’re going to put in “=WEIBULL.DIST(G26, \$D\$24, \$D\$23, FALSE)*G27”.

Do you notice how the first input, x, is not wrapped in dollar signs? This means that as we paste the formula across to the right, it will update accordingly for each column for periods 1-12 as we have set up in the timeline. This same concept also applies to multiplying the distribution by the Construction Period T/F switch, which allows us to essentially “cut off” the formula at the appropriate month (we’ll do another post on that later). The Beta and Alpha inputs are wrapped in the dollar signs to create a “sticky” reference to the cells they draw from – those references won’t change when pasted across.

After that formula is pasted across, we’ll take the sum of that whole row in column E. When we create the S-Curve, we’ll use this sum as the S-Curve’s denominator in order to normalize the sum of it to 100%.

Creating the Weibull S-Curve

To create the S-Curve, our formula is going to be a simple fraction. For column G, we’ll make a formula that says “=G40/\$E\$40”. All this is doing is taking the individual Weibull input for that period, and dividing it by the total amount computed. Paste this across, and take a sum of the row just as a check. It should come out to 100%.

The last step is to actually make the calculation. To do so, we’re going to grab the total construction costs, make them an absolute reference (wrap in \$ signs – use F4 key as a toggle when editing), and then multiply the total costs by the S-Curve. And with that, you should have a flexible, accurate construction cost curve! Now, when you switch through timing in the # of months of construction, the Weibull function will update automatically.

Hope you enjoyed the tutorial! Please feel free to leave feedback in a comment, or let us know what else you’d like to see!