Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    Chapter 13 Ratio to Moving Average

    Uploaded by

    Ivan Duran

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Chapter 13 Ratio to Moving Average

    Uploaded by

    Ivan Duran
    1 views5 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    1 views5 pages

    Chapter 13 Ratio to Moving Average

    Uploaded by

    Ivan Duran

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 5

    13 Ratio to Moving

    Average Forecasting
    Method

    I n Chapter 12, “Modeling Trend and Seasonality,” you learned how to estimate
    trend and seasonal indices. Naturally you would like to use your knowledge
    of trend and seasonality to make accurate forecasts of future sales. The Ratio to
    Moving Average Method provides an accurate, easy-to-use forecasting method for
    future monthly or quarterly sales. This chapter shows how to use this method to
    easily estimate seasonal indices and forecast future sales.

    Using the Ratio to Moving


    Average Method
    The simple Ratio to Moving Average Forecasting Method is described in this sec-
    tion via examples using data from the Ratioma.xlsx file, which includes sales of a
    product during 20 quarters (as shown in Figure 13-1 in rows 5 through 24). This
    technique enables you to perform two tasks:
    ■ Easily estimate a time series’ trend and seasonal indices.
    ■ Generate forecasts of future values of the time series.

    Using the first 20 quarters for the data exemplified in this chapter, you
    will be able to forecast sales for the following four quarters (Quarters 21
    through 24). Similar to the one in Chapter 12, this time series data has both trend
    and seasonality.
    The Ratio to Moving Average Method has four main steps:

    ■ Estimate the deseasonalized level of the series during each period (using
    centered moving averages).
    ■ Fit a trend line to your deseasonalized estimates (in Column G).
    ■ Determine the seasonal index for each quarter and estimate the future level
    of the series by extrapolating the trend line.
    ■ Predict future sales by reseasonalizing the trend line estimate.

    c13.indd 12/11/13 Page 235


    236 Part III: Forecasting

    Figure 13-1: Example of Ratio to Moving Average Method

    The following sections walk you through each main part of this process.

    Calculating Moving Averages and Centered


    Moving Averages
    To begin, you compute a four-quarter (four quarters eliminates seasonality) moving
    average for each quarter by averaging the prior quarter, current quarter, and next two
    quarters. To do this you copy the formula =AVERAGE(E5:E8) down from cell F6 to
    F7:F22. For example, for Quarter 2, the moving average is (24 + 44 + 61 + 79) / 4 = 52.
    Because the moving average for Quarter 2 averages Quarters 1 through 4 and the
    numbers 1–4 average to 2.5, the moving average for Quarter 2 is centered at Quarter
    2.5. Similarly, the moving average for Quarter 3 is centered at Quarter 3.5. Therefore,
    averaging these two moving averages gives a centered moving average that estimates
    the level of the process at the end of Quarter 3. To estimate the level of the series during
    each series (without seasonality), copy the formula =AVERAGE(F6:F7) down from cell G7.

    c13.indd 12/11/13 Page 236


    Ratio to Moving Average Forecasting Method 237

    Fitting a Trend Line to the Centered Moving Averages


    You can use the centered moving averages to fit a trend line that can be used to
    estimate the future level of the series. To do so, follow these steps:
    1. In cell F1 use the formula =SLOPE(G7:G22,B7:B22) to find the slope of the
    trend line.
    2. In cell F2 use the formula =INTERCEPT(G7:G22,B7:B22) to find the intercept
    of the trend line.
    3. Estimate the level of the series during Quarter t to be 6.94t c + 30.17.
    4. Copy the formula =intercept + slope*B25 down from cell G25 to G26:G28
    to compute the estimated level (excluding seasonality) of the series from
    Quarter 21 onward.

    Compute the Seasonal Indexes


    Recall that a seasonal index of 2 for a quarter means sales in that quar-
    ter are twice the sales during an average quarter, whereas a seasonal index
    of .5 for a quarter would mean that sales during that quarter were one-half of an aver-
    age quarter. Therefore, to determine the seasonal indices, begin by determining for
    each quarter for which you have sales (Actual Sales) / Centered Moving Average. To do
    this, copy the formula =E7/G7 down from cell H7 to H8:H22. You find, for example,
    that during Quarter 1 sales were 77 percent, 71 percent, 90 percent and 89 percent
    of average, so you could estimate the seasonal index for Quarter 1 as the average of
    these four numbers (82 percent). To calculate the initial seasonal index estimates,
    you can copy the formula =AVERAGEIF($D$7:$D$22,J3,$H$7:$H$22) from cell K3
    to K4:K6. This formula averages the four estimates you have for Q1 seasonality.
    Unfortunately, the seasonal indices do not average exactly to 1. To ensure that
    your final seasonal indices average to 1, copy the formula =K3/AVERAGE($K$3:$K$6)
    from cell L3 to L4:L6.

    Forecasting Sales during Quarters 21–24


    To create your sales forecast for each future quarter, simply multiply the trend
    line estimate for the quarter’s level (from Column G) by the appropriate seasonal
    index. Copy the formula =VLOOKUP(D25,season,3)*G25 from cell G25 to G26:G28
    to compute the final forecast for Quarters 21–24. This forecast includes estimates
    of trend and seasonality.

    c13.indd 12/11/13 Page 237


    238 Part III: Forecasting

    If you think the trend of the series has changed recently, you can estimate the
    series’ trend based on more recent data. For example, you could use the centered
    moving averages for Quarters 13–18 to get a more recent trend estimate by using
    the formula =SLOPE(G17:G22,B17:B22). This yields an estimated trend of 8.09 units
    per quarter. If you want to forecast Quarter 22 sales, for example, you take the last
    centered moving average you have (from Quarter 18) of 160.13 and add 4 (8.09)
    to estimate the level of the series in Quarter 22. Then multiply the estimate of the
    Quarter 22 level by the Quarter 2 seasonal index of .933 to yield a final forecast for
    Quarter 22 sales of (160.13 + 4(8.09)) * (.933) = 179.6 units.

    Applying the Ratio to Moving Average


    Method to Monthly Data
    Often the Ratio to Moving Average Method is used to forecast monthly sales as well
    as quarterly sales. To illustrate the application of this method to monthly data, let’s
    look at U.S. housing starts.
    The Housingstarts.xlsx file gives monthly U.S. housing starts (in thousands) for
    the period January 2000 through May 2011. Based on the data through November
    2010, you can apply the Ratio to Moving Average Method to forecast monthly U.S.
    housing starts for the period December 2010 through May 2011. You can forecast a
    total of 3.5 million housing starts, and in reality there were 3.374 million housing
    starts. The key difference between applying the method to monthly and quarterly
    data is that for monthly data you need to use 12-month moving averages to elimi-
    nate seasonality.

    Summary
    In this chapter you learned the following:
    ■ Applying the Ratio to Moving Average Method involves the following tasks:
    ■ Compute four-quarter moving averages and then determine the cen-
    tered moving averages.
    ■ Fit a trend line to the centered moving averages.
    ■ Compute seasonal indices.
    ■ Compute forecasts for future periods.

    ■ You can apply the Ratio to Moving Average Method to monthly data as well
    by following the same process but use 12-month moving averages to eliminate
    seasonality.

    c13.indd 12/11/13 Page 238


    Ratio to Moving Average Forecasting Method 239

    Exercises
    1. The file Walmartdata.xls contains quarterly revenues of Wal-Mart during
    the years 1994–2009. Use the Ratio to Moving Average Method to forecast
    revenues for Quarters 3 and 4 in 2009 and Quarters 1 and 2 in 2010. Use
    Quarters 53–60 to create a trend estimate that you use in your forecasts.
    2. Based on the data in the file airlinemiles.xlsx from Chapter 12, use the
    Ratio to Moving Average Method to forecast airline miles for the remaining
    months in 2012.

    c13.indd 12/11/13 Page 239

    You might also like