Using trend lines to analyse patterns in historical data

Using trendlines is a valuable tool in analyzing historical data and discovering patterns and relationships. This tutorial describes how to use trendlines as well as some issues you may encounter.

The importance of data in business analytics

Analysis of historical data plays a vital role in business decisions. Often, data collected from past projects and investments can be useful in providing estimates for future projects.

Being able to analyse historical data and establish relationships between key variables enables us to have systematic and relevant information about business operations. This can be a significant competitive advantage as it enables a more structured approach towards decision making.

When should historical data be used?

Important questions:

• Does the past reflect the future?
• To what extent should we rely on historical estimates?

Even though the past may not always reflect the future, historical analysis may nonetheless be useful in extracting relationships between variables. At other times, there may be no other alternatives.

Historical analysis is of particular significance to industries where there are greater levels of stability. Historical data can also provide insight into the degree of variations and risk involved in estimating variables such as sales levels.  Furthermore, it can provide a “baseline” estimate, and serve as a “sense check” against alternative predictions.

Key issues in data analysis

The key questions in data analysis are:

• What range of historical data should I collect? Which data is relevant to the decision I am trying to make?
• What is the quality of the historical data? Is there data integrity?
• Which methods should I use to analyse the data, and how should I extract the data?

In this article, we will be focusing on the third question.

Importance of identifying patterns and trends

Historical data, by itself, is not directly useful in the decision making process. Instead, it is necessary to “make sense” of that data, to identify key drivers and patterns in order to be able to extrapolate meaningful information.

Another useful aspect is that the identification of trends is easier for management to understand, especially when these trend lines are presented visually in a chart.

Choosing the appropriate trend lines

We need to remember that, for any given data set, there are multiple types of trend lines in Excel that can be fitted. When you create a chart, you have the following set of possible trend types:

• Linear
• Logarithmic
• Polynomial
• Power
• Exponential
• Moving Average

It can be difficult to identify which trend line to use, because often we do not know the underlying relationship of the variables within our data, in addition to the issue of having noise within our data.

However, a good rule of thumb is to look at simpler trend lines before attempting to examine the more complex ones. For example, we should try fitting the linear trend line first, and then look at the exponential or polynomial trend lines.

According to the Occam’s razor principle, if we have two competing trend lines that explain the data equally well, we should choose the simpler one. For example, if we find that a linear and an exponential trend line both appear to explain the data equally well, we should select the linear trend line.

But how do we measure how well the trend line fits the given data? This is achieved through examining the goodness-of-fit.

Measuring ‘goodness-of-fit’

Measures of ‘goodness-of-fit’ indicate how well the trend line fits our data set. A greater level of goodness-of-fit for a particular trend line may indicate that the particular trend line has a greater level of explanatory power, and may thus be useful in forming predictions.

Measures of goodness-of-fit include:

• R-squared
• Standard Error
• F statistics
• t statistics

The danger of overfitting

Overfitting can be a significant problem in trend analysis. Overfitting can occur when the chosen trend line is too complex for the data, i.e. the trend line has more parameters than the number of observed data points.

An example of how this can occur is when we choose the polynomial trend line with too high an order.

Putting this into business context: An example

A critical question in business is: Can we predict or influence future sales levels?

This is a question where historical analysis can be useful. A common example is to plot sales over monthly intervals, which allows us to observe any cyclical or seasonal effects on sales, as well as long-term growth or decline in sales.

Furthermore, we can analyse the effect of factors from the profit-and-loss statement. For example, we can analyse the trends between sales levels and marketing expenditure.

We can also ‘zoom in’ and analyse more specific trends. Rather than looking at sales as a whole, we can analyse sales per product, or sales per region. Similarly, rather than examining overall marketing expenditure, we could analyse it by channel, for example, television-based marketing, newspapers and magazines, internet-based marketing, etc.

When examining trend lines, it is important to take into account the strength and stability of the trend. The ‘strength’ of the relationship between two variables can be examined through correlation measures. We can analyse robustness by re-running our analysis on different sample data.

Find out more

If you want more information Occam’s Razor, visit http://www.merriam-webster.com/dictionary/occam%27s%20razor

For more information on how to add trend lines to your charts, visit http://www.mrexcel.com/tip067.shtml

To find out more about non-linear trendlines and curve-fitting, see Andrew Chan’s (AlgConsultings) blog post about non-linear interpolation, which gives chart-based examples and illustrates how to estimate curve parameters using formulae
http://algconsultings.wordpress.com/2010/09/09/nonlinear-interpolation-with-excel-to-construct-us-treasury-bond-yield-curve/

COMMENTS