Chapter 13 Averages and distribution

HAPTER

FORECASTING
1. Forecasting
Let’s say you are entering the office “Fantasy Football” league, and you are struggling
to decide which star striker to pick to fill that all-important final spot in your team.
After some debate and careful consideration, you decide the best way to choose
between players is to look at the results they have achieved in the past, how many
goals they’ve scored, how many red or yellow cards they’ve received, and how many
man-of-the-match awards they’ve got.

You, therefore, base your selection decision on the results different strikers have
achieved over time; in particular, the player whose goal tally has increased season-
upon-season.

You are, in effect, predicting that this trend of an increasing goal tally, will continue,
and this is the essence of forecasting - where the aim is to predict future results
using past data. In an accounting context, this involves using past results, for
example, sales revenues, production output and costs, along with any other relevant
information, such as market research or changes in economic conditions, in order to
predict future results.

© Astranti 2024 319

 Chapter 14 Forecasting

Example
To see how forecasting works, let's look at a problem facing Riya. She is the trainee
accountant at Air Con Ltd, an air conditioning system installer, who has been asked
to predict the 20X6 results using the actual figures from the last three years.

Period £m
Q1 20X3 (Winter) 14
Q2 20X3 (Spring) 23
Q3 20X3 (Summer) 36
Q4 20X3 (Autumn) 25
Q1 20X4 (Winter) 18
Q2 20X4 (Spring) 27
Q3 20X4 (Summer) 40
Q4 20X4 (Autumn) 29
Q1 20X5 (Winter) 22
Q2 20X5 (Spring) 31
Q3 20X5 (Summer) 44
Q4 20X5 (Autumn) 33

These results are an example of a time series, which is just a set of figures recorded
over a period of time. When Riya looks at these figures, she’s unsure of how to
analyse them. “There's no obvious pattern,” she thinks in frustration. “The results are
all over the place. Where should I begin?!”

Let's help Riya use these figures to predict the sales for the next period using time
series analysis.

2. Time series analysis

Time series analysis is a mathematical technique that involves the analysis of past
observations in order to forecast how a variable impacts a future period.

© Astranti 2024 320

 Chapter 14 Forecasting

In an ideal world, variables would move in a perfectly uniform pattern over time, with
sales revenue increasing by the same amount, say £2 million, each period. However,
one of the challenges with forecasting is that variables rarely move in a perfectly
uniform manner - as we've seen with Riya's figures. Therefore, it is important to
understand the reasons behind the changes over time if we are to forecast
accurately.

Time series analysis provides a way in which to identify both the underlying
trend in the data, for example, whether the figures generally increase or decrease
over time, and whether there are any seasonal, cyclical or random variations
which cause the actual results to deviate from the trend.

It is these four components - the long-term trend (T), the seasonal component
(S), the cyclical variation (C), and the random variation (R) - that make up a
time series analysis.

The trend (T)

The long–term trend is the underlying direction and quantity of change over
the long term. This is often influenced by things such as demographics,
technological advancements and changes in lifestyles.

A good example of a long-term trend can be seen in UK house prices between 1995
and 2015. In that time, prices rose by approximately 14% per annum. Although there
were deviations in that trend, particularly around the economic downturn of 2008
where prices decreased, there remained a long-term pattern of increasing prices.

There are three different types of trend lines which you should be aware of:
linear, logistic and exponential. Let's take a look at each:

Linear
A linear trend is a straight-line trend, and is represented by the equation:

T = a + bt

Where:

T = The trend

© Astranti 2024 321

 Chapter 14 Forecasting

a = The point at which the trend line intersects the vertical y-axis (at t=0)

b = The increase (or decrease) in one time period

t = The time period

The graph for a linear trend will look like so:

We'll take a look at how to calculate this line using regression analysis a little later.

Logistic
A logistic trend is indicative of slow growth followed by a spurt of growth and
then stagnation. This can vary in size from small and slow (the bottom line) or
quicker and larger (the top line).

© Astranti 2024 322

 Chapter 14 Forecasting

This trend can be characterised by the sale of smart phones. When a new model is
released, initial growth can be relatively slow as only the core technological
enthusiasts purchase them as soon as they are available. When everyone else realises
the benefits of owning one, sales rise sharply. The trend then slows due to saturation.

Exponential (or compound interest)

The exponential growth pattern is an example of compound interest, and is
represented by the equation:

t
T = (1 + r)

Where:

T = The trend

r = Interest rate

