I have 13 months worth of monthly data that I want to trend out for about 3
years on a month-by-monty basis. I am using the FORECAST function but am
puzzled by what I am seeing. I get values that visually "make sense" based
on the historical data, but when I calculate the month-over-month rate of
change I see puzzling results.

Here is the data:
Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07

329 369 390 448 442 437 451 504 552 487 574 610 511

So I am projecting out to April 2010. If you try this confirm that you get
1262 as the value for April 2010.

The thing that puzzles me is that if I chart this data it looks perfectly
linear for the forecasted values. Thus I would expect a uniform rate of
growth. But when I look at the month-over month growth rate, it steadily
declines from 3.197% in Jun 2007 to 1.546% by April 2010.

Can anyone help me understand what is going on? Is FORECAST the best way to
do what I am atttempting?

Thanks in advance for any assistance!

FORECAST is predicting growth of roughly 19 per month, every month.
This increase is from a larger base every month. Divide 19 by 601 and
you get 3.16%. Divide 19 by 1242 gives you 1.53%. To grow at a
constant percentage, the amount of growth must rise. In my examples,
to get 3.16% growth on a base of 1242 one must grow by about 39.

Mark Lincoln

On Jun 12, 3:02 pm, BenS wrote:
I have 13 months worth of monthly data that I want to trend out for about 3
years on a month-by-monty basis. I am using the FORECAST function but am
puzzled by what I am seeing. I get values that visually "make sense" based
on the historical data, but when I calculate the month-over-month rate of
change I see puzzling results.

Here is the data:
Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07

329 369 390 448 442 437 451 504 552 487 574 610 511

So I am projecting out to April 2010. If you try this confirm that you get
1262 as the value for April 2010.

The thing that puzzles me is that if I chart this data it looks perfectly
linear for the forecasted values. Thus I would expect a uniform rate of
growth. But when I look at the month-over month growth rate, it steadily
declines from 3.197% in Jun 2007 to 1.546% by April 2010.

Can anyone help me understand what is going on? Is FORECAST the best way to
do what I am atttempting?

Thanks in advance for any assistance!

On Jun 12, 12:02 pm, BenS wrote:
The thing that puzzles me is that if I chart this data it looks perfectly
linear for the forecasted values. Thus I would expect a uniform rate of
growth. But when I look at the month-over month growth rate, it steadily
declines from 3.197% in Jun 2007 to 1.546% by April 2010.

Can anyone help me understand what is going on?
Is FORECAST the best way to do what I am atttempting?

I cannot answer the second question. It really depends on what you
believe is the (more) correct model for your situation.

But I might be able to help you with the first question. It is really
surprising for many people.

FORECAST() is based a on a linear -- straight-line -- formula, as you
say. That is, it assumes a constant __amount__ of change per period,
measured in the units being measured.

If, instead, you expect a constant __percentage__ change per period,
that results in an exponential curve, not a straight line. Use the
GROWTH() function for that.

You might be able to understand the difference better with a concrete
example.

Suppose you start with 10000 and you add 1000 every period. The first
period, 1000/10000 is 10% growth. But the second period, 1000/11000
is 9.09% growth; and the third period, 1000/12000 is 8.33% growth; and
so on. The __percentage__ growth decreases each period because the
constant numerator becomes increasing smaller compared to the
increasing denominator.

In contrast, suppose you start with 10000 and you add 10% every
period. The first period, 10000*10% is 1000. But the second period,
11000*10% is 1100; and the third period, 12100*10% is 1210; and so
on. The __amount__ of change increases each period because the same
percentage of an increasing larger number is an increasing amount.
That is, a percentage of a large pie is bigger than the same
percentage of a small pie. That is really the power of compounding
growth.

Does all that make sense now?

Applying this to your financial situation, you need to decide if
growth by constant amount or by constant rate (percentage) best fits
your real-life situation.

HTH.