Hi k9queen!
These are the answers to your questions.
a) In order to build the seasonal indices based on a CMA, we must
first compute a series of moving averages (MA) and then average the
MA. The formula used to calculate the seasonal index for each quarter
can be found in the following Powerpoint presentation, slide 28. The
document calls it "seasonal ratio" instead of seasonal index.
Forecasting
http://www.gsu.edu/~dscgpz/chap2/chapter2.ppt
The following Word document (page 6) is also helpful
Time Series
http://www.geocities.com/sushamamangalam/lecturenotesweek6.doc
In order to easily compute all these averages, I copied the data you
provide into an Excel worksheet and did the calculations with that
program. In any case, the formulas were taken from the links above. I
obtained the following results (these are not the season indices yet).
Quarter Data MA CMA Percentage
1 218
2 247
3 243 250 250.875 96.86098655
4 292 251.75 252.625 115.5863434
1 225 253.5 255 88.23529412
2 254 256.5 257.375 98.68868383
3 255 258.25 259.375 98.31325301
4 299 260.5 261.875 114.176611
1 234 263.25 264.375 88.5106383
2 265 265.5 269 98.51301115
3 264 272.5 274.5 96.17486339
4 327 276.5 278.75 117.309417
1 250 281 284.125 87.98944127
2 283 287.25 290.875 97.29265148
3 289 294.5
4 356
The percentage column is simply the data column, divided by the CMA,
and multiplied by 100. Now, we average the percentages for each
quarter. We get:
Quarter 1: 88.24
Quarter 2: 98.16
Quarter 3: 97.11
Quarter 4: 115.69
Since the average of these four number is 99.8, we multiply them by
100/99.8=1.001 in order to normalize them, thus getting:
Quarter 1: 88.41
Quarter 2: 98.35
Quarter 3: 97.3
Quarter 4: 115.91
So these are the seasonal indices.
b) In order to deseasonalize the data, we simply multiply each data
value by 100/(seasonal index). We get:
Quarter Data Desasonalized
1 218 246.5556045
2 247 251.1252326
3 243 249.7255331
4 292 251.902901
1 225 254.4725276
2 254 258.242142
3 255 262.0576582
4 299 257.9416691
1 234 264.6514287
2 265 269.4258569
3 264 271.3067521
4 327 282.0967418
1 250 282.7472529
2 283 287.7264811
3 289 296.9986793
4 356 307.1144957
As you can see, the spikes in every 4th quarter are smoothed.
In order to compute the trend line, we must run a least squares
regression. The 'explanatory' variable here will be simply a time
index. Therefore, calling Y the explained variable (the actual data)
and X the explanatory variable, you would have to run a regression on
the following data (also adding a constant)
Y X
246.5556045 1
251.1252326 2
249.7255331 3
251.902901 4
254.4725276 5
258.242142 6
262.0576582 7
257.9416691 8
264.6514287 9
269.4258569 10
271.3067521 11
282.0967418 12
282.7472529 13
287.7264811 14
296.9986793 15
307.1144957 16
So, we have to find the coefficients 'a' and 'b' in the following
regression:
Y = a + bX
The formula for a and b can be found in slide 6 in the Powerpoint
presentation I provided above. We get that these values are:
a = 237.28
b = 3.65
So the trend line would then be...
Time Trend
1 240.93
2 244.58
3 248.23
4 251.88
5 255.53
6 259.18
7 262.83
8 266.48
9 270.13
10 273.78
11 277.43
12 281.08
13 284.73
14 288.38
15 292.03
16 295.68
Each value was calculated using the coefficients 'a' and 'b'. For
example, the first value is simply 237.28+3.65*1, the next one is
237.28+3.65*2 ans so on.
c) This forecast can be obtained by simply using as "explanatory
variables" the values 17, 18, 19 and 20, which would correspond to
each quarter of the fifth yeard (recall that the 4th quarter of the
4th year would be the 16th value). We get then:
17 299.33
18 302.98
19 306.63
20 310.28
d) Since the trend forecasts were done using deseasonalized data, we
must now adjust each forecast to see the actual value for each
quarter. This is simply a matter of undoing what we did in question b.
We must take each value and multiply it by (seasonal index)/100. We
then get:
Forecasts
deseasonalized final forecast
17 299.33 264.1441313
18 302.98 297.419657
19 306.63 297.7879181
20 310.28 358.9653847
Notice how we not only get the trend that shows that revenue is
growing quarter after quarter, but we also get the reasonable forecast
that the first quarter will be significantly worse, and the fourth one
will be significantly better (the data provided shows that this is
what happened in the previous 4 years)
Google search strategy
CMA "seasonal index"
I hope this helps! If you have any doubts regarding my answer, please
don't hesitate to request a clarification. Otherwise I await your
rating and final comments.
Best wishes!
elmarto |
Request for Answer Clarification by
k9queen-ga
on
31 Oct 2003 07:28 PST
Hi Elmarto-ga
|
Request for Answer Clarification by
k9queen-ga
on
31 Oct 2003 07:31 PST
Hi Elmarto!
I am unable to view the websites you listed for some reason.
But I want to thank you for all your help, and great answers
in the past, you explain things very clearly and I am able to make sense
from your answers! I will be posting future questions that will require
windows QM program, will you still be able to help?
k9 queen
|
Clarification of Answer by
elmarto-ga
on
31 Oct 2003 10:09 PST
Thank you very much for the very generous tip and the compliments! I'm
really glad that you enjoyed my answers, and of course I'm looking
forward to help you in the future!
Regarding the questions that would require the QM program;
unfortunately, I don't have it. I've been able to download a demo;
however, only 3 of the program's modules are functional. In any case,
I'll do my best in order to help you, and I'm sure that there is a
Researcher out there that has experience with this program.
Finally, regarding the link, I've just checked and they seem to work
just fine. If you have trouble, try right clicking on the link and
choosing "Save Target as...", that might do the trick.
Again, thanks a lot for the tip and comments!
elmarto
|
Request for Answer Clarification by
k9queen-ga
on
02 Nov 2003 11:02 PST
Hi Elmarto,
I am having trouble understanding the Time Trend part
a=237.28
b=3.65
You said in order to get 240.93 you take the a and the b and multiple
x 1
and repeat it with the next being x 2, then x3 etc.
but if I take the 237.28 + 3.65 x 2 I get 481.86 not 244.58
what am I missing?
also on part b- I do not understand where the data deseasonalized info
comes from the: "246.5556045"
|
Clarification of Answer by
elmarto-ga
on
03 Nov 2003 05:20 PST
Hi k9queen!
Regarding the time trend part, maybe the confusion comes from the fact
that I used 'x' meaning 'times', instead of the *. So
237.28 + 3.65*2 = 237.28 + 7.3 = 244.58
The same goes for the other values.
The deseasonalized data is obtained by multiplying each original value
by 100/(seasonal index), where the seasonal index is the one that
corresponds to each quarter. For example, we got that the index for
the 1st quarter is 88.41. Now, since the first observation (218) is in
the 1st quarter, we multiply it by 100/88.41. This gives 246.55...
Again, the same goes for each quarter.
I hope this clarifies the answer! If you need further help, please
don't hesitate to request another clarification.
Best wishes!
elmarto
|
Request for Answer Clarification by
k9queen-ga
on
04 Nov 2003 16:52 PST
Hi Elmarto,
Are you able to answer my problems in Excel?
(it does not have to be windows QM)
K9 queen
|
Clarification of Answer by
elmarto-ga
on
05 Nov 2003 05:30 PST
Hi k9queen!
I don't know what your questions will be about, but if I know how to
answer them, I can sure do it in Excel. Whenever you're ready, post
them and I'll do my best to give a clear answer.
Best wishes and hope to see you around GA soon!
elmarto
|
,
0 Comments
)