t = Time

© Astranti 2024 323

 Chapter 14 Forecasting

An exponential trend is shown on a graph like so:

Example
Greta deposits £10,000 into a bank account at a given annual interest rate of 10%.
Each year, the amount of interest Greta will earn is equal to 10% of the current
account value. Although the percentage is the same year-on-year, the monetary
value of the interest will increase each year as she will earn interest on the interest!

Confused? Let’s break it down…

If the initial deposit was £10,000, at the end of Year 1, the interest would be equal to
£1,000 (10% of €10,000), bringing the total of the account to £11,000.

At the end of Year 2, assuming Greta has not withdrawn or deposited any further
money, not only will she receive interest on the £10,000 deposit, she will also receive
10% interest on the £1,000 interest she received the previous year. Therefore, the
total interest received at the end of Year 2 is equal to £1,100 (10% of the £11,000
account value).

Each year, the account grows by 10%, which in monetary terms, results in the interest
payments increasing in size year-on-year.

For example, consider the size of the £10,000 deposit after 10 years. Here, using the
formula for T that we introduced earlier, where t = 10 and r = 0.1, we find:

© Astranti 2024 324

 Chapter 14 Forecasting

T = (1 + r) t = (1+ 0.1)10 = 2.59

The value of the account = £10,000 x 2.59 = £25,900

So, what would this account be worth at the end of 20 years?

Now, t = 20 and r = 0.1 and therefore:

T = (1 + r) t = (1+ 0.1)20 = 6.73,

The value of the account = £10,000 x 6.73 = £67,300

So what we can see here is that, in the first ten years, the account has grown by
£15,900 (from £10,000 to £25,900), but between Years 10 and 20, the value of the
account has increased by a whopping £41,400 (from £25,900 to £67,300).

Variations
As we have highlighted, figures rarely move in a perfectly uniform pattern over time,
and so adjustments must be made to take account of any seasonal, cyclical or
random variations that occur.

Seasonal variations (S)

These are best described as short-term trends and fluctuations which arise due to
the different circumstances that are, for example, present at different times of the
year or on different days of the week.

Examples include sales of sun cream being higher during the summer months, sales
of scarves being higher in winter, and general sales increases around holiday periods
such as Christmas.

What trends might Riya expect to see for the air conditioning company? An increase
of sales in the summer and a reduction in the winter would seem most probable.

Cyclical variations (C)

Cyclical variations happen over a much longer period than seasonal variations –
often over many years.

© Astranti 2024 325

 Chapter 14 Forecasting

The periods of recession in economies, which tend to recur every few years, are one
example of cyclic variation. In the US, for example, there have been recessions every
ten years or so – 1960, 1969, 1973, 1980, 1990, 2001 and 2007.

In Riya's case, she can largely ignore cyclical variations as her data only covers a
three-year period. Fortunately for us, this is also true in exam questions, where
cyclical variations tend not to be the focus.

Random variations (R)

These are random and impossible to forecast. Examples include a terrorist attack
or a natural disaster which can have a damaging impact on the profit of any affected
businesses. For example, the 2001 recession in the USA was closely related to the
9/11 terrorist attacks.

3. Calculating the trend

The first step is to identify the trend, and there are several different methods that
can be used to do this: the line of best fit method, the mathematical technique of
linear regression, and the high-low and moving average methods. We covered
these in an earlier chapter, but now we’ll look at them in a bit more detail over the
next two chapters.

Line of best fit

The easiest, quickest and most simple approach is to plot observations on a graph
and draw a “line of best fit”. Here's a graph of Riya's figures, the straight line showing
the line of best fit:

© Astranti 2024 326

 Chapter 14 Forecasting

We can see a nice clear trend line here. In fact, it's great because the spring and
autumn actual figures pass right through the trend line so those will be very easy to
predict!

Using the line of best fit to forecast future results

The easiest way for Riya to predict next year's numbers is to extend her graph, using
the same patterns as she found in the previous periods.

Let's do that here…

© Astranti 2024 327

 Chapter 14 Forecasting

Riya can now read her forecast from the graph.

Unfortunately, in the exam, it's unlikely you'll be asked to forecast a trend using a
graph - you'll need to be able to work out the figures more precisely using
calculations, so let’s see how to do this…

4. Calculating the variation

Once a trend has been identified, seasonal variations can be calculated. Seasonal
variations are short-term trends and fluctuations which arise due to the different
circumstances that are, for example, present at different times of the year or even on
different days of the week.

