# Predictive Analytics with Microsoft Excel: Working with Seasonal Time Series

• Print
This chapter is from the book

## Holt-Winters Models

When you add a trend to a seasonal time series, you enter the bailiwick of what’s usually known as the Holt-Winters model. At first glance it might seem as though all you need to do is add a smoothing constant for the trend, as discussed in “Using Holt’s Linear Exponential Smoothing” in Chapter 3. And that first glance is largely correct—it’s only a little more complicated than that.

The smoothing form (and therefore the error correction form) of the equation to update each season is the same as is given earlier in this chapter, in “About the Season Smoothing Formulas.” Here it is again:

In words, the seasonal index for time t starts with Delta times the difference between the current observation and the current level of the series. To that is added (1 – Delta) times the index for the season associated with the current period, or the index that’s m seasons back.

No difference with the untrended seasonal series so far, but here’s the smoothing formula for the trend:

bt = γ(Lt - Lt-1)+(1-γ)bt-1

The difference between the current level, Lt, and the prior level, Lt–1, is taken to be the value of the current trend. That difference is multiplied by Gamma. Then we obtain the value of the immediately previous trend, bt-1, and multiply by (1 – Gamma). The sum of the two terms gets us a measure of the current trend.

Finally, the formula for the level is nearly the same as in the untrended seasonal model. Here’s the formula for a trended, seasonal model:

For the untrended, seasonal model the formula is this:

The difference between the two formulas is of course due to the presence of the bt-1 term in the former formula. The value of the trend as of time t – 1 is included because we’re now dealing with a trended series. In an untrended series we assume that there is no trend, that any difference between observations not caused by season or level is part of the error in the model.

But in a trended series the trend term is explicitly acknowledged. Let’s see how it works out in practice. Figure 5.26 has the first few observations from the well-known Series G, found in Box and Jenkins’s seminal book on ARIMA analysis. Series G is a count of airline passengers during each month of a 12-year period. Because the number of airline passengers is seasonal and, at the time (1949 through 1960), was trending strongly up, a Holt-Winters model is apt.

The degree of seasonality appears to vary with the level of the series—that is, the higher the level of the series, the greater the swings between a year’s high month and its low month. This is a characteristic that suggests using a multiplicative model, which sounds a lot more forbidding than it really is, and which I discuss in Chapter 7, “Multiplicative and Damped Trend Models.” But for the time being, this chapter builds an additive Holt-Winters model that’s directly analogous to the one discussed in the preceding section, “Simple Seasonal Exponential Smoothing.”

### Initializing the Forecasts

As Chapter 4 shows, there are many ways to go about initializing forecasts, and the more complicated the model the more choices you have. For example, in various sources, I’ve seen the Box-Jenkins Series G initialized using these approaches, among others, for a Holt-Winters analysis:

• By treating the mean of the first 12 months as the level of Period 0.
• By treating the difference between the result of Period 1 and each monthly observation during the first year as initial values of the seasonal indexes.
• By dividing the difference between the series’ 12th and 1st observations by 11; more generally, subtract the first observation from the mth observation and divide by (m – 1). Treat the result as the initial value of the monthly trend.

That’s a kind of rough-and-ready way to initialize the estimates of level, season, and trend; it might be a little crude compared to the more sophisticated approaches such as optimization, backcasting, and regression. But it’s quick, reasonable, and unlikely to steer you far wrong, particularly if you’re fortunate enough to have a lengthy baseline.

There’s also the issue of when you start your forecasts. Figure 5.27 shows the first 14 periods of the Box-Jenkins Series G data, with the actual observations in column D. (The full set of 144 observations is included in the workbook for this chapter, which you can download from the publisher’s website.) In Figure 5.27, the forecasts begin after the first year.

Here’s how the formulas are set up in Figure 5.27:

• The mean of the observations for the first encompassing period—in this case, that’s 1949—is entered with this formula in cell M4:

`=AVERAGE(D2:D13)`

The cell is named Year_1_Mean.

• An estimate of the monthly trend during the first year is entered with this formula in cell M5:

`=(D13-D2)/11`

The cell is named Year_1_Trend. Bear in mind that it estimates the monthly trend during the first year.

• The seasonal indexes for the first year are initiated with this formula in cell I2:

`=D2-Year_1_Mean`

The formula is simply the difference between the observation for the first month, less the mean observation for the first year. That formula is copied and pasted down through I13, to initialize each of the 12 seasonal estimates.

• Cell G13 carries the first year’s mean into the column for level estimates with this formula:

`=Year_1_Mean`
• Cell H13 carries the first year’s monthly trend into the column for trend estimates with this formula:

`=Year_1_Trend`
• Cell J13 calculates the first smoothing forecast, this one for the 13th period, the first month of the second year. As with all the remaining forecasts, the one in cell J13 totals the level, the trend, and the seasonal index. This is the formula in cell J13:

