MsOffice.US
Home
Excel
FrontPage
Access
Word
PowerPoint
Outlook
Visio
Project
Publisher
InfoPath
Exchange
SharePoint
Commerce
Expression
Silverlight
Management
Software
Windows

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking s


  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

 

 

 

 

 

   

 
Home Excel Topics

Winterís Method

 

 

 


 

Often, we must predict future values of a time series such as monthly costs or monthly prod- uct revenues. This is usually difficult because the characteristics of any time series are con- stantly changing. Smoothing or adaptive methods are usually best suited for forecasting future values of a time series. In this section, we describe the most powerful smoothing method: Winterís method. To help you understand how Winterís method works, we will use it to fore- cast monthly housing starts in the United States (U.S.). Housing starts are simply the number

of new homes whose construction begins during a month. We begin by describing the three key characteristics of a time series.

 

Time Series Characteristics

 

The behavior of most time series can be explained by understanding the following three characteristics: base, trend, and seasonality.

 

■     The base of a series describes the seriesí current level in the absence of any seasonality. For example, suppose the base level for U.S. housing starts is 160,000. In this case, we believe that if the current month were an ďaverageĒ month relative to other months of the year, then 160,000 housing starts would occur.

■     The trend of a time series is the percentage increase per period in the base. Thus a trend

of 1.02 means that we estimate that housing starts are increasing by 2 percent each month.

■     The seasonality (seasonal index) for a period tells us how far above or below a typical month we can expect housing starts to be. For example, if the December seasonal index

is .8, then December housing starts are 20 percent below a typical month. If the June sea- sonal index is 1.3, then June housing starts are 30 percent higher than a typical month.

 

 

 

 

 

 

 

 

Parameter Definitions

 

After observing month t, we will have used all data observed through the end of month t to estimate the following quantities of interest:

 

■     Lt=Level of series

■     Tt=Trend of series

■     St=Seasonal index for current month

The key to Winterís method is the following three equations, which are used to update Lt, Tt, and St. In the following formulas, alp, bet, and gam are called smoothing parameters. The values

of these parameters will be chosen to optimize our forecasts. In the following formulas, c

equals the number of periods in a seasonal cycle (c=12 months for example) and xt equals the observed value of the time series at time t.

 

■     Formula 1: Lt=alp(xt/stĖc)+(1Ėalp)(L1*T1)

■     Formula 2: Tt=bet(Lt/L1)+(1Ėbet)TtĖ1

■     Formula 3: St=gam(xt/Lt)+(1Ėgam)stĖ-c

Formula 1 indicates that our new base estimate is a weighted average of the current observa- tion (deseasonalized) and last periodís base updated by our last trend estimate. Formula 2 indicates that our new trend estimate is a weighted average of the ratio of our current base to last periodís base (this is a current estimate of trend) and last periodís trend. Formula 3 indi- cates that we update our seasonal index estimate as a weighted average of the estimate of the seasonal index based on the current period and the previous estimate. Note that larger values

of the smoothing parameters correspond to putting more weight on the current observation.

 

We define Ft,k as our forecast (F) after period t for the period t+k. This results in the formula


 

Text Box: t)

 

