How to Use Forecast Function in Excel
In this Excel tutorial, you will learn how to use the forecast function and how to… predict the future. The forecast function predicts a future y-value based on existing x and y values using linear regression. This statistical method finds the best-fit straight line through the known data points, allowing Excel to estimate the y-value corresponding to a given x-value.
This function is useful for predicting future sales, along with requirements for inventory, or even customer trends.
Thanks to the forecast function, Excel can read the future. It is not a joke! Excel can predict some values if you give it past data.
You can use the forecast function to predict simple data and also conduct advanced analysis for your company. Let’s see how the forecast function works and how to use it.
Definition of FORECAST function
The forecast function has different arguments that are all required.
- X: This is essential. It is a data point, for which the user wants to predict the value.
- Known_y’s: This is also essential for the function. It is either a dependency array or data’s range.
- Known_x’s: This is essential. An independent array that can also be a data range.
While the forecast function may initially appear complex, its application is straightforward once you understand the inputs it requires. Below, we show examples to demonstrate its practical use and further clarify how it works.
Forecast function examples
How long do students study for the test?
You have some historical data about test equals. You also know, how long students have studied for the test.
You want to calculate how many points the student will get who has been studying for 8 hours. You can use the forecast function to calculate that.
- x equals 8, because you are looking for a value for an 8-hour argument.
- known y’s are test points, which you already know.
- known x’s are hours of study, which you also know.
The formula in this situation is =FORECAST(B11,C3:C10,B3:B10) as shown in the picture below:
The forecasted value is 77.
Value 77 means that, based on historical values, the student will get 77 points. I hope the forecast function is easy and clear. Let’s look at one more example.
Forecasting net income
You have sales and net income for some companies.
You want to calculate the net income for a $500,000 sale.
- x is $500,000 (sales)
- known y’s are the historical net incomes.
- known x’s are the historical sales.
Formula here is =FORECAST(B11,C3:C10,B3:B10)
Net income equals $11,921.
Date forecasting
You have data on sales at the end of each month. You want to predict the date when sales will reach $1,000,000.
- x is $1,000,000
- known y’s are the historical dates.
- known x’s are the historical sales.
Formula is: =FORECAST(B10;C3:C9;B3:B9)
The formatting of cells is General by default, so your result is not a date.
Change the formatting of the cell. Click right and next Format Cells… and change formatting to Date.
Excel stores dates as numbers. The forecast function returns a numerical value representing the predicted date. To display this number as a date, you must format the cell as a date. In Excel, dates are represented as sequential serial numbers, with January 1, 1900, being 1. The date formatting simply changes how this underlying number is displayed.
The result, after formatting the cell as a date, is 2011-11-03. Excel predicts that sales will reach $1,000,000 on that date.
There are some basic examples. Of course, there are also many ways to use the forecast function for business.
Leave a Reply