TUTORIAL Excel – How To Create and Project Trendlines
(Note: This Document has hyperlinks. Supporting files must be present for links to work.)
One of the side effects of Excel’s amazing mathematical capabilities is the ability to seemly recognize patterns. For example, a single variable could be tracked. Over time a pattern may emerge that could indicate a potential future result. Global temperatures, the DOW, fuel consumption, and sales growth are all examples of things that could be examined through the use of “Trendlines”. Excel has a veritable arsenal of tools and functions. If a detailed and accurate projection of exact numbers is desired, Excel provides the following functions: • • • • FORECAST LOGEST (logarithmic estimation) GROWTH FV (future value)
These functions are powerful, but complex, tools. Unfortunately, a detailed explanation of these is outside the scope of this short tutorial. For more information on specific functions and their uses, see Microsoft’s Online Help. (Hit the F1 key) For our purposes, we simply want to show how to create a trendlines on a chart. Our dataset will consist of two series of data: Sales and Claims. Two columns have been created and data entered for each month of one year. Our example begins by looking like this. We can now create a chart using this data. For specifics on creating and using Charts, see the KIP booklets “Word Essentials” and/or “Excel Essentials”. Our chart will be a clustered column chart. All cells will be referenced (including titles – cells A5:C17 in our example) All defaults were used, and the chart placed in its own worksheet. It will look like this. Once the chart is available, you can easily place trendlines by right-clicking on the series you wish to create the trend for. Right-click on any of the purple “Claims” columns, for example, and choose “Add Trendline” from the resulting menu. The window to the right will appear. From here you can change the type of the trendline. If you click on the Options tab, you will be able to change things like its name, how far forward (or back) it will forecast, and if you want to display the formulas being used. In the example we have set the clock forward by 12 “Periods”. In this case, a period is equal to one month. You should see something like this. Try creating a trendline for the “Sales” data to see where they intersect… You should now have an accurate forecast, like this.