Gaussian curve

[paul higginson]

Hello,

I trying to realistically estimate capital expenditure for a building project.
For example if the project was to cost £12M and last 12 months the expenditure would be highest over months 3/4/5/6 etc but much less over 10/11/12.
Can anyone help me take this data and distribute it over a Gaussian curve?

Thank you.

Paul

 

[lrobbo314]

It has been over 15 years since I've done anything with with statistics.

So don't look too hard to me for an explanation on the math or how to adjust it. If you bring the amplitude closer to 0, it will increase the amount of money being allocated to the later months. If you bring the amplitude further from 0, the less money will be allocated to those months.

EXCEL
	A	B	C	D	E	F	G	H
1	Month	Map	Norm	X	$ Per Month		Total:	12,000,000
2	1	-1	0.241971	2903649	$924,278.42		Amplitude	-1
3	2	-0.72727	0.306235	3674824	$1,169,756.09		XSUM:	37,698,364
4	3	-0.45455	0.359787	4317439	$1,374,310.68		DIFF:	0.3183162
5	4	-0.18182	0.392402	4708829	$1,498,896.45
6	5	0.090909	0.397297	4767566	$1,517,593.46
7	6	0.363636	0.373419	4481028	$1,426,383.70
8	7	0.636364	0.325817	3909810	$1,244,555.86
9	8	0.909091	0.263906	3166874	$1,008,067.29
10	9	1.181818	0.198437	2381239	$757,987.00
11	10	1.454545	0.138513	1662158	$529,091.88
12	11	1.727273	0.089755	1077057	$342,844.78
13	12	2	0.053991	647891.6	$206,234.39
14				SUM:	$12,000,000.00
Sheet3

Cell Formulas
Range	Formula
A2:A13	A2	=SEQUENCE(12)
B2:B13	B2	=$H$2+(($H$2*-3)/(MAX($A$2#)-MIN($A$2#))*(A2-MIN($A$2#)))
C2:C13	C2	=NORM.S.DIST(B2,FALSE)
D2:D13	D2	=C2:C13*H1
E2:E13	E2	=D2#*H4
H3	H3	=SUM(D2#)
H4	H4	=H1/H3
E14	E14	=SUM(E2#)
Dynamic array formulas.

 

[paul higginson]

Dear lrobbo314,
Thank you, that's really helpful.
I can see that this plots a graph close to what I'm after. See below
I can see how to adjust the periods and the overall value together with the peak point, but is it possible to adjust this to obtain a start and finish at zero or near to zero?

 

[lrobbo314]

Like I said, I'm not great with statistics, so I'm just playing around with numbers here. But, how about this...?

Gaussian Distribution
	A	B	C	D	E	F	G	H
1	Month	Map	Norm	X	$ Per Month		Total:	12,000,000
2	19	7.3	1.07E-12	1.28E-05	$0.00		Amplitude	-1.7
3	1	-1.7	0.094049	1128589	$579,009.30		XSUM:	23,390,068
4	2	-1.2	0.194186	2330233	$1,195,498.51		DIFF:	0.5130383
5	3	-0.7	0.312254	3747047	$1,922,378.58
6	4	-0.2	0.391043	4692512	$2,407,438.36
7	5	0.3	0.381388	4576654	$2,347,998.49
8	6	0.8	0.289692	3476299	$1,783,474.20
9	7	1.3	0.171369	2056423	$1,055,023.73
10	8	1.8	0.07895	947401.9	$486,053.42
11	9	2.3	0.028327	339924.5	$174,394.25
12	10	2.8	0.007915	94985.42	$48,731.15
13	19	7.3	1.07E-12	1.28E-05	$0.00
14				SUM:	$12,000,000.00
Sheet3

Cell Formulas
Range	Formula
B2:B13	B2	=$H$2+((9)/(MAX($A$2:$A$13)-MIN($A$2:$A$13))*($A$2:$A$13-MIN($A$2:$A$13)))
C2:C13	C2	=NORM.S.DIST(B2#,FALSE)
D2:D13	D2	=C2#*H1
E2:E13	E2	=D2#*H4
H3	H3	=SUM(D2#)
H4	H4	=H1/H3
E14	E14	=SUM(E2#)
Dynamic array formulas.

 

[paul higginson]

Genius
Thank you very much.