How to Use Microsoft Excel to Calculate Seasonal Indexes

by Ron Price; Updated September 26, 2017
Open the Excel file containing your monthly or periodic data.

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.

Step 1

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.

Step 2

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).

Step 3

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.

About the Author

Ron Price, MBA, has extensive senior level experience in business, information technology, and education. Under his pen name Ron Gilster, Price has written over 40 books for several leading publishers on a topics ranging from business and finance to IT certifications to real estate.