Module 5 Forecasting

(These notes are new and probably contain errors. Dont hesitate bringing them to my attention. If a formula or values in a table seem incorrect to you, let me know and Ill look into it.) Introduction A forecast is a prediction of future events used for planning and decision making purposes. Managers may need forecasts to anticipate changes in prices or costs, prepare for new laws or regulations, track competitors, or analyze resources. Keep in mind that, while forecasting methods provide useful information for planning purposes, they are rarely perfect and must be used in conjunction with other information. Forecasting methods may be based on mathematical models using historical data available or qualitative methods drawing on managerial experience. In this module we will explore several forecasting methods commonly used today. Components of Demand The forecasting of customer demands is at the root of most business decisions. It is an inexact science as the demand for goods and services varies greatly and can be affected by unforeseen circumstances. Forecasting demand requires uncovering the underlying patterns from available information. This section discusses the basic components of demand. The repeated observation of demand for a product or service in their order of occurrence for a pattern is known as a time series. The five basic components of most demand time series are: 1. Average, or the sum of the demand observations for each period divided by the number of periods. 2. Trend, or systematic increase or decrease in the average of the series over time. 3. Seasonal influence, or the predictable increase or decrease in demand depending on the time of day, week, month, or season. 4. Cyclical movements, or the less predictable increases or decreases in demand over longer periods of time (years or decades). 5. Random errors Cyclical movements arise for either the natural business cycle or the product (or service) life cycle which reflects the stages of demand from development to decline. Business cycle movement is difficult to predict because it is influenced by so many national and international events. Predicting the rate of demand buildup or decline in a products life cycle is also difficult. The ability to make long range forecasts depends on accurate estimates of cyclical movement; we are going to concentrate our efforts on short and medium range forecasts. The four components of demand - average, trend, seasonal influence, and cyclical movements combine to define the underlying time pattern of demand for a product or service. The fifth component, random error, results from chance variations and thus cannot be predicted. Random error is the component that makes every forecast incorrect. There are three basics methods of making forecasts judgment methods, causal methods (linear regression), and time series methods. 1

Judgment Methods These are qualitative and typically used when historical data are lacking such as when a new product is launched or technology is expected to change significantly. Four commonly use judgment methods are listed below. Sales Force Estimates Often the best information about demand comes from the sales force. They are likely to know which products customers are buying and in what quantities. The information is usually readily available by districts or regions and the data can be easily aggregated. However, sales force estimates may be biased. Individuals may not be able to differentiate between customer wants and needs. If the firm uses sales as performance measures, underestimating demand may occur. Executive Opinion High level execs are paid multi-millions to anticipate customer demands. Market Research This is a more systematic method involving questionnaires, surveys, sampling, and information analysis. It is best left to the experts in the marketing department. The IT department may be called upon to assist in gathering the data (developing an online format for a survey) or in the presentation of results. Delphi Method a process of gaining consensus from a group of experts while maintaining their anonymity. A coordinator sends data and questions to the experts, their comments are shared among the group, discussed, and, hopefully, some consensus is reached. The process typically takes a long time. Causal Methods (Linear Regression) Linear regression can be a course unto itself. Only a barebones discussion is presented here and should be a review from some previous college course. This causal method is usually very good for predicting turning points in demand and preparing long range forecasts. In simple linear regression one variable, called the dependent variable, is related to one or more independent variables by a linear equation. The relationship is easily graphed using a scatterplot in Excel. A common use of linear regression is using SAT scores to predict college GPA.
3.8 3.4 GPA 3 2.6 2.2 900

1000

1100

1200 SAT

1300

1400

1500

Let the independent variable be SAT scores and the dependent variable be first year college GPA. The eleven points in the scatterplot represent historical data (SAT, GPA) for eleven students. The next step is to find the line of best fit or regression line. The math is fairly complex but Excel computes it automatically and you only need to interpret the results. Lets use Excel to conduct a simple linear regression on a sales-advertising problem. A manager must schedule production to meet anticipated demands for a product. The following are sales and advertising data for the past five months. The marketing manager says that next month she will spend $1750 on advertising (the independent variable). We can use the linear regression tools in Excel to forecast sales for month 6. By convention, the independent variable is graphed on the x-axis so when setting this up in Excel, Advertising should be in the first column. Month 1 2 3 4 5 Sales (000 units) 264 116 165 101 209 Advertising (000$) 2.5 1.3 1.4 1.0 2.0

