Bstat12 - Lessons in Business Statistics Prepared By P.K....

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 future-demand 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 Technique-Guidelines 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 Analysis-What 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 Smoothing-Continues 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 Smoothing-Formula 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)+(1-0.3)(55) =53.5. Exponential Smoothing-Example 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 Smoothing-Example 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 Smoothing-Example Solution Exponential Smoothing-Example Microsoft Excel Solution Exponential Smoothing-Example 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 Projection-Example 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 Projection-Example 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 Projection-Example: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 Projection-Example: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 Projection-Example:Forecast for Month 13 (t t )(Y Y ) b= = (149.50/143.00) = 1.045 (t ) t 2 b a = Y t = 19.25-1.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)+ (1-Alpha)(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)+ (1-Alpha)(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 two-stage 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 Box-Jenkin 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.

Ask a homework question - tutors are online