Forecasting



Forecasting

Why forecast?

Features Common to all Forecasts

• Conditions in the past will continue in the future

• Rarely perfect

• Forecasts for groups tend to be more accurate than forecasts for individuals

• Forecast accuracy declines as time horizon increases

Elements of a Good Forecast

• Timely

• Accurate

• Reliable (should work consistently)

• Forecast expressed in meaningful units

• Communicated in writing

• Simple to understand and use

Steps in Forecasting Process

• Determine purpose of the forecast

• Establish a time horizon

• Select forecasting technique

• Gather and analyze the appropriate data

• Prepare the forecast

• Monitor the forecast

Types of Forecasts

• Qualitative

o Judgment and opinion

o Sales force

o Consumer surveys

o Delphi technique

• Quantitative

o Regression and Correlation (associative)

o Time series

Forecasts Based on Time Series Data

• What is Time Series?

• Components (behavior) of Time Series data

o Trend

o Cycle

o Seasonal

o Irregular

o Random variations

Naïve Methods

Naïve Forecast – uses a single previous value of a time series as the basis of a forecast.

[pic]

Techniques for Averaging

• What is the purpose of averaging?

• Common Averaging Techniques

o Moving Averages

o Exponential smoothing

Moving Average

[pic]

Exponential Smoothing

[pic]

Techniques for Trend

Linear Trend Equation

[pic]

[pic]

Curvilinear Trend Equation

[pic]

[pic]

Techniques for Seasonality

• What is seasonality?

• What are seasonal relatives or indexes?

• How seasonal indexes are used:

o Deseasonalizing data

o Seasonalizing data

• How indexes are computed (see Example 7 on page 109)

Accuracy and Control of Forecasts

Measures of Accuracy

o Mean Absolute Deviation (MAD)

o Mean Squared Error (MSE)

o Mean Absolute Percentage Error (MAPE)

Forecast Control Measure

o Tracking Signal

Mean Absolute Deviation (MAD)

[pic]

Mean Squared Error (or Deviation) (MSE)

[pic]

Mean Square Percentage Error (MAPE)

[pic]

Tracking Signal

[pic]

Problems:

2 – Plot, Linear, MA, exponential Smoothing

5 – Applying a linear trend to forecast

15 – Computing seasonal relatives

17 – Using indexes to deseasonalize values

26 – Using MAD, MSE to measure forecast accuracy

[pic] Problem 2 (110)

National Mixer Inc., sells can openers. Monthly sales for a seven-month period were as follows:

| |Sales |

|Month |(000 units) |

|Feb |19 |

|March |18 |

|April |15 |

|May |20 |

|June |18 |

|July |22 |

|August |20 |

a) Plot the monthly data on a sheet of graph paper.

b) Forecast September sales volume using each of the following:

1) A linear trend equation

2) A five-month moving average

3) Exponential smoothing with a smoothing constant equal to 0.20, assuming March forecast of 19(000)

4) The Naïve Approach

5) A weighted average using 0.60 for August, 0.30 for July, and 0.10 for June

c) Which method seems least appropriate? Why?

d) What does use of the term sales rather than demand presume?

EXCEL SOLUTION

(a) Plot of the monthly data

[pic]

[pic]

How to superimpose a trend line on the graph

• Click on the graph created above (note that when you do this an item called CHART will appear on the Excel menu bar)

• Click on Chart > Add Trend Line

• Click on the most appropriate Trend Regression Type

• Click OK

[pic]

[pic]

(b) Forecast September sales volume using:

1) Linear Trend Equation

• Create a column for time period (t) codes (see column B)

• Click Tools > Data Analysis > Regression

• Fill in the appropriate information in the boxes in the Regression box that appears

[pic]

[pic]

2) Five-month moving average

[pic]

3) Exponential Smoothing with a smoothing constant of 0.20, assuming March forecast of 19(000)

• Enter the smoothing factor in D1

• Enter “19” in D5 as forecast for March

• Create the exponential smoothing formula in D6, then copy it onto D7 to D11

[pic]

4) The Naïve Approach

