Normal Distribution of a Budget over 12 Months

Jason wrote:

I am trying to apply a normal distribution to a budget of $1MM to span 12 months. Is there a function within Excel to help with this?

No.  But you can use statistical theory to develop Excel formulas to accomplish what you want.

However, without further constraints, there is not one single answer.  The following produces a normal distribution of 1MM in columns C and D based on the standard normal distribution.

The formulas are:

A3:     =A2+8/12

B3:     =B2+1

C3:    =(NORMSDIST(A3)-NORMSDIST(A2))*1000000/(1-2*NORMSDIST(-4))

D3:    =ROUND(C3,0)

C16:  =SUM(C3:C14)

D16:  =SUM(D3:D14)

A3:D3 are copied down through A14:D14.

Note that since column D is rounded, the fact that the sum in D16 is exactly 1MM is a coincidence.  In theory, it could be off by as much as +/- 6 (12 * 0.5).

Theory of operation....

Somewhat arbitrarily, we choose the range of the normal distribution to be from -4sd to 4sd, divided into 12 "bins" (like a histogram).  Each "bin" represents a month.  The width of each bin is 8/12 = (4-(-4))/12.

Note that -4sd is the left-hand limit of the first bin, whereas all the other z-scores are the right-hand limits, which is common for Excel "bins".  Therefore, the right-hand limit of the first bin is -4+8/12.  -4sd is effectively the right-hand limit of the bin before the first month.

We want the sum of the 12 bins to be 1MM.  But -4sd to 4sd represents less than 100% of the total area under the complete curve, which extends to +/- infinity.  In fact, -4sd to 4sd represents 1-2*NORMSDIST(-4), which is about 99.994%.

Therefore, the total area under the curve is 1MM/(1-2*NORMSDIST(-4)), where 2*NORMSDIST(-4) represents the area under the left and right tails outside the -4sd to 4sd range.

For each bin, NORMSDIST(A3) represents the percentage of the total area to the left; that is, from -infinity to the right-hand limit of the bin.

Thus, the area under that portion of the curve (the bin) is represented by (NORMSDIST(A3)-NORMSDIST(A2))*total.

As I noted, this is based on the limited specifications that you provided.

In actual practice, you might want to have a minimum amount in the 1st and 12th months.  Or you might want to have a specific average and standard deviation.  (The latter is less likely, IMHO.)

The results with further constraints would be very different.

Say we want the mean to be 6.5 months and the S.D to be 1

In E1 enter 6.5

In F1 enter 1 (the standard deviation)

In G1 enter .7272727272 (first scaling factor)

In A1 enter -4

In A2 enter =A1+$G$1 and copy down thru A12

In B1 enter =A1*F$1+E$1 and copy down thru B12

In C1 enter =NORMDIST(B1,$E$1,$F$1,FALSE) and copy down thru C12

In C13 enter =SUM(C1:C12)

In D1 enter =C1/C$13 and copy down thru D12

D1 thru D12 is the normally distributed money profile.

In A1 thru D12 we see