Finding Trends in Your Data

You can use the data in Excel 2007 workbooks to discover how your business has performed in the past, but you can also have Excel 2007 make its best guess as to future shipping revenues if the current trend continues. As an example, consider the following graph for Consolidated Messenger.

This graph shows the fleet maintenance costs for the years 2001 through 2006. The total has increased from 2001 to 2006, but the growth hasn't been uniform, so guessing how much maintenance costs would increase if the overall trend continued would require difficult mathematical computations. Fortunately, Excel 2007 knows that math. To have Excel 2007 project future values in the maintenance costs data series, click the chart and then, on the Layout tab, in the Analysis group, click Trendline. Click More Trendline Options to display the Format Trendline dialog box.

The Trendline Options tab page of the Format Trendline dialog box enables you to choose the data distribution that Excel 2007 should expect when it makes its projection. The right choice for most business data is Linearthe other distributions (such as Exponential, Logarithmic, and Polynomial) are used for scientific and operations research applications.

Tip

If you don't know which distribution to choose, try Linear first.

After you pick the distribution type, you need to tell Excel 2007 how far ahead to project the data trend. The horizontal axis of the chart used in this example shows revenues by year from 2001 to 2006. To tell Excel 2007 how far in the future to look, type a number in the Forecast section's Forward box. In this case, to look ahead one year, type 1 in the Forward box and then click OK to add the trend line to the chart.

Tip

When you click the Trendline button in the Analysis group, one of the options Excel 2007 displays is Linear Forecast Trendline, which adds a trend line with a two-period forecast.

As with other chart elements, you can double-click the trend line to open a formatting dialog box and change the line's appearance.

In this exercise, you'll add a trend line to a chart.

USE the Future Volumes workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Charting folder.

OPEN the Future Volumes workbook.

1.	Select the chart.
2.	On the Layout contextual tab, in the Analysis group, click Trendline and then click More Trendline Options. The Format Trendline dialog box appears.
3.	In the Trend/Regression Type area, select the Linear option button.
4.	In the Forecast area, in the Forward field, type 3.
5.	Click Close. Excel 2007 adds the trend line to the chart.

CLOSE the Future Volumes workbook.