Don’t let the term “seasonal” confuse you; we’re not just talking about variations that
occur in summer and winter for example, but also variations such as sales of coffee
being higher in the morning, or an increase in customers to a shopping centre on a
Saturday.

Each time-series model is based on the assumption that seasonal variations are
either fixed amounts, in which an additive model is used, or a percentage amount,
in which a multiplicative model is used.

© Astranti 2024 328

 Chapter 14 Forecasting

The additive model is useful when the seasonal variation is relatively constant over
time, so, for example, in the instance where a shop recognises its sales are £10
million higher in summer than in winter year-on-year.

The multiplicative model is useful when the seasonal variation increases or decreases
over time and, therefore, would be used in the case where a business recognises
demand for its products is, for example, 25% higher in December each year. Here,
the size of the variation in monetary terms will differ each year depending on the
value of the underlying trend.

Let’s formalise this by looking at the models in a little more detail…

Additive model
An additive model is relevant when the seasonal variation stays the same each
period and is represented by the formula.

Y= T + S + C + R

So, the actual value (Y) is determined by adding together the values of the trend (T),
the seasonal variation (S), the cyclical variation (C) and the random variation (R).

However, in your exam, both cyclical and random variations are ignored and,
therefore, the equation can be simplified to:

Y=T+S

It is useful to see this rearranged in order to understand how the seasonal variation
(S) - which under the additive model, we refer to as the additive variation - can be
calculated simply by finding the difference between the actual value and the trend:

S=Y-T

(All components must be in the same unit in an additive model.)

Going back to Riya’s company, Air Con Ltd, let’s say that the sales of air conditioning
units are expected to be £8m above the trend in summer months and £8m below the
trend in the winter months.

So, in summer 20X3, the trend value is £24m. Sales are expected to be £8m above
the trend and, therefore, the actual value is expected to be £32m.

© Astranti 2024 329

 Chapter 14 Forecasting

In winter 20X3, the trend value is £26m. Sales are expected to be £8m below the
trend and, therefore, the actual value is expected to be £18m.

Repeating this for all of the data points in the series would result in a graph that
looks like so:

Multiplicative model
The multiplicative model assumes the seasonal variation to be a constant
proportion, a set percentage or ratio for example, and is represented by the
formula:

Y=TxSxCxR

So the actual value (Y) is determined by multiplying the value of the trend (T), by the
seasonal variation (S), then the cyclical variation (C) and finally the random variation
(R).

As with the additive model, cyclical and random variations are also ignored for the
multiplicative model and, therefore, the equation here simplifies to:

© Astranti 2024 330

 Chapter 14 Forecasting

Y=TxS

Also, as with the additive model, it is useful to see this rearranged as follows in order
to understand how the multiplicative variation (S) can be calculated simply by
dividing the actual value by the trend:

Y
S=
T

Take an example in which sales are always 10% above the trend in summer and 10%
below the trend in winter.

In summer 20X3, the trend value is £24m. Therefore, this means the seasonal
variation is £2.4m (10% of £24m), and so the actual expected value is £26.4, which is
10% higher than the trend.

In winter 20X3, on the other hand, the trend value is £26m, so the seasonal variation
is £2.6m (10% of £26m). Therefore, the actual expected sales value is £23.4m, which is
10% lower than the trend.

In this case, we'd expect to see a graph that looks more like this:

© Astranti 2024 331

 Chapter 14 Forecasting

Here, the gap above and below the trend line increases as time goes on. It's always
the same percentage increase, but as the trend rises, the size of the gap rises too.

For example, in summer 20X3, the trend is 24, and the increase is 10% which is a rise
of 2.4 to 26.4. But two years later, in summer 20X5, the trend is 32, and the increase
of 10% now represents a rise of 3.2 to 35.2. So in both periods, the increase is the
same percentage amount of 10%, but because in summer 20X5 the value of the
trend is higher, the increase is greater.

Calculating the adjusted value

Now we have identified and understood how to apply the two different models that
can be used, we next need to understand how to generate the forecasted figures,
also called the “adjusted values” because they represent the underlying trend values,
adjusted for any seasonal variation.

This process is best understood through a fresh example, in which we are going to
forecast figures for the next year, given this year’s data.

Example
Let’s consider a company making bespoke wedding rings that wants to forecast the
sales volumes for Quarters 5 to 8, using the sales figures for quarters 1 to 4:

Period Units sold

