If you have years worth of sales data, you can use that data, with the help of Excel, to predict upcoming trends and help you plan for your business. Forecasts can help you with important business decisions, such as managing staff and inventory, planning budgets, and predicting cash flow issues. Sales are seasonal and without forecasting, it can be hard to see the bigger picture. Is your good season really good? Or is it actually just OK when compared to the past five “good seasons?” Is your bad season really bad, or just par for the course? Seasonal forecasting can help you figure these things out and help you get back on track.

Here’s how to set up a seasonal forecast in Excel:

1. Gather your data. You will need several years’ worth of data, at a minimum, and most critically, the same number of measurement units for each year. For example, if you are measuring data on a monthly basis, and you have data for three years, you should have data from three Junes, three Novembers, three Februarys, and so on. If there’s an uneven number of months, your analysis will be thrown off. Depending on your business, you can update your forecast with weekly, monthly or even quarterly data. For the purposes of this blog, we’ll assume you’re using monthly data.
2. Enter your data. In Column A, enter your months. Column B, C, D, etc. will be your data for each year (enter the years into the first row). Label the final column the “Seasonal Index,” but leave it alone for now. In the first row, list your years. You are hopefully using at least three years of data. Populate your data.
3. Using Excel, calculate the total for each year, the grand total of all your data, the average for each year, the average for each month across all years, and an overall average for all the data.
4. Calculate the Seasonal Index for each month by dividing the monthly average by the overall monthly average.
5. Deseasonalize your data by dividing the sales figure for that month by the seasonal index for that month. More advice on how to do this is here.
6. Use the forecast function in Excel to create a straight-line forecast from your deseasonalized data.

To see these steps demonstrated in a video using quarterly data, watch below:

If you’d like a general primer on creating a forecast in Excel without accounting for seasonality, click here. For a more sophisticated seasonal forecasting technique that takes into account unpredictable factors such as a stock market dip or a major change to the business that affected sales but is unlikely to recur, click here.

If all of this seems overwhelming to you, you’re not alone. Collaborating with multiple employees within Excel is not easy, and manually entering data from ERP, accounting or CRM systems can be a daunting task. While Excel is a powerful tool, it requires a lot of work to get your data ready to analyze. But there is an easier way! Consider automating your budgeting and forecasting with cloud-based IBM Planning Analytics from Datamensional.