Data Analysis for Managers

Unit 8
Forecasting Methods
Unit 8 – Forecasting Methods

Learning Objectives

At the completion of this unit, you will be able to:

• Apply the concept of business forecasting
• Explain the time series analysis
• Use time series for predicting and planning the business operations

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 2
Unit 8 – Forecasting Methods

Table of contents
S.No Details Page No.
1. Introduction 4
2. Importance of Business Forecasting 5
3. Methods of Business Forecasting 7
3.1 Time Series 8
3.1.1 Components of Time Series 8
3.1.2 Methods of Measuring Trends 13
4. Chapter Summary 23

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 3
Unit 8 – Forecasting Methods

1. Introduction
Forecasting business trends are key for successful business operations. This true of banking as banks
deal with multiple business entities. For example, there are business loans that rely heavily on
projections.
Under forecasting methods will help you make a more realistic estimate of these projections.
As a banker you will also find occasion to use it within the bank for projecting sales within the bank.
The uses of forecasting methods for a banker are wide and varied and an understanding of these
methods will provide a tool that you will find use for in multiple situations both at the bank as well as for
your own personal use.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 4
Unit 8 – Forecasting Methods

2. Importance of Business Forecasting

The business of banking is closely intertwined with firms and businesses dotting the economic landscape. Banks lend to companies and this calls for a
judicious assessment of the prospects of the business entities. Forecasting the movement of economic variables such as the movement of interest rates is
very important, as they have an impact on the bank’s business. Bank’s will be able to devise appropriate strategies based on the forecasts. Some of the
situations where forecasting is useful:
• Assessing past performance of branches to detect trends
• Assessing factors that are seasonal
• Identifying trends
• Making sales forecasts
• Understanding sales cycles and identifying their impact on business decisions
• Forecasting exchange rate movements
• Detecting early warning signals
• Determining inventory levels
Let us try to understand the necessity of forecasts in an imaginary situation.
You have taken charge as the Branch Manager (BM) of your bank in a new city. This promotion is the result of your good performance in your previous role
as the Assistant Branch Manager. You are feeling on top of the world! This branch is in a city that has recorded a lot of new investments in the previous
year. That is another reason for you to feel happy! There is a lot of potential for your career growth if you perform well in this new position.
© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 5
Unit 8 – Forecasting Methods

The first action item on your list is meeting all the bank personnel and discussing development plans for the upcoming financial year, as you have assumed
charge just before the new financial year. You see a few emails pending in the inbox and the first mail is about the business development plan for the
financial year.
So, to kickstart the planning process, you pull out business plan documents of previous three years and observe the following:
• Business development plans have several components.
• The components are related to existing clients and the acquisition of new clients, both individual and corporate.
• Business growth over the last three years is 15-18% year-on-year in most of the areas.
Now, you must develop a business development plan for the upcoming year and here are the key concerns:
• What should be your approach in drafting the business development plan?
• Will you also project growth of 15-18% or more?
In this situation, you may consider the following action items before planning:
• Study the market for opportunities and analyse the situation before creating a business development plan.
• Study the economic condition of the area/city.
• Track the changes that happened in the immediate past and their impact on the business of your branch.
• Analyze the performance of your branch staff over the last few years. Some of the personnel’s contribution may not be reflected on records because of
their recent posting to the branch.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 6
Unit 8 – Forecasting Methods

You should work out all these factors while ensuring the smooth running of the branch. It is necessary to engage some statistical methods, to understand
the information around you and form a meaningful opinion about the future with confidence. Expert judgement is sometimes needed in forecasting. But it
may not be enough due to the possibility of human biases and human errors. Business confidence indices are also popularly used, and this indicates the
present level of confidence.
3. Methods of Business Forecasting
Forecasting methods can be broadly classified into the qualitative method and quantitative method. Where quantitative uses previously available data to
make estimations about the future. Where in the case of the qualitative method individual opinions and judgments will be collected to arrive at future
estimates.
Some of the qualitative methods of forecasting are:
a) Panel Consensus - Under this method, a group of concerned individuals will come together to discuss and arrive at the conclusions and estimations.
For example: To prepare a budget for the next financial year of XYZ bank, managers of the different departments come together to discuss and arrive
at the estimates.
b) Delphi Method - Opinions of experts will be collected by circulating questionnaires for serval rounds to arrive at the conclusions and estimates.
c) Market research - Under this method, a well-structured questionnaire will be shared among the respondents, and based on the responses estimations
will be made.

