Cash Budgeting (3) – Statistical Techniques

FFM · Chapter 6

Cash Budgeting (3) – Statistical Techniques

1 Introduction

As previously explained, budgets require us to make forecasts and therefore depend on the assumptions that we make. In this chapter we will look at several techniques that may be useful when making our forecasts.

2 Calculating growth rates

If one of the items in our budget has been growing in the past, then it may be sensible when forecasting in the future to assume that the same average rate of growth will continue.

A company has had the following sales revenue in each of the past 5 years:

Year		Sales Revenue
		$
20Y1		300,000
20Y2		384,000
20Y3		410,000
20Y4		480,000
20Y5		520,000

What is the average rate of growth rate per annum?

Solution

Although there are five years shown there are only four increments: Y1 to Y2, Y2 to Y3, Y3 to Y4 and Y4 to Y5.

If the average annual growth rate is g, then we can say that, as four applications of growth are needed:

300,000 x (1 +g) x (1 +g) x (1 +g) x (1 +g) = 520,000

300,000 x (1 + g)⁴ = 520,000

(1 + g)⁴ = 520,000/300,000 = 1.733

1+g = ⁴√1.733 = 1.147

g = 0.147 or 14.7%

3 Regression

If there is a reasonable degree of linear correlation between two variables, we can use regression analysis to calculate the equation of the best fit for the data.

For example, if we have forecast the production each month for our budget, then if we know the relationship between the cost and the level of production we can then forecast the cost each month.

This is known as least squares linear regression.

If the equation relating two variables, x and y, is

y = a + bx

then the values of a and b may be calculated using the following formulae (which are given in the examination).

x is the independent variable (like output); y is the dependent variable (like cost). Output causes costs so cost is dependent on production volume.

The following table shows the number of units produced each month and the total cost incurred:

	Units	Cost ($ ‘000)
January	100	40
February	400	65
March	200	45
April	700	80
May	600	70
June	500	70
July	300	50

Calculate the regression line, y = a + bx

Solution

To keep the numbers smaller, the volume made has been converted to hundreds (eg 200 becomes 2)

x	y	xy	x²	y²
1	40	40	1	1,600
4	65	260	16	4,225
2	45	90	4	2,025
7	80	560	49	6,400
6	70	420	36	4,900
5	70	350	25	4,900
3	50	150	9	2,500
28	420	1,870	140	26,550

y = 32.86 + 6.79x

