I need some help with figuring out the ratio-to-moving average. Can someone help me?
Here's the problem:
Sales of roof material, by quarter, since 1994 for Carolina Home Construction, Inc. are shown below (in $000):
Quarter
Year I II III IV
1994 210 180 60 246
1995 214 216 82 230
1996 246 228 91 280
1997 258 280 113 298
1998 279 267 116 304
1999 302 290 114 310
2000 321 291 120 320
a. Determine the typical seasonal patterns for sales using the ratio-to-moving average method.
b. Deseasonalize the data and determine the trend equation.
c. Project the sales for 2001, and then seasonally adjust each quarter.
I do not know if you use Excel or SPSS statistical package but this problem can be solved easily on SPSS.
let me know.
You can look for this chapter .. google it and on page 40 there is a similar example solved using excel
Chapter 16 - Time Series and Forecasting - McGraw-Hill
even better..
here is an example on youtube
Excel - Time Series Forecasting - Part 1 of 3
Ok solving this problem using Excel and 4-year moving average, here are the answers:
a. the seasonality components are as follows: Q1=1.18, Q2=1.14, Q3=0.43, and Q4=1.24
b. the trend equation is Sales = 164.69 (intercepT) + 4.14 (slope) * Time Period
where time period is the quarter. For year 2 quarter 3, the time period would be 7
c. The forecast for year 2001 seasonally adjusted sales is as follows:
Q1= 337.3, Q2=328.5, Q3=126.7 and Q4 369.9
Good luck.
Hussein.elsayed at gmail
To solve this problem using the ratio-to-moving average method, we need to follow several steps.
Step 1: Calculate the moving averages for each quarter.
- To calculate the moving average, add the sales for the current quarter and the sales for the previous three quarters, and then divide by 4.
- For example, to calculate the moving average for Quarter I of 1995, add the sales for Quarter I of 1994, Quarter II of 1994, Quarter III of 1994, and Quarter IV of 1994, and then divide by 4.
Step 2: Calculate the ratios.
- To calculate the ratio, divide the sales for each quarter by the moving average for that quarter.
- For example, to calculate the ratio for Quarter I of 1995, divide the sales for Quarter I of 1995 by the moving average for Quarter I of 1995.
Step 3: Determine the typical seasonal patterns.
- Calculate the average ratio for each quarter.
- If the average ratio for a quarter is greater than 1, it indicates that the sales for that quarter are typically higher than the overall average. If it is less than 1, it indicates that the sales for that quarter are typically lower than the overall average.
Step 4: Deseasonalize the data.
- To deseasonalize the data, divide the sales for each quarter by the average ratio for that quarter.
- This will give you the deseasonalized sales for each quarter, which can be used to determine the trend equation.
Step 5: Determine the trend equation.
- To determine the trend equation, create a scatter plot of the deseasonalized sales over time (quarters).
- Use regression analysis or a line of best fit to determine the equation of the trend line.
Step 6: Project the sales for 2001.
- Use the trend equation to predict the sales for each quarter of 2001.
- Simply substitute the quarter numbers into the trend equation to calculate the projected sales.
Step 7: Seasonally adjust each quarter for 2001.
- To seasonally adjust the sales for each quarter of 2001, multiply the projected sales by the average ratio for that quarter.
By following these steps, you should be able to determine the typical seasonal patterns, deseasonalize the data, determine the trend equation, project the sales for 2001, and then seasonally adjust each quarter.