O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

6.7. Estimating Seasonal Indices

Problem

You're working with a time series that shows some seasonal variation and you'd like to compute the seasonal indices prior to deseasonalizing the data.

Solution

You can compute seasonal indices using any of a number of methods. I'll show you how easy it is to compute such indices in Excel using the average-percentage method.

Discussion

Take a look at the time series shown in Figure 6-23.

Average monthly temperatures

Figure 6-23. Average monthly temperatures

This time series consists of average monthly temperatures for the state of Louisiana from 1996 to 1999. The vertical gridlines correspond to three-month quarters, and it's clear there's an obvious seasonal variation in average temperatures. In Recipe 6.9, I'll show you how to forecast the average monthly temperatures for the year 2000 given this historical data. Part of that forecast analysis requires you to isolate the seasonal variation in temperatures. To do so, you must first compute the seasonal indices.

There are many standard methods for computing seasonal indices. The method I'll show you to illustrate how to use Excel for such calculations is the average-percentage method.

I'll use the series shown in Figure 6-23 as an example. To apply the average-percentage method, compute the annual average temperature for each year and then express each monthly temperature as a percentage of the average annual temperature for the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required