Click on the chart tittle and change to “ Sales Forecast Report”Ī) Right click on one of the series (blue bars).Ĭ) Select Linear under Trend/Regression Type and type 6 as your forecast period as demonstrated below.Ī linear trendline has been inserted and can help you to forecast future sales trends. Select a cell within the data range and press F11.ģ. We start by creating a column chart for the data below.Ģ. and then click Layout > Trendline > Two Period Moving Average. Note: Download the sample workbook to practice this exerciseġ. For example, you have created a column chart in Excel as below screenshot shown.
The smaller the interval, the closer the moving averages are to the actual data. Conclusion: The larger the interval, the more the peaks and valleys are smoothed out. Repeat steps 2 to 8 for interval 2 and interval 4. 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. Excel cannot calculate the moving average for the first 5 data points because there are not enough previous data points. When you have existing data for which you want to forecast a trend, you can create a trendline in a chart. I have heard of problems with the trendline formula in Excel 2007 with some data sets. Fortunately Microsoft® Excel® gives you the ability to forecast quickly without cracking your head with complex mathematical models using trendlines. When I plot your data in Excel 2003, I get this closely fitting trendline: y -746.65×3 317.45×2 3.2524x + 273.16 R² 0.9996. Forecasting is integral to business success but it can be frustrating when you don’t have the right tools.