Ft,k=Lt*(T  ks

 


 

t+c.


 

 

This formula first uses the current trend estimate to update the base k periods forward. Then the resulting base estimate for period t+k is adjusted by the appropriate seasonal index.

 

Initializing Winterís Method

 

To start Winterís method, we must have initial estimates for the series base, trend, and seasonal indexes. We will use monthly housing starts for the years 1986 through 1987 to initialize Winterís method. Then we will choose our smoothing parameters to optimize

our one-month-ahead forecasts for the years 1988 through 1996. See Figure 53-1 and the file House2.xlsx. Weíll use the following process.

 

Step 1: We will estimate, for example, the January seasonal index as the average of January housing starts for 1986 through 1987 divided by the average monthly starts for 1986


 

 

through 1987. Therefore copying from G14 to G15:G25 the formula =AVERAGE(B2,B14)/ AVERAGE($B$2:$B$25) will generate our estimates of seasonal indexes. For example, the January estimate is 0.75 and the June estimate is 1.17.

 

Step 2: To estimate the average monthly trend, we take the twelfth root of (1987 mean starts divided by the 1986 mean starts). We compute this in cell J3 (and copy it to cell D25) with the formula =(J1/J2)^(1/12)

 

Figure 53-1    Initialization of Winterís method

 

Step 3: Going into January 1987, we estimate the base of the series as the deseasonalized

December 1987 value. This is computed in C25 with the formula =(B25/G25).

 

Estimating the Smoothing Constants

 

We are now ready to estimate our smoothing constants. In column C, we will update the

series base; in column D, the series trend; and in column G, our seasonal indexes. In column

E, we compute our forecast for next month, and in column F, we compute our absolute per- centage error for each month. Finally, we will use solver to choose smoothing constant values that minimize the sum of our absolute percentage errors. Weíll use the following process.

 

Step 1: In G11:I11, we enter trial values (between 0 and 1) for our smoothing constants.

 

Step 2: In C26:C119, we compute the updated series level with (1) by copying from C26 to

C27:C119 the formula =alp*(B26/G14)+(1Ėalp)*(C25*D25).


 

 

 

Step 3: In D26:D119, we use (2) to update the series trend. Copy from D26 to D27:D119 the formula =bet*(C26/C25)+(1Ėbet)*D25.

 

Step 4: In G26:G119, we use (3) to update the seasonal indexes. Copy from G26 to G27:G119

the formula =gam*(B26/C26)+(1Ėgam)*G14.

 

Step 5: In E26:E119, we use (4) to compute the forecast for the current month by copying from E26 to E27:E119 the formula =(C25*D25)*G14.

 

Step 6: In F26:F119, we compute the absolute percentage error for each month by copying from F26 to F27:F119 the formula =ABS(B26-E26)/B26.

 

Step 7: We compute the average absolute percentage error for the years 1988 through 1996 in

F21 with the formula =AVERAGE(F26:F119).

 

Step 8: We can now use the Microsoft Office Excel 2007 Solver feature to determine smooth- ing parameter values that minimize our average absolute percentage error. The Solver Parameters dialog box is shown in Figure 53-2.

 

Figure 53-2    Solver Parameters dialog box for Winterís model

 

We choose our smoothing parameters (G11:I11) to minimize the average absolute percentage error (cell F21). The Excel Solver ensures we will find the best combination of smoothing con- stants. Smoothing constants must be between 0 and 1. We find that alp=.54, bet=.02, and gam=.29 minimizes our average absolute percentage error. You might find slightly different values of the smoothing constants, but you should obtain a MAPE close to 7.3 percent. In this example, there are many combinations of the smoothing constants that give forecasts having approximately the same MAPE. Our one-month-ahead forecasts are off by an average of 7.3 percent.


 

 

 

Remarks


 

 

■     Instead of choosing our smoothing parameters to optimize one-period forecast errors, we could, for example, have chosen to optimize the average absolute percentage error incurred in forecasting total housing starts for the next six months.

 

■     Suppose our time series is sales of a software product and we have conducted a major promotion during June 2000. Assume predicted sales for June 2000 were 20,000 units, but we sold 35,000 units. Then a good guess is that the promotion caused 15,000 extra sales during June. When updating the base, trend, and seasonal indexes, however, we should not put in June 2000 sales of 35,000. We should put in June 2000 sales of our forecast (20,000); otherwise, we will incorrectly bump up our forecasts of future sales. When making a forecast for a future month in which there is a promotion similar to the June promotion, we would just bump up the Winterís method forecast by using the for- mula 35,000/20,000=75%!

 

■     If at the end of month t we wanted to forecast sales for the next four quarters, we would simply add ft,1+ft,2+ft,3+ft,4. If desired, we could choose our smoothing parameters to minimize the absolute percentage error incurred in estimating sales for the next year.

 

Problems

 

All the data for the following problems is in the file Quarterly.xlsx.

 

1.    Use Winterís method to forecast one-quarter-ahead revenues for Apple.

 

2.    Use Winterís method to forecast one-quarter-ahead revenues for Amazon.com.

 

3.    Use Winterís method to forecast one-quarter-ahead revenues for Home Depot.

 

4.    Use Winterís method to forecast total revenues for the next two quarters for Home

Depot.

 

 

List of our Excel topics
(Excel 2003) Advance tutorials
(Excel 2003) Algebra
(Excel 2003) Analyzing external data in Excel
(Excel 2003) Analyzing Your Database
(Excel 2003) Arithmetic
(Excel 2003) Basic Actions
(Excel 2003) Basic Sort Filter
(Excel 2003) Catching data entry errors
(Excel 2003) Charting
(Excel 2003) Charts overview  
(Excel 2003) Conditional Formatting 
(Excel 2003) Constraint and References 
(Excel 2003) Copying Cells
(Excel 2003) Creating a workspace in Excel
(Excel 2003) Creating custom lists
(Excel 2003) Creating Simple Workbook
(Excel 2003) Data Subtotals 
(Excel 2003) DATA VALIDATION  
(Excel 2003) Displaying Symbols
(Excel 2003) Don't use AutoFormat on an entire worksheet
(Excel 2003) EDATE function
(Excel 2003) Ensure proper data entry with validation
(Excel 2003) Establishing Criteria
(Excel 2003) Express yourself with comments
(Excel 2003) Filtering
(Excel 2003) Filtering Database
(Excel 2003) Filters  
(Excel 2003) Format cells  
(Excel 2003) Formatting The Appearance of a Workbook
(Excel 2003) Formatting  Appearance Workbook
(Excel 2003) Functions 
(Excel 2003) Getting Random Sample
(Excel 2003) Guidelines Charting
(Excel 2003) Headers&Footers
(Excel 2003) Hiding duplicate records
(Excel 2003) Hyperlink to a specific cell
(Excel 2003) Insert Date&Time
(Excel 2003) Linear Regression
(Excel 2003) Linking Documents
(Excel 2003) Macro security settings
(Excel 2003) Macros 
(Excel 2003) new features
(Excel 2003) Opening a workbook at startup
(Excel 2003) Page Setup
(Excel 2003) Page set-up Overview 
(Excel 2003) Pivot Tables and Charts
(Excel 2003) PointerShapes
(Excel 2003) Printing
(Excel 2003) Printing overview 
(Excel 2003) Protect a worksheet  
(Excel 2003) Quickly setting a print area
(Excel 2003) Quickly shuffling toolbar buttons 
(Excel 2003) Rank Function
(Excel 2003) SOLVER  
(Excel 2003) Split  Worksheet
(Excel 2003) Statistics
(Excel 2003) Summarize data with grouping
(Excel 2003) Trigonometry
(Excel 2003) text export
(Excel 2003) Using functions for Spreadsheet solutions etc
(Excel 2003) Working PivotTable
(Excel 2003) Working with Clip Art
(Excel 2003) Working with Tables
(Excel 2003) Presentations
(Excel 2003) Step-by-Step Examples
(Excel 2007) Convert Text  Columns
(Excel 2007) Getting Random Sample
(Excel 2007) Getting started Excel
(Excel 2007) New features
(Excel 2007) Running Macros
(Excel 2007) Set Print Titles
(Excel 2007) Split  Worksheet
(Excel 2007) Presentations 
(Excel 2007) An Overview Excel
(Excel 2010) Date-Related Functions
(Excel 2010) Adding Hyperlinks
(Excel 2010) Adding Information Worksheets
(Excel 2010) AutoCorrect AutoFill AutoFit
(Excel 2010) Conditional Formatting
(Excel 2010) Data-entry tips
(Excel 2010) Details the New features
(Excel 2010) Excel (Excel 2010)  Tips Tricks
(Excel 2010) EXCEL SHORT CUTS
(Excel 2010) Formatting Cells
(Excel 2010) Getting Started
(Excel 2010) Grouping Worksheets
(Excel 2010) History Excel
(Excel 2010) IF Statements
(Excel 2010) Inserting Dates Times
(Excel 2010) Internal Rate Return
(Excel 2010) Manipulating Dates Times Text
(Excel 2010) Mastering Art Replacement
(Excel 2010) Moving Worksheets from One Workbook to Another
(Excel 2010) Operator precedence
(Excel 2010) Operator precedence
(Excel 2010) Parts of the Excel Screen
(Excel 2010) Paste Special
(Excel 2010) Smart Formatting Tricks
(Excel 2010) Spell Check
(Excel 2010) Undo Redo AutoRecover
(Excel 2010) What Is Excel Good For
(Excel 2010) Whatís new
(Excel 2010) Working with Names
(Excel 2003) Creating Charts
(Excel 2003) Writing Formulas
(Excel 2007) Introduction to charts
(Excel 2003) Using Functions
(Excel 2007) Functions
(Excel 2010) Match Function
(Excel 2010) Text Functions
(Excel 2010) INDEX Function
 Excel Functions Glossary
(Excel 2010) Evaluating Investments
(Excel 2007) Sort filter
(Excel 2010) Range Names
(Excel 2003) Graphing Data Curve Fitting
(Excel 2007) Adding Picture
(Excel 2003) Basic Graphing
(Excel 2010) VBA, Macros, and Other Ways to automate Excel
(Excel 2010) Calculating Various Workbook Elements and Publishing 
(Excel 2010) Consolidating Sheets
(Excel 2010) Function Groups
(Excel 2010) Function Junction
(Excel 2010) Leveraging Excel Functions Using Excel Services
(Excel 2010) Using Excel Functions to Enhance Project Management Productivity
(Excel 2010) The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
(Excel 2010) The OFFSET Function
(Excel 2010) The Auditing Tool
(Excel 2010) The Goal Seek Command
(Excel 2010) Using the Scenario Manager for Sensitivity Analysis 
(Excel 2010) Financial Functions
(Excel 2010) Circular References
(Excel 2010) COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
(Excel 2010) Sensitivity Analysis with Data Tables
(Excel 2010) An Introduction to Random Variables
(Excel 2010) Analysis of Variance One-Way ANOVA
(Excel 2010) Array Formulas and Functions
(Excel 2010) Calculating an Optimal Bid
(Excel 2010) Consolidating Data
(Excel 2010) Creating Subtotals
(Excel 2010) Determining Customer Value
(Excel 2010) Absolute-relative reference problems
(Excel 2010) Estimating Straight Line Relationships
(Excel 2010) Borrowing and Investing Formulas
(Excel 2010) Forecasting in the Presence of Special Events
(Excel 2010) Examples of Formulas Using the COUNTIF Function
(Excel 2010) Importing Data from a Text File or Document
(Excel 2010) Importing Data from the Internet
(Excel 2010) Incorporating Qualitative Factors into Multiple Regression
(Excel 2010) Introduction to Monte Carlo Simulation
(Excel 2010) Introduction to Multiple Regression
(Excel 2010) Date-Related Functions
(Excel 2010) Modeling Exponential Growth
(Excel 2010) Modeling Nonlinearities and Interactions
(Excel 2010) Nonlinear Pricing
(Excel 2010) Excel Data Types
(Excel 2010) Function Procedure
(Excel 2010) Pricing Stock Options
(Excel 2010) Examples of Formulas Using the COUNTIF Function
(Excel 2010) Randomized Blocks and Two-Way ANOVA
(Excel 2010) Simulating Stock Prices and Asset Allocation Modeling
(Excel 2010) Sorting in Excel
(Excel 2010) Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes
(Excel 2010) Summarizing Data by Using Descriptive Statistics
(Excel 2010) Summarizing Data by Using Histograms
(Excel 2010) Tables
(Excel 2010) The Binomial, Hypergeometric, and Negative Binomial Random Variables
(Excel 2010) Creating a Custom Number Format
(Excel 2010) The INDIRECT Function
(Excel 2010) The Normal Random Variable
(Excel 2010) The Poisson and Exponential Random Variable
(Excel 2010) The Power Curve
(Excel 2010) Using Correlations to Summarize Relationships
(Excel 2010) Using Moving Averages to Understand Time Series
(Excel 2010) Using PivotTables to Describe Data
(Excel 2010) Creating a Calculated Field or Calculated Item
(Excel 2010) Using Solver for Capital Budgeting
(Excel 2010) Using Solver for Financial Planning
(Excel 2010) Using Solver to Determine the Optimal Product Mix
(Excel 2010) Using Solver to Schedule Your Workforce
(Excel 2010) Using Solver to Solve Transportation or Distribution Problems
(Excel 2010) Validating Data
(Excel 2010) Weibull and Beta istributions: Modeling Machine Life and Duration of a Project
(Excel 2010) Winterís Method
(Excel 2010) Excel Counting and Summing Functions
(Excel 2010) Formatting dates and times
(Excel 2010) Formula Problems and Solutions
(Excel 2010) Formulas returning an error
(Excel 2010) Miscellaneous Calculations
(Excel 2010) Mismatched parentheses
(Excel 2010) Megaformula Examples
(Excel 2010) Introducing Arrays
(Excel 2010) Pivot table terminology
(Excel 2010) Functions Used in Lookup Formulas
(Excel 2010) Project Management Establishing Project Management Fundamentals
(Excel 2010) Project Management Establishing Excel and Office 2007 SharePoint Server Fundamentals
(Excel 2010) Project Management Initiating the Project
(Excel 2010) Project Management Determining Project Requirements
(Excel 2010) Project Management Planning and Acquiring Resources
(Excel 2010) Project Management Assessing and Tracking Risk
(Excel 2010) Project Management Constructing the Project Schedule and Budget
(Excel 2010) Project Management Establishing Change Control Processes
(Excel 2010) Project Management Controlling Project Outcomes and Archiving Documents
(Excel 2010) Project Management IF Statements.
(Excel 2010) VBA Simulation Basic Tutorial
(Excel 2010) VBA Overview
(Excel 2010) VBA Programming Spreadsheets
(Excel 2010) VBA Programming Language
(Excel 2010) VBA Writing Macros
(Excel 2010) VBA QUE
(Excel 2010) Excel Function Reference
 Excel SOFTWARE SUPPORTING Excel 
(Excel 2010) Several concepts contribute to the time value of money:
(Excel 2010) The Pros and Cons of Megaformulas
(Excel 2010) Types of validation criteria you can apply
(Excel 2010) Using Assignment Expressions
(Excel 2010) Using Lookup Functions
(Excel 2010) Understanding Some Database Terminology
(Excel 2010) The Pros and Cons of Megaformulas
(Excel 2010) Using Built-In VBA Functions
(Excel 2010) Exercises
(Excel 2003) PivotTable_Feature.

 

[Excel/KeywordExcel.htm]

Welcome to

MsOffice.us

This is a free website for learning and practicing all the basics of Microsoft Office 2010 training courses, online help, lessons and more...

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

 

 

  Home  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking