Wk6 DQ - Data Analysis & Business Intelligence

Lind_18e_Chap018_PPT.pptx

Home >Business & Finance homework help >Wk6 DQ - Data Analysis & Business Intelligence

Forecasting with Time Series Analysis

Chapter 18

18-1

This chapter shows how to use time-series data to forecast future events. First, we look at the components of a time series. Then, we examine some of the techniques used to analyze time-series data. Finally, we use these techniques to forecast future events.

Learning Objectives

LO18-1 Identify and describe time series patterns.

LO18-2 Compute forecasting using simple moving averages.

LO18-3 Compute and interpret the Mean Absolute Deviation.

LO18-4 Compute forecasts using exponential smoothing.

LO18-5 Compute a forecasting model using regression analysis.

LO18-6 Apply the Durban-Watson statistic to test for autocorrelation.

LO18-7 Compute seasonal indexes and use the indexes to make seasonally adjusted forecasts.

18-2

Components of a Time Series

A time series is a collection of data over a period of time

The trend is the long-run direction of the time series

The seasonal variation is a pattern that tends to repeat itself from year to year for most businesses

TREND PATTERN The change of a variable over time.

SEASONALITY Patterns of highs and lows in a time series within a calendar year. These patterns tend to repeat each year.

18-3

There are four components to a time series, the secular trend, the cyclical variation, seasonal variation, and the irregular variation; the first we examine is the secular trend.

Components of a Time Series Continued

The cyclical component is the fluctuation above and below the long-term trend line over a longer time period

The irregular variation is divided into episodic and residual components

IRREGULAR COMPONENT The random variation in a time series.

CYCLES A pattern of highs and lows occurring over periods of many years.

18-4

The irregular variation is divided in two components; the episodic variations are unpredictable, but they can usually be identified (a flood is an example). The residual variations are random in nature are often called chance fluctuations. Neither episodic nor residual variation can be predicted into the future.

Secular Trend Examples

A graph of the secular trend of the number of Home Depot associates shows how the number has increased over time

The average price of gasoline increased from 2005 to 2013 and since then has declined

18-5

There are four components to a time series; the first we examine is the secular trend. It shows the long-run change over time.

Seasonality Example

Almost all businesses tend to have recurring seasonal patterns

Men’s and women’s apparel have high sales right before Christmas and low sales in January

Sporting good stores will have seasonal fluctuations

18-6

The next time series we examine is seasonal variation. The time period under consideration is usually quarterly or monthly but could be weekly. This example is of quarterly sales of Hercher Sporting Goods, Inc. There is a distinct seasonal pattern to its business. Most of the sales are in the first and second quarters; the third quarter is its slow season. Companies with pronounced seasonal components can mitigate the effects with an offsetting seasonal business, like the ski resorts do with their golf courses. When data is reported annually, there will be no seasonal variation.

Cyclical Variation Example

A typical business cycle consists of a period of prosperity followed by periods of recession, depression, and then recovery

In periods of recession, employment, production, the DJIA, and other business and economic series are below the long-term trend lines

In times of prosperity, they are above the long-term trend lines

18-7

The next time series we examine is the cyclical variation. This example of sales of batteries by National Battery Retailers, Inc. shows the cyclical nature of their business. Even with the cyclical variation, the long-term secular trend is growth.

Moving Averages

A moving average is used to smooth the trend in a time series

It is the basic method used in measuring seasonal fluctuation

To apply a moving average, the data needs to follow a fairly linear trend and have a rhythmic pattern of fluctuations

This is accomplished by “moving” the mean values through the time series

18-8

First, we’ll work with moving averages examples that are not weighted and then we’ll look at an example of a moving average that is weighted. The weighted moving average assigns the most weight to more recent data and less weight to older data; in any case, the sum of the weights must equal 1.

Moving Average Example

18-9

Shown is a time series of the monthly market price for a barrel of oil over 18 months. Use a three-period and a six-period simple moving average to forecast the oil price for May 2019.

With any time series data set, the first task is to graph the data with a line graph. We observe from the graph that oil prices were stable over the 18-month period. Also, over the 18 months a seasonal pattern is not evident. Therefore, using a simple moving average is a good choice to forecast the price of oil for May 2019.

