Getting an semi accurate forecast

[Guest]

Think retail sales....I have data from last 2 years of monthly sales, and I am looking for a formula that would take into effect increases or decreases in sales trends year to year but also the month to month trend and the end result would be the forecast of upcoming months sales based on these trends. My feelings are that current trends in increases or decreases should carry more weight than say this April to last April. What I am trying to say is that say Last year to this year growth only shows 2% but since I took over the last couple months have shown increases anywhere from 4 to 8 percent, what formula helps me budget upcoming months sales that incorporates this new found increase

 

[Chris Rogers]

you may want to graph your data and then add a trend line with various
weightings to the months in the details of the trend line.

 

[Michael R Middleton]

Chapter 20, "Time Series Seasonality," of my book "Data Analysis Using
Microsoft Excel: Updated for Office XP," has step-by-step details for three
methods: regression using indicator variables, autoregression, and classical
time series decomposition. If you search for those phrases using Google
Groups, you may find some online assistance.

- Mike

www.mikemiddleton.com

 

[Herbert Seidenberg]

Mike:
Got your book and it is great.
Can you please tackle the data analysis problem I posted
on Feb 6, "wrong personal.xls reference"?
Herb

 

[Michael R Middleton]

Herb -

Thanks for the feedback about the book.

I cannot replicate your personal.xls problem. I don't use a personal.xls
workbook, but when I created one with an innocuous macro (for bolding a
selection) and restarted Excel, the Anova tool still made the correct
reference (Excel 2003 SP1, Windows XP Home). Perhaps there's a macro in your
personal.xls that causes the problem you're seeing.

- Mike

++++++++++++++++++++++++++++++++++

Mike: Got your book and it is great. Can you please tackle the data
analysis problem I posted on Feb 6, "wrong personal.xls reference"? Herb <
++++++++++++++++++++++++++++++++++

Why do the Data Analysis tools reference the Personal.xls instead of the
current workbook?

Example:
1. Tools | Data Analysis | Anova... | Input Range:
2. Select A1:A10
3. The Input Range field will show: [Personal.xls]Sheet1!$A$1:$A$10
4. If I unhide the Personal.xls and close it, the Analysis Tool will
reference the current workbook.
Same problem on Excel 2002/Windows ME and Excel 2003/Windows XP. <