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 |