3- and 6- Period Moving Average Example

18-10

Using a three-period simple moving average, the forecast for May 2019 would be the average of the prices from the most recent 3 months: February, March, and April of 2019. The forecast is computed as follows:

Using a six-period simple moving average, the forecast for May 2019 would be the average of the prices from the most recent 6 months: November, December, January, February, March, and April of 2019. The forecast is computed as follows:

Usually business and economic series do not have periods of oscillation of equal length or oscillations of equal amplitudes; therefore, the moving averages will not always result in a precise line. Here, both moving averages seem to adequately describe the trend in production since 1998.

Forecasting Error

18-11

Any estimate or forecast is likely to be imprecise. The error, or lack of precision, is the difference between the actual observation and the forecast.

This difference is called a deviation of the forecast from the actual value.

The mean of the absolute errors is called the mean absolute deviation (MAD).

The three-year moving average is computed by taking three years at a time, summing the attendance numbers and dividing by 3. That means each year is given the same weight, 1/3. We’ll compute the three-year weighted moving average next.

3-Period Moving Average Including Error

18-12

We report that the forecast for May 2019 is $64.50 with a MAD of $5.49. Using a three-period simple moving average, we can expect the forecasted May 2019 oil price to be between $59.01 (found by $64.50 − $5.49) and $69.99 (found by $64.50 + $5.49).

To find the forecasting error for the three-period moving average model, we begin by computing the forecast for February 2018 by averaging the first three periods, November 2017, December 2017, and January 2018. This process starts in February 2018 and moves through the time series to April 2019. Now with the actual observed price and the forecast for each month, we can calculate the error for each monthly forecast.

6-Period Moving Average Including Error

18-13

Using a 6-month moving average model, the MAD or the average variability of forecast error is $7.01. Recalling that the 6-month moving average forecast for May 2019 is $61.06, we can expect the forecasted May 2019 oil price to be between $54.05 (found by $61.06 − $7.01) and $68.07 (found by $61.06 + $7.01).

To find the MAD for a six-period simple moving average model, compute all possible six-period moving averages. May 2018 is the first possible average that can be calculated using six prior values of price.

When using a simple moving average, we must choose the number of past periods to include in the average. The decision can be based on the choice that results in the smallest MAD. In this case, the MAD for the 3-month moving average, $5.49, is smaller, and therefore it is preferred to the 6-month moving average with a MAD of $7.01.

Caution: Do not make any general conclusions about the relationship between the number of periods and the error based on this single example.

Simple Moving Average Comparison

18-14

An outcome of using more periods in a simple moving average is its effect on the variation of the forecasts.

The variation in the forecasts is related to the number of observations in a simple moving average. More periods will reduce the variation in the forecasts.

This figure shows the actual oil prices and the 3-month and 6-month moving average forecasts. You can see that the range of forecast values for the 6-month averages is smaller than the range of forecasts for the 3-month averages.

Simple Exponential Smoothing

18-15

t = time period

t+1 = next time period

Alpha (α) = smoothing constant

Smoothing constant is between 0 and 1

Selecting a smoothing constant value near 1 means that recent data will receive more weight than older data.

SMOOTHING CONSTANT A value applied in exponential smoothing to determine the weights assigned to past observations.

Another time series model used to forecast a stationary pattern is simple exponential smoothing. It differs from a simple moving average because it applies differing weights to past observations.

As we decide the number of periods to include in a simple moving average, exponential smoothing requires us to select a value of α. The value of α determines the relative size of the weights applied to the past observed values in computing the weighted average.

Simple Exponential Smoothing Example

18-16

Using simple exponential smoothing with a smoothing constant of 0.1, the exponential smoothing equation would be:

Forecastt+1 = Forecastt + 0.1(error)t

The forecast for January 2018 is:

ForecastJanuary = ForecastDecember + 0.1(error)December ForecastJanuary = $59.93 + 0.1($1.26) = $60.0560 Forecast ErrorJanuary = $66.23 − $60.06 = $6.1740

The smoothing formula is applied through the time series data until the last possible forecast for May 2019 is made,.

Simple Exponential Smoothing Example Continued

18-17

The smoothing formula is applied through the time series data until the last possible forecast for May 2019 is made.