Quantitative Methods :
• Time series method
• Causal Method
Time series Method: Under the time series method, a set of historical data collected at successive points of time will be analyzed to arrive at future
estimates.
© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 7
Unit 7 - Correlation and Regression

3.1 Time Series

Consider the amount of vehicle loans disbursed as shown.
Year 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
Loan amount
15 18 19 22 19 24 25 28 26 30 26
(Rs. Crores)
Based on the given information, the bank would like to forecast the target and identify trends. Analysis of such variables that follow a time sequence is
called a time series analysis. For example, changes in interest rates over a period is a variable following a time sequence. Time series methods such as
moving averages are commonly used in stock selection.
3.1.1 Components of Time Series
Consider the plot of data on air conditioner sales over the last few years.

Sales of Air Conditoners

450.00
400.00
350.00
300.00
250.00
200.00 Fig. 8.1: Time Series Analysis
150.00
100.00
50.00
0.00
Feb-12
May-09

Jun-10

Jan-11
Apr-08

Oct-08

Jul-11
Aug-06

Sep-07

Dec-09
Mar-07

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 8
Unit 8 – Forecasting Methods

You can observe a lot of variations, which may be caused by several factors, in the plotted graph. How to interpret this graph to make forecasts for the
business plan? For this, we need to understand the pattern and measurement techniques of time series. These patterns are influenced by many factors
and the factors that impact the time series are also called as components of the time series. The key components are:
a. Secular Trend
b. Seasonal Trend
c. Cyclical Trend
d. Irregular Trend

3.1.1.1 Secular Trend

This is a time series trend which indicates changes that tend to incline towards an increase or decrease in the long run. Example, changes in price indices;
the wholesale price indices grow progressively.
Population between FY05 and FY11 (in million)

1186
1170
1154
1138
1122 Fig. 8.2: Time Series – Secular Trend
1106
1089

FY05 FY06 FY07 FY08 FY09 FY10 FY11

Source: Reserve Bank of India (RBI)

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 9
Unit 8 – Forecasting Methods

3.1.1.2 Seasonal Trend

This trend reveals the variable changes occurring within a period or a season. Examples are variations in data related to weather conditions or climatic
changes. Changes can also occur during festivals. For example, gold coin sales spikes during festivals such as Akshaya Tritiya.

Air Conditoner Sales - Seasonal Peak

80.00

70.00

60.00
Fig. 8.3: Time Series – Seasonal Trend
50.00

40.00

30.00

20.00

10.00

0.00
Jun-06 Jun-07 Jun-08 Jun-09 Jun-10 Jun-11

In the example provided, you find that air condition sales go up regularly in summer, peaking in June. Similarly, you get winter clothes at 30-40-50%
discount in February-March every year as the winter wanes.
Seasonal variations are studied to adjust business activities, investment policies and inventory control. These variations occur due to:
• Customs and habits of people
• Nature of the year itself
© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 10
Unit 8 – Forecasting Methods

3.1.1.3 Cyclical Trend

Cyclical trends occur due to changes in business cycles such as depression and trough. Business cycles are said to follow a cycle wherein phases of
prosperity, decline, depression and recovery fluctuate over a period. The long-term oscillations that represent consistent rises and declines in the values of
the variable are called cyclic variations.

Cyclical Trend
250.00

Fig. 8.4: Time Series – Cyclical Trend

200.00

150.00

100.00

50.00

0.00
Nov-06 Apr-08 Aug-09 Jan-11 May-12

A study of these trends helps in deciding on an appropriate business strategy.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 11
Unit 8 – Forecasting Methods

3.1.1.4 Irregular Variation

Variations that occur due to unpredictable forces like floods, famines, earthquakes, etc., show irregular or random trends.

Random Variation
120.00

100.00

80.00

60.00 Fig. 8.5: Time Series – Random Variation

40.00

20.00

0.00
May-05 Oct-06 Feb-08 Jul-09 Nov-10 Apr-12
-20.00

