statiscal analysis

[Dawn]

Hi, I was wondering if anyone could help me with a
problem. I have data for 10 years on yearly
contributions. What is the best formula to predict the
next 5 years ahead? I typically use the GROWTH and
FORECAST formula but they tend to look at only the last 2
or 3 data entries and make a linear suggestion from that
instead of taking the whole 10 entries and developing an
estimate from that. Is there a way around this or is
this my best hope?

Thanks
Dawn

 

[Dawn]

Both GROWTH (nonlinear exponential) and FORECAST (linear) can use as many
data points as you want upon which to base projections.

That's great....I'm only looking at a 9 year period
anyways. My problem is that normally yearly
contributions aren't linear or exponential and who's to
say they will be for the projected 5 years in advance?
For some the contributions are so up and down, I just
wish the spreadsheet would take this into consideration
and reflect that in the projections.

I recommend that you first plot contributions versus time to identify any
patterns. Then choose (using your business judgment) the appropriate
functional form for fitting the historical data and for

making projections.

If you're going to analyze only the contribution data, your choices are
limited.

This is pretty much my whole point to the project.

 

[Dawn]

Here's something else I wanted to point out.
I followed an example on the Microsoft help in regards to
the Growth funcion. Using the example given at the
bottom regarding sales for the 17 and 18th month, I used
this example and applied the years 2000 to 2008 instead,
as the x correspondents. In doing so, it responded with
the #NUM! remark. By playing with the #'s though, I
found this only came up when the corresponding year you
were looking for (in other words, the new_x's) changed to
1865. At 1864, the proper total came up. Am using the
wrong #'s and should just use 1,2,3,4, etc. or is there a
glitch with the software?

ps...I can send you the document, showing how I figured
this out.

Dawn

 

[Mike Middleton]

Dawn -

My problem is that normally yearly contributions aren't linear or

exponential and who's to say they will be for the projected 5 years in
advance? <

You're the one to say. As I said before: " ... choose (using your business
judgment) the appropriate functional form for fitting the historical data
and for making projections."

For some the contributions are so up and down, I just wish the spreadsheet

would take this into consideration and reflect that in the projections. <

It does. These curve-fitting functions find the best fit that minimizes the
sum of the squared deviations between the actual historical data and the
fitted values.

Here's something else I wanted to point out. I followed an example on the

Microsoft help in regards to
the Growth funcion. Using the example given at the bottom regarding sales
for the 17 and 18th month, I used this example and applied the years 2000 to
2008 instead, as the x correspondents. In doing so, it responded with the
#NUM! remark. By playing with the #'s though, I found this only came up
when the corresponding year you were looking for (in other words, the
new_x's) changed to 1865. At 1864, the proper total came up. Am using the
wrong #'s and should just use 1,2,3,4, etc. or is there a glitch with the
software? <

The #NUM! error code is likely the result of using values (2000,...) that
are too extreme for Excel's internal computations. Your workaround (1,2,...)
is appropriate.

- Mike Middleton, www.usfca.edu/~middleton