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

benbulloch

New Member
Joined
Dec 14, 2010
Messages
9
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.

Help and thanks in advance!

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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
3CodeCategoryCost 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-16
4002-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
CellFormula
R3=+EDATE(Q3,1)
S3=+EDATE(R3,1)
T3=+EDATE(S3,1)
U3=+EDATE(T3,1)
V3=+EDATE(U3,1)
W3=+EDATE(V3,1)
X3=+EDATE(W3,1)
Y3=+EDATE(X3,1)
Z3=+EDATE(Y3,1)
AA3=+EDATE(Z3,1)
AB3=+EDATE(AA3,1)
AC3=+EDATE(AB3,1)
AD3=+EDATE(AC3,1)
AE3=+EDATE(AD3,1)
AF3=+EDATE(AE3,1)
AG3=+EDATE(AF3,1)
AH3=+EDATE(AG3,1)
AI3=+EDATE(AH3,1)
AJ3=+EDATE(AI3,1)
AK3=+EDATE(AJ3,1)
AL3=+EDATE(AK3,1)
AM3=+EDATE(AL3,1)
AN3=+EDATE(AM3,1)
G4=+E4/'[Riverdale Park proforma 2-19-14.xlsm]Assumptions'!$D$28
J4=+EDATE(I4,19)
K4=+(YEAR($J4)-YEAR($I4))*12+MONTH($J4)-MONTH($I4)+1
N4=+IF($E4=SUM(Q4:FE4),"Ok","Error")
Q4=+IF(AND($L4="Flat",Q$3>=$I4,Q$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",Q$3>=$I4,Q$3<=$J4),(NORM.DIST((YEAR(Q$3)-YEAR($I4))*12+MONTH(Q$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(Q$3)-YEAR($I4))*12+MONTH(Q$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
R4=+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)
S4=+IF(AND($L4="Flat",S$3>=$I4,S$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",S$3>=$I4,S$3<=$J4),(NORM.DIST((YEAR(S$3)-YEAR($I4))*12+MONTH(S$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(S$3)-YEAR($I4))*12+MONTH(S$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
T4=+IF(AND($L4="Flat",T$3>=$I4,T$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",T$3>=$I4,T$3<=$J4),(NORM.DIST((YEAR(T$3)-YEAR($I4))*12+MONTH(T$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(T$3)-YEAR($I4))*12+MONTH(T$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
U4=+IF(AND($L4="Flat",U$3>=$I4,U$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",U$3>=$I4,U$3<=$J4),(NORM.DIST((YEAR(U$3)-YEAR($I4))*12+MONTH(U$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(U$3)-YEAR($I4))*12+MONTH(U$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
V4=+IF(AND($L4="Flat",V$3>=$I4,V$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",V$3>=$I4,V$3<=$J4),(NORM.DIST((YEAR(V$3)-YEAR($I4))*12+MONTH(V$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(V$3)-YEAR($I4))*12+MONTH(V$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
W4=+IF(AND($L4="Flat",W$3>=$I4,W$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",W$3>=$I4,W$3<=$J4),(NORM.DIST((YEAR(W$3)-YEAR($I4))*12+MONTH(W$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(W$3)-YEAR($I4))*12+MONTH(W$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
X4=+IF(AND($L4="Flat",X$3>=$I4,X$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",X$3>=$I4,X$3<=$J4),(NORM.DIST((YEAR(X$3)-YEAR($I4))*12+MONTH(X$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(X$3)-YEAR($I4))*12+MONTH(X$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
Y4=+IF(AND($L4="Flat",Y$3>=$I4,Y$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",Y$3>=$I4,Y$3<=$J4),(NORM.DIST((YEAR(Y$3)-YEAR($I4))*12+MONTH(Y$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(Y$3)-YEAR($I4))*12+MONTH(Y$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
Z4=+IF(AND($L4="Flat",Z$3>=$I4,Z$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",Z$3>=$I4,Z$3<=$J4),(NORM.DIST((YEAR(Z$3)-YEAR($I4))*12+MONTH(Z$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(Z$3)-YEAR($I4))*12+MONTH(Z$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AA4=+IF(AND($L4="Flat",AA$3>=$I4,AA$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AA$3>=$I4,AA$3<=$J4),(NORM.DIST((YEAR(AA$3)-YEAR($I4))*12+MONTH(AA$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AA$3)-YEAR($I4))*12+MONTH(AA$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AB4=+IF(AND($L4="Flat",AB$3>=$I4,AB$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AB$3>=$I4,AB$3<=$J4),(NORM.DIST((YEAR(AB$3)-YEAR($I4))*12+MONTH(AB$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AB$3)-YEAR($I4))*12+MONTH(AB$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AC4=+IF(AND($L4="Flat",AC$3>=$I4,AC$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AC$3>=$I4,AC$3<=$J4),(NORM.DIST((YEAR(AC$3)-YEAR($I4))*12+MONTH(AC$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AC$3)-YEAR($I4))*12+MONTH(AC$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AD4=+IF(AND($L4="Flat",AD$3>=$I4,AD$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AD$3>=$I4,AD$3<=$J4),(NORM.DIST((YEAR(AD$3)-YEAR($I4))*12+MONTH(AD$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AD$3)-YEAR($I4))*12+MONTH(AD$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AE4=+IF(AND($L4="Flat",AE$3>=$I4,AE$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AE$3>=$I4,AE$3<=$J4),(NORM.DIST((YEAR(AE$3)-YEAR($I4))*12+MONTH(AE$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AE$3)-YEAR($I4))*12+MONTH(AE$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AF4=+IF(AND($L4="Flat",AF$3>=$I4,AF$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AF$3>=$I4,AF$3<=$J4),(NORM.DIST((YEAR(AF$3)-YEAR($I4))*12+MONTH(AF$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AF$3)-YEAR($I4))*12+MONTH(AF$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AG4=+IF(AND($L4="Flat",AG$3>=$I4,AG$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AG$3>=$I4,AG$3<=$J4),(NORM.DIST((YEAR(AG$3)-YEAR($I4))*12+MONTH(AG$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AG$3)-YEAR($I4))*12+MONTH(AG$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AH4=+IF(AND($L4="Flat",AH$3>=$I4,AH$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AH$3>=$I4,AH$3<=$J4),(NORM.DIST((YEAR(AH$3)-YEAR($I4))*12+MONTH(AH$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AH$3)-YEAR($I4))*12+MONTH(AH$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AI4=+IF(AND($L4="Flat",AI$3>=$I4,AI$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AI$3>=$I4,AI$3<=$J4),(NORM.DIST((YEAR(AI$3)-YEAR($I4))*12+MONTH(AI$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AI$3)-YEAR($I4))*12+MONTH(AI$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AJ4=+IF(AND($L4="Flat",AJ$3>=$I4,AJ$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AJ$3>=$I4,AJ$3<=$J4),(NORM.DIST((YEAR(AJ$3)-YEAR($I4))*12+MONTH(AJ$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AJ$3)-YEAR($I4))*12+MONTH(AJ$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AK4=+IF(AND($L4="Flat",AK$3>=$I4,AK$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AK$3>=$I4,AK$3<=$J4),(NORM.DIST((YEAR(AK$3)-YEAR($I4))*12+MONTH(AK$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AK$3)-YEAR($I4))*12+MONTH(AK$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AL4=+IF(AND($L4="Flat",AL$3>=$I4,AL$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AL$3>=$I4,AL$3<=$J4),(NORM.DIST((YEAR(AL$3)-YEAR($I4))*12+MONTH(AL$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AL$3)-YEAR($I4))*12+MONTH(AL$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AM4=+IF(AND($L4="Flat",AM$3>=$I4,AM$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AM$3>=$I4,AM$3<=$J4),(NORM.DIST((YEAR(AM$3)-YEAR($I4))*12+MONTH(AM$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AM$3)-YEAR($I4))*12+MONTH(AM$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
AN4=+IF(AND($L4="Flat",AN$3>=$I4,AN$3<=$J4),$E4/$K4,0)+IF(AND($L4="S-Curve",AN$3>=$I4,AN$3<=$J4),(NORM.DIST((YEAR(AN$3)-YEAR($I4))*12+MONTH(AN$3)-MONTH($I4)+1,$K4/2,$M4,TRUE)-NORM.DIST((YEAR(AN$3)-YEAR($I4))*12+MONTH(AN$3)-MONTH($I4),$K4/2,$M4,TRUE))/(1-2*NORM.DIST(0,$K4/2,$M4,TRUE))*$E4,0)
<tbody> </tbody>
<tbody> </tbody>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

shahnyl

New Member
Joined
Jun 2, 2014
Messages
2
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
Joined
Aug 20, 2014
Messages
1
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?
 

drbolt

New Member
Joined
Aug 25, 2017
Messages
2
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.

So, My formula reads:
=+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/
 

mccracken

New Member
Joined
Sep 18, 2018
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,086
Messages
5,545,878
Members
410,711
Latest member
Josh324
Top