Seasonal forecasting in Google Sheets - the quick and easy way

Hi there! In this post, I’m going to show you how to quickly make forecasts on seasonal, time series data directly in a Google Sheets spreadsheet.

Pick dataset

I have a dataset of daily sales for a store from 2010 to 2018 in a Google Sheets spreadsheet. You can copy the spreadsheet from here.

I’ve plotted a line chart on the dataset; we can see that the pattern repeats itself every year.

Set objective

Using this dataset, I want to create daily sales forecasts for the year 2019. To prepare for that, I’ve extended the range in the Date column to 31st December 2019, added a column for the predicted values, and extended the chart’s data range to cover the new column.

There’s one more thing we need to decide - how much data to set aside to check the accuracy of the projections. I’m going to use the entirety of 2017 and 2018 for this.

Why FORECAST function doesn’t work

Usually people try to create the projections with Google Sheets’ FORECAST function. However, the function doesn’t work well with seasonal data because it uses a basic linear regression model. You can see it clearly in the chart.

Install ForecastSheets add-on

In order to get better predictions, I’ll use the ForecastSheets add-on, which you can install from the Google Workspace Marketplace.

After installing it, refresh the Google Sheets page, then open the add-on from the Extensions menu.

The add-on’s sidebar will appear on the right hand side of the page.

Run forecast

In the add-on sidebar, enter the cell range where it can find the input data, and the cell range where you want the estimates to be populated. In the last input box, we’ll enter the value 365, as we know the data is of daily frequency, and that it exhibits annual seasonality.

Then, click submit, and in a few seconds the predictions will be generated.

We can see that these forecasts match the actual values a lot more closely.

Conclusion

So there you have it! In just a few simple steps, we were able to generate accurate forecasts directly in Google Sheets using the ForecastSheets add-on.