These movements do not have any regular period or time of occurrences. For example, the effects of national strikes, floods, earthquakes, etc. It is very
difficult to study the behaviour of such a time series chart.
It should, however, be remembered that patterns don’t fit in with 100% accuracy and there are always unaccounted variations.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 12
Unit 8 – Forecasting Methods

3.1.2 Methods of Measuring Trends

There are many methods of measuring trends. Freehand method, semi-averages method, moving average method, the method of least squares and non-
linear trend are some of the measuring methods. We will now study some of the relatively simple and easy to use methods. The following methods can be
applied almost immediately to practical situations at bank branches:
• Freehand or Graphics Method
• Moving Average Method
3.1.2.1 Freehand Method
The easiest method of drawing a trend curve is with freehand. You can plot values against time on a graph or in an Excel sheet and join the points to
identify the trend. The trend line fitting is done by drawing a line through the middle of the data set in such a manner that the variations on either side are
more or less the same. The advantage with this method is that you can eyeball the line and derive a pattern.
The freehand method has some disadvantages:
• It depends on individual judgement.
• Trend curve is arbitrary and individual.
Example – Forecasting Loan Amount
Find a trend with the help of freehand curve method for the data given below:
Year 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011
Loan amount
15 18 19 22 19 24 25 28 26 30 26
(Rs. Crores)
© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 13
Unit 8 – Forecasting Methods

Solution:
1. Plot the data on a graph sheet.
2. Draw a trend line manually to fit the data.

Loan amount (Rs. Crores)

32
30
28
26
24
22
20
Fig. 8.6: Free Hand Plotting
18
16
14
12
10
8
6
4
2
0
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012

3. Use the trend line to forecast loan amount for the year 2012. The straight line can be extended to provide a forecast for the succeeding years. In this
case the extended red line indicating the trend touches 30.50.
4. This gives a value of about 30.5 Crores which is the forecast for 2012

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 14
Unit 8 – Forecasting Methods

3.1.2.2 Method of Moving Averages

A moving average is called as such as it takes the average of a subset of a defined period say 3 years. For example, in a dataset whereby the data is
annual starting from the year 2010 the 3 year moving average would be the average for 2010, 2011 and 2012 and the next set would be 2011, 2012 and
2013. The advantage is that it removes short term irregular variations and this process of removing irregular variations is called as smoothing. This
smoothing process to helps capture the underlying trends if any that reside within the data.
Example: Plotting smoother set of data
Consider the following data on loan.

Loan amount 3 year moving 4 year moving

Year
(Rs. Crores) average average

2001 15
2002 18
2003 19 17.33
2004 22 19.67 18.50
2005 19 20.00 19.50
2006 24 21.67 21.00
2007 25 22.67 22.50
2008 28 25.67 24.00
2009 26 26.33 25.75
2010 30 28.00 27.25
2011 26 27.33 27.50
© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 15
Unit 8 – Forecasting Methods

1. Plot the data.

2. Compute the average for 3 years. It is taken for every three years in the sense that the 2001, 2002 and 2003 form the first data set average and then
2002, 2003 and 2004 represent the second data set average and so on.
3. Compute the moving average for 3 years.
4. Plot 3 year’s moving average. This is smoother than the given data.
5. Compute the average for 4 years.
6. Compute the moving average for 4 years.
7. Plot 4 year’s moving average. Now the data set is smoother to use for forecasting.
30

28

26

24
Fig. 8.7: Measuring with Moving Averages
22

20

18

16

14
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 16
Unit 8 – Forecasting Methods

The advantages of moving averages method are:

• Objective, simple and useful for determining seasonal, cyclical and irregular variations
• Helps in identifying trends
The disadvantage is in the selection of period as it must not contain any business cycle during that period.

Causal Method - This method is based on the assumption that the variable (dependent variable) which we intend to forecast has a cause and effect
relationship with one or more other variables (independent variables).

3.1.2.3 Method of Least Squares

The method of least squares involves finding the line of best fit that minimises the sum of squares of the distances between the line and points on the
graph.
In layman terms that are less precise we can say that the line of best fit refers to finding the middle path between the data that minimises the distance
between them.
y

Fig. 8.8: Line of Best Fit

