How to Use Microsoft Excel to Calculate Seasonal Indexes
A seasonal index indicates how a periodic amount -- typically a month -- compares to the average of all periods in an extended period, such as a year. Because seasonal indexes measure price fluctuations, they're commonly used in sales forecasting, but seasonal indexes can be used to analyze any activity that is influenced by the season or specific time of year. Microsoft Excel is an excellent tool for calculating seasonal indexes.
Open the Excel workbook that contains your data. Your data should be arranged in adjacent columns or rows to simplify the functions and their calculations.
In the cell below the last entry of the period amounts, type the function =SUM(...) , replacing the ellipses with the cell references of the cells you want to total up for all of the period amounts. Underneath the total, type in an =AVERAGE(...) function, using the same cell references, to calculate the average period amount. In the example shown, the two entries are =SUM(B2:B13) and =AVERAGE(B2:B13).
The seasonal index of each value is calculated by dividing the period amount by the average of all periods. This creates a relationship between the period amount and the average that reflects how much a period is higher or lower than the average.
The formula for calculating the index is
=Period Amount / Average Amount or, for example, =B2/$B$15.
The index amount represents a decimal fraction indicating the ratio of a period amount to the average of all periods. For example, the index for January is 0.76. This means that January is about 76 percent of the average. August has an index of 1.83, indicating it is about 183 percent of the average.