Deseasonalize the data using the quarterly seasonal factors developed.

Case 16.3 Wagner Machine Works Further Instructions and Guidance
Mary Lindsey has recently agreed to leave her upper-level management job at a major paper manufacturing firm and return to her hometown to take over the family machine-products business.

The U.S. machine-products industry had a strong position of world dominance until recently, when it was devastated by foreign com-petition, particularly from Germany and Japan. Among the many problems facing the American industry is that it is made up of many small firms that must compete with foreign industrial giants.

Wagner Machine Works, the company Mary is taking over, is one of the few survivors in its part of the state, but it, too, faces increasing competitive pressure. Mary’s father let the business slide as he approached retirement, and Mary sees the need for an immediate modernization of their plant.

She has arranged for a loan from the local bank, but now she must forecast sales for the next three years to ensure that the company has enough cash flow to repay the debt. Surprisingly, Mary finds that her father has no forecasting system in place, and she cannot afford the time or money to install a system like that used at her previous company.
Wagner Machine Works’ quarterly sales (in millions of dollars) for the past 15 years are shown in the table.
While looking at these data, Mary wonders whether they can be used to forecast sales for the next three years. She wonders how much, if any, confidence she can have in a forecast made with these data. She also wonders if the recent increase in sales is due to growing business or just to inflationary price increases in the national economy.
Required Tasks:
1. Briefly summarize the data (include an explanation of the results of descriptive statistics of the data, variables included, how the data was collected, and any pertinent information about the data available in the case study)
2. Identify the central issue in the case.
3. Plot the quarterly sales for the past 15 years for Wagner Machine Works.
4. Identify and describe any patterns that are evident in the quarterly sales data.
5. The following steps have been done for you and the results presented in a table of results below. Students are encouraged to reproduce these results for additional insight.
a. If you have identified a seasonal pattern, estimate quarterly seasonal factors.
b. Deseasonalize the data using the quarterly seasonal factors developed.
c. Run a regression model on the deseasonalized data using the time period as the independent variable.
d. Develop a seasonally adjusted forecast for the next three years.
e. Prepare a report that includes graphs and analysis
f. Describe in detail how each of the steps above are carried out using Excel. Consult the textbook for additional information. Students are encouraged to attempt to reproduce the results for their own additional insight and understanding.
6. Explain and interpret the results displayed in the table of results below.
7. Students are required to prepare a report that includes graphs and tables showing the results of the statistical analyses. The report must state the conclusion of the statistical analyses as well as make practical recommendations were appropriate.

Quarter 1 2 3 4
Seasonal Index 1.0209 1.0562 0.8969 1.0193

Regression Statistics
Multiple R 0.983066144
R Square 0.966419044
Adjusted R Square 0.965840062
Standard Error 1652.905207
Observations 60
ANOVA
Df SS MS F Significance F
Regression 1 4560330427 4560330427 1669.169406 1.91522E-44
Residual 58 158461546.1 2732095.623
Total 59 4718791973
Coefficients Standard Error t Stat P-value Lower 95%
Intercept 7047.816202 432.1693866 16.30799501 2.484E-23 6182.735833
Period 503.4104542 12.32173948 40.85546971 1.91522E-44 478.7458313

Year Quarter Period Unadjusted Forecast Seasonal Index Adjusted Forecast
2001 Quarter 1 61 37756 1.0209 38545
Quarter 2 62 38259 1.0562 40408
Quarter 3 63 38763 0.8969 34765
Quarter 4 64 39266 1.0193 40023
2002 Quarter 1 65 39769 1.0209 40600
Quarter 2 66 40273 1.0562 42535
Quarter 3 67 40776 0.8969 36571
Quarter 4 68 41280 1.0193 42075
2003 Quarter 1 69 41783 1.0209 42656
Quarter 2 70 42287 1.0562 44662
Quarter 3 71 42790 0.8969 38377
Quarter 4 72 43293 1.0193 44127

Deseasonalize the data using the quarterly seasonal factors developed.
Scroll to top