Quarter 1 135
Quarter 2 165
Quarter 3 150
Quarter 4 190

After reviewing past sales figures, the company’s management accountant has
determined that the sales follow a trend represented by the equation:

y = 100 + 25x

Where:

y = Sales in units

x = The quarter number

© Astranti 2024 332

 Chapter 14 Forecasting

Let's see how to calculate the forecasted figures for Q5 to Q8…

Step 1: Calculate the trend

The first step is to calculate the trend using the equation provided by setting the x
value equal to the quarter number and calculating the y value.

For example:

In Quarter 1: x = 1, and y = 100 + (25 x 1) = 125

In Quarter 2: x=2, and y = 100 + (25 x 2) = 150

Doing this for each of the four quarters produces the following values:

Period Units sold Trend

Q1 135 125
Q2 165 150
Q3 150 175
Q4 190 200

Step 2: Calculate the seasonal variations

Now we have the trend, we can calculate the seasonal variations for each quarter.
However, remember that two different models could be used: the additive model
and the multiplicative model.

We'll do both to show you how these work as either could be tested in your exam:

Additive model

Under the additive model, the variations are calculated by finding the difference
between the actual sales and the trend. We also saw this expressed earlier as:

S=Y-T

For example, the trending sales figure expected in Quarter 1 was 125, while actual
sales were 135. Therefore, the additive variation is equal to 10.

© Astranti 2024 333

 Chapter 14 Forecasting

Additive
Period Units sold Trend
variation
Q1 135 125 10
Q2 165 150 15
Q3 150 175 -25
Q4 190 200 -10

Multiplicative model

The multiplicative variations are calculated as a percentage difference between the

trend and the actual figures, and are found by dividing the actual value by the trend.

Y
S=
T

For example, in Quarter 1:

135
S= = 1.08
125

This, therefore, represents a positive 8% variation, i.e. the number of units sold was
8% higher than the trend.

What about a quarter where the number of units sold is less than the trend? Well, we
can still use the same equation:

190
S= = 0.95
200

Here, we can see that the number of units sold equated to 95% of the trend value.
Therefore, that is a -5% variation, i.e. the number of units sold was 5% lower than the
trend.

Doing this calculation for each of the four quarters produces the following variation
values:

© Astranti 2024 334

 Chapter 14 Forecasting

Multiplicative
Period Units sold Trend
variation
Q1 135 125 8.00%
Q2 165 150 10.00%
Q3 150 175 -14.29%
Q4 190 200 -5.00%
Step 3: Forecast sales

Now we have all we need to generate the forecasted figures. Again, we will do this
for both the additive and multiplicative models.

Additive model

The trend for Quarters 5 to 8 is forecast using the equation y = 100 + 25x as we used
earlier. For Quarter 5, the trend indicates that sales will be 225 units:

y = 100 + (25 x 5)

Period Trend
Q5 225
Q6 250
Q7 275
Q8 300

We then adjust this figure for seasonal variations by adding on the variation amounts
that we calculated earlier.

Period Additive variation

Q1 10
Q2 15
Q3 -25
Q4 -10

Now we have calculated the variations for the actual data, for Q1 to Q4, but in all
time series models, we make the assumption that the variations repeat in cycles.

So here, as we are using quarters, the additive variations will repeat every four
quarters. So, for example, in Q1 of each year, the additive variation will always be 10.
Therefore, this means the additive variation in Q5 (the first quarter of Year 2), will

© Astranti 2024 335

 Chapter 14 Forecasting

also be +10 units, and so adding this variation will result in a forecast sales figure of
235 units (225 + 10). Doing the same calculation for other quarters gives us:

Additive
Period Trend Forecast sales
variation
Q5 225 10 235
Q6 250 15 265
Q7 275 -25 250
Q8 300 -10 290

Multiplicative model

For the multiplicative model, the forecast figures in this scenario are calculated by
taking the trending sales figures and adding a percentage of sales as a seasonal
variation. The percentage to be used for each quarter was calculated in the previous
step, and like with the additive example, we assume that the variations repeat every
four quarters, so the Q1 variation is the same year in, year out.

For example, we saw earlier that our trend equation of y = 100 + 25x indicates sales
of 225 units for Quarter 5. The multiplicative seasonal variation for Q1 was calculated
as 8%, which is, therefore, the seasonal variation for Q5. So for Q5, forecast sales will
be calculated as 225 + (8% x 225), which amounts to 243 units.

