Normal Distribution in Excel

[Excel_NOOB_1]

Hi, everyone,
I have a quick question for normal distribution in Excel.

There's a construction project with the following monthly expenditures:

JUL 2008 $ 4,903
AUG 2008 $ 3,591
SEP 2008 $ 15,452
OCT 2008 $ 12,806
NOV 2008 $ 59,988
DEC 2008 $ 121,808
JAN 2009 $ 261,162
FEB 2009 $ 264,769
MAR 2009 $ 306,240
APR 2009 $ 438,695
MAY 2009 $ 397,569
JUN 2009 $ 345,190

The project's allocated amount is $12 million and the time period is from July 2008 to December 2010. Is there anyway I can forecast future monthly expenditures (from July 2009 & onwards) based on normal distribution model using Excel 2003?

Your input is greatly appreciated.

Thank you.

Excel Noob.

 

[Cindy Ellis]

I'm not sure it's appropriate to apply a normal distribution to project spend...but assuming you have a basis for it, you can estimate the standard deviation as 1/6 of the project duration (in this case 30 month duration, 5 month s.d.). The midpoint of the project is the end of month 15, so to balance the spend around the midpoint, we need to use 15.5 ((duration +1)/2). Then you can use the normdist function, dividing the nominal result by the ratio of the number of months per standard deviation. The following is an example...just enter the remaining months of the project, and copy the formulas to the first row and copy down.

Excel Workbook
	A	B	C	D
1	Budget	12000000	Duration	30
2	Calendar	Month	sd's from mean	Budget$
3	Jul-08	1	-2.90	14286
4	Aug-08	2	-2.70	25010
5	Sep-08	3	-2.50	42068
6	Oct-08	4	-2.30	67985
7	Nov-08	5	-2.10	105561
8	Dec-08	6	-1.90	157478
9	Jan-09	7	-1.70	225718
10	Feb-09	8	-1.50	310842
11	Mar-09	9	-1.30	411285
12	Apr-09	10	-1.10	522845
13	May-09	11	-0.90	638605
14	Jun-09	12	-0.70	749409
15	Jul-09	13	-0.50	844957
16	Aug-09	14	-0.30	915331
17	Sep-09	15	-0.10	952686
Sheet2

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C3</td><td >=0-(6/$D$1)*(<span style=' color:008000; '>(<span style=' color:#0000ff; '>($D$1+1)</span>/2)</span>-B3)</td></tr><tr><td >D3</td><td >=NORMDIST(C3,0,1,0)/($D$1/6)*$B$1</td></tr><tr><td >C4</td><td >=0-(6/$D$1)*(<span style=' color:008000; '>(<span style=' color:#0000ff; '>($D$1+1)</span>/2)</span>-B4)</td></tr><tr><td >D4</td><td >=NORMDIST(C4,0,1,0)/($D$1/6)*$B$1</td></tr><tr><td >C5</td><td >=0-(6/$D$1)*(<span style=' color:008000; '>(<span style=' color:#0000ff; '>($D$1+1)</span>/2)</span>-B5)</td></tr><tr><td >D5</td><td >=NORMDIST(C5,0,1,0)/($D$1/6)*$B$1</td></tr><tr><td >C6</td><td >=0-(6/$D$1)*(<span style=' color:008000; '>(<span style=' color:#0000ff; '>($D$1+1)</span>/2)</span>-B6)</td></tr><tr><td >D6</td><td >=NORMDIST(C6,0,1,0)/($D$1/6)*$B$1</td></tr></table> </table>
You should validate it against your data...it doesn't look to me that it fits particularly well, but it may be close enough for your purposes.
Hope this helps,
Cindy

 

[Excel_NOOB_1]

Hi, Cindy,

Thank you very much for the spreadsheet. It was very helpful.
I have some questions about the formulas.

Std is assumed to be 5, right? This can be vary depending on how much you think the data might fluctuate, right?

Cell C3 = 0 - (6/$D$1)*((($D$1+1)/2)-B3)
-or-
Cell C3 = 0 - ((15.5) - Current Month)/5

The reason that ((15.5) - Current Month) is divided by 5 is to normalize it?

Cell D3 = NORMDIST(C3,0,1,0)/($D$1/6)*$B$1
-or-
Cell D3 = NORMDIST(C3,0,1,0)/(5) * 12 Million

For this one, I am a little confused on why NORMDIST(C3,0,1,0) is divided by 5. Can you tell me why?

My statistic knowledge is rusty. I apologize if I ask any silly questions.

Thank you.

Excel Noob.