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.