Activity 3

Topics: Microsoft Excel, Time series analysis, Moving average Pages: 9 (806 words) Published: February 15, 2015
﻿
Complete the following case study and problems and submit the results in either a Microsoft Word document or a Microsoft Excel spreadsheet. If you choose to use an Excel spreadsheet, place each problem on a separate sheet and label the tab with problem number. Save your document with a descriptive file name, including the assignment and your name.

Chapter 4:
North-South Airline Case Study: In January 2008, Northern Airlines merged with Southeast Airlines . . . 3-1Data collected on the yearly demand for 50-pound bags of fertilizer at Wallace Garden Supply are shown in the following table.

Year Demand for fertilizer
(1,000s of bags)
1 4
2 6
3 4
4 5
510
6 9
710
811
915
1016
1118

a. Develop a 3-year moving average to forecast sales.

b. Then estimate demand again with a weighted moving average in which sales in the most recent year are given a weight of 3 and a weight of 2 for the second past year and sales in the other 2 years are each given a weight of 1.

c. Which method do you think is best?

In this case, the 3 year moving average is the better method as the Mean Absolute Deviation (MAD) is only 3.042 as compared to 3.347 for the weighted moving average method. What it actually means is that each forecast missed the actual value by 3.042 units instead of 3.347 units.

d. Develop a trend line for the demand for fertilizer using any computer software.

e. You have now developed three different forecasts. These are a 3-year moving average, a weighted moving average, and a trend line. Which one would you use? Explain your answer.

By observing the MAD in part (d), it can be observed that the value is the smallest among the 3 methods, hence I will choose the trend line as the forecast predicted is more accurate. Moreover the demand for the future can also be seen.

3-2Sales of Cool-Man air conditioners have grown steadily during the past 5 years:

YearSales
1 440
2 465
3 520
4 573
5 586
6?

The sales manager had predicted, before the business started, that year 1’s sales would be 410 air conditioners. a. Using exponential smoothing with a weights of a = 0.30, 0.60, and 0.90 develop forecasts for years 2 through 6.

b. What effect did the smoothing constant have on the forecasts for Cool-Man air conditioners?

As the smoothing constant move towards 1, the MAD between the data and forecast decreases enhancing the accuracy of the forecast predicted from 74.014 (a = 0.30), to 52.618 (a = 0.60), to 38.692 (a = 0.90).

c. Which smoothing constant gives the most accurate forecast?

By utilizing the smoothing constant of .90, it provides the most accurate forecast.

d. Use a three-year moving average forecasting model to forecast the sales of Cool-Man air conditioners.

e. Use the trend projection method to develop a forecast for the sales of Cool-Man air conditioners.

f. Which of the five models you developed was the most accurate (best)?

Trend projection method was the most accurate as the MAD is the lowest at 9.04.

3-3A major source of revenue in Texas is a state sales tax on certain types of goods and services. Data are compiled and the state comptroller uses them to project future revenues for the state budget. One particular category of goods is classified as Retail Trade. Four years of quarterly data (in \$millions) for one particular area of southeast Texas follow:

QuarterYear 1Year 2Year 3Year 4
1 218 225234250
2 244 254265283
3 243 255264289
4 298 301334362

a. Compute seasonal indices for each quarter based on a CMA.

b. Deseasonalize the data and develop a trend line on the deseasonalized data.

c. Use the trend line to forecast the sales for each quarter of year 5.

Y5Q1 (Year 5 Quarter 1) corresponds to period 17, Y5Q2 to period 18 and so on. Hence,

Y5Q1 (17) = 314.05
Y5Q2 (18) = 319.24
Y5Q3...