Google Sheets Forecast Function: Easy Beginner’s Guide

Discovering trends and predicting values is an essential application of financial data analysis. Google Sheets, like most spreadsheet processors, includes multiple tools for financial analytics.

In fact, just for data forecasting based on current trends, Google Sheets offers a number of tools. Some of these tools include:

1. The FORECAST Function
2. The TREND Function
3. The SLOPE function (using which you can predict any y-value, given an x-value.
4. Trendlines in Google Sheets Charts
5. Third party add-ons

In this tutorial we will cover the Google Sheets FORECAST function. We will show you how to use this function to analyze a set of known x-y values and project the trends to predict an unknown y-value, for a given known x-value.

What Does the Google Sheets FORECAST Function do?

The FORECAST function in Google Sheets predicts future y-values based on a set of known x-y values. The function uses linear regression to determine the relationship between the known x and y values.

As such, this function can be used to predict values like future market trends, inventory requirements or sales growth.

Premium Inventory Tracker

For example, a common application of the FORECAST function is in Sales forecasting. The function helps calculate anticipated measures of how sales are likely to rise in response to some of the company’s frequent market initiatives.

So, if you have historical data of monthly sales, where the month is in one column (known x-values), and the corresponding sales amount is in another column (known y-values), then the FORECAST function can be used to predict the sales amount probable for a given month in the future (provided there is a linear relationship between the time and sales amounts).

The function uses historic sales data to identify trends and then projects these trends into the future. This can help us gain insights, understand how certain measures will translate into future sales and expenses, and in the end, make informed decisions.

Syntax for the Google Sheets FORECAST Function

The syntax for FORECAST function in Google Sheets is as follows:

=FORECAST (unknown_x_value, known_y_values, known_x_values)

Here,

• unknown_x_value is the value on the x-axis that we want to forecast.
• know_y_values is the range or array that represents the set of dependent values
• know_x_values is the range or array that represents the set of independent values

The FORECAST formula takes in the set of x-values and y-values as well as the known value of x (that does not exist in the known x-values) for which we want to calculate the corresponding y-value.

It returns the possible y-value corresponding to the value in the first parameter after performing linear regression on values in the second and third parameters.

Example to Understand How to Use the FORECAST Formula For Google Sheets Predictions

Let us take an example to understand how the FORECAST function can be used in Google Sheets.

Consider the following dataset, which contains historical data on monthly sales for the past year (year 2020). Column A contains the months and column B contains monthly sales for each month:

If we plot this data on a line chart, here’s how it is going to look:

Note: To create the line chart, simply select the data range and navigate to Insert->Chart from the main menu. The line chart is the default chart that should be displayed based on our data.

As we can see from the above chart, our monthly data has an upward linear trend and there are no outliers.

Note: You can take a look at the trendline for this data. To insert a trendline, simply click on the chart, click on the hamburger icon and select ‘Edit Chart’. From the Chart Editor, navigate to Customize->Series and check the box next to ‘Trendline’.

Since the data seems to have a linear relationship, it is possible to apply linear regression to it.  

We will apply the FORECAST formula on this data to forecast revenue for February 2021.

In other words, we want to predict the sales amount (y-value) for the x-value that is in cell A14.

Type in the following formula in cell B14 and press the return key:

=FORECAST(A14,A2:A13,B2:B13)

The FORECAST function will return the predicted sales for Feb 2021 as follows:

Points to Remember About the FORECAST Function

The FORECAST function can provide invaluable information and insights. However, there are a few points that you should keep in mind when using the function:

• Make sure that there are no outliers in the data before applying the FORECAST function.
• If the x-value is not numeric, the function will return a #VALUE! error.
• The variance between the known x-values should be 0. Otherwise the function will return a #DIV/0! error.
• The known x and y value columns should both be of the same size. Otherwise, the FORECAST function will return an #N/A error.

All said and done, you’ll have to take the predictions made by the Google Sheets FORECAST function with a grain of salt, since they are not always definitive or accurate. The function simply deduces possible results based on the relationship it observes between the known variables.

It does not take into account unforeseen deviations or the possibility of trends changing due to external forces. So use the Google Sheets forecast function as a simple guide, but make sure to verify the forecasts before deciding on a definitive course of action.