Trend analysis – Level 4 study tips

The aim of this article is to cover:

• What is a trend?
• How do we calculate a trend?
• What is a seasonal variation?
• How do we use trend analysis to make forecasts?

What is a trend?

A trend is described as general movement of data over time. A trend can be positive or negative depending on the data in question.  Once calculated, trends can be used as a means of analysing historic data so that forecasts can be made for the future. A classic example of trend analysis would be in the retail industry, where sales of goods could be analysed over time and the underlying trend could be calculated.  In turn, the underlying trend can then be used to make forecasts for sales in the future. Once a forecast has been made then the business can be managed accordingly, for example inventory management, decisions about whether to continue selling certain products and whether the business needs any further investment to support growth.

How is a trend calculated?

In order to calculate the movement of data over time we can start by calculating the moving average.  The moving average is a means of calculating averages of our actual data in small batches, this process ‘smooths out the peaks and troughs’ of the data over time.  The following data is a series of data representing sales of a product in units over a 12 month period.

It is clear here, that any trend or underlying pattern is not easy to spot. If we plot this data on a graph we can see that the data is ‘messy’.

In order to smooth out this data we can calculate a moving average.  To calculate a three point moving average we:

• Add up the data from January, February and March
• Divide by three
• List the answer next to the data from the ‘middle’ month (February)
• Repeat the process, this time exclude the first month (January) from the first calculation and include the next month (April)
• Repeat for all months

By calculating the moving average the underlying pattern of the data will emerge. It is important that at this point it is recognised that there will not be a moving average for January or for December as we have insufficient data to calculate them.

Important note: The same process can be followed for any moving average that is an odd number (three, five, seven etc. moving average). If dealing with a moving average for an even number (a four point moving average for example) then see the end of the article to see how to deal with this.

Table with three point moving average

The three point moving averages show a clear pattern, increasing steadily every month.  We can apply the following formula to calculate the average increase in trend per month:

Most recent trend less – Opening trend

Number of trends less 1

Transferring our own data into this formula:

This calculation tells us that the underlying trend is an increase of 90 units per month.

Plotting the trend line on a graph

Looking closely at the data, it is clear that although the trend line is linear, the actual data fluctuates around this line.  In some months the sales are above the trend line, in others the sales are below it.  The trend line cannot go beyond November based on the data given because we would require the actual data for November, December and January (added together and divided by three).  The gap between the actual data and the trend line is known as the seasonal variation.

Seasonal variation can be described as the difference between the trend of data and the actual figures for the period in question.  A seasonal variation can be a numerical value (additive) or a percentage (multiplicative).  The term ‘seasonal’ is applied to a time period, not necessarily a traditional season (summer, autumn etc.).  For example sales may be a lot higher for a store around Christmas, but lower in January.

Making forecasts

As we have established previously, the graph cannot extend beyond November (trend line) and December (actual data) because we have insufficient data.  We now need to be able to work with what we have, in order to make forecasts for the future months and years.  The ability to make forecasts for the future is a fundamental aspect of management accounting as it enables a business to make realistic and informed decisions.  It is a realistic scenario that a business owner would like a projection of future sales revenue and trend analysis would be a useful tool in answering this question.

In order to make a forecast, we will have to take into account two factors:

1. The trend
2. The seasonal variation

The key formula connecting our data is as follows:

Actual Data or Forecast Data = Trend + Seasonal Variation

This formula can be switched around:

Trend = Actual Data or Forecast Data – Seasonal Variation

Seasonal Variation = Actual Data or Forecast Data – Trend

It will be this last formula that will be used to find the seasonal variation for our data.

Now we have established the variations, it is clear that they are operating on a repeating cycle (-450 then -900 then +1350).  These sorts of seasonal variations could occur in numerous industries.  An example of an industry which may find it operates on a pattern similar to the one we have calculated is a pharmacy which despatches prescriptions of contact lenses on a quarterly basis. The pharmacy may find that customers rush in at the end of a quarter to collect their prescription but in the other 2 months they are not as busy.

