Demand Forecasting:Time Series Models

Professor Stephen R. LawrenceCollege of Business and AdministrationUniversity of ColoradoBoulder, CO 80309-0419

Forecasting HorizonsLong Term5+ years into the futureR&D, plant location, product planningPrincipally judgement-basedMedium Term1 season to 2 yearsAggregate planning, capacity planning, sales forecastsMixture of quantitative methods and judgementShort Term1 day to 1 year, less than 1 seasonDemand forecasting, staffing levels, purchasing, inventory levelsQuantitative methods

Short Term Forecasting:Needs and UsesScheduling existing resourcesHow many employees do we need and when?How much product should we make in anticipation of demand?Acquiring additional resourcesWhen are we going to run out of capacity?How many more people will we need?How large will our back-orders be?Determining what resources are neededWhat kind of machines will we require?Which services are growing in demand? declining?What kind of people should we be hiring?

Types of Forecasting ModelsTypes of ForecastsQualitative --- based on experience, judgement, knowledge;Quantitative --- based on data, statistics;Methods of ForecastingNaive Methods --- eye-balling the numbers;Formal Methods --- systematically reduce forecasting errors;time series models (e.g. exponential smoothing);causal models (e.g. regression).Focus here on Time Series ModelsAssumptions of Time Series ModelsThere is information about the past;This information can be quantified in the form of data;The pattern of the past will continue into the future.

Forecasting ExamplesExamples from student projects: Demand for tellers in a bank;Traffic on major communication switch;Demand for liquor in bar;Demand for frozen foods in local grocery warehouse.Example from Industry: American Hospital Supply Corp.70,000 items;25 stocking locations;Store 3 years of data (63 million data points);Update forecasts monthly;21 million forecast updates per year.

Simple Moving AverageForecast Ft is average of n previous observations or actuals Dt :Note that the n past observations are equally weighted.Issues with moving average forecasts: All n past observations treated equally;Observations older than n are not included at all;Requires that n past observations be retained;Problem when 1000's of items are being forecast.

Simple Moving AverageInclude n most recent observationsWeight equallyIgnore older observations

weighttoday123...n1/n

Moving Averagen = 3

Welcome

Tools for Managers

Time-Series Forecasting

Professor Stephen R. Lawrence

College of Business and Administration

Graduate School of Business and Administration

University of Colorado

Boulder, CO 80303

303/492-4351

Stephen.Lawrence@Colorado.Edu

&A

Page &P

Instructions

Time-Series Forecasting

Instructions

Throughout this workbook, cells in which you are to enter data are shaded in light green.

Cells which show calculated results are shaded in light yellow with red type.

Many cells are commented with informational notes. You should see a red triangle in the

upper right corner of many cells. If you do not, turn on commenting by selecting menu

item VIEW | COMMENTS.

Other informational cells are shown in white with black type.

Step 1:Move to worksheet "Work" and remove old data (reset) by clicking on the CLEAR button.

Step 2:Move to worksheet "Data" and enter array of Dates and Observations (Obs).

Note that Dates must be in number or date format, and Observations must be numbers.

Move to worksheet "Data Chart" and study the chart of the time-series data.

In particular, note if the time-series shows seasonality, and if so, its period.

Step 3:Return to "Work" worksheet and enter required data in green shaded cells.

Time-Series NameThe name you wish to give the time series data

Periods per SeasonThe number of periods in a season if the data shows seasonality

Periods ForwardThe number of periods into the future for each forecast (usually 1)

Time-Series UnitThe units of the demand data (e.g., cases, customers, etc.)

Time UnitThe time units of the data (e.g., months, weeks, years, )

