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!