The forecast for Quarters 5 to 8 using a multiplicative seasonal variation is as follows:

Multiplicative Forecast
Period Trend
variation sales
Q5 225 8.00% 243
Q6 250 10.00% 275
Q7 275 -14.29% 236
Q8 300 -5.00% 285

Additive vs multiplicative
You should notice that each forecast model produces slightly different results. In
most cases, seasonal variations will increase or decrease in line with the movement of
the trend.

© Astranti 2024 336

 Chapter 14 Forecasting

It is very unlikely that sales will increase by exactly ten units during the first quarter of
every single year, as our additive model suggests. It is far more likely that the
increase in first-quarter sales will go up as sales increase. For this reason, a
multiplicative model is usually considered the superior option.

Note that, in the real world, it would be more beneficial to calculate the trend and
seasonal variation over a number of years, rather than just one year as we’ve done in
this example. If we were able to calculate the trend over three or four years and use
averages of our yearly variations, we could smooth out any random variations, and
the accuracy of our forecast will be improved. We’ll see how this works in the next
example…

5. Forecasting a trend line using linear

regression
So far, we have seen how the trend can be determined by drawing a line of best fit
through the data points on a graph. But this is prone to human error, and so a
business will want a more accurate method of calculating the trend.

This is where linear regression comes in: a mathematical technique used to predict
the value of one variable based on the value of another variable. In a time series
analysis, this involves predicting sales or cost figures over time. We looked at linear
regression in a previous chapter, but now we will cover it in a bit more detail.

Regression analysis involves determining an equation for the line of best fit, which
will take the following form:

T = a + bt

Where:

T = The trend

a = The point at which the trend line intersects the vertical y-axis (at t = 0)

© Astranti 2024 337

 Chapter 14 Forecasting

b = The increase (or decrease) in one time period

t = The time period

The values of a and b can be calculated using the least squares formula given on
the formula sheet. These formulae require us to understand what each of the
variables n, x and y represents:

• x represents the quarter number, e.g. the first quarter is x = 1, second

quarter x = 2 and so on

• y represents the actual data, e.g. Q1 is 24.6, Q2 is 38.4 and so on

• n represents the number of items of data, e.g. n = 16

The symbol ∑ also features in the formula, which simply means the “sum of”.

(n∑xy - ∑x∑y)
b= and a = y – bx
(n∑x2 - (∑x)2)

Where ȳ represents the average of the y values and x̄ represents the average of the x
values:

∑y ∑x
y= and x=
n n

These formulas may look complex, but they're not that bad when you know how they
work. Let’s see them in action in an example…

Example

Gavin, the accountant at a company manufacturing electric heaters, has a similar

problem to Riya. He's been asked by his boss to forecast sales for the next year
based on past data, and again, the data looks random. Here's his data:

© Astranti 2024 338

 Chapter 14 Forecasting

Time £m
20X0 Q1 24.6
20X0 Q2 38.4
20X0 Q3 36.9
20X0 Q4 48.0
20X1 Q1 32.3
20X1 Q2 44.8
20X1 Q3 42.0
20X1 Q4 60.3
20X2 Q1 39.8
20X2 Q2 47.6
20X2 Q3 54.9
20X2 Q4 72.8
20X3 Q1 56.9
20X3 Q2 59.1
20X3 Q3 59.9
20X3 Q4 72.0

The scatter plot of the above data is as follows:

© Astranti 2024 339

 Chapter 14 Forecasting

As you can see, there is an upwards rising trend, but it's not exactly a straight line. So
Gavin could use a line of best fit method to determine the trend, by drawing a
straight line through the data keeping approximately the same number of plots on
either side of the line like so:

However, this isn’t the most accurate way to determine the trend, and Gavin’s
managers want accuracy in their figures! So let's see how Gavin can use linear
regression and time series analysis to make his predictions, firstly by working out the
trend line which we'll do in this section and then calculating the variation and final
forecast which we'll do in the next step.

Step 1: Calculating the trend line

We first need to construct a table (below) containing the x-values, representing the
quarter number, and the y-values, representing the actual sales figures in £ms
(millions).

As the formula requires us to know xy and x2, we also put those as headings into a
table and work them out. Finally, we need to calculate the totals for each column,
which are shown in the bottom row of the table.

Time Period £m
(x) (y) (xy) (x2)

© Astranti 2024 340

 Chapter 14 Forecasting

20X0 Q1 1 24.6 24.6 1

