This series looks at linear regression for the Professional Diploma in Accounting qualification from AAT. We will start off with the high low technique, and follow up with a focus on regression in part 2.
Linear regression series
Within this article, we’ll be looking at linear regression within a time series analysis. We will look at the trend analysis part of time series analysis, specifically focusing on data that varies in a linear manner, and use the high low technique to make forecasts.
Time series analysis is a statistical technique used to:
- examine how data has changed over a given time period
- identify the underlying trend
- and then predict future figures
based on the assumption that the data will behave in the future as it did in the past.
Businesses often use time series analysis to predict both future revenues and costs. The process involves calculating moving averages and the average change over the period, adjusting for seasonal variation and forecasting.
Combining cost behaviour with linear regression
I have written about the high low technique in previous articles and have also stated that at professional level, we are expected to adjust the basic technique to accommodate more complex situations. So far we have incorporated stepped fixed costs and bulk discounts. This article is slightly different and combines our knowledge and understanding of cost behaviour with that of linear regression.
Let’s imagine we are the management accountant for a company that manufactures reusable bamboo products. and the sales for coffee cups for the last six months have been:
It’s not hard to see that sales are increasing month on month by 500 units, and if we plot the figures on a graph we can see that they produce a straight line:
It’s logical to assume that the data will continue to behave in the future as it did in the past, so we can forecast that in January sales are likely to be 6,760 cups, increasing to 7,260 in February.
This is a very simple, and to be honest, unrealistic example. However, without getting too confused by mathematical theories, linear regression techniques are based on the assumption that the relationship between two variables can be represented by a straight line and can be calculated by the equation: y = a + bx.
We know February’s sales are likely to be 7,260 cups, but now let’s prove it.
Proving the forecast
In the equation, y = a + bx:
- ‘y’ represents the forecast, so is the answer we are calculating
- ‘a’ is the first figure we have in the data set, i.e. 3,760 units. It’s the fixed point or element that the rest of the data changes in relation to
- ‘b’ is the amount by which the data increases each month i.e. 500 units. It is the variable amount per unit that will change in proportion to the number of time periods
- ‘x’ is the forecast time period, in this case February, which is seven months after the start of the period.
What we end up with is:
3,760 units + (500 units x 7 months) = 7,260 units
So how does the high low technique fit in? Well, normally it’s used to separate a total semi-variable cost into its variable and fixed elements. In effect, we are using our understanding of cost behaviour to find missing figures.
Free Excel webinar
Join us on May 1st and learn how to present effectively in Excel from expert Deborah Ashby.
Using the high low technique
If you look at the calculation above again, it could be written as:
Fixed element + (variable cost per unit x number of periods) = total semi-variable cost
It is important to note that you need to allow for context when applying the equation. For example, ‘y’ is a forecast quantity in our scenario but it can be the forecast value of either revenue or total cost. Likewise, ‘x’ can denote a number of time periods or a number of units.
Let’s say the company’s delivery costs vary in a linear manner and can be expressed using the regression equation where ‘x’ equals the number of units delivered. From past data, we know that the cost of delivering 3,800 and 6,300 units is £18,680 and £27,680 respectively.
The information tells us that ‘x’ is the quantity and gives us two sets of figures from which we can use the high low technique to calculate ‘a’ and ‘b’ because we know that ‘a’ is the fixed element and ‘b’ the variable cost per unit. The details we know are:
The variable cost per unit or ‘b’ is calculated as the difference between the two costs divided by the difference between the two quantities: £9,000 ÷ 2,500 units = £3.60 per unit delivered. The total variable elements are then calculated by multiplying each quantity by the variable cost per unit, in other words ‘b’:
The fixed elements or ‘a’ are calculated by deducting the total variable elements from the total costs:
Now if we want to forecast the cost of delivering 7,260 units in February, we can apply the equation:
I hope you’ll agree that once you apply the high low technique to the linear regression formula, you can see that really we are still using the technique to make forecasts, and that this application is no more complicated than the adjustments we have made for stepped fixed costs or bulk discounts.
Part two has more of a regression focus, where we will apply our understanding of linear regression to a more complicated scenario, with incomplete information that does not vary in a linear manner.
Read more on the high-low technique here;
- High-low technique at Foundation level: Separating a total semi-variable cost into its variable and fixed elements
- Management accounting: decision and control. High low technique with stepped fixed costs
- MDCL: High low techniques with discounts
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.