Cashflow Spread

[mashburn]

I am trying to spread a budget over a “normal” distribution. After some research, I found the normal distribution functions and formulas that will do this, but I can not figure out why the distributed amount does not match the original amount to be spread. I can get pretty close if I “tweak” the SD and average, but I need to set this up so users only need to put in the start/end dates and the amount to spread.

Cashflow Spread.xlsx
	A	B	C	D
1
2	Start date	5/1/2023	<=Input start date here
3	End Date	8/21/2024	<=Input endate date here
4	Month Periods	17
5	Amount to spread	$1,341,740
6
7	Average	8.50
8	Standard Deviation	2.83
9	1.00	0%	5,898	5,898
10	2.00	1%	13,810	19,708
11	3.00	2%	28,923	48,631
12	4.00	4%	53,735	102,366
13	5.00	7%	88,303	190,669
14	6.00	10%	128,216	318,885
15	7.00	12%	164,431	483,316
16	8.00	14%	186,215	669,531
17	9.00	14%	186,215	855,747
18	10.00	12%	164,431	1,020,177
19	11.00	10%	128,216	1,148,394
20	12.00	7%	88,303	1,236,696
21	13.00	4%	53,735	1,290,431
22	14.00	2%	28,923	1,319,354
23	15.00	1%	13,810	1,333,164
24	16.00	0%	5,898	1,339,062
25	17.00	0%	2,312	1,341,374
26		1.00	1,341,374
27
Example

Cell Formulas
Range	Formula
B4	B4	=DATEDIF(B2,B3+15,"M")+1
B7	B7	=B4/2
B8	B8	=B7/3
A9:A25	A9	=SEQUENCE(B4,1,1,1)
B9:B25	B9	=NORM.DIST(A9#,$B$7,$B$8,FALSE)+(1-NORM.DIST(COUNT(A9#),$B$7,$B$8,TRUE))*2/$B$4
C9:C25	C9	=B9#*$B$5
D9	D9	=C9
D10:D25	D10	=C10+D9
B26:C26	B26	=SUM(B9#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9:B25	Other Type	DataBar		NO
B5	Expression	=MOD(ROW(),4)=0	text	NO
D9:D25	Other Type	DataBar		NO
C9:C25	Other Type	DataBar		NO

 

[JamesCanale]

I think there are two mathematical issues, normal functions are continuous, but in excel they are discrete by necessity. So, some inaccuracies are going to creep in - taking finer steps may help.

Second, real normal functions are infinite, but you are chopping them off where they are lower than 1 or greater than 17. Chopping them off where they are small is a fine approximation, but error creeps in.

A solution might be to stick with the calculated average and SD, make a simple normal distribution. Then get that area (sum) and divide the total to distribute by that number, to get the $ distribution to add up to the original amount.

MrExcelPlayground19.xlsx
	A	B	C	D	E
2	Start date	5/1/2023	<=Input start date here
3	End Date	8/21/2024	<=Input endate date here
4	Month Periods	17
5	Amount to spread	$1,341,740.00
6
7	Average	9.0000000000			0.917802458
8	Standard Deviation	4.898979486		1	$1,341,740.00
9	1	0.021465704	$31,380.82
10	2	0.029340139	42892.495
11	3	0.038466581	56234.487
12	4	0.048373711	70717.769
13	5	0.058349833	85301.913
14	6	0.067510952	98694.598
15	7	0.074922669	109529.82
16	8	0.079754766	116593.89
17	9	0.081433752	119048.41
18	10	0.079754766	116593.89
19	11	0.074922669	109529.82
20	12	0.067510952	98694.598
21	13	0.058349833	85301.913
22	14	0.048373711	70717.769
23	15	0.038466581	56234.487
24	16	0.029340139	42892.495
25	17	0.021465704	31380.82
Sheet4

Cell Formulas
Range	Formula
B4	B4	=DATEDIF(B2,B3+15,"M")+1
B7	B7	=AVERAGE(SEQUENCE(B4))
B8	B8	=STDEV.P(SEQUENCE(B4))
E7	E7	=SUM(B9#)/D8
E8	E8	=SUM(C9#)
A9:A25	A9	=SEQUENCE((B4)*D8,1,1,1/D8)
B9:B25	B9	=NORM.DIST(A9#,$B$7,$B$8,FALSE)
C9:C25	C9	=B9#*$B$5/D8/E7
Dynamic array formulas.