[pic]

5) A weighted average using 0.60 for August, 0.30 for July, and 0.10 for June

[pic]

[pic]Problem 5 (110)

A cosmetics manufacturer’s marketing department has developed a linear trend equation that can be used to predict annual sales of its popular Hand & Foot Cream.

yt =80 + 15 t

where: yt = Annual sales (000 bottles) t0 = 1990

a) Are the annual sales increasing or decreasing? By how much?

b) Predict annual sales for the year 2006 using the equation

[pic]Problem 15 (113)

Obtain estimates of daily relatives for the number of customers at a restaurant for the evening meal, given the following data. (Hint: Use a seven-day moving average)

|Day |Number Served |Day |Number Served|

|1 |80 |15 |84 |

|2 |75 |16 |77 |

|3 |78 |17 |83 |

|4 |95 |18 |96 |

|5 |130 |19 |135 |

|6 |136 |20 |140 |

|7 |40 |21 |37 |

|8 |82 |22 |87 |

|9 |77 |23 |82 |

|10 |80 |24 |98 |

|11 |94 |25 |103 |

|12 |125 |26 |144 |

|13 |135 |27 |144 |

|14 |42 |28 |48 |

Excel Solution

• Type a 7-day average formula in E6 ( =average(C3:c9) )

• In F6, type the formula =C6/E6

• Copy the formulas in E6 and F6 onto cells E7 to E27

• Compute the average ratio for Day 1 (see formula in E12)

• Copy and paste the formula in E12 onto E13 to E18 to complete the indexes for Days 2 to 7

[pic]

[pic]Problem 17 (113) – Using indexes to deseasonalize values

New car sales for a dealer in Cook County, Illinois, for the past year are shown in the following table, along with monthly (seasonal) relatives, which are supplied to the dealer by the regional distributor.

| |Units Sold | | |Units Sold | |

|Month | |Index |Month | |Index |

|Jan |640 |0.80 |Jul |765 |0.90 |

|Feb |648 |0.80 |Aug |805 |1.15 |

|Mar |630 |0.70 |Sept |840 |1.20 |

|April |761 |0.94 |Oct |828 |1.20 |

|May |735 |0.89 |Nov |840 |1.25 |

|Jun |850 |1.00 |Dec |800 |1.25 |

a) Plot the data. Does there seem to be a trend?

b) Deseasonalize car sales

c) Plot the deseasonalized data on the same graph as the original data. Comment on the two graphs.

Excel Solution

a) Plot of original data (seasonalized car sales)

[pic]

b) Deseasonalized Car Sales

[pic]

c) Graph of seasonalized car sales versus deseasonalized car sales

[pic]

[pic]Problem 26 (115) – Using MAD, MSE, and MAPE to measure forecast accuracy

Two different forecasting techniques (F1 and F2) were used to forecast demand for cases of bottled water. Actual demand and the two sets of forecasts are as follows:

| | |Predicted Demand |

|Period |Demand |F1 |F2 |

|1 |68 |66 |66 |

|2 |75 |68 |68 |

|3 |70 |72 |70 |

|4 |74 |71 |72 |

|5 |69 |72 |74 |

|6 |72 |70 |76 |

|7 |80 |71 |78 |

|8 |78 |74 |80 |

a) Compute MAD for each set of forecasts. Given your results, which forecast appears to be the most accurate? Explain.

b) Compute MSE for each set of forecasts. Given your results, which forecast appears to be the most accurate? Explain.

c) In practice, either MAD or MSE would be employed to compute forecast errors. What factors might lead you to choose one rather than the other?

d) Compute MAPE for each data set. Which forecast appears to be more accurate?

Excel Solution

[pic]

-----------------------

Coded time period

Sales data

Coded time period

Create formula in F6 (see circled formula), then copy onto F7 to F17

=(c7-d7)^2

=ABS(c7-d7)/c7

=AVERAGE(G8:G15)

=SUM(J8:J15)/(COUNT(J8:J15)-1)

=AVERAGE(M8:M15)

=ABS(c7-d7)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download