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)
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
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 3 Code Category Cost Code Description Current Budget $ / MPDU $ / Mkt Rate Acq Loan Start Date End Date Months Timing StDev Checksum Test/Scrap Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 4 002-01 Construction Construction $ 10,000,000 $ - $ 121,951 Yes Mar-14 Oct-15 20 S-Curve 8.0 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>
</tbody>Sheet1
Worksheet Formulas
Cell Formula 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>