Forecasting is integral to business success but it can be frustrating when you don’t have the right tools. Fortunately Microsoft® Excel® gives you the ability to forecast quickly without cracking your head with complex mathematical models using trendlines.
When you have existing data for which you want to forecast a trend, you can create a trendline in a chart. For example, if you have a chart in Excel that shows sales data for the first few months of the year, you can add a trendline to the chart that shows the general trend of sales (increasing or decreasing or flat) or that shows the projected trend for months ahead.
Note: Download the sample workbook to practice this exercise
Applies To: Microsoft Excel 2007, 2010 and 2013
1. We start by creating a column chart for the data below.
2. Select a cell within the data range and press F11.
3. Click on the chart tittle and change to “Sales Forecast Report”
4. To create a linear trendline:
a) Right click on one of the series (blue bars).
b) Select Add Trendline.
c) Select Linear under Trend/Regression Type and type 6 as your forecast period as demonstrated below.
(d) Select Close.
A linear trendline has been inserted and can help you to forecast future sales trends.
