Many times, business professionals are called upon to look at prior data, gaze into a crystal ball, and make predictions for the future based on that data. Commonly, these estimates underlie budgets and forecasts that are prepared in Excel. Fortunately, for professionals needing to create these types of calculations, Excel provides a number of tools useful in the process. For those who want to use formulas, the following may be quite useful when predicting the future with Excel:
- FORECAST, and
However, one of the easiest and most powerful tools is to create a chart in Excel and add a trendline to the chart.
Figure 1 - Sample Data for Forecasting with a Trendline
For example, given the data shown Figure 1, suppose you need to create a forecast of sales for the next six weeks. One way of completing this task is to create a line chart from the historical data and then inject a trendline on the chart, and instruct Excel to build a forecast based on the trendline. From the data shown in Figure 1, the chart in Figure 2 has been created.
Figure 2 - Sample Line Chart
To create the trendline, right click on the line in the chart and select Add Trendline… from the pop-up menu to open the Format Trendline dialog box shown in Figure 3. Enter in the Forecast section of the dialog box the number of periods into the future for which you want to forecast values. Also, adjust Trend/Regression Type options to match those that provide you with the best forecast. In the example shown, a linear regression has been selected.
Figure 3 - Format Trendline Dialog Box
Upon clicking Close, Excel modifies the original chart to include a trendline for the desired six periods, as shown in Figure 4.
Figure 4 - Chart with Six Period Forecast on Trendline
Of course, forecasting and predicting future values is an art, not a science, and no technique is guaranteed to provide exact insight into what the future might hold. However, Excel’s forecasting tools – including adding trendlines to charts – provide us with great opportunities to enhance the quality of the estimates we so frequently prepare.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips83.