This predicts the cost (in $000, for any volume produced where the volume is in hundreds of units. If x is in units, then the line would be:

y = 32.86 + 0.0679x

And if the cost were to be in $ also, the equation would become:

y = 32,860 + 67.9x

4 Problems with regression analysis

Results are unreliable if the regression analysis is based on only a few readings.
Regression analysis applied to any set of points will produce the best straight line joining them up even if a straight line is entirely inappropriate. For example, a curve might be a better fit than a straight line.
Care is needed when using the regression line to predict values outside the range examined (extrapolation) as no evidence of how one variable affects another has been collected from outside the range. Trying to predict a value that is within the range of data examined is safer (interpolation).
Even a good association between the variables does not prove cause and effect.

5 The correlation coefficient

If the costs and volumes in above example were plotted onto a graph it would show:

It is obvious from the graph that the cost/volume relationship is not a perfect straight line....but it's not bad. To measure how good the fit is you need to calculate the correlation coefficient, r.

A correlation coefficient of +1 indicates perfect positive linear correlation: as one variable increases so does the other and all the points can be placed on a straight line. An example could be total variable cost plotted against volume of production.

A correlation coefficient of -1 indicates perfect negative linear correlation: as one variable increases the other decreases and all the points can be placed on a straight line. An example could be volume of goods sold plotted against selling price

The further away from + or – 1, the less linear correlation exists. A correlation coefficient of zero means that there is no association - the points are effectively randomly distributed. An example could be your age in days plotted against your mobile phone number.

The correlation coefficient may be calculated using the following formula:

The coefficient of determination is r². So if r, the coefficient of correlation, = 0.9, the coefficient of determination is 0.81. This can be interpreted as meaning that 81% of the variation in the y value can be explained by (or is associated with) the change in the value of the x variable.

Using the data in the illustration above, calculate the correlation coefficient

Solution

Coefficient of correlation:

This is almost 1, showing that there is a very good fit to a straight line....and, indeed, the graph supports this conclusion.

6 Time series analysis

Managers often wish to look at the trend of costs or sales over time as a basis for forecasting the future. It is unlikely in practice that past results will follow a smooth pattern for various reasons.

Of particular interest to us in are seasonal variations which we can attempt to identify.

7 Definitions

Time series: a set of observations taken at equal intervals of time e.g. monthly

Time series can contain the following elements:

Trend: the underlying pattern of a time series when the short term fluctuations have been smoothed out.
Cyclical Variations: the wave-like appearance of a number of time series graph when taken over a number of years. Generally this corresponds to the influence of booms and slumps in an industry.
Seasonal variations: the regular rise and fall over shorter periods of time (<1 year). For example, umbrella sales are likely to be higher than average every winter and lower than average every summer. Or sales of certain products could be higher every weekend.
Random (residual) variations: these are other, unpredictable variations.

8 Moving averages

In order to estimate the trend and the seasonal variations, we use the method of moving averages.

Set out below are the sales per quarter (in 000’s of units) of a company over 3 years.

	*Quarter*
	1	2	3	4
2000	80	87	82	90
2001	90	95	93	102
2002	105	112	103	116

Identify the trend and calculate the average seasonal variation.

Solution

		Actual sales	4 ¼ average	TREND (centered average)	Seasonal variation
2000	1	80

	2	87
			84.75
	3	82		86.00	–4.00
			87.25
	4	90		88.25	+1.75
			89.25
2001	1	90		90.63	–0.63
			92.00
	2	95		93.50	+1.50
			95.00
	3	93		96.88	–3.88
			98.75
	4	102		100.88	+1.12
			103.00
2002	1	105		104.25	+0.75
			105.50
	2	112		107.25	+4.75
			109.00
	3	103

	4	116

	1	2	3	4
2000			–4.00	+1.75
2001	–0.63	+1.50	–3.88	+1.12
2002	+0.75	+4.75
	+0.12	+6.25	–7.88	+2.87
average	+0.06	+3.13	–3.94	+1.44

Steps:

Looking at the columns from left to right:

The quarters of are listed in time order: 2000 Qtr 1, 2, 3, 4 2001 Qtr1, Qtr 2 .. etc
The sales are listed in time order: 80, 87, 82, 90, 90, 95 etc
Each group of four quarters is added up and the average taken. One new quarter at a time is added and the oldest dropped as the calculations progress. So: (80 + 87 + 82 + 90)/4 = 84.75; (87 + 82 + 90 + 90)/4 = 87.25...etc. The idea is that each average contains one reading from each season so the high and low seasonal effects are averaged out.
Because this is based on 4 seasons, the averages above are 'between' quarters 2 and 3, then 3 and 4. To get them 'opposite a particular quarter, each adjacent pair is averaged again. So, (84.75 +87.25)/2 = 86 and this is opposite quarter 3 of 2000. These figures represent the trend.
Comparing the trend (in which seasonal effects have been ironed out) to the original data gives the seasonal variation..
Finally, all the seasonal variations for particular quarters are collected and averaged.

The trend line can be quantified by saying its value has gone from 86.00 to 107.25 in 7 increments so:

(107.25 - 86.00)/7 = 3.04 per season

The trend and seasonal variations can then be used to make predictions. For example, the sales for season 3 of 2003 would be predicted as follows:

Last trend figure, above is for Season 2, 2002. Season 3 of 2003 is 5 seasons further on, so its trend will be predicted as:

107.25 + 5 x 3.04 = 122.45

The Season 3 seasonal variation is -3.94, so adjusting for this seasonal dip, Season 3 of 2003 is predicted to be:

122.45 - 3.94 = 118.51

9 The multiplicative model

In the previous example we calculated the seasonal variations in terms of units.

However, if the trend is increasing it would perhaps be more sensible to accept an increasing seasonal variation.

The multiplicative model deals with this by measuring the actual seasonal variation as a percentage of trend.

Using the data from above together with the trend already calculated, calculate the average seasonal variation using the multiplicative model.

Solution - multiplicative model

		Actual sales	TREND	Seasonal variation
2000	1	80

	2	87

	3	82	86.00	95.3%

	4	90	88.25	102.0%

2001	1	90	90.63	99.3%

	2	95	93.50	101.6%

	3	93	96.88	96.0%

	4	102	100.88	101.1%

2002	1	105	104.25	100.7%

	2	112	107.25	104.4%

	3	103

	4	116

	1	2	3	4
2000			95.3	102.0
2001	99.3	101.6	96.0	101.1
2002	100.7	104.4

average	100%	103%	95.7%	101.6%

All figures and techniques are the same up to the trend column

Now, instead of taking the difference between the trend and the original data, the proportional increases or decreases are calculated. Thus 82/86 = 0.953 or 95.3%. This implies that Season 3 is a very quiet season as sales are lower than the trend (and it corresponds to the -4 on the additive model, also implying seasonally low sales).

Finally, the indices for each season are collected together and averaged.

10 Chapter 6 questions

Question 1

A business has the following results:

Year	Profit ($000)
2017	1,300
2018	1,450
2019	1,500
2020	1,580
2021	1,610

What are the average growth rates for the following periods:

(a) 2019 - 2021

(b) 2018 - 2021

Question 1

(a) 2019 – 2021

Two increments, so 1,500 x (1 + g)² = 1,610

(1 + g)² = 1,610/1,500 = 1.0733

1 + g = √1.0733 = 1.036

g = 3.6%

(b) 2018 – 2021

Three increments, so 1,450 x (1 + g)³ = 1,610

(1 + g)³ = 1,610/1,450 = 1.11

1 + g = ³√1.11 = 1.035

g = 3.5%

Four increments, so 1,300 x (1 + g)⁴ = 1,610

(1 + g)⁴ = 1,610/1,300 = 1.238

1 + g = ⁴√1.238 = 1.0548

g = 5.48%

Practice question

The following are the four elements of a time series. Which two are estimated using the moving average technique?

The trendCyclical variationsRandom variationsSeasonal variations

Question 3

If the coefficient of correlation is 0.7, what is the coefficient of determination and what is its interpretation?

Question 3

0.7² = 0.49. This means that 49% of the variation in the y value can be explained by the variation in the x value.

Question 4

A linear regression analysis on sales data against time has produced a line in the format
y = a + bx. The specific equation is:

Sales (in units) = 12,304 + 650 x Year

The analysis took 2015 as year 1.

What sales would be predicted for 2024?

Question 4

If 2015 = 1, 2016 = 2 etc, then 2024 = 10, so forecast sales are 12,304 + 650 x 10 = 18,804.

Question 5

In the context of linear regression, what is 'extrapolation' and why is care needed with that process?

Question 5

‘Extrapolation’ is the process of predicting a value outside the range of values used for the linear regression analysis. Costs v volume might have been investigated over the range of output 1,000 units to 3,000 units. Predicting costs at output of 5,000 units is extrapolation as that output is beyond what has been investigated. There is no evidence that the cost v volume relationship derived up to 3,000 units will be the same beyond 3,000 units. Eg overtime might have to be paid and this would add to costs more than predicted.

Answer the questions one at a time. Your progress is saved so you can leave and come back.

Open chapter practice