alphaThe base smoothing constant (0

Example:

Moving Average Forecasting

Exponential Smoothing IInclude all past observationsWeight recent observations much more heavily than very old observations:

weighttodayDecreasing weight given to older observations

Exponential Smoothing IInclude all past observationsWeight recent observations much more heavily than very old observations:

weighttodayDecreasing weight given to older observations

Exponential Smoothing IInclude all past observationsWeight recent observations much more heavily than very old observations:

weighttodayDecreasing weight given to older observations

Exponential Smoothing IInclude all past observationsWeight recent observations much more heavily than very old observations:

weighttodayDecreasing weight given to older observations

Exponential Smoothing: ConceptInclude all past observationsWeight recent observations much more heavily than very old observations:

weighttodayDecreasing weight given to older observations

Exponential Smoothing: Math

Exponential Smoothing: Math

Exponential Smoothing: MathThus, new forecast is weighted sum of old forecast and actual demandNotes:Only 2 values (Dt and Ft-1 ) are required, compared with n for moving averageParameter a determined empirically (whatever works best)Rule of thumb: < 0.5Typically, = 0.2 or = 0.3 work wellForecast for k periods into future is:

Exponential Smoothinga = 0.2

Welcome

Tools for Managers

Time-Series Forecasting

Professor Stephen R. Lawrence

College of Business and Administration

Graduate School of Business and Administration

University of Colorado

Boulder, CO 80303

303/492-4351

Stephen.Lawrence@Colorado.Edu

&A

Page &P

Instructions

Time-Series Forecasting

Instructions

Throughout this workbook, cells in which you are to enter data are shaded in light green.

Cells which show calculated results are shaded in light yellow with red type.

Many cells are commented with informational notes. You should see a red triangle in the

upper right corner of many cells. If you do not, turn on commenting by selecting menu

Other informational cells are shown in white with black type.

Step 1:Move to worksheet "Work" and remove old data (reset) by clicking on the CLEAR button.

Step 2:Move to worksheet "Data" and enter array of Dates and Observations (Obs).

Note that Dates must be in number or date format, and Observations must be numbers.

Move to worksheet "Data Chart" and study the chart of the time-series data.

In particular, note if the time-series shows seasonality, and if so, its period.

Step 3:Return to "Work" worksheet and enter required data in green shaded cells.

Time-Series NameThe name you wish to give the time series data

Periods per SeasonThe number of periods in a season if the data shows seasonality

Periods ForwardThe number of periods into the future for each forecast (usually 1)

Time-Series UnitThe units of the demand data (e.g., cases, customers, etc.)

Time UnitThe time units of the data (e.g., months, weeks, years, )

alpha

beta

gamma

Step 4:Click on the SETUP button to initialize forecasting calculations for your data.

The quality of the resulting forecast is shown in the "Forecast Error Measures" cells

Move to speadsheet "Forecast Chart" to observe a plot of observations and forecasts.

A plot of forecasting errors over time can be seen in worksheet "Error Chart."

Step 5:

The OPTIMIZE button automatically invokes Solver to find optimal parameter values.

forecasting errors.

Example:To see an example, move to worksheet "Work," and click on the EXAMPLE button.

Once the example data is loaded, click on the SETUP button to initialize forecast.

&A

Page &P

Number of measurement periods in a season (e.g., 12 months in an annual season)

Number of periods into the future for which forecasting is to occur (e.g., every month, forecast demand 6 months away)

The units of the time series (customers, orders, shipments, ...)

The time units of the time-series (hours, days, weeks, months, quarters, years, ...)

Smoothing constant for base forecast

Smoothing constant for trend

Smoothing constant for seasonality

The name you wish to give the time series data

Work

Exponential Smoothing Time-Series Forecasting

Time-Series NameInternet Unicycle Sales (1000's)

Periods per Season12p > 0Data Horizon96Month

Periods Forward1f > 0Forecast Horizon108Month

Time-Series UnitUnits

Time UnitMonth

Forecast Error Measures

alpha0.2000 W Then Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(offset(RC,-Forward,-3)+Forward*offset(RC,-Forward,-2))*offset(RC,-Periods_per_Season,-1)"' Error Cells(I + H, E).Select ActiveCell.FormulaR1C1 = "=RC[-6] - RC[-1]"' Absolute Error Cells(I + H, A).Select ActiveCell.FormulaR1C1 = "=Abs(RC[-2])"' Squared Error Cells(I + H, V).Select ActiveCell.FormulaR1C1 = "=RC[-1]^2"' End If Next I'' Forecast beyond end of data' For I = Range("Data_Horizon").Value + 1 To Range("Forecast_Horizon").Value Cells(I + H, B).Select ActiveCell.FormulaR1C1 = "=R[-1]C+R[-1]C[1]" Cells(I + H, T).Select ActiveCell.FormulaR1C1 = "=R[-1]C" Cells(I + H, S).Select ActiveCell.FormulaR1C1 = "=R[-12]C" Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(R[-1]C[-3]+R[-1]C[-2])*R[-12]C[-1]" Next I' Cells(7, 1).SelectEnd Sub''' CLEAR DATA'Sub Clear_Data()Attribute Clear_Data.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("A7:J1006").Select Selection.ClearContents Selection.Interior.ColorIndex = xlNone Range("A7:B1006").Select Selection.Font.ColorIndex = 10 With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End With' Range("C7:J1006").Select Selection.Font.ColorIndex = 3 With Selection.Interior .ColorIndex = 19 .Pattern = xlSolid End With Cells(7, 1).Select' Range("Name").Value = "" Range("Periods_per_Season") = 1 Range("Forward") = 1 Range("Units") = "" Range("Time_Units") = "" Range("alpha").Value = 0.2 Range("beta").Value = 0.4 Range("gamma").Value = 0.6 Range("Data_Horizon") = 0 Range("Forecast_Horizon") = 0' Worksheets("Work").Activate Application.Goto Reference:="Name"End Sub''' CLEAR CALCULATIONS'Sub Clear_Calculations()Attribute Clear_Calculations.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("C7:J1006").Select Selection.ClearContentsEnd Sub''' EXAMPLE PROBLEM'Sub Example_Problem()Attribute Example_Problem.VB_ProcData.VB_Invoke_Func = " \n14"' Call Clear_Data' Sheets("Example").Select Range("A7:B102").Select Selection.Copy Sheets("Data").Select Range("A7").Select ActiveSheet.Paste Sheets("Example").Select Range("Name").Value = Range("F6").Value Range("Periods_per_Season").Value = 12 Range("Forward") = 3 Range("Units") = "Cases" Range("Time_Units") = "Month" Sheets("Data").Select Cells(7, 1).Select'End Sub

Sub Optimize()' SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverSolveEnd Sub

Attribute VB_Name = "Sheet4"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Sheet3"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Chart1"Attribute VB_Base = "0{00020821-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Example:

Exponential Smoothing

Complicating FactorsSimple Exponential Smoothing works well with data that is moving sideways (stationary)Must be adapted for data series which exhibit a definite trendMust be further adapted for data series which exhibit seasonal patterns

Holts Method:Double Exponential SmoothingWhat happens when there is a definite trend?

MonthDemandActualForecast

Holts Method:Double Exponential SmoothingIdeas behind smoothing with trend:``De-trend'' time-series by separating base from trend effectsSmooth base in usual manner using Smooth trend forecasts in usual manner using Smooth the base forecast BtSmooth the trend forecast TtForecast k periods into future Ft+k with base and trend

ES with Trenda = 0.2, b = 0.4

Welcome

Tools for Managers

Time-Series Forecasting

Professor Stephen R. Lawrence

College of Business and Administration

Graduate School of Business and Administration

University of Colorado

Boulder, CO 80303

303/492-4351

Stephen.Lawrence@Colorado.Edu

&A

Page &P

Instructions

Time-Series Forecasting

Instructions

Throughout this workbook, cells in which you are to enter data are shaded in light green.

Cells which show calculated results are shaded in light yellow with red type.

Many cells are commented with informational notes. You should see a red triangle in the

upper right corner of many cells. If you do not, turn on commenting by selecting menu

Other informational cells are shown in white with black type.

Step 1:Move to worksheet "Work" and remove old data (reset) by clicking on the CLEAR button.

Step 2:Move to worksheet "Data" and enter array of Dates and Observations (Obs).

Note that Dates must be in number or date format, and Observations must be numbers.

Move to worksheet "Data Chart" and study the chart of the time-series data.

In particular, note if the time-series shows seasonality, and if so, its period.

Step 3:Return to "Work" worksheet and enter required data in green shaded cells.

Time-Series NameThe name you wish to give the time series data

Periods per SeasonThe number of periods in a season if the data shows seasonality

Periods ForwardThe number of periods into the future for each forecast (usually 1)

Time-Series UnitThe units of the demand data (e.g., cases, customers, etc.)

Time UnitThe time units of the data (e.g., months, weeks, years, )

alpha

beta

gamma

Step 4:Click on the SETUP button to initialize forecasting calculations for your data.

The quality of the resulting forecast is shown in the "Forecast Error Measures" cells

Move to speadsheet "Forecast Chart" to observe a plot of observations and forecasts.

A plot of forecasting errors over time can be seen in worksheet "Error Chart."

Step 5:

The OPTIMIZE button automatically invokes Solver to find optimal parameter values.

forecasting errors.

Example:To see an example, move to worksheet "Work," and click on the EXAMPLE button.

Once the example data is loaded, click on the SETUP button to initialize forecast.

&A

Page &P

Number of measurement periods in a season (e.g., 12 months in an annual season)

Number of periods into the future for which forecasting is to occur (e.g., every month, forecast demand 6 months away)

The units of the time series (customers, orders, shipments, ...)

The time units of the time-series (hours, days, weeks, months, quarters, years, ...)

Smoothing constant for base forecast

Smoothing constant for trend

Smoothing constant for seasonality

The name you wish to give the time series data

Work

Exponential Smoothing Time-Series Forecasting

Time-Series NameInternet Unicycle Sales (1000's)

Periods per Season12p > 0Data Horizon96Month

Periods Forward1f > 0Forecast Horizon108Month

Time-Series UnitUnits

Time UnitMonth

Forecast Error Measures

alpha0.2000 W Then Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(offset(RC,-Forward,-3)+Forward*offset(RC,-Forward,-2))*offset(RC,-Periods_per_Season,-1)"' Error Cells(I + H, E).Select ActiveCell.FormulaR1C1 = "=RC[-6] - RC[-1]"' Absolute Error Cells(I + H, A).Select ActiveCell.FormulaR1C1 = "=Abs(RC[-2])"' Squared Error Cells(I + H, V).Select ActiveCell.FormulaR1C1 = "=RC[-1]^2"' End If Next I'' Forecast beyond end of data' For I = Range("Data_Horizon").Value + 1 To Range("Forecast_Horizon").Value Cells(I + H, B).Select ActiveCell.FormulaR1C1 = "=R[-1]C+R[-1]C[1]" Cells(I + H, T).Select ActiveCell.FormulaR1C1 = "=R[-1]C" Cells(I + H, S).Select ActiveCell.FormulaR1C1 = "=R[-12]C" Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(R[-1]C[-3]+R[-1]C[-2])*R[-12]C[-1]" Next I' Cells(7, 1).SelectEnd Sub''' CLEAR DATA'Sub Clear_Data()Attribute Clear_Data.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("A7:J1006").Select Selection.ClearContents Selection.Interior.ColorIndex = xlNone Range("A7:B1006").Select Selection.Font.ColorIndex = 10 With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End With' Range("C7:J1006").Select Selection.Font.ColorIndex = 3 With Selection.Interior .ColorIndex = 19 .Pattern = xlSolid End With Cells(7, 1).Select' Range("Name").Value = "" Range("Periods_per_Season") = 1 Range("Forward") = 1 Range("Units") = "" Range("Time_Units") = "" Range("alpha").Value = 0.2 Range("beta").Value = 0.4 Range("gamma").Value = 0.6 Range("Data_Horizon") = 0 Range("Forecast_Horizon") = 0' Worksheets("Work").Activate Application.Goto Reference:="Name"End Sub''' CLEAR CALCULATIONS'Sub Clear_Calculations()Attribute Clear_Calculations.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("C7:J1006").Select Selection.ClearContentsEnd Sub''' EXAMPLE PROBLEM'Sub Example_Problem()Attribute Example_Problem.VB_ProcData.VB_Invoke_Func = " \n14"' Call Clear_Data' Sheets("Example").Select Range("A7:B102").Select Selection.Copy Sheets("Data").Select Range("A7").Select ActiveSheet.Paste Sheets("Example").Select Range("Name").Value = Range("F6").Value Range("Periods_per_Season").Value = 12 Range("Forward") = 3 Range("Units") = "Cases" Range("Time_Units") = "Month" Sheets("Data").Select Cells(7, 1).Select'End Sub

Sub Optimize()' SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverSolveEnd Sub

Attribute VB_Name = "Sheet4"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Sheet3"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Chart1"Attribute VB_Base = "0{00020821-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Example:

Exponential Smoothing with Trend

Winters Method: Exponential Smoothing w/ Trend and SeasonalityIdeas behind smoothing with trend and seasonality:De-trend: and de-seasonalizetime-series by separating base from trend and seasonality effectsSmooth base in usual manner using Smooth trend forecasts in usual manner using Smooth seasonality forecasts using g

Assume m seasons in a cycle12 months in a year4 quarters in a month3 months in a quarteret cetera

Winters Method: Exponential Smoothing w/ Trend and SeasonalitySmooth the base forecast BtSmooth the trend forecast TtSmooth the seasonality forecast St

Winters Method: Exponential Smoothing w/ Trend and SeasonalityForecast Ft with trend and seasonalitySmooth the trend forecast TtSmooth the seasonality forecast St

ES with Trend and Seasonalitya = 0.2, b = 0.4, g = 0.6

Welcome

Tools for Managers

Time-Series Forecasting

Professor Stephen R. Lawrence

College of Business and Administration

Graduate School of Business and Administration

University of Colorado

Boulder, CO 80303

303/492-4351

Stephen.Lawrence@Colorado.Edu

&A

Page &P

Instructions

Time-Series Forecasting

Instructions

Throughout this workbook, cells in which you are to enter data are shaded in light green.

Cells which show calculated results are shaded in light yellow with red type.

Many cells are commented with informational notes. You should see a red triangle in the

upper right corner of many cells. If you do not, turn on commenting by selecting menu

Other informational cells are shown in white with black type.

Step 1:Move to worksheet "Work" and remove old data (reset) by clicking on the CLEAR button.

Step 2:Move to worksheet "Data" and enter array of Dates and Observations (Obs).

Note that Dates must be in number or date format, and Observations must be numbers.

Move to worksheet "Data Chart" and study the chart of the time-series data.

In particular, note if the time-series shows seasonality, and if so, its period.

Step 3:Return to "Work" worksheet and enter required data in green shaded cells.

Time-Series NameThe name you wish to give the time series data

Periods per SeasonThe number of periods in a season if the data shows seasonality

Periods ForwardThe number of periods into the future for each forecast (usually 1)

Time-Series UnitThe units of the demand data (e.g., cases, customers, etc.)

Time UnitThe time units of the data (e.g., months, weeks, years, )

alpha

beta

gamma

Step 4:Click on the SETUP button to initialize forecasting calculations for your data.

The quality of the resulting forecast is shown in the "Forecast Error Measures" cells

Move to speadsheet "Forecast Chart" to observe a plot of observations and forecasts.

A plot of forecasting errors over time can be seen in worksheet "Error Chart."

Step 5:

The OPTIMIZE button automatically invokes Solver to find optimal parameter values.

forecasting errors.

Example:To see an example, move to worksheet "Work," and click on the EXAMPLE button.

Once the example data is loaded, click on the SETUP button to initialize forecast.

&A

Page &P

Number of measurement periods in a season (e.g., 12 months in an annual season)

Number of periods into the future for which forecasting is to occur (e.g., every month, forecast demand 6 months away)

The units of the time series (customers, orders, shipments, ...)

The time units of the time-series (hours, days, weeks, months, quarters, years, ...)

Smoothing constant for base forecast

Smoothing constant for trend

Smoothing constant for seasonality

The name you wish to give the time series data

Work

Exponential Smoothing Time-Series Forecasting

Time-Series NameInternet Unicycle Sales (1000's)

Periods per Season12p > 0Data Horizon96Month

Periods Forward3f > 0Forecast Horizon108Month

Time-Series UnitUnits

Time UnitMonth

Forecast Error Measures

alpha0.2000 W Then Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(offset(RC,-Forward,-3)+Forward*offset(RC,-Forward,-2))*offset(RC,-Periods_per_Season,-1)"' Error Cells(I + H, E).Select ActiveCell.FormulaR1C1 = "=RC[-6] - RC[-1]"' Absolute Error Cells(I + H, A).Select ActiveCell.FormulaR1C1 = "=Abs(RC[-2])"' Squared Error Cells(I + H, V).Select ActiveCell.FormulaR1C1 = "=RC[-1]^2"' End If Next I'' Forecast beyond end of data' For I = Range("Data_Horizon").Value + 1 To Range("Forecast_Horizon").Value Cells(I + H, B).Select ActiveCell.FormulaR1C1 = "=R[-1]C+R[-1]C[1]" Cells(I + H, T).Select ActiveCell.FormulaR1C1 = "=R[-1]C" Cells(I + H, S).Select ActiveCell.FormulaR1C1 = "=R[-12]C" Cells(I + H, F).Select ActiveCell.FormulaR1C1 = "=(R[-1]C[-3]+R[-1]C[-2])*R[-12]C[-1]" Next I' Cells(7, 1).SelectEnd Sub''' CLEAR DATA'Sub Clear_Data()Attribute Clear_Data.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("A7:J1006").Select Selection.ClearContents Selection.Interior.ColorIndex = xlNone Range("A7:B1006").Select Selection.Font.ColorIndex = 10 With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End With' Range("C7:J1006").Select Selection.Font.ColorIndex = 3 With Selection.Interior .ColorIndex = 19 .Pattern = xlSolid End With Cells(7, 1).Select' Range("Name").Value = "" Range("Periods_per_Season") = 1 Range("Forward") = 1 Range("Units") = "" Range("Time_Units") = "" Range("alpha").Value = 0.2 Range("beta").Value = 0.4 Range("gamma").Value = 0.6 Range("Data_Horizon") = 0 Range("Forecast_Horizon") = 0' Worksheets("Work").Activate Application.Goto Reference:="Name"End Sub''' CLEAR CALCULATIONS'Sub Clear_Calculations()Attribute Clear_Calculations.VB_ProcData.VB_Invoke_Func = " \n14" Worksheets("Data").Activate Range("C7:J1006").Select Selection.ClearContentsEnd Sub''' EXAMPLE PROBLEM'Sub Example_Problem()Attribute Example_Problem.VB_ProcData.VB_Invoke_Func = " \n14"' Call Clear_Data' Sheets("Example").Select Range("A7:B102").Select Selection.Copy Sheets("Data").Select Range("A7").Select ActiveSheet.Paste Sheets("Example").Select Range("Name").Value = Range("F6").Value Range("Periods_per_Season").Value = 12 Range("Forward") = 3 Range("Units") = "Cases" Range("Time_Units") = "Month" Sheets("Data").Select Cells(7, 1).Select'End Sub

Sub Optimize()' SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$11", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$12", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=1, FormulaText:="1" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverAdd CellRef:="$C$13", Relation:=3, FormulaText:="0" SolverOK SetCell:="$G$13", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$11:$C$13" SolverSolveEnd Sub

Attribute VB_Name = "Sheet4"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Sheet3"Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Attribute VB_Name = "Chart1"Attribute VB_Base = "0{00020821-0000-0000-C000-000000000046}"Attribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = TrueAttribute VB_TemplateDerived = FalseAttribute VB_Customizable = True

Example:

Exponential Smoothing withTrend and Seasonality

Forecasting PerformanceMean Forecast Error (MFE or Bias): Measures average deviation of forecast from actuals. Mean Absolute Deviation (MAD): Measures average absolute deviation of forecast from actuals.Mean Absolute Percentage Error (MAPE): Measures absolute error as a percentage of the forecast.Standard Squared Error (MSE): Measures variance of forecast error

How good is the forecast?

Forecasting Performance Measures

Want MFE to be as close to zero as possible -- minimum biasA large positive (negative) MFE means that the forecast is undershooting (overshooting) the actual observationsNote that zero MFE does not imply that forecasts are perfect (no error) -- only that mean is on targetAlso called forecast BIAS

Mean Forecast Error (MFE or Bias)

Mean Absolute Deviation (MAD)Measures absolute errorPositive and negative errors thus do not cancel out (as with MFE)Want MAD to be as small as possibleNo way to know if MAD error is large or small in relation to the actual data

Mean Absolute Percentage Error (MAPE)Same as MAD, except ...Measures deviation as a percentage of actual data

Mean Squared Error (MSE)Measures squared forecast error -- error varianceRecognizes that large errors are disproportionately more expensive than small errorsBut is not as easily interpreted as MAD, MAPE -- not as intuitive

Fortunately, there is software...