Chapter 15: Using Formulas for Financial Analysis
15
This workbook is available on this book’s website at http://www.wiley.com/go/excel2019bible. The
file is named forecasting example.xlsx.To create a forecast, start with historical, time-based data—for example, monthly sales.
Figure 15.25 shows a simple example. Column B contains monthly sales data from 2012
through 2015. We also created a chart, which shows that sales tend to be cyclical, with
lower sales during the summer months. The goal is to forecast the monthly sales for the
next two years.
FIGURE 15.25
Four years of monthly sales data
Start by selecting the data. For this example, we selected the range A1:B49. Choose Data
➪ Forecast ➪ Forecast Sheet, and Excel displays the Create Forecast Worksheet dialog box
shown in Figure 15.26. (Options is clicked to display additional parameters.) The dialog box
shows a chart that displays the historical data, the forecasted data, and the confidence
limits for the forecast.