How to Use Microsoft Excel to Calculate Seasonal Indexes

Written by marlon trotsky
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Microsoft Excel to Calculate Seasonal Indexes
Seasonal index numbers should always total 12. (chart background image by Stasys Eidiejus from Fotolia.com)

A seasonal index is a measure of seasonal variation--a change to a variable, such as profits or sales volume, that occurs on a regular basis. For example, many toy manufacturers notice a bump in sales around holidays like Christmas and Thanksgiving. The index allows the researcher to observe what a data set would look like if you control for seasonal variation. Seasonal indexes can be represented in a graph. Microsoft Excel allows you to determine your seasonal index. After you've entered in all the data in a worksheet, you can complete the following steps to come up with the data set's index.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Open a new worksheet in your workbook and enter a title in the first row. Label A2 "Month" and B2 "Average." Enter your data into column A.

  2. 2

    Enter the array formula ="AVERAGE(IF((MONTH(Date)=$A3)*(SlsRatio>0),SlsRatio))" into the formula bar for B3. Press "Enter" while holding down "Ctrl" and "Shift" to enter the formula.

  3. 3

    Copy the formula to the range B4:B14 to determine the averages for the rest of the months of the year, by entering =SUM(B3:B14) in cell B15. If the total in B15 isn't exactly 12 you must enter =(12/$B$15)*B3 in cell C3. Then, copy the preceding formula to the range C4:C14.

  4. 4

    Copy the SUM formula {=SUM(B3:B14)} from cell B15 to cell C15. The C column's total should now be 12. Label the C column by selecting the range C2:C14 then simultaneously holding down "Ctrl," "Shift" and "F3." Double-check that only C2 is checked, then press "OK."

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.