Show trends and forecast sales with charts

Creating a sales forecast for an upcoming year can be a daunting task — especially when you're busy trying to hit your current sales numbers. Fortunately, you can use prior sales data and tools in Excel 2003 to help predict future sales within a chart. The best part is that you don't have to be a math or programming wizard to come up with your forecast — Excel does it for you!

In this article, you'll learn how to:

  • Create a chart in Excel by using the Chart Wizard.
  • Understand trendlines and how they can help you in sales forecasting.
  • Use sample sales data to create a trendline in a chart and to forecast sales four quarters into the future.
  • Find the right trendline for your data.
  • Name a trendline.

Forecasting the future

Have you ever wanted a crystal ball to look into the future and see what your sales might look like a year from now? It's not really magic (it's just math), and trendlines can help you do it.

Before you start forecasting, remember that revenue projections are only as meaningful as your baseline data. Make sure that:

  • You have enough data to show a meaningful trend. Insufficient baseline data might skew results. For example, seasonal fluctuations might be mistaken for long-term trends if baseline data is from only one year.
  • The data is ordered from earliest to most recent.
  • No data is missing. If data is unavailable for a period, enter an estimate.
  • All periods are for comparable amounts of time, such as weeks, months, or years.

For example, say you have the following data on your past sales:

Sales data

Year Sales (millions)
FY 93   .75
FY 94 1.55
FY 95 2.35
FY 96 2.22
FY 97 2.34
FY 98 2.54
FY 99 2.55
FY 00 2.75
FY 01 3.11
FY 02 3.24
FY 03 3.15

You can easily create a line chart with this data by using the Chart Wizard in Excel, which you'll learn how to do later in this article.

Line chart of sales data

Sales chart example

Then, using trendlines, you can project your sales into the future based on your past four quarters. The following is an example of a chart that has a sales projection trendline.

Chart with sales projection trendline

Sales forecast example

So what are trendlines, really?

Trendlines are graphical representations of trends in data that you can use to analyze problems of prediction. Such analysis is also called regression analysis. By using regression analysis, you can extend a trendline in a chart beyond the actual data to predict future values. For example, the preceding chart uses a simple logarithmic trendline that is forecast ahead four quarters to clearly show a trend toward rising revenue.

Hip to R² (R-squared)

So you might be wondering: How reliable are these trendlines, anyway? The answer has to do with something called R-squared — or, more specifically, the R-squared value of the trendline (this is where math comes in). Think of the R-squared value as a magic number — in this case, a number between 0 and 1.

You don't have to understand all about R-squared values — just remember: A trendline is most reliable when its R-squared value is at or near 1. When you fit a trendline to your existing data, Excel automatically calculates its R-squared value based on a formula. The more closely a trendline fits your existing data, the more accurate a forecast that uses this trendline is likely to be. If you want, you can display this value on your chart.

The best trendline for your data

Choosing the right trendline for your data is very important. Checking the R-squared value can help you choose the best trendline for your data. It also helps to understand the types of trendlines that are likely to fit different scenarios.

A linear trendline     is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.

A logarithmic trendline     is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. For example, when you start a business, sales might increase quickly. As the business matures, increases in sales are likely to be more gradual.

A moving average trendline     works well for data that fluctuates higher and lower. It smoothes out fluctuations in data to show patterns or trends more clearly. You select the number of data points that you want the trendline to average and use as a point in the line.

To learn more about the different trendline types, click "Choosing the best trendline for your data" in the See Also section.

Calculate a linear trendline in a chart

Before you can calculate a trendline, you need to have your data in a chart.

  1. Start Excel, and type or copy the sample data, including the headers, in the Sales data table that appeared earlier in this article.
  2. Highlight the data that you want to include in the chart (in this example, the contents of cells A1 through B12).

 Note   To quickly select all adjacent columns and rows that contain data, click one cell, and then press CTRL+A.

  1. On the Insert menu, click Chart.
  2. On the Standard Types tab, under Chart type, click Line.
  3. Click Finish.
  4. To remove the legend and make more room for your chart, click Legend on the Chart toolbar.

Now, calculate a trendline:

  1. Click in the chart area to select the chart.

The Chart menu appears when the chart is selected.

  1. On the Chart menu, click Add Trendline.
  2. In the Add Trendline dialog box, click the Type tab.
  3. Under Trend/Regression type, click Linear. This creates a straight trendline.
  4. Click the Options tab.
  5. In the Forward box, under Forecast, type 4.
  6. Select the Display R-squared value on chart check box.
  7. Click OK.

Chart with linear trendline

You just created your first trendline!

The R-squared value, which tells you how closely your trendline follows your data, appears on the chart. With a linear trendline, the value is .8351. As you can see by the chart, the trendline doesn't follow the data very closely because the sales rise rapidly in the first few years and then start to level out. A straight-line (linear) trendline that includes the rapid-growth early years may overestimate future sales.

Change the trendline type to logarithmic

Logarithmic trendlines are generally good for data that rises quickly and then starts to level off. You can change the trendline type and see whether it fits your data better.

  1. Click the trendline on the chart.
  2. On the Format menu, click Selected Trendline.
  3. On the Type tab, under Trend/Regression type, select Logarithmic.
  4. Click OK.
  5. If you want, you can drag the R-squared value to an area where you can see it better.

Chart with logarithmic trendline

Now the R-squared value is .9459, which tells you that the logarithmic trendline fits your data better than the linear trendline with an R-squared value of .8351. You can also tell by looking at the chart that the trendline fits your sales data better. Because sales are not likely to grow as quickly in the next four years as they did in the early years, this trendline is a better predictor of future sales.

View a moving average trendline

Moving average trendlines smooth out fluctuating values, so trends are displayed. You might be familiar with moving average trendlines in stock analysis, but these trendlines can also be useful for analyzing sales.

  1. Click the trendline on the chart.
  2. On the Format menu, click Selected Trendline.
  3. Under Trend/Regression type on the Type tab, select Moving Average, and under Period, select 3.

 Note   Period is the number of data points that is used to calculate the sequence of averages. For example, if Period is 3, the moving average is the average of the last three data points.

  1. Click OK.

Chart with moving average trendline

You can experiment with different periods. Generally, lower period values show more immediate trends, and higher period values show longer-term trends.

The R-squared value is not available with the moving average trendline.

Trendlines can help you make sense of your sales and improve your planning with better sales forecasting.

 
 
Applies to:
Excel 2003