By adding a linear trend to an Excel chart, the program can display the equation directly on the chart (see figure below). You can use this equation to calculate future sales. Functions FORECAST (PREDICTION) and TREND (TREND) give the same result.
Explanation: Excel uses the least squares method to find the line that best fits the points. R value2 is 0.9295, which is a very good value. The closer it is to 1, the better the line fits the data.
- Use the equation to calculate future sales:
=7.7515*A12+18.267
- Use the function FORECAST (PREDICTION) to calculate future sales:
=FORECAST(A12,$B$2:$B$11,$A$2:$A$11)=ПРЕДСКАЗ(A12;$B$2:$B$11;$A$2:$A$11)
Note: When we stretch a function FORECAST (PREDICTION) down, the absolute references ($B$2:$B$11 and $A$2:$A$11) remain the same, while the relative reference (A12) changes to A13 и A14.
- If you like array formulas better use the function TREND (TREND) to calculate future sales:
{=TREND(B2:B11,A2:A11,A12:A14)}{=ТЕНДЕНЦИЯ(B2:B11;A2:A11;A12:A14)}
Note: Select a range first E12: E14. Then enter the formula and click Ctrl + Shift + Enter. The formula bar will enclose it in curly braces, indicating that it is an array formula {}. To delete a formula, select a range E12: E14 and press the key Delete.