# Thread: Cost Distribution based on Bell Shaped Curve Thanks:  1 Post #4896748 (1) Likes:  1 Post #4896748 (1)

1. ## Cost Distribution based on Bell Shaped Curve

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.  Reply With Quote

2. ## Re: Cost Distribution based on Bell Shaped Curve

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.  Reply With Quote

3. ## Re: Cost Distribution based on Bell Shaped Curve

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?  Reply With Quote

4. ## Re: Cost Distribution based on Bell Shaped Curve

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

sheet

 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

 Cell Formula B2 =\$A2*(NORMSDIST(B1-4.5)-IF(B1=1,0,NORMSDIST(A1-4.5))) C2 =\$A2*(NORMSDIST(C1-4.5)-IF(C1=1,0,NORMSDIST(B1-4.5))) D2 =\$A2*(NORMSDIST(D1-4.5)-IF(D1=1,0,NORMSDIST(C1-4.5))) E2 =\$A2*(NORMSDIST(E1-4.5)-IF(E1=1,0,NORMSDIST(D1-4.5))) F2 =\$A2*(NORMSDIST(F1-4.5)-IF(F1=1,0,NORMSDIST(E1-4.5))) G2 =\$A2*(NORMSDIST(G1-4.5)-IF(G1=1,0,NORMSDIST(F1-4.5))) H2 =\$A2*(NORMSDIST(H1-4.5)-IF(H1=1,0,NORMSDIST(G1-4.5))) I2 =\$A2*(NORMSDIST(I1-4.5)-IF(I1=1,0,NORMSDIST(H1-4.5))) J2 =\$A2*(NORMSDIST(J1-4.5)-IF(J1=1,0,NORMSDIST(I1-4.5)))

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

5. ## Re: Cost Distribution based on Bell Shaped Curve

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  Reply With Quote

6. ## Re: Cost Distribution based on Bell Shaped Curve

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%?  Reply With Quote

7. ## Re: Cost Distribution based on Bell Shaped Curve

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)

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

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
-

Sheet1
Worksheet FormulasCell
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")

```  Reply With Quote

8. ## Re: Cost Distribution based on Bell Shaped Curve Originally Posted by benbulloch 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/  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•