`=G13+H13+I2`

Notice that the formula uses the seasonal value from the first month of the first year in cell I2.

• The forecast for Period 13 is repeated from cell J13 to cell E14 with this formula in E14:

`=J13`

And the forecast error, the difference between the current observation and the forecast for the 13th period, is entered in cell F14 with this formula:

`=D14-E14`
• The smoothing forecasts begin with the level estimate in cell G14, with this formula:

`=Alpha*(D14-I2)+(1-Alpha)*(G13+H13)`

You remove the seasonal effect from the observed value for the current period by subtracting the seasonal index (cell I2) from the observed value (cell D4); the result is multiplied by Alpha. The projected level (cell G13) and the projected trend (cell H13) as of the prior period are summed and the result is multiplied by (1 – Alpha). The two terms are summed to return the estimated series level as of Period 13.

• The trend as of Period 13 is forecast in cell H14 with this formula:

`=Gamma*(G14-G13)+(1-Gamma)*H13`

The difference between the prior level and the current level (cells G13 and G14) is taken to be the current trend observation, and is multiplied by Gamma. The value in cell H13 is the forecast trend as of the prior period, and is multiplied by (1 – Gamma). The two terms are summed to return the estimated series trend as of Period 13.

• The seasonal index as of Period 13 is forecast in cell I14 with this formula:

`=Delta*(D14-G14)+(1-Delta)*I2`

The difference between the current observation in cell D14 and the current level in cell G14 is taken to be the current seasonal effect; it is multiplied by Delta. The prior seasonal effect in cell I2 is multiplied by (1 – Delta). The two terms are summed to reach a smoothed seasonal index for Period 13.

• The forecast for the next period is placed in cell J13 with the formula

`=G14+H14+I3`

totaling the current level, trend, and seasonal index. Notice that although we have just calculated the value of the seasonal index for Period 13 in cell I14, for the purpose of making the current forecast we use the seasonal index from one year back in cell I3.

• The remainder of the time series can now be forecast by selecting cells E14:J14, copying them, and pasting them from E15 to J145. You can get the one-step-ahead forecast by copying G145:J145 into G146:J146. And you can push the final forecast a little further out by following the procedures I suggest in an earlier section of this chapter, “Dealing with the End of the Time Series.”

### Starting the Forecasts Earlier

Notice in Figure 5.27 that the forecasting process does not begin until the 13th period. There’s no particular reason that this should be so. Compare Figure 5.27 with Figure 5.28.

In Figure 5.28, I have inserted extra rows for periods –11 through 0. This provides a place to store the seasonal estimates based on the differences between the first year’s mean and its monthly observations. Notice that in Figure 5.28, the first forecast is for t = 1, whereas the first forecast is for t = 13 in Figure 5.27.

With a good, long baseline such as is available in Box-Jenkins Series G, this difference isn’t cause for much concern. Notice, for example, that the values for the root mean square error are very close to one another in Figures 5.27 and 5.28. And see Figure 5.29 for another sort of comparison.

In Figure 5.29, I show the individual errors based on the actual versus forecast differences for time periods 13 through 144; column A shows the errors when the forecasts begin at t = 13, and column B shows the errors when the forecasts begin at t = 1. When the forecasts begin at t = 13, the forecast error for that period is different than when they begin at t = 1. Therefore the level forecast for t = 14, and for subsequent periods, is different for the two sets of forecasts.

In this case the differences between the forecast errors in columns A and B are very small relative to the overall series mean. Nevertheless, the differences are greater during the first 40 to 50 time periods than during the final 40 or 50. You can’t tell that from the chart because the charted values are so close to one another that one charted series obscures the other. But you could easily get a very different result with another set of observations, perhaps one that isn’t so well behaved.

Also, note in the chart that the swings are greater as the level of the series increases. This is an argument for using a multiplicative model rather than an additive model. I discuss the differences between the models in Chapter 7.

First, though, Chapter 6, “Names, Addresses, and Formulas,” discusses some techniques in Excel that can make it much more straightforward to design and interpret the more complex smoothing models.

• 🔖 Save To Your Account

### InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

## Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

## Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

### Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

### Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

### Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

### Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

### Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

### Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

## Other Collection and Use of Information

### Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

### Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

### Do Not Track

This site currently does not respond to Do Not Track signals.

## Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

## Children

This site is not directed to children under the age of 13.

## Marketing

Pearson may send or direct marketing communications to users, provided that

• Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
• Such marketing is consistent with applicable law and Pearson's legal obligations.
• Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
• Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

## Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

## Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

## Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

## Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

## Sharing and Disclosure

Pearson may disclose personal information, as follows:

• As required by law.
• With the consent of the individual (or their parent, if the individual is a minor)
• In response to a subpoena, court order or legal process, to the extent permitted or required by law
• To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
• In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
• To investigate or address actual or suspected fraud or other illegal activities
• To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
• To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
• To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.