Question

Year	Sales
1	424.50
2	432.00
3	504.00
4	582.00
5	609.00
6	618.00
7	624.00
8	652.50
9	642.00
10	652.50
11	693.00
12	678.00
13	711.00
14	714.00
15	745.50
16	730.50
17	784.50
18	792.00
19	798.00
20	828.00

1.Compute the two-period and four-period moving average predictions for the data set.

a. Prepare a line graph comparing the moving average predictions against the orig-inal data.

b. Do the moving averages tend to overestimate or underestimate the actual data? Why?

c. Compute forecasts for the next 2 years using the two-period and four-period moving average techniques.

2. Use regression analysis to fit a linear trend model to the data set.

a. What is the estimated regression function?

b. Interpret the R2 value for your model.

c. Prepare a line graph comparing the linear trend predictions against the original data.

d. What are the forecasts for the next 2 years using this technique?

Answer 1

1.a

Two period MA forecast for year 3=AVERAGE(B2:B3)=428.25

(drag this cell formula down till year 22)

Four period MA forecast for year 5=AVERAGE(B2:B5)=485.63

(drag this cell formula down till year 22)

b.The moving averages tend to underforecast because the forecast values for each year for both the methods are lower than the original sales values.

c.Moving average forecasts for next two years are highlighted in yellow in the above snapshot.

2.Using excel,we draw the regression line as shown below :

a.The regression equation is ,

Sales=18.34*year+468.1

b.The value of R square=0.919

therefore,the value of r =sqrt(R square)=sqrt(0.919)=0.958

The value of r indicates that there exists a direct and strong relationship between sales and year.

d.Forecast for year 21=18.34*year+468.1=18.34*21+468.1=853.24

Forecast for year 22=18.34*year+468.1=18.34*22+468.1=871.58