20X0 Q2 2 38.4 76.8 4
20X0 Q3 3 36.9 110.7 9
20X0 Q4 4 48.0 192.0 16
20X1 Q1 5 32.3 161.5 25
20X1 Q2 6 44.8 268.8 36
20X1 Q3 7 42.0 294.0 49
20X1 Q4 8 60.3 482.4 64
20X2 Q1 9 39.8 358.2 81
20X2 Q2 10 47.6 476.0 100
20X2 Q3 11 54.9 603.9 121
20X2 Q4 12 72.8 873.6 144
20X3 Q1 13 56.9 739.7 169
20X3 Q2 14 59.1 827.4 196
20X3 Q3 15 59.9 898.5 225
20X3 Q4 16 72.0 1,152.0 256
Totals ∑x =136 ∑y =790.3 ∑xy = 7540.1 ∑x2 =1496

Now we can work out by inputting our figures from the table into the least squares
formula:

(n∑xy - ∑x∑y)
b=
(n∑x2 - (∑x)2)

(16 x 7,540.1) – (136 x 790)

=
(16 x 1,496) - 1362

120,642 -107,481
=
23,936 -18,496

= 13,161

© Astranti 2024 341

 Chapter 14 Forecasting

5,440

= 2.42

Now in order to calculate a, we must first find the values of ȳ and x̄:

∑x 136
x= = = 8.5
n 16
∑y 790.3
y= = = 49.39
n 16

We can now input these values into the formula for a:

a = y – bx

a = 49.39 – 2.42 (8.5)

a = 28.82

Finally, we can substitute the values for a and b into the trend line formula:

T = a + bt

T = 28.82 + 2.42t

Where t represents the number of quarters with the first quarter, t=1, being Q1 2010.

Step 2: Trend line forecasts

We can now make a forecast trend line value based on the data.

Let's say we want to forecast the trend in Q2 of 20X4, as Q4 of 20X3 is represented

by t =16, then Q2 of 20X4 is represented by t=18. Therefore:

T = 28.82 + (2.42 x 18)

© Astranti 2024 342

 Chapter 14 Forecasting

T = 72.38

In the same way, let's also calculate the trend for all of the periods between Q1 20X0
and Q4 20X3 and for the next year (20X4) too, simply by substituting the quarter
number (shown in the table as the period), into the trend line formula.

Period Time Trend

1 20X0 Q1 31.24
2 20X0 Q2 33.66
3 20X0 Q3 36.08
4 20X0 Q4 38.50
5 20X1 Q1 40.92
6 20X1 Q2 43.34
7 20X1 Q3 45.76
8 20X1 Q4 48.18
9 20X2 Q1 50.60
10 20X2 Q2 53.02
11 20X2 Q3 55.44
12 20X2 Q4 57.86
13 20X3 Q1 60.28
14 20X3 Q2 62.70
15 20X3 Q3 65.12
16 20X3 Q4 67.54
17 20X4 Q1 69.96
18 20X4 Q2 72.38
19 20X4 Q3 74.80
20 20X4 Q4 77.22

The trend line is not what his boss will be interested in though! She will want a
forecast that adjusts for seasonal variations. While we've seen how to do that in
previous sections, in Gavin's case, we have data across four years, and the variations
are not equal each quarter (notice how there is no obvious pattern above and below
the line in the following graph), so we'll need a different method.

© Astranti 2024 343

 Chapter 14 Forecasting

In this case, the average variations should be used. Let's see how to calculate them in
the next section.

6. Variations around the trend line

The first step when calculating the variations around a trend line is to calculate the
trend. As we calculated this in the previous section, this is a nice easy first step for us!

Next, we need to calculate the seasonal variations. The multiplicative and

additive models could both be used here, and we find the seasonal variations in
exactly the same way as we did earlier.

So, for the additive model:

Seasonal variation = Actual Value – Trend

Whereas for the multiplicative model:

Actual value
Seasonal variation =
Trend

We'll assume a multiplicative model is used in Gavin’s case (but we could just as
easily do this for the additive model). Therefore, the seasonal variations are as
follows:

© Astranti 2024 344

 Chapter 14 Forecasting

Time Trend £m (y) Variation (S)

