Cost Distribution based on Bell Shaped Curve

[bluesband]

I'm working on a budget forecast where costs are budgeted in the shape of a bell-shaped curve. Costs in the middle months are greater then costs during the early or finishing months.

Say the periods occur monthly over 12 to 48 months, how can I generate a cost distribution schedule totaling 100% such that the duration and shape of the curve can be easily modified?

Once the distribution is generated I can apply this to my budget number to spread the costs.

Thanks in advance for any input.

 

[iliace]

You should post some sample data and sample results that you want, so that you can get an example.

Beyond that, look into the NORMDIST() worksheet function - which, I believe, may be part of the Analysis Toolpak in Excel 2003 and earlier.

 

[bluesband]

Sorry, no data, but here is an example that may make my objective clearer.

Say I had a $1,200 annual budget. I could forecast the monthly expenditures on a straightline basis at $100/mo. However, I know that monthly expenditures are uneven and somewhat mirror a bell-shaped curve. The distribution of costs are disproportionately low in the early months (months 1-3) and in the later months (months 10-12) while during the middle months (months 4 - 9) expenditures ramp up and are disproportionately higher.

I'd like the flexibility to vary the duration since not all expenditures occur over a 12 month period. Also varying the shape of the bell curve (steep or flat) to more closely anticipate the relative differences in monthly expenditures would be helpful.

Applications may include an advertising budget where expenditures are based on seasonality. Another application would be for construction costs where the bulk of the costs occur in the middle of the project.

Suggestions?

 

[iliace]

Hmm. Is this good enough? I'm not terribly well-versed in this - it looks like the curve is too narrow.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8	9
2	1200	$0.28	$7.17	$72.72	$290.08	$459.51	$290.08	$72.72	$7.17	$0.28
sheet

 

[Dave Patton]

Identify the distribution factors say in A11:A14 (4 different sets of rates)
Put distribution percentages in each row B11:M14 1.8% 1.8% 3.6% .... (Total 100%)
Code for allocation type C2
Amount to be allocated D2
Allocation E2 =SUMPRODUCT(SUMIF($C2,$A$11:$A$14,$D2),B$11:B$14)
Copy formula from E2 across

 

[bluesband]

The idea of using a statistically based distribution (i.e. a normal distribution, a binomial distribution, or whatever is appropriate to generate a bell-shaped curve) was to determine the allocation percentages. Once the allocation percentages totaling 100% are calculated allocating costs becomes the easy part.

Manually inputting distribution percentages defeats the purpose as this process would be time consuming if the durations of the expenditures varied between budgeted line items (i.e some costs are incurred over 6 months, some over 10 months, and others over 12 months).

Any advice on generating a bell-shaped curve such that the sum of discrete data points = 100%?

 

[benbulloch]

I've been screwing around with the exact question for quite some time and finally have a good normally-distributed s-curve generation formula for spreading project costs over a number of months. Adjust the StDev parameter to flatten or steepen the s-curve.

My question now is how to do a skewed distribution where either the start or end of the project has more cost. Anyone with ideas on that would be my hero. See my post here: non-normal (skewed) project cost distribution across months - gamma distribution? calculus?

In any event see the formula here that I believe addresses an s-curve cost spreading problem for projects, finance, construction etc. Formula is for cell R4 in the HTML below.

=+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
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
4
002-01
Construction
Construction
 $ 10,000,000 
 $ - 
 $ 121,951 
Yes
Mar-14
Jan-15
11
S-Curve
5.0
Ok
 664,144 
 794,649 
 913,638 
 1,009,390 
 1,071,594 
 1,093,168 
 1,071,594 
 1,009,390 
 913,638 
 794,649 
 664,144 
 - 

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>

</thead><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)
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)
G4
=+E4/'S:\Streetscape Partners, LLC\Prince Georges County projects\Riverdale Park\Financials\[Riverdale Park proforma 2-19-14.xlsm]Assumptions'!$D$28
J4
=+EDATE(I4,10)
K4
=+(YEAR($J4)-YEAR($I4))*12+MONTH($J4)-MONTH($I4)+1
N4
=+IF($E4=SUM(Q4:FE4),"Ok","Error")

<thead>

</thead><tbody>

</tbody>

<tbody>

</tbody>

 

[drbolt]

My question now is how to do a skewed distribution where either the start or end of the project has more cost. Anyone with ideas on that would be my hero. See my post here: non-normal (skewed) project cost distribution across months - gamma distribution? calculus?
[/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/

 

[kd164]

Hi,

Does anyone know how to implement a bimodal distribution which cashflows can run off?

Many thanks
K