Monthly growth rate

1st, I think this needs to be a math question before it can be programmed. You indicate that you don't want to assume a linear growth curve, so what kind of growth curve do you want? exponential, logarithmic, quadratic, other??

I am limited in my understanding of business math and what assumptions one might choose for this. Linear and exponential are probably the easiest ones to program into Excel, and it seems that exponential growth models are frequently used in these scenarios. A linear trend can be calculated using the TREND() or FORECAST() function. An exponential curve can be calculated using the GROWTH() function. (function help files https://support.office.com/en-us/art...6-c6d90033e188 ).

1) In A2 enter 0, in B2 enter 12
2) IN C2:N2, enter 1 to 12.
3a) for a linear trend, in C1 enter =TREND($A$1:$B$1,$A$2:$B$2,C2) and copy/fill across. note the combination of relative and absolute references to make copying easy.
3b) for an exponential trend, in C1 enter =GROWTH($A$1:$B$1,$A$2:$B$2,C2) and copy fill across.
3c) for a different trend type, regress coefficients for the desired curve and enter the desired formula in C1.

You could enter December's sales, guess at a monthly growth rate, and then use Goal Seek (or Solver) to tweak the growth rate to get the desired total:

	A	B	C
1	Growth Rate	0.06096841
2	Dec Sales	20,000
3	Month	Sales
4	Jan	21,219	B4: =$B$2 * (1 + $B$1) ^ (ROWS(B$3:B4) - 1)
5	Feb	22,513
6	Mar	23,886
7	Apr	25,342
8	May	26,887
9	Jun	28,526
10	Jul	30,265
11	Aug	32,111
12	Sep	34,068
13	Oct	36,146
14	Nov	38,349
15	Dec	40,687
16	Total	360,000	B16: =SUM(B4:B15)

Originally Posted by thomased

A1: 240,000 (last years sales)
B1: 360,000 (next years sales forecast)
C1:N1: next years sales forecast by month (Jan-Dec)
I need a formula to calculate the monthly sales in cells C1:N1.

Personally, I would not forecast monthly sales that way. Be that as it may, try the following paradigm.

	A	B	C
1	Actl Sales'16	240,000.00
2	Avg Sales'16	20,000.00	monthly	B2: =B1/12
3	Est Sales'17	360,000.00
4	Est growth	6.096841%	monthly	B4: =RATE(12, -B2, 0, B3, 1)
5	Jan'17	21,219.37		B5: =$B$2*(1+$B$4)^ROWS($B$5:B5)
6	Feb'17	22,513.08		Copy B5 into B6:B16
7	Mar'17	23,885.67
8	Apr'17	25,341.94
9	May'17	26,886.99
10	Jun'17	28,526.25
11	Jul'17	30,265.45
12	Aug'17	32,110.69
13	Sep'17	34,068.43
14	Oct'17	36,145.52
15	Nov'17	38,349.26
16	Dec'17	40,687.35
17	TOTAL	360,000.00		B17: =SUM(B5:B16)

Notes:

1. It would be better to replace last year's average monthly sales in B2 with last year's sales in Dec. You did not provide that data.

2. If you want next year's monthly sales to go across (e.g. C1:N1), replace ROWS($B$5:B5) with COLUMNS($C$1:C1).

3. We could simplify the formulas in B5 and B6:B16 as follows:

B5: =B2*(1+B4)
B6: =B5*(1+$B$4)
Copy B6 into B7:B16

The advantage of the more complex formula that I use is: it allows us to round the monthly results to a desired precision without cascading the inaccuracy month-to-month.

If you do round, do not expect the sum in B17 to be exactly the same as B3.