Construct a basic scatter plot then select the chart and select Chart-Add Trendline from the menu options. In the Add Trendline dialog box select Type Linear and select Options- Display equation on chart and Display R-squared value on chart. Your results should look something like this.
300
Sales (000 units)

250 200 150 100 50 0 0

y = 109.23x - 8.135 2 R = 0.9595

Advertising (000$)

Now what? Use the regression equation Y = 109.23X 8.135 to forecast Sales (Y) from Advertising (X) Forecasted sales Y = 109.23 * 1.750 8.135 = 183,018 units. What about the R2. (Note some texts use r2.) The mathematical name for R2 is coefficient of determination. It indicates the proportion of the variation of the dependent variable that is explained by the regression equation. For your purposes, it ranges from 0.00 to 1.00 and values close to 1.00 are desirable. You are probably more familiar with the term, correlation. Correlation measures the strength of the relationship between the independent and dependent variable and can be calculated by taking the square root of the coefficient of determination, R2. In this example, the correlation coefficient, R (or r) = .98. 3

Time Series Methods 1. Simple Moving Averages This method is used to estimate the average of a demand time series by attempting to remove the effects of random fluctuations. The calculations are straightforward and involve finding the average demand for the n most recent time periods and using that average as the forecast for the next time period. Moving averages are common in forecasting share prices in stock market analysis; you will find these averages shown on most brokerage sites. An example applied to weekly patient arrivals (demand) at a clinic is included on the spreadsheet on the next page. A formula for the 6-week moving average of patient arrivals might look like this: Ft = (At-1 + At-2 + At-3+ At-4 + At-5 + At-6)/6, where Ft is the forecasted patient arrivals for the next time period, At-1 is the historical patient arrival data for the most recent week, At-2 is the historical patient arrival data for the second most recent week, etc. 2. Weighted Moving Averages In a simple moving average each demand has the same weight. In a weighted moving average forecasting, each demand can be assigned a weight relative to the other demands making up the average. The sum of the weights must be 1.00. The advantage is that it allows you to emphasize recent data over earlier data. The weighted moving averages in the following spreadsheet were calculated using a weight of 0.5 for the most recent, 0.3 for the second most recent, and 0.2 for the third most recent. The patient arrivals for the next time period, Ft, are calculated as follows: Ft = .5 *At-1 + .3*At-2 + .2*At-3

440 430 420 Arrivals 410 400 390 380 370 0

Demand 3-week MA 6-week MA Weighted MA

10

15 Weeks

20

25

30

Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

Historical Demand 400 380 411 415 395 375 410 397 407 422 430 392 396 415 387 401 389 407 398 427 412 378 390 408 429 408 393 409

3-week MA forecast

6-week MA forecast

Weighted MA forecast

Exponential smoothing = .1

Exponential smoothing = .4

397 402 407 395 393 394 405 409 420 415 406 401 399 401 392 399 398 411 412 406 393 392 409 415 410 403

396 398 401 400 401 407 410 407 410 407 404 397 399 400 402 406 402 402 402 407 404 401 406

399.5 406.8 404.2 389.0 396.5 396.5 404.6 412.5 423.0 409.4 401.6 404.7 397.2 399.6 392.2 400.4 398.9 414.3 413.7 398.0 390.8 396.6 414.9 414.3 404.7 404.0

390.0 392.1 394.4 394.5 392.5 394.3 394.5 395.8 398.4 401.6 400.6 400.1 401.6 400.2 400.2 399.1 399.9 399.7 402.4 403.4 400.9 399.8 400.6 403.4 403.9 402.8 403.4

390.0 398.4 405.0 401.0 390.6 398.4 397.8 401.5 409.7 417.8 407.5 402.9 407.7 399.4 400.1 395.6 400.2 399.3 410.4 411.0 397.8 394.7 400.0 411.6 410.2 403.3 405.6

3. Exponential Smoothing Exponential smoothing is a more sophisticated weighted average method that calculates the average of a time series by giving the recent demands more weight than earlier demand. It is the most frequently used formal forecasting method because of its simplicity, small amount of data needed to support it, low cost, and the ability to be extended for more complex situations. Unlike the weighted average which needed n periods of past data and n weights, exponential smoothing needs only three values to forecast a value for the next time period the forecast for the current period, the demand for the current period, and a smoothing parameter, alpha (), where 0 1. Here is the formula: Forecast for the next time period, Ft+1 = *(demand for current time period) + (1 )* (forecast calculated for current time period) = *Dt + (1 )*Ft

