In this lesson, you will learn how to use the forecast function and how to... predict the future. The Forecast function can either calculate or predict the future value with the help of the current value. A predicted value is the y-value that comes from a given x value. The popular values are both the x- and y-values, and the new value will be predicted with the help of linear regression.
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.
It can look difficult, but it is not. Let's see some examples to explain more.
Forecast function examples
Example 1: A Simple Forecast Formula
The data in this example has been laid out on the table, and we need to try and predict the future value. Our business would need to predict the future, and we are using the date to acknowledge future predictions.
Example 2: IF and Forecast
This is about using the forecast in combination with IF functions. These two will address the issues that are associated with knowing how to predict the future. This data already makes it possible and looks like this:
Example 3: Minimum and Forecast
The business is booming. But, we do not want to make assumptions about the ways that the business was built. The usage of the forecast in this example, is about using the minimum sales.
Example 4: Maximum and Forecast
The maximum and forecast formulas are combined together to get the result.
Example 5: The Double Forecast Formula
This example uses two different forecast formulas together:
Example 6: Match and Forecast
The business is having some misunderstanding of the whole data set, and we need to know that the business itself has full comprehension. This example uses both Match and Forecast functions.
Example 7: Multiple Results
This example handles issues that explain how to use the same data, and get results for three different cells. These cells will be marked before you type in the formula. Once the formula is written, then you could press F2, followed by CTRL (CMD on an apple) + SHIFT, and then press enter.
Example 8: Forecast minus Minimum
This is the formula where we are working on more sophisticated information. The example explains the prediction of the future value, but also minus the minimum of the details.
Example 9: Forecast with Text
This example is what makes it possible to predict something, especially under the circumstances that it has been laid out and labeled.
Example 10: Average function and Forecast
The understanding of this situation is, we would like to use the average function in combination with the forecast function to find out the value of the prediction.
Example 11: 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.
Example 12: 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
known y's is historical net income.
known x's are historical sales.
Formula here is =FORECAST(B11,C3:C10,B3:B10)
Net income equals $11,921.
Example 13: Date forecasting
You can also forecast dates. Here’s an example.
In this table, you have data on sales at the end of each month. You want to know where you will have a million dollars in sales.
x is $1,000,000
known y's are historical dates.
known x's are 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.
The result is 2011-11-03. Excel predicts that your company will have 1 million USD in sales on that day.
There are some basic examples. Of course, there are also many ways to use the forecast function for business. The forecast function is easy to use and really powerful.
Tip in case of errors:
If the x argument is nonnumeric, the FORECAST function returns the #VALUE! error.
If known_y's and known_x's are empty or contain a different number of data points, the FORECAST function returns the #N/A error.
If the variance of the known_x argument is zero, the function FORECAST returns the #DIV/0! error.