Excel Tips and Tricks / Business Reporting Question: Is there a function in MS Excel that can be used to predict future sales based on past performance or sales trends?
Answer: Yes, by using the Trend function
Description: Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.
Syntax: TREND(known_y's, [known_x's], [new_x's], [const])
Why: To predict future sales based on past performance or trends
Applies To: Excel 2003, 2007, 2010
1. The data below will be used for illustration purposes:
2. To predict the sales for periods 10, 11 and 12.
3. Select cell B11 and enter; =Trend(B2:B10,A2:A10,A11:A13).
4. The projected sales for periods 10, 11 and 12 will be as below:
The projected sales for periods 10, 11 and 12 can thus be estimated based on past trend or performance.