How to Use Microsoft Excel's FORECAST Formula
By Ron Price
If you have multiple pairs of numbers that represent measurements or outcomes in which one of the numbers in each pair -- the X value -- is based on the value of the other number -- the Y value -- you can use an efficient Microsoft Excel function to solve for the unknown in each pair. Excel's FORECAST() function provides a handy way to predict or forecast what the value of Y should be for any value of X.
Organize the Data
In two adjacent columns, enter the X and Y values. Although the example in the screenshot shows the values in a sequence, it is not necessary to put the values in any particular sequence. Just be sure that the X and Y value pairings remain associated with one another.
Enter the Formula
Since the Excel FORECAST() function needs only three elements, two of which you have already entered, the next step is to decide the X values for which you wish to forecast Y values. As shown in the screenshot, the FORECAST() function computes a ratio that represents the relationship between all of the X values and all of the Y values. It then uses this ratio to predict a value for Y using any specific value of X. The arguments of the FORECAST() function are, in sequence, the X value for which you wish to forecast a Y value, the range of known Y values and the range of known X values.
As shown in the example, the FORECAST() function is:
=FORECAST(E13,$C$6:$C$16,$B$6:$B$16)
where, the equal sign (=) designates this is a formula, the value $C$6:$C$16 designates the known Y values and $B$6:$B$16 designates the known X values. This function statement then "forecasts" the corresponding Y value for the specified X value (E13), based on the value pairs in columns C and B (known Y's and known X's). Enter this formula in each of the cells in which you wish to forecast a Y value for a specified X value.
Tips
In the example shown, the X and Y values were designated using what is called absolute cell references ($C$6:$C$15 and $B$6:$B$15). This designation locks the X and Y data values to the same exact location for each use of the FORECAST() function.
Apply the FORECAST function
The Excel FORECAST() function has many practical applications. It can be used to predict future sales based on the past performance of specific periods, project inventory or stock requirements for expected future sales, estimate consumer spending or demand trends, determine material inventory or stock requirements, or determine any value based on statistical pairs that indicate prior performance.
References
Writer Bio
Ron Price, MBA, has extensive senior level experience in business, information technology, and education. Under his pen name Ron Gilster, Price has written over 40 books for several leading publishers on a topics ranging from business and finance to IT certifications to real estate.