MAD is then computed:

When using simple exponential smoothing, we must choose a value for the smoothing constant. Similar to simple moving averaging, the decision can be based on the smoothing constant that results in the smallest error. With the oil price time series, large smoothing constants (more than 0.90) result in the smallest MADs. You can verify this result by using Excel to design a worksheet and substituting different values of α.

Simple Exponential Smoothing Example Concluded

18-18

The forecast with the high alpha value, the green line graph, is very responsive to the most recent oil price.

The forecast with the low alpha value, the red line graph, is much smoother and follows the average of oil prices over time.

The weights are determined by the smoothing constant value. If we select a relatively large value of alpha, such as 0.9, 90% of the weight is placed on the most recent observation. The forecasts are mostly determined by the price from the prior period. The remaining 10% of the weight is applied to the past observations. In this case, the forecast is very responsive to the most recent observed value. In contrast, a relatively small value of alpha, such as 0.1, places only 10% of the weight on the most recent observation and then distributes the remaining 90% of the weight more evenly over the past observations. The result is forecasts with less variability.

Time Series with a Trend: Regression Analysis

18-19

If the trend is linear, regression analysis is used to fit a linear trend model to the time series.

This time series is two years of monthly demand data. Each observation is labeled with the month.

Regression analysis is used to model a time series that shows either a positive increasing trend or a negative decreasing trend.

Each observation is sequentially numbered with the variable, time period. The variable, time period, is very important because it is the X-axis variable in the line graph of the data and the independent variable in the trend analysis.

Regression Analysis Example

18-20

We create a line graph of the data. The left graph shows a positive upward trend pattern of demand over time. If we apply an averaging technique such as simple moving averaging or exponential smoothing, these models do not fit the trend pattern. The middle graph shows that a 6-month moving average forecast model does not fit the trending time series; the averaging technique consistently underestimates the trend. The appropriate choice is to model the trend pattern with simple linear regression. The right graph shows the regression result; the trend line effectively fits the time series.

Regression Analysis Example Continued

18-21

The data must be presented in terms of a dependent variable and an independent variable.

The line is represented by the following equation:

Applying the results to the regression equation, the trend forecasting model is:

Demand = 32.63768 + 4.82565 (Time Period)

Applying the trend equation, the forecasts for months 25 and 26 (January and February of year 3) are:

Demand (Time Period 25) = 32.63768 + 4.82565 (25) = 153.2789

Demand (Time Period 26) = 32.63768 + 4.82565 (26) = 158.1046

Regression Analysis Example Concluded

18-22

We now need an estimate of the forecast error.

The absolute value of error for the first time period is:

Absolute Value of Forecast error = |40 − 37.4633| = 2.5367

If a trend forecasting model is used to compute forecasts beyond the next period, the forecast is more uncertain and will have more error. In the next example/solution, we illustrate the application of regression analysis when analyzing a time series with trend.

The Durbin-Watson Statistic

One of the assumptions traditionally used in regression is that the residuals are independent, that is, they’re not correlated

But in time series data, successive residuals are not independent because an event in one time period often influences the event in the next time period

This condition is called autocorrelation

Example

The owner of a furniture store decides to have a sale this month and spends a lot of money advertising the event. We expect a correlation between the two events this month. But it is likely that some of the effects of advertising carries over to the next month

AUTOCORRELATION Successive residuals are correlated.

18-23

If residuals are correlated, problems arise when we try to conduct tests of hypothesis about the regression coefficients.

The Durbin-Watson Statistic Continued

Autocorrelation, r, is the strength of the correlation

But instead of hypothesis testing r, we use d

The Durbin-Watson statistic is used to test for autocorrelation

The value of d can range from 0 to 4. A value of 2 means there is no autocorrelation among the residuals. If it’s close to 0, positive autocorrelation; close to 4, negative

18-24

Notice in the graph of residuals at the top of the slide, there are runs of residuals above and below the 0 line. If we computed the correlation between successive residuals, it is likely the correlation would be strong. We’ll use the Durbin-Watson statistic in our analysis, and we begin by first determining the residuals for each observation.

The Durbin-Watson Statistic Hypothesis Test

