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>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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?
 
Upvote 0
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/
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top