20X0 Q1 31.24 24.6 0.79
20X0 Q2 33.66 38.4 1.14
20X0 Q3 36.08 36.9 1.02
20X0 Q4 38.50 48.0 1.25
20X1 Q1 40.92 32.3 0.79
20X1 Q2 43.34 44.8 1.03
20X1 Q3 45.76 42.0 0.92
20X1 Q4 48.18 60.3 1.25
20X2 Q1 50.60 39.8 0.79
20X2 Q2 53.02 47.6 0.90
20X2 Q3 55.44 54.9 0.99
20X2 Q4 57.86 72.8 1.26
20X3 Q1 60.28 56.9 0.94
20X3 Q2 62.70 59.1 0.94
20X3 Q3 65.12 59.9 0.92
20X3 Q4 67.54 72.0 1.07

Following the calculation of the seasonal variations, the next step is to work out
the average (mean) seasonal variation. We do this by calculating the averages for
each quarter:

For example, for Quarter 1, we need to add together the four quarter 1 variations
and divide by the number of Q1s in our time series. The calculation is therefore:

0.79 + 0.79 + 0.79 + 0.94

= 0.83
4

We can do the same for each of the quarters and find the averages as follows:

© Astranti 2024 345

 Chapter 14 Forecasting

Quarter Average
Q1 0.83
Q2 1.00
Q3 0.96
Q4 1.21

Now we’re ready to make a prediction! As we are using the multiplicative model, we
use the following formula to find the forecasted values:

Actual (forecasted) values (Y) = Trend (T) x Seasonal variation (S)

Essentially, we’re taking the trend values we calculated earlier and multiplying them
by the average seasonal variation for that quarter, which we calculated using the
figures for 20X0 to 20X3.

The calculation below has been performed based on the rounded figures. In your
exam, take note of how the question asks you to round your answers as this could
affect your final answer.

Period Trend Av. Variation Forecast (£m)

20X4 Q1 69.96 0.83 58.07
20X4 Q2 72.38 1.00 72.38
20X4 Q3 74.80 0.96 71.81
20X4 Q4 77.22 1.21 93.44

Finally, Gavin has his forecast!

This takes into account both the trend and the seasonal variations, so it should be a
fair estimate, and his boss should be happy, particularly if the long-term trend keeps
on going up as quickly as it has in the past!

If we were using the additive model…

• We would use the following formula to find the seasonal variation:

Seasonal variation = Actual Value – Trend

© Astranti 2024 346

 Chapter 14 Forecasting

• The average variation for each quarter would be calculated in the same way as
under the multiplicative model

• The forecast would be generated by adding the average seasonal variation to

the trend (Actual value = Seasonal variation + Trend)

7. Correlation
In the regression analysis section, we learned how to generate an equation to define
the relationship between two different variables, the time period (x) and the revenue
(y). But what we now need to understand is just how strong the relationship
between these two variables is, also known as the correlation.

One way to do this is to draw the line of best fit through the data and see how close
each of the points in the time series, is in relation to the line of best fit.

If the line of best fit runs exactly through all of the points in the data set, then this is
an example of perfect correlation:

If there is some evidence of a relationship, for example, a generally increasing

trend or a generally decreasing trend over time, but with some variation, then this is
an example of a partially correlated data set:

© Astranti 2024 347

 Chapter 14 Forecasting

If there is no evidence of a relationship between the two variables and they simply
seem completely unrelated, then there is no correlation:

As well as defining two variables as either perfectly correlated, partially correlated or

having no correlation, the relationship can also be defined as positive or negative.

© Astranti 2024 348

 Chapter 14 Forecasting

Continuing with Gavin’s figures, and plotting the line of best fit through the data
points, we can see that the points do vary in relation to the line of best fit, and
although there is a general trend of revenues increasing over time, there is not a
perfectly linear pattern:

© Astranti 2024 349

 Chapter 14 Forecasting

Therefore, we can say that there is a partial correlation between the time period and
the revenues.

Correlation coefficient
The correlation between two variables can also be calculated mathematically using
the correlation coefficient. This is represented by r and can be calculated using the
following formula:

(n∑xy) - (∑x∑y)
r=
√[(n∑x2 - (∑x)2)(n∑y2 - (∑y)2)]

“Oh, no!” we hear many of you cry. “That equation looks even worse than the
regression analysis one!” Again, the key lies in constructing the correct table and
substituting in the correct values, and it's not too bad when you know how to do it!

If the value of r is close to 1, then this indicates a strong positive correlation, if

the value is close to 0 then this indicates there is no correlation between the
variables, and if it is close to -1 then the correlation is strongly negative.

Example
So let's take some new data, this time comparing costs (y) against the number of
units made (x) and see how well correlated the data is.

No of units Total costs