Now that we know the average trend movement (+90) per month and the seasonal variation cycle, we can make forecasts for future time periods.  This can be accomplished by doing the following:

• Using the November three point moving average (trend) as a starting point
• Add 90 for every additional month required
• Add or subtract the relevant seasonal variation, taking into account the repetitive nature of the seasonal variations

Forecast data =

Last calculated trend + (average trend movement x number of periods after November*) + seasonal variation**

*as a numerical value, December = 1, January = 2 etc.

**the relevant seasonal variation depending on the month required

Workings

December    = 4230 + (90 x 1) – 900    = 3420

January         = 4230 + (90 x 2) + 1350 = 5760

February       = 4230 + (90 x 3) – 450   = 4050

Table showing all forecast sales until June

The graph below shows an extended version of the graph previously used.  It demonstrates the forecast data beyond the limitations (time constraints) of the data we were given.

Why is this useful?

A management accountant could use this technique to make forecasts.  This would then be communicated to either their own manager, or perhaps a client they are working for.  If a trend is negative, it could be that this forecast is used to predict when sales for a product are going to drop below the break-even point and hence stop becoming profitable.

Trend analysis is not only useful for sales figures but also other areas of different businesses such as production and purchases.

Limitations

When using trend analysis it is always important to bear in mind that the further into the future our forecasts are, the less reliable a forecast will become.  Nobody can realistically predict the future and although the seasonal variations in this example are neat and tidy, what happens if there is a flood? Or suddenly the product we are selling becomes obsolete?  Obviously the forecast would not be an accurate one in this situation.

Further note – four point moving average

Earlier in the article, the case study looked at a three point moving average.  When looking at a four (or any even number) point average there is a little bit more work to do.  The following is an example of what to do when faced with an even number moving average.  Imagine for example you were analysing data based on four month periods:

• List the data in a vertical list, leaving a space between each row
• As with a three point moving average, systematically work your way through the data finding the average of the data in small batches. This time find the averages of the data in steps of four.  The average should be listed in the row adjacent to the middle of the four numbers, so for the opening average we would list it between numbers two and three.
• Once you have calculated the averages, logically we cannot calculate any seasonal variations because the averages are not adjacent to a specific month. We therefore must work our way through and calculate the averages of our previous calculations, this time in pairs.  This new average will be listed in between each pair, so the first one on the table shown will be listed adjacent to Q3 of 20X4.
• Once this column is filled with our averages, the same process as a three point moving average can be followed and forecasts can be made.

Example:

1584 – 1824 = -240

1344 – 1776 = -432

Etc.

The completed table which could be used for forecasting purposes can now be constructed.

• The average movement in trend is -48 per quarter (1296-1824)/(12-1)
• The seasonal variations cycle every 4 months
• Forecasts could be made using this information

Forecast = 1296* – (48 x quarters after from Q2 20X7) + seasonal variation**

*last calculated trend

** The relevant seasonal variation depending on the quarter required

Forecast Sales for 20X8 are therefore as follows:

Q1 = 1296 – (48 x 3) + 480         = 1632

Q2 = 1296 – (48 x 4) + 192         = 1296

Q3 = 1296 – (48 x 5) – 240         = 816

Q4 = 1296 – (48 x 6) – 432         = 576

Conclusion

This article has shown how to calculate trends, analyse them and use them to make forecasts for the future.  The skills used are important for Financial Performance, Budgeting and the optional Cash Management unit.  The skills are also transferable to the workplace, where analysing patterns can be part of the role of a management accountant.  Communicating findings to either the management of the business or clients will potentially be of paramount importance.

To access your eLearning tools click the image below and login

Mathew Pickering is an AAT lecturer at The Sheffield College, part of the team which won Training Provider of the year (medium size provider) in 2015.