This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Lessons in Business Statistics
Prepared By
P.K. Viswanathan Chapter 12: Forecasting Introduction
Many crucial decisions made by
management
depend
upon
the
assessment of the futuredemand for
products and services, sales growth, and
cost trends. Management must forecast
the future in order to make sound
decisions today. So, managers need
efficient and reliable forecasting
methods for business planning. This
chapter presents a few widely used
forecasting techniques in practice. What is the demand for
our latest model personal
computer? 1) Forecasting  Basics
Why Forecasting?
Demand or sales forecasting is the foundation stone upon which the entire business planning is built.
An organization cannot predict its profitability without predicting
sales revenue.
Sales revenue cannot be predicted without
forecasting sales in physical quantities. The entire production program and materials resource planning
cannot be achieved without a realistic sales forecast of the various
products the organization would like to market. Corporate plans, turnaround plans and competitive business
strategies need the help of forecasting. In other words, not to
forecast is to assume status quo and do nothing. This will never be
acceptable to any manager in any organization. 1) Forecasting  Basics
Why Forecasting?
We must of course recognize the fact that future is uncertain and
therefore no forecasting can be hundred percent accurate. This is a
paradox in forecasting: on the one hand you need sales forecasts
and on the other hand no forecast can be accurate. Managers in any business enterprise have no choice between
forecasting and not forecasting because without a sound
forecasting system, the risk of making a wrong decision increases.
Managers however have choice amongst the methods of
forecasting. Forecasting Techniques in Practice
Visual Selecting the Right Forecasting
TechniqueGuidelines Availability of data: If no appropriate historical data are
available, quantitative techniques of forecasting are not possible.
Only qualitative forecasting techniques are possible. Accuracy envisaged: Greater the accuracy needed, greater is the
need for sophisticated techniques of forecasting. Urgency with which the forecast is sought: If forecasts are
required urgently, only less sophisticated techniques are possible
to use. Cost: This includes cost of forecasting exercise and what it costs
the firm if a wrong forecast is made. 2) Qualitative Methods of Forecasting
When Do You Use Qualitative Forecasting? Imagine your company is about to introduce a
new product that is unknown in the market. In
this context, there will be no historical data
that you could use to forecast your sales. It is
a situation where you will find complete
absence of any useful data. Under these
circumstances, qualitative forecasting is the
only method by which you could forecast your
sales for your new product. Qualitative Methods in Practice Expert Opinion Market Survey Delphi Method Historical Analogy Expert Opinion In this method, a group of experts from diverse background such as
marketing, sales, finance, operations, and purchasing are asked to
make forecast for the product under consideration. A consensus is
then reached on a forecast figure. Each expert brings with him/her a
set of biases, and perspectives that might influence the forecast. Of
course, their judgment would be substantiated by a wealth of
information that include past data, industry growth rates, competitive
strategies and reactions from customers and distributors. The advantages of this method: 1) It is fast and efficient. 2) It is
timely and based on good information content. 3) It uses the
collective knowledge of experts. The disadvantages of this method: 1) Experts can make mistakes. 2)
Subjectivity and bias of experts can vitiate the forecast. 3) The group
dynamics of the experts could be greatly influenced by the degree of
dominance of a particular person. He who could shout loudest might
get his way. Market Survey
In this method, you conduct a market survey of customers’
intentions to buy a product. A carefully designed questionnaire is
administered to the selected target audience of customers.
Customers are selected independently using a representative random
sample. This method is very popular and if carefully implemented
will give you good results.
This is the apt technique to use, particularly if you want to forecast sales
for a new product or new brand. This method of forecasting requires the active cooperation of the target
audience. The sample size must be reasonably large. Larger the sample size, smaller
will be the standard error and sampling error. Larger the sample size, the more time consuming and costly the survey
will be. So, you have to strike a balance between sample size and cost. Delphi Method In the expert opinion method of forecasting, a consensus forecast
is arrived at after eliciting the opinion and views of experts with
diverse background. Certainly this method is subject to group
dynamics (effects). At times, judgments may be highly influenced
by persuasions of some group members who have strong likes and
dislikes. Delphi method attempts to retain the wisdom and
accumulated knowledge of a group while simultaneously
attempting to reduce the group effects. In Delphi method, group members are asked to make individual
assessment about a forecast. These assessments are compiled and
then fed back to the members, so that they get the opportunity to
compare their judgment with others. They are then given an option
to revise their forecasts. After three or four replications, group
members reach their final conclusion. Historical Analogy
This method is applied when a new product is about to be
introduced by a company. Forecasting sales for new products
are difficult in view of lack of proper historical data.
Historical analogy method attempts to forecast sales for a
new product based on the performance of related or similar
products in the market place. The database of sales of these
products forms the basis for forecasting. Drawbacks of Historical Analogy You cannot precisely say how your new product is similar or related
to a particular product. Suppose you have a number of products that you feel are similar to
yours. Which of these will you consider as most similar to yours? Products that are similar to yours could have failed in the past for a
variety of reasons. Let us say a similar product failed in the past
because whenever there was an advertisement about this product, it
was not available on the shelf. So, the consumers developed a
negative perception about this product and became skeptical about its
availability. You may not know all these and simply conclude your
product will also fail! 3) Quantitative Methods of Forecasting
Quantitative forecasting uses statistical analysis of data
to forecast sales. Time series analysis and causal model
fall under the purview of quantitative forecasting. In this chapter, we will discuss time series analysis
(projective methods) of forecasting and Causal model
that uses regression analysis for forecasting. Please note that we have already covered regression
analysis extensively in chapter 10. You are expected to
go through chapter 10 so that you will be able to
appreciate regression method of forecasting when we
discuss it. Time Series AnalysisWhat is
Time Series? Time series are series of observations that
are taken at regular intervals of time. Data on
weekly sales, monthly sales, and annual
sales are examples of time series. Like many other data sets, if you have a time
series data set, the first step in analyzing it is
to draw a graph, particularly a simple scatter
diagram or a line graph that will reveal
sharply any underlying patterns. Components of Time Series Trend (T) represents the long term behavior of a time series. This
would tell whether the time series data reveal a steady upward or
downward movement. Seasonal Variation (S) represents variation caused by season.
Typically this shows variation in demand during peak and lean
season. For example, demand for snow tires will be at its peak
during winter in USA. Cyclical Variation(C) represents the typical business cycles that
occur sporadically in several years. For example, in stock market,
you will witness cycle of buoyancy or boom and cycle of recession
that occur once in a while between many years. Random Variation(R) represents irregular variations that occur
by chance having no assignable cause. Random variation cannot be
predicted. Essence of Moving Average The pattern revealed in observations vary
over a time horizon. Instead of taking the
average of all historical data, only the latest n
periods of the data are used to get a forecast
for the next period. This is the very essence
of moving average forecast. Moving Average (MA) Forecast for the next
period = Average of n most recent time series
data. Moving Average Example Problem A company is interested in forecasting demand
for one of its products. Past data on demand
for the last 12 months are available and given
below: Using a period of 3 months, make a
moving average forecast for period 13(13th
month). Moving Average Example Problem
Data Set
Month
1
2
3
4
5
6
7
8
9
10
11
12 Sales(100
units)
15
9
16
17
11
20
10
17
12
9
18
20 Moving Average Example Problem
Solution Moving Average –Example problem
Microsoft Excel Solution Drawbacks of Moving Average Moving averages do not react well to
seasonal variations All observations considered in a time
horizon are given the same weight A large amount of historical data should
be gathered and maintained to update
forecast values The choice of the period(n) is generally
arbitrary. Exponential Smoothing
Exponential smoothing is a particular case of moving
Average in which there are three components.
1) The forecast for the most recent period
2) The actual value for the period
3) A smoothing constant This smoothing constant is a
.
weighting factor that lies between 0 and 1.
The selection of the right kind of is matter of judgment by
the experienced user. But, it must be chosen very carefully. Exponential SmoothingContinues
Exponential smoothing is an excellent forecasting
technique for short term forecasting. It is used not only in sales forecasting but also in
forecasting input prices in materials procurement. The single biggest advantage is that this technique is
extremely simple to use. Exponential SmoothingFormula
New Forecast
forecast). = (actual value)+(1
)(last The meaning of this statement is explained with an
example. Suppose, the actual sale for month 2 is 50
units and your forecast for month 2 is 55 units. Let
us take = 0.3.
New Forecast = (0.3)(50)+(10.3)(55) =53.5. Exponential SmoothingExample
A company is interested in forecasting demand for
one of its products. Past data on demand for the last
12 months are available and given below: Using
exponential smoothing technique, forecast demand
for month 13. Take =0.2 Exponential SmoothingExample
Data Set
Month
1
2
3
4
5
6
7
8
9
10
11
12 Sales(100
Units)
15
14
16
17
15
18
20
22
23
21
24
26 Exponential SmoothingExample
Solution Exponential SmoothingExample
Microsoft Excel Solution Exponential SmoothingExample
Solution Forecast for Month 13 Forecast for the 13th Month
= 0.2(actual value)+(1
)(last forecast)
= 0.2(26)+(1 0.2)(20.2) = 21.36.
This is the demand forecast for the month 13. Points to Ponder on Selection of
Smoothing Constant The question that is often raised in the usage of exponential
smoothing is “is there a scientific way to fix the value of the
smoothing constant The answer is both Yes and No.
? Yes, because you can get that value of that gives the minimum
mean square error. No, because mean square error is highly influenced by the
square terms of individual errors. Judgment of based on experience and tracking forecasting
efficiency is the only way out. This is indeed a weakness of
exponential smoothing method of forecasting. Trend Projection In this method, we fit a trend line using the time series
data. This trend line could be linear or nonlinear
(quadratic trend, exponential trend, etc). We will discuss the linear trend that is popular and very
much used in practice. The reason for its popularity emerges from the
following rationale. Most of the nonlinear trend lines
can be converted into linear lines by mathematical
transformation. The linear trend is a reasonable good approximation of
trend pattern that is revealed by time series data. In simple terms, we fit an equation of the form
Y =a+bt using the method of least square. What happens if the trend is
nonlinear? Suppose the trend equation is of the form
Y = a + bt + ct2. How will you project future trend?
You let t2 = u. This will make the equation, Y = a +
bt + cu. This is a typical multiple regression model
that can be solved using Microsoft Excel. Suppose Y = aebt. Take Log on both sides, this
becomes Log Y = Log a + bt. This is of the form, Z
= A + bt which is a simple linear regression model
that can be easily solved. Trend ProjectionExample
A company is interested in forecasting
demand for one of its products. Past data on
demand for the last 12 months are available
and given below: Trend ProjectionExample
Data Set
Month
1
2
3
4
5
6
7
8
9
10
11
12 Sales(100
Units)
15
14
16
17
15
18
20
22
23
21
24
26 Trend ProjectionExample:Data Set
If you use formula approach, the formula will have t in
the place of X as covered in Chapter 10.
(t t)(Y Y)
b = t )
(t
2 b
a = Y t
The basic calculations are shown in the following spreadsheet. Trend ProjectionExample:Basic Calculations
Month(t) Sales (Y)
1
2
3
4
5
6
7
8
9
10
11
12 (100 units)
15
14
16
17
15
18
20
22
23
21
24
26 6.5 19.25 (t t) ( Y Y ) (t t )(Y Y ) (t t) 2 5.5 4.25 23.3750 30.2500 4.5 5.25 23.6250 20.2500 3.5 3.25 11.3750 12.2500 2.5 2.25 5.6250 6.2500 1.5 4.25 6.3750 2.2500 0.5 1.25 0.6250 0.2500 0.5 0.75 0.3750 0.2500 1.5
2.5 2.75
3.75 4.1250
9.3750 2.2500
6.2500 3.5 1.75 6.1250 12.2500 4.5 4.75 21.3750 20.2500 5.5 6.75 37.1250
149.5000 30.2500
143.0000 Trend ProjectionExample:Forecast for Month
13
(t t )(Y Y )
b=
= (149.50/143.00) = 1.045
(t )
t 2 b
a = Y t = 19.251.0455*6.5 = 12.45 So, the fitted line is given by
Y =12.45+1.045t
Forecast demand for month 13 = 12.45+(1.045)(13)
=26.04(units of 100) Multiple Regression Forecasting
Things to do in a Multiple Linear Regression Model Postulate the model Y = a+bX1+cX2+dX3+………….. Enter the sample data for Xs and Y in Microsoft Excel. Perform the Regression Analysis and get the summary output from Excel Write the Regression Equation using the intercept and coefficient of Xs from Excel summary output. Predict Y for
given Xs Validate the model statistically by looking at R2 as well as F statistic in the ANOVA that tests the null hypothesis of no
linear relationship. After statistical validation use the model for estimation and prediction Multiple Regression Forecasting
Case Problem
To measure the effect of advertising and sales
promotional efforts, the following data were
collected form a consumer marketing
company for the last 10 months. Figures in the
following table are in $1000. Multiple Regression Forecasting
Case Problem –Data Set
Month Sales(Y) Advertisement Sales Promotional
Expense (X1) Expense (X2)
1
200
45
15
2
250
50
20
3
300
55
24
4
650
85
45
5
400
65
30
6
300
55
25
7
320
57
27
8
450
68
32
9
350
60
28
10
550
70
37 Multiple Regression Forecasting
Case Problem –Questions
1) Set up a regression model by taking Sales (Y) as
the dependent variable and advertisement
expense (X1) and sales promotion expense (X2)
as independent variables and validate the model
using R2 value
2) Forecast X1 and X2 for month 11 by using
exponential smoothing technique
3) Now forecast sales for month 11 using results
obtained in 2) Multiple Regression Forecasting
Case Problem –Excel Output
Month Sales(Y) Advertisement
Expense (X1)
1
2
3
4
5
6
7
8
9
10 200
250
300
650
400
300
320
450
350
550 45
50
55
85
65
55
57
68
60
70 Sales
Promotional
Expense (X2)
15
20
24
45
30
25
27
32
28
37 SUMMARY OUTPUT
Regression Statistics
Coefficients
Multiple R
0.987153
R Square
0.974471 Intercept
195.761
Adjusted R Square
0.967177 X Variable 1
5.033966
Standard Error
25.16275 X Variable 2
9.388292
Observations
10 Multiple Regression Forecasting
Case Problem –Q1 of the problem
As you can see, from the output, the following:
Y = 195.76(2 places of decimal taken)
X1 = 5.03, X2 =9.39. So the fitted model is
Y = 195.76+5.03X1+9.39X2.
The model has a good accuracy level as evidenced from
the R2 value that is quite high = 0.97(two places of
decimal). Even the adjusted R2 = 0.97 indicating the
robustness of the model to predict. In other words R2
value is close to 1 and hence the model is reliable in
forecasting. This answers 1) of the question. Multiple Regression Forecasting
Case Problem –Q2) of the problem
Invoke Exponential Smoothing under Data
Analysis Pack. Enter the input data for X1 and X2
separately. Use a dampening factor of 0.7(same as
Alpha =0.3). The following output emerges. Multiple Regression Forecasting
Case Problem –Q2) Excel Output
Exponential Smoothing Forecast Values
X1
X2
45.00
15.00
46.50
16.50
49.05
18.75
59.84
26.63
61.38
27.64
59.47
26.85
58.73
26.89
61.51
28.42
61.06
28.30 Multiple Regression Forecasting
Case Problem –Q2) Answers
The smoothed values will start from period 2 only in
Excel’s Exponential smoothing and will not be available
for period 1.
Forecast of X1 for period 11 = Alpha (actual value in
period 10)+ (1Alpha)(forecast of period 10) =
0.3(70)+0.7(61.06) =63.74.
Forecast of X2 for period 11 = Alpha (actual value in
period 10)+ (1Alpha)(forecast of period 10) =
0.3(37)+0.7(28.3) =30.91.
This answers 2) of the question. Multiple Regression Forecasting
Case Problem –Q3) Answers
Forecast of sales for period 11 is obtained by
substituting the values of X1 and X2 (obtained in the
previous part given above) in the regression
equation
Y = 195.76+5.03X1+9.39X2.
Forecast of sales for period 11
= 195.76+5.03(63.74)+9.39(30.91) =415.1. Since
sales are in $ 1000, the forecast for period 11 is a
sale of $415100. Multiple Regression Model Limitations The most crucial assumption made is that the independent variables are not
correlated with each other. If they are correlated, then the reg ression
coefficients cannot be estimated. This problem is called multicollinearity.
The procedure followed for resolving multicollinearity is to drop the
independent variable that has the highest standard deviation and then rework
the model again. You may also like to use twostage least square method that
is part of econometrics. The other way is to transform a set of correlated
independent variables into an uncorrelated set of variables by the technique
called principal component analysis. This is an advanced technique
requiring the help of advanced statistical software like SPSS. When there are wild fluctuations in one or more of the independent variables,
multiple regression model crumbles and will be highly unreliable. In order to use the multiple regression model for prediction, you have to first
predict the values of the independent variables using some other prediction
method. In forecasting problems, multiple regression at best can work for short and
medium term only. It cannot be successfully used for long term forecasting. Accuracy Aspect in Forecasting 1) 2) There are two methods in practice that are used for
understanding forecast error.
Average Absolute Error this is obtained by computing the
absolute difference between forecast value and actual value
for every element in the time series data set and then taking
the average of all these values.
Average Percentage Relative Error in this method, you
first compute the absolute difference between forecast value
and actual value for every element in the data set and then
divide each one of them by the corresponding actual value.
Take the average of such values and multiply by 100. You get
average percentage error. Which of these methods you choose
is a matter of judgment. Accuracy Aspect in Forecasting
3) Intuitively and logically the graph of forecast values should
be reasonably close to the actual values. If it is not, look for
reasons and gather more data. Revise your model completely
if needed. 4) Accuracy can be greatly improved if you have a large
amount of historical data. This will permit you to use
advanced forecasting techniques like BoxJenkin method,
Adaptive Filtering, and Econometric models of forecasting.
The discussion of these is beyond the scope of this chapter.
Those interested can refer to books on Econometrics for
treatment on these advanced topics. ...
View
Full
Document
This note was uploaded on 02/24/2012 for the course BUSINESS 281 taught by Professor Gray during the Spring '12 term at Florida State College.
 Spring '12
 gray
 Business, Sales

Click to edit the document details