Don’t forget to share this post

Financial forecasting is as popular as ever, but the tools have certainly improved. If you’ve ever had to spend time on linear regressions, you’ll be happy to meet the Google Sheets FORECAST function. The function will take your historical data and return the predicted future value, so you can use it to analyze trends in your financial data. If your data shows exponential growth, you may want to consider using the GROWTH formula instead.

In this article, you will learn about the FORECAST function and some similar functions available in Google Sheets. You will also learn how to use the formula to extrapolate future sales and expenses values based on historical data.

Google Sheets FORECAST Function

The FORECAST function can be used to calculate future y-values, based on known x- and y-values. Linear regression is used to find the best fit for existing data and calculate future values based on this relationship. You can get the same results using TREND, which is an array formula.

You may have noticed that there is a FORECAST.LINEAR formula in Google Sheets. Using this will get you the same results as using FORECAST or TREND, as they all use the least squares method to find the best fit.

FORECAST Formula Syntax

The syntax for the FORECAST function is the following:

=FORECAST(x, data_y, data_x)
  • x represents the value we want to forecast (y) on the x-axis.
  • data_y represents the array of existing values for the dependent variable.
  • data_x represents the array of existing values for the independent variable.

There are some important points to keep in mind when using the FORECAST formula:

  • x values must be numerical or you’ll get a “#VALUE!” error.
  • data_y and data_x must have the same dimensions or you’ll get an “#N/A” error.

Use FORECAST to Calculate Projected Values

First, you’ll see a practical example of FORECAST to calculate the projected value for sales in a given year based on data from previous years. Second, you’ll learn how to apply this formula for projected expenses for a trimester based on the previous three.

Since FORECAST is not an array formula, this will require an extra step.

How To Use Goal Seek in Google Sheets
How To Use Goal Seek in Google Sheets

Goal Seek for Sheets is a powerful add-on for Google Sheets for data analysis. Here’s how to use Goal Seek in Google Sheets.

READ MORE

Use FORECAST to Calculate Projected Sales

In this example, FORECAST is used to predict the value for 2022, based on the known sales values for the years 2016 to 2021. Each column should contain data corresponding to the year value and the sales.

  1. 1. In Google Sheets, click on the cell where you want the projected sales value to appear.
Google Sheets FORECAST Function Examples Sales Historical Data
Google Sheets FORECAST Function (+ Examples) - Sales Historical Data
  1. 2. Type in the FORECAST formula as shown below.
Google Sheets FORECAST Function Examples Forecast Formula
Google Sheets FORECAST Function (+ Examples) - Forecast Formula
  1. 3. Select the value of x for which you want the predicted value. Add a comma to move to the following argument.
Google Sheets FORECAST Function Examples New X Value
Google Sheets FORECAST Function (+ Examples) - New X Value
  1. 4. Select the cells containing data_y, the known values for the dependent variable. Then include all the cell values from sales. Add a comma to move to the last argument in the formula.
Google Sheets FORECAST Function Examples Known Y Values
Google Sheets FORECAST Function (+ Examples) - Known Y Values
  1. 5. Select the cells containing data_x, the known values for the independent variable. Here, cell values correspond to the year. Close the parenthesis and press “Enter”.
Google Sheets FORECAST Function Examples Known X Values
Google Sheets FORECAST Function (+ Examples) - Known X Values
  1. 6. That’s it. The projected value for your chosen year should appear as shown below.
Google Sheets FORECAST Function Examples Projected Sales
Google Sheets FORECAST Function (+ Examples) - Projected Sales

How to Use GOOGLEFINANCE Function in Google Sheets?

Import current or historical financial market data from Google Finance & monitor real-time. Here's how to use the GOOGLEFINANCE function in Google Sheets

READ MORE
How to Use GOOGLEFINANCE Function in Google Sheets

Use FORECAST to Calculate Projected Expenses

Now, you’ll learn how to use FORECAST and ARRAYFORMULA to get the projected expenses for the next three months, based on the known values for the last nine months.

  1. 1. Open the historical data you want to use in Google Sheets.
Google Sheets FORECAST Function Examples Expenses Historical Data
Google Sheets FORECAST Function (+ Examples) - Expenses Historical Data
  1. 2. Select the cells where you want the projected expenses to appear.
Google Sheets FORECAST Function Examples Select Cell Array
Google Sheets FORECAST Function (+ Examples) - Select Cell Array
  1. 3. Type in the ARRAYFORMULA, as shown below.
Google Sheets FORECAST Function Examples ARRAYFORMULA
Google Sheets FORECAST Function (+ Examples) - ARRAYFORMULA
  1. 4. After the parenthesis, type FORECAST and open a new parenthesis.
Google Sheets FORECAST Function Examples Add FORECAST Formula
Google Sheets FORECAST Function (+ Examples) - Add FORECAST Formula
  1. 5. Instead of selecting just one cell for the value of x, select the array. Add a comma to move to the second argument of the formula.
Google Sheets FORECAST Function Examples New Array of X Values
Google Sheets FORECAST Function (+ Examples) - New Array of X Values
  1. 6. Select the cells containing data_y, the known values for the dependent variable. Add a comma to move to the last argument in the formula.
Google Sheets FORECAST Function Examples Known Array of Y Values
Google Sheets FORECAST Function (+ Examples) - Known Array of Y Values
  1. 7. Select the cells containing data_x, the known values for the independent variable. Here, select the month values of the first three trimesters.
Google Sheets FORECAST Function Examples Known Array of X Values
Google Sheets FORECAST Function (+ Examples) - Known Array of X Values
  1. 8. Remember to close both parentheses before pressing “Enter”.
Google Sheets FORECAST Function Examples Close Parentheses
Google Sheets FORECAST Function (+ Examples) - Close Parentheses
  1. 9. That’s it. The projected expenses for the next three months are shown below.
Google Sheets FORECAST Function Examples Projected Expenses
Google Sheets FORECAST Function (+ Examples) - Projected Expenses

Conclusion

The FORECAST formula in Google Sheets can be used to predict future values based on historical data. As with any prediction of the future, it shouldn’t be taken too literally, as it assumes that the relationship between the variables will remain unaffected in the future. However, if you have historical data that shows a linear trend and you don’t foresee big changes in the near future, the FORECAST formula can be very useful.

After reading this article, you know about the FORECAST function and how to use it to calculate projected yearly sales and monthly expenses. You’ve also learned how to use the ARRAYFORMULA to obtain multiple predicted values from FORECAST.

To learn more about financial formulas and forecasting, check out these related articles:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jul 7 2022, Updated Jun 18 2023