Share
Explore BrainMass

Regression - 3 period moving average, and forecasting

1. The chart below gives sales data (in dollars) for McTeale, small auto-parts supplier.

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
2000 414 482 396 530 551 396 365 415 424 485 684 802
2001 457 432 465 598 632 424 392 476 489 555 768 883
2002 505 477 534 636 696 466 442 506 531 610 825 973

i. Plot the sales data in a chart in Excel.
a. Add trend line to the chart
b. Create a separate chart with a 3-period moving average.

ii. Use Excel's regression function to estimate the trend line and produce a forecast for four periods ahead.

iii. What do these charts indicate about the firm's sales over the past 3 years and expected sales for the next four periods?

iv. What do the forecasted values calculated in part ii tell you about the limitations of this type of modeling?

See attached file for full problem description.

Attachments

Solution Preview

1. The chart below gives sales data (in dollars) for McTeale, small auto-parts supplier.

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
2000 414 482 396 530 551 396 365 415 424 485 684 802
2001 457 432 465 598 632 424 392 476 489 555 768 883
2002 505 477 534 636 696 466 442 506 531 610 825 973

i. Plot the sales data in a chart in Excel.
a. Add trend line to the chart

I put the data in an Excel file, then made a chart and added a trend line to the chart:

b. Create a separate chart with a 3-period moving average.

I made a separate column with the 3-period ...

$2.19