Here is how the values were arrived at for the exponential smoothing column in the table. Assume = 0.1 and lets find forecast a demand for week 4. The exponential smoothing method requires estimating an initial value for the average. Suppose we use demand data for weeks 1 and 2 and average them, obtaining 390, and use this as the initial value for Ft which will be F3 in this first calculation. Then Ft+1 = *Dt + (1 )*Ft = F4 = .1 *D3 + (1 .1)*F3 = 0.1*411 + .9*390 = 392.1 Once the first one is completed the rest is automatic. F5 = .1 *D4 + .9*F4= .1*415 + .9*392.1 = 394.4 Demand alpha = 0.1 alpha = 0.4

440 430 420 Demand 410 400 390 380 370 0

10

15 Weeks

20

25

30

4. Trend Adjusted Exponential Smoothing Because of its simplicity, exponential smoothing is at a disadvantage when the underlying average is changing such as a time series with a trend. However, the approach can be modified to account for trends. A trend is a time series is a systematic increase or decrease in the average over time. To improve the accuracy of exponential smoothing we include an estimate of the trend into the calculations. The trend-adjusted exponential smoothing method uses the following formula, with two parameters, alpha () for the average and beta () for the trend. Average for current period = *(demand for current period) + (1 - )*(Average + Trend for previous period) or At = *Dt + (1 )*(At-1 + Tt-1) Trend for current period = *(average for current period average from last period) + (1 )*trend estimate last period = Tt = *(At At-1) + (1 )*Tt-1 Forecast for next period, Ft+1 = At + Tt

Again, getting started requires estimating initial values. Analyzing historical data (not provided) from the previous 4 weeks, the average demand was 28 with an average increase of 3 per week. Therefore we can let D0 = 28, F0 = 28 and T0 = 3. There are lots of ways to make these initial estimates but anything reasonable is ok because they have little impact on future results as time passes. The results for an example are in the table below. We will answer the question on how to choose alpha and beta values shortly.
Forecast alpha=.2 beta=.2 31.00 33.04 38.51 41.43 43.10 48.44 49.29 54.08 58.99 57.72 59.79 61.02 61.24 62.96 64.23 68.67

Week 0* 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Actual Demand 28 27 44 37 35 53 38 57 61 39 55 54 52 60 60 75

Smoothed Average 28 30.20 35.23 38.21 40.14 45.08 46.35 50.83 55.46 54.99 57.17 58.63 59.21 60.99 62.37 66.38

Trend Estimate 3 2.84 3.28 3.22 2.96 3.36 2.94 3.25 3.52 2.72 2.62 2.38 2.02 1.97 1.86 2.29

* not actual data but estimated from historical data

5. Seasonally Adjusted Forecasting The final technique we need is adjusting for seasonal influences. You can see the high demand in the third quarters and low demand in first quarters; it is especially evident in the graph. This might be sales of leaf rakes for example high in the fall and low in the spring.
quarter 1 2 3 4 Total Ave/qtr year 1 45 335 520 100 1000 250 year 2 70 370 590 170 1200 300 year 3 100 585 830 285 1800 450 year 4 100 725 1160 215 2200 550

1200 1000 Demand 800 600 400 200 0 0 4 8 Quarter 12 16

Here are the steps to forecast the demand for each quarter of year 5. 1. Determine the yearly demand for year 5. Over the last 4 years the average increase has been 400 units ((2200-1000)/3 = 400), therefore it is reasonable to expect a demand of 2600 in year 5 or 650 in each quarter of year 5 if seasonal influences are not taken into account. 2. Determine the seasonal index associated with each quarter by dividing the quarterly demand by the average demand per quarter for that year. Then, calculate the average index for each quarter
quarter 1 2 3 4 year 1 0.18 1.34 2.08 0.40 year 2 0.23 1.23 1.97 0.57 year 3 0.22 1.30 1.84 0.63 year 4 0.18 1.32 2.11 0.39 average 0.20 1.30 2.00 0.50

45/250 = .18

3. Finally, multiply the expected demand per quarter (without seasonal influences) by the seasonal index for each quarter to obtain a forecast for each quarter of year 5.
quarter 1 2 3 4 calculations 650*.20 650*1.30 650*2.00 650*.50 forecast, year 5 130 845 1300 325