Step 1: The null and alternate hypotheses are

H0: No residual correlation (

H1: Positive residual correlation ( > 0)

Step 2: Select the level of significance

Step 3: Select the test statistic; we use d

Find d in Appendix B.9, you’ll need , n, and k

Step 4: The decision rule is altered from what we are used to because this time, there is also a range of values where the data is inconclusive

Step 5: Calculate the test statistic

Step 6: If the null hypothesis is rejected, we conclude that autocorrelation is present

18-25

The critical values are found in Appendix B.9. We need which is the significance level, n is sample size, and k is the number of independent variables. This next example will show the details.

Durbin-Watson Statistic Example

Shown are monthly total sales (in millions of dollars) for the retail and food service industry sourced from the U.S. Census data.

Evaluate the regression results for autocorrelation using the Durbin-Watson statistic.

18-26

Trend forecast equation:

Monthly retail and Food service sales = 412,980.44 + 1399.16 (Time Period)

We’ll determine the regression equation.

Durbin-Watson Statistic Example (2 of 5)

18-27

Residuals are then calculated

The residuals do not show a random pattern distributed around the expected value of zero.

Autocorrelation is indicated by a non-random, downward trend followed by an upward trend.

This pattern shows a strong case for autocorrelation.

Durbin-Watson Statistic Example (3 of 5)

We’ll use an Excel spreadsheet to investigate autocorrelation.

We find , the fitted values, for each of the 20 months. The results are in column E.

Next find the residual, the difference between the actual value and the fitted values, column F.

In column G, we lag the residuals one period.

In H, we find the difference between the current residual and the residual in the previous and square the difference.

In I, we square the values in F.

18-28

To investigate the possible autocorrelation, we’ll need to determine the residuals for each observation. The residual, reported in column F, is slightly different due to rounding in the software.

Durbin-Watson Statistic Example (4 of 5)

We’ll use these results in the hypothesis test on the next slide.

To calculate d, we need the sums of columns H and I.

18-29

This is the Excel spreadsheet from the previous slide.

Durbin-Watson Statistic Example (5 of 5)

Now find the critical values of d

Step 1: State the null and alternate hypothesis

H0: No residual correlation

H1: Positive residual correlation

Step 2: Select the level of significance; we’ll use .05

Step 3: Select the test statistic, d

Step 4: Formulate the decision rule,

reject H0 if d < 1.20 and do not reject H0 if d > 1.41,

no conclusion is reached if d is between 1.20 and 1.41

Step 5: Make decision; reject H0, d = 0.78

Step 6: Interpret: reject H0, autocorrelation is present

18-30

Use Appendix B.9 to find the critical values of d. There are two critical values to use in the decision rule. The graph helps explain. Remember, d was calculated on the previous slide. It was .78, so it falls in the rejection region; there is positive autocorrelation present.

Seasonal Factor

The seasonal factor is estimated using the ratio-to-moving-average method

Seasonal factors are computed on a monthly or a quarterly basis

Example

Line graph shows a sample of monthly bookings (room nights) from hotels, motels, and guest houses in Victoria, Australia

Shows an increasing, positive trend in bookings over the 36 months

Shows seasonality

18-31

For example, the graph shows the highest bookings in the months of October and March, and the lowest bookings in December and June. For the most part, these observations are the basis for concluding that the time series displays seasonality or a seasonal pattern.

Seasonal Factor Continued

18-32

To quantify a seasonal pattern in a time series, we apply the concept of an index.

An index is a quantitative way to compare values.

For each time period in the time series, we will compare the observed value to the value or base predicted by the regression equation.

For the periods with the highest recurring values, the seasonal index will be greater than 1.0.

For periods with the lowest recurring values, the seasonal index will be less than 1.0.

Seasonal Index Example

The line graph shows a sample of monthly bookings (room nights) from hotels, motels, and guest houses in Victoria, Australia. The data spans 3 years, or 36 months. Forecast monthly accommodation bookings for the next 12 months.

The graph shows a pattern of seasonality and a positive increasing trend.

18-33

For any time series analysis, the first task is to graph the data and observe patterns in the data. We repeat the graph here with the trend line. The graph shows a pattern of seasonality and a positive increasing trend. Therefore, our time series analysis will model both of these patterns and account for the randomness or variation with MAD.

Seasonal Index Example (2 of 5)

Step 1: Determine the regression line

Step 2: Calculate the base value for each time period using the regression line.

Step 3: Calculate the index for each time period by taking the y-value divided by the base value.

Step 4: Average the indexes by month to get a monthly index.

18-34

The first step is to fit the time series with a trend line using regression analysis. We use regression analysis in Excel to find the equation of the regression line. We can use the regression equation to calculate the “average” (Base Value) bookings for each time period in the time series. Then we calculate the seasonal effect (Index) for each time period. Finally we average the indexes, by month, to get a monthly index.

Seasonal Index Example (3 of 5)

Step 5: Calculate an estimate of the forecasting error by computing the MAD

18-35

This calculation is applied to all 36 month. The MAD is:

Step 6: Make forecasts of total monthly bookings for the next 12 months.

Period 1 (June) Bookings = [391.098 + 2.628 (Time Period)] (Month Index for June)

Total Monthly Bookings (Period 1) = 391.098 + 2.628 (1) (0.88) = 347.920

Period 5 (October) Bookings = [391.098 = 2.628 (Time Period)] (Month Index for October

Totally Monthly Bookings (Period 5) = 391.098 + 2.628 (5) (1.12) = 451.816

= 10.404

Total Bookings (Period 37) = 391.098 + 2.628 (Time Period)

= 391.098 + 2.628 (37) = 488.341

Seasonal Index Example (4 of 5)

18-36

Step 7: Seasonally adjust the predictions by multiplying by the appropriate monthly index.

Seasonally adjusted forecast (Period 37) = (488.341)(0.88) = 429.740

This information is used by resorts to plan for a variety of decisions such as pricing, the number of people to staff a resort, and the quantities of inventories to hold. All forecasts must include the average forecasting error or the MAD. All forecasts incorporate an average error of 10.404.

Seasonal Index Example (5 of 5)

18-37

The accompanying graph of the bookings data clearly shows a pattern: a clear upward trend and clear seasonality. Compare the line graph of actual bookings (in blue) to the line graph of the forecast bookings (in green). Also, see that the graph shows the forecasts (in green) for the next year, periods 37 through 48. From the graph we observe that time series analysis performed well in replicating the time series pattern.

While the results of a time series forecasting analysis can be very good, every time series forecast must include an important disclaimer: A time series forecast assumes that the patterns in the historical data will continue into the future.

Chapter 18 Practice Problems

18-38

Question 7

18-39

Using simple moving averages and the following time series data, respond to each of the items.

Graph the time series data. What do you observe?

Compute all possible forecasts using a four-period simple moving average model.

Compute all possible forecasts using a six-period simple moving average model.

Compute the MADs for each moving average forecast.

Which forecast has less error?

LO18-2,3

Question 11

18-40

Using simple exponential smoothing and the following time series data, respond to each of the items. (Note the data is the same as exercise 7.)

LO18-4

Graph the time series data. What do you observe?

Compute all possible forecasts using a smoothing coefficient (α) of 0.35.

Compute all possible forecasts using a smoothing coefficient (α) of 0.85.

Compute the MADs for each moving average forecast.

Which forecast model would you choose? Why?

Question 13

18-41

Using the time series data in the table, respond to the following items.

LO18-5

Graph the data.

Based on the graph, describe the time series pattern.

For this time series, why is forecasting with a trend model appropriate?

Evaluate a trend forecasting model using simple linear regression. What is the forecasting error?

What is the predicted annual change of industry sales?

Predict sales for the next three periods.

Question 17

18-42

Using the same time series data and trend forecast model as in exercise 13, respond to the following items.

LO18-6

Plot the residuals associated with the trend model for this data.

Test for autocorrelation using the .05 significance level.

Report and interpret your result.

Question 19

18-43

Using the following time series data, respond to the following items.

LO18-7

Graph the time series.

Based on the graph, describe the time series pattern.

For this time series, why is forecasting with a seasonally adjusted trend model appropriate?

Evaluate a seasonally adjusted trend forecasting model. What is the forecasting error?

What are the quarterly indexes?

Forecast sales for future periods 17 through 20.