Cost Distribution based on Bell Shaped Curve
Results 1 to 8 of 8

Thread: Cost Distribution based on Bell Shaped Curve

  1. #1
    New Member
    Join Date
    Apr 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  2. #2
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    "A problem well-stated is a problem half-solved." -Charles F. Kettering

  3. #3
    New Member
    Join Date
    Apr 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  4. #4
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    ABCDEFGHIJ
    1123456789
    21200$0.28 $7.17 $72.72 $290.08 $459.51 $290.08 $72.72 $7.17 $0.28

    Spreadsheet Formulas
    CellFormula
    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
    "A problem well-stated is a problem half-solved." -Charles F. Kettering

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,733
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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

  6. #6
    New Member
    Join Date
    Apr 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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%?

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 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")

  8. #8
    New Member
    Join Date
    Aug 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cost Distribution based on Bell Shaped Curve

    Quote Originally Posted by benbulloch View Post
    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/

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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