If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
What Is a Forecast?
A forecast predicts something for the future. For example, the expectations of the future numbers in terms of company growth can be called a forecast. The forecast function, in short, is used to calculate the straight lines that are based on known X and Y values. The values of Y can be calculated for the values of X inside or outside the range of the X values.
What Is a Trend?
A trend is something that represents a particular status of something currently happening. For instance, there is a trend for the youth of today’s generation to be rather glued to their smartphones. OK, maybe not all of them, but it is definitely a trend!
When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. The FORECAST and TREND function give the exact same result.
Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a good fit. The closer to 1, the better the line fits the data.
- Use the equation to calculate future sales.
- Use the FORECAST function to calculate future sales.
Note: when we drag the FORECAST function down, the absolute references ($B$2:$B$11 and $A$2:$A$11) stay the same, while the relative reference (A12) changes to A13 and A14.
- If you prefer to use an array formula, use the TREND function to calculate future sales.
Note: first, select the range E12:E14. Next, type =TREND(B2:B11,A2:A11,A12:A14). Finish by pressing CTRL + SHIFT + ENTER. The formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range E12:E14 and press Delete.