Calculate contributions changing at set rate toward known future value

Question

I need to solve a financial math problem. I have a revenue goal set based on target company growth rate. Given this total revenue goal for next year, I need to set sales goals each month that have the growth rate (monthly) applied to them. They will total the annual revenue goal. What this looks like is contributions that increase every occurrence by a set rate. Once I determine either the first or last month's goal, I can discount back or find the future values easily.

The problem I have is that I know what these goals need to total, but not what the first or last goal would equal. Hypothetically, I supposed I could use the mean goal (annual goal/12) to give me the goal for the middle of the year and discount back and scale up from June. However, since there is a growth rate, the compounding causes exponential rather than linear growth of the goals. What kind of formula can I use to solve this? Would I treat this as ongoing (but changing) contributions toward an investment with a set future value and growth rate? Or is there some sort of Goal Solver functionality that will help? I am currently doing this in Google Sheets but can switch to Excel or another medium. (I use R heavily, so not afraid of some programmatic methods).

If I cannot figure this out, I will just apply a linear function to it and use the difference in revenue each year as the slope.

Answer 1

Approach:

Let's assume your business starts in Sep-2017, a Month 0, with S units sold.

The constant growth rate, for each next month, was defined in your Business Case as a q, equal to 8% ( 1.08 )

Month  0: S            [units], be it 1, 3 or 76,538,112,257
Month  1: S * q
Month  2: S * q * q
Month  3: S * q * q * q
..
Month 11: S * q * q * q * ... * q

>>> S = 1
>>> q = 1.08
>>> [ S * ( q ** i ) for i in range( 12 ) ]
[ 1.0,
  1.08,
  1.1664,
  1.2597120000000002,
  1.3604889600000003,
  1.4693280768000005,
  1.5868743229440005,
  1.7138242687795209,
  1.8509302102818825,
  1.9990046271044333,
  2.158924997272788,
  2.331638997054611
  ]

The S units "Scale-free" sum ( independent on the initial amount )

help determine the relation between the target T units sold in total and any S, given q

>>> sum( [ S * ( q**i ) for i in range( 12 ) ] )
18.977126460237237

Here one can see, how inaccurate would be any attempt to use averages and similar guesses to approximate the progress of the powers of q during the period of compounding a constant growth rate ( yielding a T of ~ 19 x the S over 12 months at a given constant rate q of just 8% -- do not hesitate to experiment with other values of q to see the effect sharper and sharper ).

So for an example of a total T of 19,000 units sold during the Year 0, keeping the growth rate of 8% p.m.:

The initial seed for S would be a target T divided by the sum of ( constant growth ) scaling coefficients:

T / sum( [ S * ( q**i ) for i in range( 12 ) ] )

To be on the safer side,

>>> int( 1 + T / sum( [ S * ( q**i ) for i in range( 12 ) ] ) )
1002

>>> sum( [ 1002 * ( q**i ) for i in range( 12 ) ] )
19015.08 ...

>>> [ int( 1002 * ( q**i ) )  for i in range( 12 ) ]
[ 1002,
  1082,
  1168,
  1262,
  1363,
  1472,
  1590,
  1717,
  1854,
  2003,
  2163,
  2336
  ]

Month  0: S                         ~ 1,002 [units]
Month  1: S * q                     ~ 1,082
Month  2: S * q * q                 ~ 1,168
Month  3: S * q * q * q             ~ 1,262
..                                  ~ 1,363
.                                   ~ 1,472
                                    ~ 1,590
                                    ~ 1,717
                                    ~ 1,854
.                                   ~ 2,003
..                                  ~ 2,163
Month 11: S * q * q * q * ... * q   ~ 2,336
_____________________________________________________________
                                     19,012 [unit] per Year 0

So Good Luck & Go Get It Sold!