Table of Contents
Creating a forecast in Google Sheets is a simple process that involves using the built-in FORECAST function. You need to provide the historical data to the function, which it will then use to generate a future prediction. You can also adjust the parameters of the function to make the forecast more accurate. To do this, you need to provide additional information such as the degree of confidence, the number of periods, and the confidence interval. An example of how to create a forecast in Google Sheets is provided in the instructions below.
You can use the FORECAST function in Google Sheets to calculate the expected value of some future observation using historical data.
This function uses the following methods:
Method 1: Forecast One Future Value
=FORECAST(A17, B2:B16, A2:A16)
This particular formula forecasts the y-value that corresponds to the x-value in cell A17 using the range B2:B16 as the past y-values and the range A2:A16 as the past x-values.
Method 2: Forecast Multiple Future Values
=ArrayFormula(FORECAST(A17:A19, B2:B16, A2:A16))
This particular formula forecasts the y-values that corresponds to the x-values in the range A17:A19 using the range B2:B16 as the past y-values and the range A2:A16 as the past x-values.
The following examples show how to use each method in practice.
Example 1: Forecast One Future Value
The following screenshot shows how to use the FORECAST function to forecast the sales value for 4/1/2021 using A2:A16 as the historical months and B2:B16 as the historical sales values:
The FORECAST function predicts that there will be 156.955 total sales on 4/1/2021.
Example 2: Forecast Multiple Future Values
The following screenshot shows how to use the FORECAST function to forecast the sales values for 4/1/2021 through 6/1/2021 using A2:A16 as the historical months and B2:B16 as the historical sales values:
We can see that the FORECAST function produces a forecasted number of sales for each of the three months that we specified.
Click on the last three bars in the chart to change each of their fill colors to red to indicate that they represent predicted values:
The x-axis shows the date and the y-axis shows the sales for each date.
Note 1: Under the hood, the FORECAST function simply uses simple linear regression to find the line that best fits the dataset and then uses the fitted regression model to predict future values.
Note 2: You can find the complete documentation for the FORECAST function in Google Sheets .