a {
X

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 17
Unit 8 – Forecasting Methods

To find a linear trend for a given time series data, we must find the values of a and b using the equations:

b=
 Xy − nXy
 X − nX 2 2

X
y
Year Year Xy X2 yc
a = y − bX Loan value
2001 15 1 15 1 16.4545
Example – Forecasting Loan Amount using the Least Squares Method 2002 18 2 36 4 17.7455
Let us look at the loan data and fit a trend line using the least square method. 2003 19 3 57 9 19.0364
2004 22 4 88 16 20.3273
2005 19 5 95 25 21.6182
2006 24 6 144 36 22.9091
2007 25 7 175 49 24.2000
2008 28 8 224 64 25.4909
2009 26 9 234 81 26.7818
2010 30 10 300 100 28.0727
2011 26 11 286 121 29.3636
ΣY = 252 ΣX= 66 Σ(Xy) = Σ(X2) = 506
1654
Y̅ = 22.9091 X̅ = 6 b = 1.2909

X̅2 = 36 a = 15.1636

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 18
Unit 8 – Forecasting Methods

Solution
Representing year on year value starting from 1 going up by 1 is called codification.
1. Plot data.
30
Fig. 8.9: Measuring with Moving Averages
29
28
2. Compute Xy and X2.
27
26 3. Compute a and b using the formula described earlier.
25
4. Compute yc based on the equation yc = a + bX.
24
23 5. Plot yc, which is the trend line using the least square method.
22
6. Forecast loan amount for any subsequent year using the trend line
21
20
equation.
19 For X = 12 i.e. 2012, y = 15.16 + 1.29X = 30.64. So, your loan forecast for
18 2012 is Rs.30.64 crores.
17
16
15
2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

The advantage of this method is that it identifies the trend based on the functional relationship between the values and the time component. Seasonality
and cyclicality getting ignored is a disadvantage of this method.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 19
Unit 8 – Forecasting Methods

3.1.2.3.1 Using Excel for Business Forecasts

Excel is a very useful tool in business forecasting. The FORECAST function available in Excel helps in forecasts. This function is based on the method of
least squares.
Example – FORECAST Function of Excel
To illustrate the use of FORECAST function, let us analyse a scenario. A bank manager is keen on forecasting the credit business of the branch at the end
of the year 2018. Following are the loan figures for the past ten years:
y
Year
Loan
2007 15
2008 18
(Please note that the figures given in the example are highly improbable; given for an intuitive
2009 21
understanding of the concept of least squares method.)
2010 24
2011 27
2012 30
2013 33
2014 36
2015 39
2016 42
2017 45

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 20
Unit 8 – Forecasting Methods

Solution
It is obvious that the figure for 2018 is 48 and we can get this just by studying the pattern. We can also get the result by using the method of least squares.
Let us use the FORECAST function in excel to get the same.
1. Add the year 2018 in the last cell.

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 21
Unit 8 – Forecasting Methods

2. Use the FORECAST function.

In the function, A14 stands for the cell showing 2018 and A3 to A13 stands for the period 2007 to 2017 and B3 to B13 stands for the values starting
from 15 and ending with 45. The FORECAST function is displayed as =FORECAST(A14,B3:B13,A3:A13).
3. The result should be displayed as follows:

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 22
Unit 8 – Forecasting Methods

4. Chapter Summary
Here are the key points discussed in this unit.
• Forecasting the movement of economic variables is very important as they have an impact on the business.
• Time series analysis reflects changes happening in a given period.
• Various trends such as secular, cyclical, seasonal and irregular can be tracked using time series analysis.
• A trend can be measured by:
o Freehand method
o Moving average method
o Least squares method

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 23
Unit 8 – Forecasting Methods

Required Reading………..

Book References
• Levin & Rubin (2008), Statistics for Management (7th Edition), Prentice Hall
Publishing
• John Weimer (2012), Statistics for Managers Using Excel, Prentice Hall Publishing
• Amir D Aczel & Jayavel Sounderpandian (2006), Complete Business Statistics (6th
Edition), Tata McGraw Hill Publishing
• Damodar Gujarati (2007), Basic Econometrics (4th Edition), Tata McGraw Hill
Publishing

Web References
• www.statistics.com

© Copyright 2017, all rights reserved. Manipal Global Education Services Pvt. Ltd. 24