S Curve dilemma

tmontana · Apr 2, 2010

I have been provided monthly construction costs over an 11 month period that have been plotted using an S-curve weighting. Basically, the total construction cost number is $16,469,000 and is allocated as follows over 11 months:

<TABLE style="WIDTH: 58pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=77 border=0 x:str><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 3285" width=77><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right width=77 height=15 x:num="413863">$413,863 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="982200">$982,200 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1433313">$1,433,313 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1767204">$1,767,204 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1983871">$1,983,871 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="2083316">$2,083,316 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="2065537">$2,065,537 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1930535">$1,930,535 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1678310">$1,678,310 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="1308861">$1,308,861 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="822190">$822,190 </TD></TR></TBODY></TABLE>

This really is a bell curve. How would I go about plotting it without hardcoding the numbers? There must be some formula based on the number of months.

Thanks

tmontana · Apr 2, 2010

I can't seem to get it to work. The proportions you had given me (under the normal distribution) are equal, that is month 2 = month 11, month 3 = month 10, month 4 = month 9 and so on. The distribution I have is not identical.

Greg Truby · Apr 3, 2010

Correct. As I said, you would need to play with the standard deviation breakpoints to get it where you want them.

tusharm · Apr 3, 2010

The equation you showed is in no way going to generate a S curve.

You need to talk to someone in your industry who knows more about how this cost allocations are done. Maybe, that is Greg but if not an Excel forum is not the right place to find out how a particular industry manages cost allocation estimates.

tmontana said:
The issue is I have hundreds of different cost item (i.e. consruction costs, architectural costs, legal fees, broker fees, etc.) spread across various periods of time. What I do know is they all follow the same distribution pattern, which I believe is called the S curve. I need to create a formula that mimics this pattern so that i have the flexibility of changing my timing without having to manually change the weights each time.

I think the formula is based on some ploynomial function such as:

y = a +b(x)^2 +c(x)^3,

where y is the weight from 0% to 100% and x is the number of months. But I can't seem to get it to match.

Greg Truby · Apr 3, 2010

tusharm said:
...Maybe, that is Greg ...

I think it's pretty obvious by now that it ain't me!

He obviously is generating a cost allocation breakout that approximates a bell curve and a cumulative sum of a bell (normal) distribution does generate "S" curves when charted. Best we can do is what we've done - show the generic tools that can be tweaked to yield results akin to those described. As you say, if there is some other, very specific, cost allocation equation that is to be followed, that needs to come from another source (though I suppose there's a small possibility that a MrExcel member might know the OP's industry's standard cost allocation procedures).

igetafix · Aug 29, 2011

I have a spreadsheet that does this for you.

VB looks like this but I did not write it....

Function last_day(m, y)
Select Case m
Case 4, 6, 9, 11
last_day = 30
Case 2
date1 = DateValue("28-2-" & y)
If Month(date1 + 1) = 2 Then last_day = 29 Else last_day = 28
Case Else
last_day = 31
End Select
End Function

Function scurve(total, first, last, point)
x = (point - first) / (last - first)
y = x - 0.016 * x ^ 2 + 0.016 * x - 1 / 3.021 * (6 * x ^ 3 - 9 * x ^ 2 + 3 * x)
scurve = total * y
If x <> 1 Then scurve = Int(scurve / 1000) * 1000
End Function

Function valdate(no, first, last, dom, lag)
If lag < 1 Then lag = 14
last = DateValue(last)
d = Day(first)
m = Month(first) - 1 + no
y = Year(first)
first_val = d + lag
If m > 12 Then
m = m - 12
y = y + 1
End If
If dom = "Last day" Or dom > last_day(m, y) Then dom = last_day(m, y)
If dom < d Then m = m + 1
If dom < first_val Then
If dom = last_day(m, y) Then dom = last_day(m + 1, y)
m = m + 1
End If
If dom > last_day(m, y) Then dom = last_day(m, y)
valdate = DateSerial(y, m, dom)
If valdate > last Then valdate = last
End Function

HarryS · Sep 19, 2011

Tru something along these lines

Code:

Private Sub CommandButton1_Click()
    doSpread "4/11/11", 10, 20000
End Sub
Sub doSpread(DateStart As Date, nMonths%, Amt!)
    Dim Mv!, Prob1V!, Prob2V!, Steps!, RR%
    Set ra = Range("d4")    ' where the column data to start
    Steps = 4 / nMonths    ' only doing -2z to + 2z
    For Mv = -2 + Steps To 2 - Steps Step Steps
        Prob2V = Application.WorksheetFunction.NormDist(Mv, 0, 1, True)
        RR = RR + 1
        ra(RR, 0) = DateStart + DateSerial(0, RR, 0)
        ra(RR, 1) = (Prob2V - Prob1V) * Amt
        ra(RR, 2) = ra(RR, 1) + ra(RR - 1, 2)
        Prob1V = Prob2V
    Next Mv
    RR = RR + 1
    ra(RR, 0) = DateStart + DateSerial(0, RR, 0)
    ra(RR, 1) = (1 - Prob2V) * Amt
    ra(RR, 2) = ra(RR, 1) + ra(RR - 1, 2)
End Sub

benbulloch · Feb 20, 2014

I've been screwing around with the exact question for quite some time and have a good normally-distributed s-curve generation formula for spreading project costs over a number of months. 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: http://www.mrexcel.com/forum/excel-...cross-months-gamma-distribution-calculus.html

In any event see the formula here that I believe elegantly solves the original poster's s-curve problem...now to figure out a skewed cost distribution (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)

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
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
4 002-01 Construction Construction  $ 10,000,000  $ -  $ 121,951 Yes Mar-14 Dec-14 10 S-Curve 5.0 Ok  779,273  913,999  1,030,119  1,115,617  1,160,992  1,160,992  1,115,617  1,030,119  913,999  779,273  -

<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)
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)
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,9)
K4 =+(YEAR($J4)-YEAR($I4))*12+MONTH($J4)-MONTH($I4)+1
N4 =+IF($E4=SUM(Q4:FE4),"Ok","Error")

<tbody>

</tbody>

<tbody>

</tbody>

HarryS · Feb 20, 2014

To deal with other distributions ... Get a series of points that approximate the distribution you want...use a scatter plot... fit a polynomial of say degree 5... get its the function coefficients... use integration to get area function. get total area... step through the points so that the probability is area segment / total area and this multiplied total cost should distribute your costs to the distribution that you chose.

S Curve dilemma

tmontana

Board Regular

tmontana

Board Regular

Excel Facts

Greg Truby

MrExcel MVP

tusharm

MrExcel MVP

Greg Truby

MrExcel MVP

igetafix

New Member

HarryS

Board Regular

benbulloch

New Member

HarryS

Board Regular

Similar threads

Forum statistics

Share this page

	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
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
4	002-01	Construction	Construction	$ 10,000,000	$ -	$ 121,951	Yes	Mar-14	Dec-14	10	S-Curve	5.0	Ok				779,273	913,999	1,030,119	1,115,617	1,160,992	1,160,992	1,115,617	1,030,119	913,999	779,273	-

S Curve dilemma

Board Regular

Board Regular

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

Board Regular

New Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock