Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 11 Times Series 463

difference of the values or the ratio? In many cases seasonal changes are best

expressed in ratios, especially if there is substantive growth in yearly sales.

As annual sales increase, the difference between the November and

December values should also increase, but the ratio of sales between the two

months might remain nearly constant. This is called multiplicative seasonality.

To quantify the effect of the season on each month’s value, we need to as-

sign a multiplicative factor to each month. If the month’s sales are equal to

the expected yearly average, we’ll give it a multiplicative factor of 1. Conse-

quently, months with higher-than-average sales have multiplicative factors

greater than 1, and months with lower-than-average sales have multiplica-

tive factors less than 1.

As an example, consider Table 11-4, which shows seasonal sales and

multiplicative factors.

Table 11-4 Multiplicative Seasonality

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Sales 220 310 359 443 374 660 1030 1320 1594 1093 950 610

Factor 0.48 0.58 0.60 0.69 0.59 1.00 1.48 1.69 1.99 1.29 1.02 0.59

Adjusted

sales

458.3 534.5 598.3 642.0 633.9 660.0 695.9 781.1 801.0 847.3 931.4 1033.9

The monthly sales fi gures are shown in the fi rst row of the table. The mul-

tiplicative factors based on previous years’ sales are shown in the second row.

Dividing the sales in each month by the multiplicative factor yields the adjusted

sales. Plotting the sales values and adjusted sales values in Figure 11-21 reveals

that sales have been steadily increasing throughout the year. This information

is masked in the raw sales data by the seasonal effects.

Figure 11-21
Plot of adjusted
sales data