Question

Problem description: The quarterly sales for specific educational software over the past five years are given in the following table. YEARYEAR2 YEAR3 YEAR4 YEARS Quarter 11710 1820 1830 1920 2010 Quarter 2 960 Quarter 32720 910 2840 1090 2900 1130 3200 1230 3350 Quarter 4 2430 2200 27402790 You are asked to conduct a time series analysis on this set of data and provide your quarterly forecasts of sales for specific educational software for Year 6. You should use the forecasting tools you leamed in your BSAT 382 class to conduct an appropriate analysis in order to provide the manager with your findings. Your forecasts have to be done on computer, not by hand. Your final answers (typed; NOT hand-written) should be no more than one page long and should include answers to the following questions: (1) How do you know there is a seasonal variation in the data? (2) What are the values of quarterly seasonal indexes? (3) How can you tell if there is an upward, downward or no trend? (4) How do you interpret the values of"a" and "b" (in the context of this problem) in the following trend projection equation? T 5) What are your forecasted quarterly sales for Year 6? a + b * t In addition to your one-page writing, please attach the appropriate computer printouts, and turn it in on November 27, 2018 at the beginning of the class.

Answer 1

Time series analysis and forecasting using time series decomposition method is following:

FORMULAS:

Cell	Formula	Copy to
E2	=FORECAST(C2,$D$2:$D$21,$C$2:$C$21)	E2:E25
F2	=D2/E2	F2:F21
G2	=AVERAGE(F2,F6,F10,F14,F18)	G2:G5
G6	=G2	G6:G25
H2	=E2*G2	H2:H25

(1) In the time series graph, it is noticeable that sales is lowest in quarter 2 and highest in quarter 3 of very year. This pattern repeats every four quarters, this indicates presence of seasonal variation.

(2) Values of seasonal indices are:

Quarter 1 = 0.897

Quarter 2 = 0.501

Quarter 3 = 1.385

Quarter 4 = 1.152

(3) The time series graph of sales shows that every peak and trough is higher than the previous years. This indicates an upward trend.

(4) Values of regression coefficients are:

Intercept, a = 1685.9

Slope, b = 41.195

This means that sales increases by 41.195 every quarter.

(5) Forecast of sales for year 6 is following:

Quarter 1 = 2382

Quarter 2 = 1352

Quarter 3 = 3807

Quarter 4 = 3221