# non-normal (skewed) project cost distribution across months - gamma distribution? calculus?

#### benbulloch

##### New Member
I have a real estate development project with hundreds of cost line items. One cost line is appended below.

I want to distribute the cost for each item across time in a number of distribution "shapes" depending on the kind of cost. Obviously this is easy to evenly distribute ("Flat" in Column L (data validation)). My formula can also currently "normally" distribute these costs in a typical bell-curve shaped distribution. In construction this called an "s-curve" for obvious reasons. By adjusting the Standard Deviation for a cost, I can tell the formula how "steep" or "flat" I want my curve. Column L tells the formula to pick one of the two shapes.

My problem is that many of my costs aren't normally distributed (bell-curve) or flat. They are very often front-loaded or rear loaded in a skewed bell-curve.

My first thought was to dust off 11th grade calculus since I should be able to numerically integrate to calculate the area under any curve between two points (months in this case) on the x-axis. I know the total area under the curve (the total cost) and am just figuring out the area under the curve for a specific shape and between two months. But how to define the shape?? Cosine wave? Also I had never seen any calculus functions in Excel.

I also thought a different statistical analysis (maybe gamma distribution) might get me where I need to be but I'm struggling to make it work. Gamma has a skew concept.

Here is my formula for R4 which incorporates "flat" distribution and a "normal" s-curve.

=+IF(AND(\$L4="Flat",R\$3>=\$I4,R\$3<=\$J4),\$E4/\$K4,0)+IF(AND(\$L4="S-Curve",R\$3>=\$I4,R\$3<=\$J4),(NORM.DIST((YEAR(R\$3)-YEAR(\$I4))*12+MONTH(R\$3)-MONTH(\$I4)+1,\$K4/2,\$M4,TRUE)-NORM.DIST((YEAR(R\$3)-YEAR(\$I4))*12+MONTH(R\$3)-MONTH(\$I4),\$K4/2,\$M4,TRUE))/(1-2*NORM.DIST(0,\$K4/2,\$M4,TRUE))*\$E4,0)

Rich (BB code):
``````Excel 2012
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN3CodeCategoryCost Code Description Current Budget \$ / MPDU \$ / Mkt RateAcq LoanStart DateEnd DateMonthsTimingStDevChecksumTest/ScrapFeb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14Jan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15Oct-15Nov-15Dec-15Jan-164002-01ConstructionConstruction \$ 10,000,000 \$ - \$ 121,951YesMar-14Oct-1520S-Curve8.0Ok - 312,473 359,588 407,401 454,423 499,026 539,521 574,270 601,793 620,871 630,635 630,635 620,871 601,793 574,270 539,521 499,026 454,423 407,401 359,588 312,473 - - -

<tbody>

</tbody>Sheet1​

Worksheet Formulas

<tbody>

</tbody>

<tbody>

</tbody>``````

• mccracken

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### shahnyl

##### New Member
Thank you - I have started using your formula to forecast costs in a construction project and the S-Curve works perfectly. I have a question however, my current financial model is used not only to forecast, but also to track costs to a project. As such, actual costs incurred from the previous month are input on a monthly basis in a different worksheet and a formula is used to reiterate our forecast to account for variances between budgeted costs and actual costs. Currently the forecasting is done on a straight-line basis, however, I would like to incorporate an S-Curve distribution.

Are you aware of any way to account for the delta between actual costs and budgeted amounts using your current formula while maintaining the structural integrity of the S-Curve distribution?

Said another way, how would I reiterate the Forecast every month to account for the input of actual costs which come in either above or below the forecasted amount?

#### cntaylor316

##### New Member
Man this is good stuff. I have been trying to figure a similar problem out for weeks. I am interested have you got in ideas on either creating custom curves or like you said a gamma distribution?

Ping

#### drbolt

##### New Member
I have a real estate development project with hundreds of cost line items. One cost line is appended below.

I want to distribute the cost for each item across time in a number of distribution "shapes" depending on the kind of cost...
My problem is that many of my costs aren't normally distributed (bell-curve) or flat. They are very often front-loaded or rear loaded in a skewed bell-curve.

I also thought a different statistical analysis (maybe gamma distribution) might get me where I need to be but I'm struggling to make it work. Gamma has a skew concept.

[/code]

Ben,

I changed your formula a bit to allow the mean to skew the cash flows left or right. Instead of dividing by 1 - 2 * 'the left tail', I found the percentage from 0 to the last month (NORM.DIST(\$K4,MEAN,\$M4,TRUE)-NORM.DIST(0,MEAN,\$M4,TRUE)). Then when you increase the mean above K4/2, the cash flows skew to the right, and when you decrease the mean below the average duration, the cash flows skew to the left.

=+IF(AND(\$L4="Flat",R\$3>=\$I4,R\$3<=\$J4),\$E4/\$K4,0)+IF(AND(\$L4="S-Curve",R\$3>=\$I4,R\$3<=\$J4),(NORM.DIST((YEAR(R\$3)-YEAR(\$I4))*12+MONTH(R\$3)-MONTH(\$I4)+1,MEAN,\$M4,TRUE)-NORM.DIST((YEAR(R\$3)-YEAR(\$I4))*12+MONTH(R\$3)-MONTH(\$I4),MEAN,\$M4,TRUE))/(NORM.DIST(\$K\$4,MEAN,\$M4,TRUE)-NORM.DIST(0,MEAN,\$M4,TRUE))*\$E4,0)
where, MEAN is a named range that initially contains the value, K4/2, but can be changed higher or lower to skew right or left.

There is a cash flow model developed by Ken-Yu Lin Runner that uses Excel's gamma distribution function to skew the cash flows, but I wanted a little more simplicity. You can find that model at the bottom of this page: http://faculty.washington.edu/kenyulin/

• Wookie and mccracken

#### mccracken

##### New Member
Exactly the help that I needed. The exercise I'm working on is future sales pipeline work. Basically looking at market reports and determining clashes with manpower.
We are a large industrial construction company on the Gulf Coast. I sure would like to see if you could review my work. My next step is to utilize this data in Power BI. duration of mahours based on flat or "even distribution" over the project.

Replies
0
Views
88
Replies
2
Views
101
Replies
6
Views
169
Replies
0
Views
188
Replies
4
Views
179