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

[benbulloch]

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)

Excel 2012

B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
Code
Category
Cost Code Description
 Current Budget
 $ / MPDU
 $ / Mkt Rate
StDev
Checksum
Test/Scrap
002-01
Construction
Construction
 $ 10,000,000
 $ -
 $ 121,951
Ok
 -
 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>

[TD="align: center"]3[/TD]


[TD="align: center"]Acq Loan[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Months[/TD]
[TD="align: center"]Timing[/TD]


[TD="align: right"][/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Jul-14[/TD]
[TD="align: right"]Aug-14[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Oct-14[/TD]
[TD="align: right"]Nov-14[/TD]
[TD="align: right"]Dec-14[/TD]
[TD="align: right"]Jan-15[/TD]
[TD="align: right"]Feb-15[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Apr-15[/TD]
[TD="align: right"]May-15[/TD]
[TD="align: right"]Jun-15[/TD]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]Aug-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Oct-15[/TD]
[TD="align: right"]Nov-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Jan-16[/TD]


[TD="align: center"]4[/TD]


[TD="align: center"]Yes[/TD]
[TD="align: center"]Mar-14[/TD]
[TD="align: center"]Oct-15[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]S-Curve[/TD]
[TD="align: right"]8.0[/TD]


[TD="align: right"][/TD]
[TD="align: right"][/TD]


</tbody>
Sheet1​
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]R3[/TH]
[TD="align: left"]=+EDATE(Q3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]S3[/TH]
[TD="align: left"]=+EDATE(R3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]T3[/TH]
[TD="align: left"]=+EDATE(S3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U3[/TH]
[TD="align: left"]=+EDATE(T3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]V3[/TH]
[TD="align: left"]=+EDATE(U3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]W3[/TH]
[TD="align: left"]=+EDATE(V3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]X3[/TH]
[TD="align: left"]=+EDATE(W3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Y3[/TH]
[TD="align: left"]=+EDATE(X3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Z3[/TH]
[TD="align: left"]=+EDATE(Y3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AA3[/TH]
[TD="align: left"]=+EDATE(Z3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AB3[/TH]
[TD="align: left"]=+EDATE(AA3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AC3[/TH]
[TD="align: left"]=+EDATE(AB3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD3[/TH]
[TD="align: left"]=+EDATE(AC3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AE3[/TH]
[TD="align: left"]=+EDATE(AD3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AF3[/TH]
[TD="align: left"]=+EDATE(AE3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AG3[/TH]
[TD="align: left"]=+EDATE(AF3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AH3[/TH]
[TD="align: left"]=+EDATE(AG3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AI3[/TH]
[TD="align: left"]=+EDATE(AH3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AJ3[/TH]
[TD="align: left"]=+EDATE(AI3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AK3[/TH]
[TD="align: left"]=+EDATE(AJ3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AL3[/TH]
[TD="align: left"]=+EDATE(AK3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AM3[/TH]
[TD="align: left"]=+EDATE(AL3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AN3[/TH]
[TD="align: left"]=+EDATE(AM3,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=+E4/'[Riverdale Park proforma 2-19-14.xlsm]Assumptions'!$D$28[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J4[/TH]
[TD="align: left"]=+EDATE(I4,19)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K4[/TH]
[TD="align: left"]=+(YEAR($J4)-YEAR($I4))*12+MONTH($J4)-MONTH($I4)+1[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=+IF($E4=SUM(Q4:FE4),"Ok","Error")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]R4[/TH]
[TD="align: 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,$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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]S4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]T4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]V4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]W4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]X4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Y4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Z4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AA4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AB4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AC4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AE4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AF4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AG4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AH4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AI4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AJ4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AK4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AL4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AM4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AN4[/TH]
[TD="align: left"]=+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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shahnyl]

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]

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?

 

[Developerintraining]

Ping

 

[drbolt]

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]

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.