I have some merchandise that I am trying to determine what the sales trend
is going to be for the next three months. I want this based off of the 24
months worth of sales I have. My sheet has sales for the last 24 months,
month by month.

Month Sales
1 3
2 4
3 5
4 6
5 7
6 8
7 8
8 7
9 6
10 5
11 4
12 3
13 3
14 4
15 5
16 6
17 7
18 8
19 8
20 7
21 6
22 5
23 4
24 3

25
26
27

I am trying to use the TREND function. For example if my sales were this I
would expect the result for month 25 to be 3, if month 1 & 13 represented
January. The result I get is 5.5 which is nothing more than the average of
all 24 months.

T.I.A.

Look at teh FORECAST function and see if it suits your needs.

Dave
--
Brevity is the soul of wit.


"Grant" wrote:

I have some merchandise that I am trying to determine what the sales trend
is going to be for the next three months. I want this based off of the 24
months worth of sales I have. My sheet has sales for the last 24 months,
month by month.

Month Sales
1 3
2 4
3 5
4 6
5 7
6 8
7 8
8 7
9 6
10 5
11 4
12 3
13 3
14 4
15 5
16 6
17 7
18 8
19 8
20 7
21 6
22 5
23 4
24 3

25
26
27

I am trying to use the TREND function. For example if my sales were this I
would expect the result for month 25 to be 3, if month 1 & 13 represented
January. The result I get is 5.5 which is nothing more than the average of
all 24 months.

T.I.A.

Grant -

When working with time series data, the first step is to "look at the data"
to identify patterns. Then choose an appropriate model. And finally use
Excel to fit an equation.

The TREND and FORECAST worksheet functions are usually used to determine
long-run average behavior. For your obviously-manufactured data, there is no
upward or downward trend, and the TREND and AVERAGE functions do describe
the long-run behavior.

But a plot shows a strong repetitive pattern, and the obvious forecast for
your obviously-manufactured data is 3,4,5 for months 25,26,27.

When the time series data show strong repetitive patterns, the appropriate
model often incorporates seasonality. Chapter 20, Time Series Seasonality,
of my book, Data Analysis Using MS Excel, has step-by-step instructions for
three models: indicator variables, autoregression, and classical time series
decomposition.

- Mike
http://www.mikemiddleton.com

"Grant" wrote in message
...
I have some merchandise that I am trying to determine what the sales trend
is going to be for the next three months. I want this based off of the 24
months worth of sales I have. My sheet has sales for the last 24 months,
month by month.

Month Sales
1 3
2 4
3 5
4 6
5 7
6 8
7 8
8 7
9 6
10 5
11 4
12 3
13 3
14 4
15 5
16 6
17 7
18 8
19 8
20 7
21 6
22 5
23 4
24 3

25
26
27

I am trying to use the TREND function. For example if my sales were this I
would expect the result for month 25 to be 3, if month 1 & 13 represented
January. The result I get is 5.5 which is nothing more than the average of
all 24 months.

T.I.A.

Often statistics only serve to confuse. I would guess that these are quite
high value items, otherwise you would not be too bothered about them. You
have very few numbers to base a forecast on. If you have many different
customers and there are a large number of potential customers then a
reliable forecast might be impossible. If you have only a small number of
customers you might get a better idea by ringing some of them up, asking
such things as how they got on with your product, how it compared to
competitors, any suggestions for improvements, and, BTW, are you in the
market for more soon?


"Grant" wrote in message
...
I have some merchandise that I am trying to determine what the sales trend
is going to be for the next three months. I want this based off of the 24
months worth of sales I have. My sheet has sales for the last 24 months,
month by month.

Month Sales
1 3
2 4
3 5
4 6
5 7
6 8
7 8
8 7
9 6
10 5
11 4
12 3
13 3
14 4
15 5
16 6
17 7
18 8
19 8
20 7
21 6
22 5
23 4
24 3

25
26
27

I am trying to use the TREND function. For example if my sales were this I
would expect the result for month 25 to be 3, if month 1 & 13 represented
January. The result I get is 5.5 which is nothing more than the average of
all 24 months.

T.I.A.