(x) (y) (xy) (x2) (y2)
(000’s) (000’s) (000’s) (000’s) (000’s)
2.0 21 42,000 4,000 441,000
1.5 19.5 29,250 2,250 380,250
3.0 24 72,000 9,000 576,000
2.5 23.2 58,000 6,250 538,240
3.5 25.5 89,250 12,250 650,250
2.2 22.2 48,840 4,840 492,840
∑x = 14.7 ∑y = 135.4 ∑xy =339,340 ∑x2 = 38,590 ∑y2 = 3,078,580

© Astranti 2024 350

 Chapter 14 Forecasting

Then we need to substitute these values into the formula.

(n∑xy) - (∑x∑y)
r=
√[(n∑x2 - (∑x)2)(n∑y2 - (∑y)2)]

(6 x 339,340) – (14.7 x 135.4)

r=
√[(6 x 38,590) – (14.7)2) (6 x 3,078,580 – (135.4)2)]

2,036,040 – 1,990.38
r=
√[(231,540 – 216.09) (18,471,480 – 18.333.16)]

2,034,049.62
r=
√[(231,323.91) (18,453,146.84)]

2,034,049.62
r=
2,066,072.14

r= 0.985

As the answer is near to 1, this means that there is a very strong correlation between
the x and y values in our data set. Put another way, if we were to draw a line of best
fit, all of the data points would lie very close to the line so for this data there is little
need to adjust for variations – we can simply use the trend line to make our
predictions!

Coefficient of determination
The coefficient of determination is a simple calculation, measuring the extent to
which a change in y is determined by a change in x. It is calculated by squaring
the correlation coefficient, r, therefore:

Coefficient of determination = r2

If the coefficient of determination is 0 that means the change of the X value had
nothing to do with the change in the Y value. If the coefficient of determination is 1
then the change in the Y value was solely down to the change in the X value.

© Astranti 2024 351

 Chapter 14 Forecasting

So, using the correlation coefficient we have already calculated, we can now work out
the coefficient of determination for the data set:

r = 0.985

2
r = 0.970

As can be seen from the line of best fit, there is a very strong relationship between a
change in the value of Y and a change in the value of X.

Rank correlation coefficient

Also known as the Spearman's rank correlation coefficient, the rank correlation
coefficient, expressed as RS, is used to determine the correlation (if any) between
the rankings of two distributions. This may sound complicated, but the theory and
reasoning can be explained with an example.

Let's say we have ten employees and we conduct two tests on them: the first one
tests their productivity and the second one their happiness. We then rank them in
order of both test results:

Employee Productivity ranking Happiness ranking

Jane 2nd 5th
Mary 10th 8th
Luke 3rd 4th
James 7th 6th
Sophie 1st 3rd
Tim 5th 1st
Martin 8th 9th
Susan 4th 2nd
Becky 6th 10th
Ben 9th 7th

We can now use the rank correlation coefficient to see if there is any relationship
between the rank achieved for productivity and the rank achieved for happiness.

The rank correlation coefficient can be calculated like so:

© Astranti 2024 352

 Chapter 14 Forecasting

6 ∑ d2
Rs = 1 -
n (n2 - 1)
Where:

n = The number of points in the data set

d = The difference between rankings

So, let's calculate this using our employee ranking data:

Productivity Happiness Difference in Difference

Employee
ranking ranking rankings squared
(d) (d2)
Jane 2nd 5th 3 9
Mary 10th 8th 2 4
Luke 3rd 4th 1 1
James 7th 6th 1 1
Sophie 1st 3rd 2 4
Tim 5th 1st 4 16
Martin 8th 9th 1 1
Susan 4th 2nd 2 4
Becky 6th 10th 4 16
Ben 9th 7th 2 4
∑ d2 = 60

Here, n is equal to the number of employees, so n = 10.

Which, when inserted into the formula, gives us:

© Astranti 2024 353

 Chapter 14 Forecasting

6 x 60
Rs = 1 -
10 (100 - 1)

360
Rs = 1 -
990

Rs = 1 – 0.364

Rs = 0.636

The closer to 1 the figure is, the better the correlation. So, we can see that there
is, in fact, some correlation between the ranking for productivity and happiness.
However, it is fairly low so could not be considered a strong correlation.

So what might we conclude? Making staff happy is worth doing to ensure they are
more productive, but it shouldn't be our total focus! Perhaps we also need to put
time into new processes, IT or training.

© Astranti 2024 354