Choosing a Time Series Method Now we have many choices so how do we decide which method and parameters to use? Forecast Errors Forecasts almost always contain errors and they can be classified as either bias errors or random errors. Bias errors are the result of consistent mistakes in which the forecast tends to be consistently high or low. The other type of error, random error, results from unpredictable factors that cause the forecast to deviate from the actual demand. It is desirable to minimize both types of errors by selecting appropriate forecasting models, but eliminating all forms of errors is impossible. Before errors can be minimized, they must be quantified. A forecast error is simply the difference between the forecast and actual demand for a given time period and may be expressed mathematically by Et = Dt - Ft There are several ways to measure the forecasting errors but we are only going to look at the Cumulative Sum of Forecast Errors (CFE) and Mean Absolute Deviation (MAD). CFE is simply the sum of the forecast errors, Et. MAD = |Et |/n (Note: the Excel function =AVEDEV finds the MAD) The benefits of using these two measures is that the CFE is effective in measuring bias, the higher the CFE, the more bias the forecast is, while the MAD is effective in measuring the overall accuracy of the forecasting method. Lets apply these measures to an earlier example. As can be seen in the below table choosing the best forecasting method is not always straight forward. It is desirable to use the one with the lowest CFE and MAD. In this example no one method meets that criterion. The 6 week MA and ES (=.1) have the lowest MAD but the highest CFE, while for the weighted MA and 3 week MA just the opposite is true. A reasonable compromise might be exponential smoothing with =.4 which has the middle value for both measures. Two analysts might choose different methods depending on the situation. It is also possible to try other values for weights and parameters and observe the behavior of the CFE and MAD.

If your answers differ from these slightly it is probably because different rounding techniques were used.

10

Assignment Complete any 3 for a B-, any 4 for a B, any 5 for a B+, any 6 for an A- and all 7 for an A. 1. Go to http://www.ship.edu/~mtmars/StatU/StatU.html Randomly select at least 30 students and interview them. Use the data gathered to construct a scatterplot of study hours (independent variable) against GPA. Include R2 and the regression equation on the chart. If an incoming student says he studies 15 hours a week, what GPA would you predict for him. Comment briefly on the strength of the relationship (correlation). 2. Get about 6 month historical data on a stock or mutual fund of your choice. Use an Excel spreadsheet to chart the closing prices for the six month period. On each scatterplot include two simple moving averages, say 15 and 30 day, for example, and one weighted average, with at least three weights of your choosing. (If you dont have a favorite site to get financial data try http://chart.yahoo.com/d) 3. Get about 6 month historical data on a stock or mutual fund of your choice, but different from the one selected in 2. above. Use an Excel spreadsheet to chart the closing prices for the six month period. On the chart include two exponential smoothing forecasts with different alphas of your choosing. 4. The following data are available for the monthly demand of a product:
Period 1 2 3 4 5 6 7 8 9 10 11 12 Demand 215 250 275 285 350 285 305 300 300 380 350 325

Using Trend Adjusted Exponential Smoothing with = = 0.2, determine the forecast for the next periods (i.e. 13). Suppose that the actual demand for period 13 turned out to be 395. What would be your forecast for period 14 at the end of period 13? What will be your forecast for period 15?

11

5. Use seasonally adjusted forecasting techniques on the data below to forecast the demand for each month in year 4. Include a chart of the historical and forecasted demand.
Month January February March April May June July August September October November December Year 1 23 21 24 26 22 27 25 27 24 23 29 31 Year 2 28 32 34 38 31 32 40 38 42 44 45 47 Year 3 49 52 45 43 44 40 46 48 54 50 48 56

6. Duplicate the values and interaction on the spreadsheet found at: http://www.ship.edu/~mtmars/MIS_530/forecasting/int_forecasting.xls Use it to determine the best combination of alpha and beta. 7. For the data below calculate the CFE and MAD for at least 7 different forecasting schemes. Include at least two each of simple moving average, weighted moving average, and exponential smoothing with different time periods, weights, and/or parameters as appropriate. Include a table of the calculated CFE and MAD measures and a brief discussion of which forecasting scheme is most appropriate and why.
Period 1 2 3 4 5 6 7 Demand 100 80 110 115 105 110 125

12

120

13