Growth Models FM

Chapter 5 Growth Models 5.1 Forecasting with no data (GROWCOMP) 42 5.2 Linear growth (LIN) 47 5.3 Exponential growth (EXP) 51 5.4 Modified exponential growth (MODEXP) 54 5.5 Logistic growth (LOG) 58 This chapter presents growth models for cases in which when little historical data exist. The growth models are also used in scenario analysis for long-term trend extrapolation based on alternative assumptions about the future. If you have no data at all, use the worksheet in section 5.1, which projects a variety of assumed growth patterns into the future. When data develop, use the models in sections 5.2-5.5 to fit growth curves or functions of time using ordinary-least-squares regression. The linear growth model (Section 5.2) predicts a constant amount of growth each time period, while the exponential (Section 5.3) predicts constant percentage growth. In both linear and exponential growth, the forecasts are unbounded. In the modified exponential (Section 5.4) and logistic (Section 5.5) models, the amount of growth declines each period, so the forecasts are bounded by a saturation level. Growth Models 41 5.1 Forecasting with no data (GROWCOMP) It is heroic to forecast with no data but there may be no alternative. Data may not exist for new products, markets, or business locations or for budgeting new categories of costs or expenses. GROWCOMP in Figure 5-1 is a handy tool for analyzing assumptions about future data. The basic idea is to assume data values for the first period in the future and for some later period, perhaps when we believe the data or market will stabilize. Models are then used to fill in unknown data between these two periods, using a variety of alternative growth patterns. One of these patterns should be more credible than the others and should help develop a final forecast. This procedure saves a lot of work in creating assumed growth patterns and makes the forecasting process more objective. In Figure 5-1, suppose the year is 1950 and we are interested in predicting the development of the television market, specifically the annual percentage of households in the U.S. that will purchase one or more television sets. Alternative growth patterns or trends are computed based on the set of assumptions shown at the top of the worksheet. First, you specify the data for the first year (1950) in cell C5. Assume perfect hindsight and use the actual penetration value listed in column Q (not shown in Figure 5-1). Thus the first year's data in C5 is set at 9.00, meaning that we expect 9 per cent of households will own at least one television set in 1950. Next, in cell C6, estimate the saturation level, the maximum value of the data in the future or the point at which growth stops. Here we assume saturation will occur at 99% penetration. In cells G5 and G6, two additional assumptions are required: a target period and value in the future. We assume that in period 10 (1959), penetration will reach 85%. Using these assumptions, four alternative growth patterns are computed in columns C, F, I, and L: linear, simple exponential, modified exponential, and logistic growth. Equations used to compute each type of growth are shown in row 9. (Note: these equations are equivalent to those presented below for fitting regression models.) Model coefficients are listed in rows 10-12. Estimated data values are plotted in Figure 5-2, with percentage growth by period plotted in Figure 5-3. In Figure 5-2, all growth curves start at the same point, the first-year data assumption of 9.00, and run through the target value of 85 in 1959. The first option, linear growth, is a middle-ground assumption. Growth is simply a constant amount each time period. In percentage terms, linear growth declines more rapidly than the other models. 42 Growth Models Figure 5-1 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 A B C GROWTH Growth model projections TV penetration First period data Saturation level Year 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 D 9.00 99.00 E F Target period Target data G H I J K L M 10 85.00 LINEAR GROWTH SIMPLE EXPONENTIAL MODIFIED EXPONENTIAL LOGISTIC Y = a + bx a= b= r= Y = a(1+r)n a= b= r= Y = a - brn a= 99.0000 b = 110.6708 r= 0.8132 Y = 1 / (a-brn) a = 0.0101 b = -0.1594 r = 0.6337 Period Forecast 1 9.00 2 17.44 3 25.89 4 34.33 5 42.78 6 51.22 7 59.67 8 68.11 9 76.56 10 85.00 11 93.44 12 101.89 13 110.33 14 118.78 15 127.22 16 135.67 17 144.11 18 152.56 19 161.00 20 169.44 21 177.89 22 186.33 23 194.78 24 203.22 25 211.67 26 220.11 27 228.56 28 237.00 29 245.44 30 253.89 31 262.33 32 270.78 33 279.22 34 287.67 35 296.11 36 304.56 0.556 8.444 #N/A Growth amount 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 8.44 Growth % 93.83% 48.41% 32.62% 24.60% 19.74% 16.49% 14.15% 12.40% 11.03% 9.93% 9.04% 8.29% 7.65% 7.11% 6.64% 6.22% 5.86% 5.54% 5.24% 4.98% 4.75% 4.53% 4.34% 4.16% 3.99% 3.84% 3.69% 3.56% 3.44% 3.33% 3.22% 3.12% 3.02% 2.94% 2.85% N 7.0128 #N/A 0.2834 Growth Forecast amount 9.00 11.55 2.55 14.82 3.27 19.02 4.20 24.41 5.39 31.33 6.92 40.21 8.88 51.61 11.40 66.23 14.62 85.00 18.77 109.09 24.09 140.00 30.91 179.67 39.67 230.58 50.91 295.93 65.34 379.78 83.86 487.40 107.62 625.52 138.12 802.78 177.26 1030.26 227.49 1322.21 291.95 1696.89 374.68 2177.75 480.85 2794.86 617.11 3586.85 791.99 4603.26 1016.42 5907.71 1304.44 7581.79 1674.08 9730.27 2148.48 12487.56 2757.30 16026.20 3538.64 20567.59 4541.39 26395.90 5828.30 33875.79 7479.89 43475.27 9599.49 55794.99 12319.72 Growth Models Growth % Forecast 9.00 28.34% 25.81 28.34% 39.48 28.34% 50.60 28.34% 59.64 28.34% 66.99 28.34% 72.97 28.34% 77.83 28.34% 81.78 28.34% 85.00 28.34% 87.61 28.34% 89.74 28.34% 91.47 28.34% 92.88 28.34% 94.02 28.34% 94.95 28.34% 95.71 28.34% 96.32 28.34% 96.82 28.34% 97.23 28.34% 97.56 28.34% 97.83 28.34% 98.05 28.34% 98.23 28.34% 98.37 28.34% 98.49 28.34% 98.58 28.34% 98.66 28.34% 98.72 28.34% 98.78 28.34% 98.82 28.34% 98.85 28.34% 98.88 28.34% 98.90 28.34% 98.92 28.34% 98.94 Growth amount 16.81 13.67 11.12 9.04 7.35 5.98 4.86 3.95 3.22 2.61 2.13 1.73 1.41 1.14 0.93 0.76 0.62 0.50 0.41 0.33 0.27 0.22 0.18 0.14 0.12 0.10 0.08 0.06 0.05 0.04 0.03 0.03 0.02 0.02 0.01 Growth % Forecast 9.00 186.78% 13.49 52.96% 19.74 28.16% 27.93 17.87% 37.90 12.33% 48.97 8.92% 60.10 6.66% 70.20 5.08% 78.58 3.93% 85.00 3.08% 89.64 2.43% 92.86 1.93% 95.02 1.54% 96.44 1.23% 97.36 0.99% 97.96 0.80% 98.34 0.64% 98.58 0.52% 98.73 0.42% 98.83 0.34% 98.89 0.28% 98.93 0.22% 98.96 0.18% 98.97 0.15% 98.98 0.12% 98.99 0.10% 98.99 0.08% 99.00 0.06% 99.00 0.05% 99.00 0.04% 99.00 0.03% 99.00 0.03% 99.00 0.02% 99.00 0.02% 99.00 0.02% 99.00 43 Growth amount Growth % 4.49 6.25 8.19 9.97 11.07 11.13 10.11 8.37 6.42 4.64 3.21 2.16 1.42 0.92 0.59 0.38 0.24 0.15 0.10 0.06 0.04 0.03 0.02 0.01 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 49.93% 46.29% 41.50% 35.68% 29.22% 22.72% 16.82% 11.93% 8.18% 5.46% 3.59% 2.33% 1.50% 0.96% 0.61% 0.39% 0.25% 0.16% 0.10% 0.06% 0.04% 0.03% 0.02% 0.01% 0.01% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Figure 5-2 UNIT GROWTH PROJECTIONS 180 Linear 160 Exponential 140 Logistic Mod. Exponential 120 SATURATION LEVEL 100 80 60 40 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 16 17 18 Figure 5-3 PERCENT GROWTH PROJECTIONS 60% Linear Exponential 50% Mod. Exponential Logistic 40% 30% 20% 10% 0% 1 44 2 3 4 5 6 7 8 9 10 11 Growth Models 12 13 14 15 The second option, the simple exponential model, produces constant percentage growth. After the target value is reached, exponential growth quickly gets out of hand and reaches extreme penetration values. But it takes some time for the simple exponential to build up steam compared to the other models. The result is that simple exponential growth between any starting point and any target value is always more pessimistic than the other models. The modified exponential gives an excellent fit to the actual data in column Q (actual data are not plotted to avoid cluttering the graph). Early growth from the modified exponential is sensational, more than 186% from 1950 to 1951. The modified-exponential curve runs far above the other possibilities for every year until the target penetration is reached. After that, the modified exponential gradually approaches the saturation level. Growth slows dramatically as the saturation level is approached. The modified exponential always produces the most optimistic growth pattern between any two data values. If you expect strong early growth in your data, use the modified exponential model as a planning tool. Finally, logistic growth should be used when you expect growth to follow an S-shaped pattern. This curve is one of a large family of S-shaped curves that display slow growth at first, followed by a period of steep growth, and finally by a gradual decline in growth to the saturation level. This type of forecasting model originated in the study of the spread of epidemics during the early part of this century. Today the logistic is used for the same purpose as well as in forecasting the development of markets for both industrial and consumer goods. The logistic is another middle-ground assumption, falling between the modified exponential and the simple exponential. In terms of percentage growth in Figure 5-3, the logistic always starts out somewhere below the modified exponential. Gradually, the logistic catches up as the saturation level is approached. Growth Models 45 Forecasting with no data is hazardous but the process is certainly more objective when you make some attempt to develop a model of the future. Although an overwhelming variety of growth models are available in the literature of forecasting, the ones in this section are commonly used in practice and should at least give you a starting point for making predictions. You can think of the area between the modified exponential and the simple exponential curves as the range of likely values for future data, often called the envelope of growth potential. The size of this range depends on your assumptions about first-period data, target data, and saturation level. Whatever your assumptions, we can summarize as follows: the modified-exponential model generates the best-case scenario, while the simple exponential generates the worst case. The linear and logistic models are middleground possibilities. The most critical assumption in GROWCOMP worksheet is the first-period data value. You should spend most of your time estimating this number and you should do sensitivity analysis on the effects of alternative values. Once you get an actual value for the first period, update the worksheet and look again at the assumptions for the saturation level and the target data in the future. After three or four data values have been observed, you should attempt to fit a forecasting model to your data. Each of the growth models in GROWCOMP can be fitted to data as discussed in sections 5.2-5.5. Some will protest that you need a lot more than three or four data values to fit a forecasting model but in the real world you cannot afford to wait. Something must be done to adjust the forecasts based on how the early data behave. The best way to make such adjustments is to switch to a forecasting model based on the data. 46 Growth Models 5.2 Linear growth (LIN) Perhaps the simplest forecasting model is a straight line fitted to the data using ordinaryleast-squares regression. The explanatory or X variable in the regression is time, a series of consecutive integers. The regression line is computed to minimize the sum of squared differences between actual data and the line. Linear-growth regression is appropriate when you expect the amount of growth each time period to be relatively constant. An equivalent assumption is that percentage growth is expected to decline continuously. It is important to understand that all past data are equally weighted in linear-growth regression. If recent data should receive more weight, the trend-smoothing model in Chapter 4 is a better choice. The linear growth model is written: Yt = a + bt (5-1) Y is the dependent variable and the subscript t refers to time, a series of consecutive integers. The regression constant or intercept (starting point) for the line is a. The parameter b is the slope or amount of growth each period; b is also called the X variable coefficient. Linear growth models are commonly used in modeling population data, as shown in Figures 5-4 and 5-5. The population of DeWalt, Texas, has marched along a straight line since the city was incorporated in 1986 and the forecasts reflect a constant amount of annual growth. LIN requires installation of the Excel Data Analysis Toolbox. Enter data in columns A and B, then enter the number of warm-up data and the last period to forecast in cells C10 C11. Next, select Tools, Data Analysis, Regression. In the dialog box for the Y range, paint over cells in column B, starting at row 25. Click on the box for the X range and paint over cells in Column C. Ensure that the number of data used to fit the regression model agrees with the number of warm-up data in cell C10. In the box for output range, enter O1. Finally, click OK. The estimated Y values in column D are computed using equation 5-1. The a and b coefficients are found in the regression output (see Figure 5-6) in cells P17 - P18. The intercept is 7105.45 and the slope is 232.60 per time period. Growth Models 47 Figure 5-4 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 48 A B C D E LIN.XLS DeWalt, Texas population Linear regression on time Yt= a + bt Select Tools, Data Analysis, Regression. Enter ranges as follows: Y range includes column B, X range includes column C, Output range = O1. Ensure number of data selected for Y and X ranges agree with the nbr. of warm-up data entered in cell C10. INPUT: Nbr. of warm-up data Last period to forecast OUTPUT: Number of data Nbr. of outliers Warm-up MSE Forecasting MSE Warm-up MAD Forecasting MAD Mon/yr. 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 F Warm-up SSE Warm-up MSE Forecasting SSE Fcst SSE - Warm-up SSE Nbr. of forecast periods Forecasting MSE 16 25 Warm-up Sum abs. err. Warm-up MAD Forecasting Sum abs. err. Fcst Sum abs. - Warm-up Sum ab Nbr. of forecast periods Forecasting MAD Last forecast at period 16 0 2077.18 0.00 35.89 0.00 Regression ranges Y range X-range Data t 7,350 1 7,521 2 7,810 3 8,070 4 8,254 5 8,488 6 8,812 7 9,010 8 9,165 9 9,324 10 9,666 11 9,874 12 10,202 13 10,342 14 10,632 15 10,801 16 17 18 19 20 21 22 23 24 25 G H INTERMEDIATE CALCULATIONS: RMSE (Square root of MSE) 3 X RMSE Est Y 7338.05 7570.65 7803.25 8035.86 8268.46 8501.06 8733.66 8966.26 9198.86 9431.46 9664.07 9896.67 10129.27 10361.87 10594.47 10827.07 11059.68 11292.28 11524.88 11757.48 11990.08 12222.68 12455.28 12687.89 12920.49 e = Y - Est Y 11.95 -49.65 6.75 34.14 -14.46 -13.06 78.34 43.74 -33.86 -107.46 1.93 -22.67 72.73 -19.87 37.53 -26.07 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Growth Models e^2 142.77 2465.41 45.50 1165.82 209.02 170.53 6137.11 1913.03 1146.72 11548.66 3.74 513.82 5289.78 394.84 1408.35 679.83 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A I 45.58 136.73 33234.94 2077.18 33234.94 0.00 0 0.00 574.22 35.89 574.22 0.00 0 0.00 25 Figure 5-5 Linear regression on time 14,000 13,000 Actual Forecast 12,000 11,000 10,000 9,000 8,000 7,000 2010 2008 2006 2004 2002 2000 1998 1996 1994 1992 1990 1988 1986 6,000 Figure 5-6 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 O SUMMARY OUTPUT P Q R S T U Regression Statistics Multiple R 0.999097862 R Square 0.998196538 Adjusted R Square 0.998067719 Standard Error 48.72293151 Observations 16 ANOVA df Regression Residual Total Intercept X Variable 1 1 14 15 SS 18395171 33234.93676 18428405.94 MS 18395171 2373.924055 F Significance F 7748.845615 1.30083E-20 Coefficients 7105.45 232.6014706 Standard Error 25.55052084 2.64237196 t Stat 278.0941354 88.0275276 P-value 1.33298E-27 1.30083E-20 Growth Models Lower 95% 7050.649534 226.9341413 Upper 95% 7160.250466 238.2687998 49 A variety of other statistics are found in the regression output. R square (often denoted r2) in cell P5 is a measure of the goodness of fit of the regression line, the proportion of total variation in the Y variable (population) that is explained by the relation with the X variable (time). If all of the data fell on the regression line, R square would be 1.0. The standard error in cell P7 is also called the standard error of the estimate and is the square root of the sum of the squared differences between actual and estimated Y values divided by n-2, where n is the number of data. The reason for dividing by n-2 is that two degrees of freedom are lost in computing the regression line. The regression standard error is similar to the RMSE or square root of the mean-squarederror value in cell I5. Recall that three times the RMSE is used to set control limits in the control-chart graph (not shown here). We will continue to use the RMSE to set control limits for consistency with the control charts for exponential smoothing in Chapter 4. Several statistical tests can be made on the significance of the linear relationship between X and Y. First, an F statistic is computed in cell S12. Although a full explanation is beyond our scope here, the F statistic is the ratio of explained to unexplained variation in Y. When F is large, the regression line explains a large proportion of the variation in Y. The significance of F is computed in cell T12, interpreted as the probability that the true regression slope or X coefficient is actually equal to zero. This probability is very small, as it should be given the excellent fit of the regression line. An equivalent test can be made with the t-statistic for the X variable in cell R18. The t-statistic is the X coefficient divided by the standard error of the coefficient. The P-value is the probability that the true X coefficient is actually zero and again is very small. 50 Growth Models 5.3 Exponential growth (EXP) A classic case of exponential growth is the behavior of IBM world-wide sales during the early days of the computer (Figures 5-7 and 5-8). Percentage growth is roughly constant, with increasing amounts of growth each period. The exponential growth model is written: ln (Yt) = a + bt (5-2) Data in column B are converted to logarithms in column D. Logs convert exponential growth to linear growth so the standard linear regression functions can be used. Column D is used as the Y input to the regression, with the X input as the series of consecutive integers in column C. Estimated logs of the Y data are in column E, with antilogs (final forecasts) in column F. This example presents a forecast simulation because the warm-up data are set to 18 in cell C10. Thus the Y-range for the regression is D25:D42 and the X-range is C25:C42 (the last 3 data are not used to compute the regression equation). The model performs well in the forecast periods (1965-1967) with a forecasting MAD of 89.89 units per year. Also note that the fit of the model is excellent with an R square value of 0.9916 in cell P5 (not shown here). The exponential growth model is most useful early in the life cycle of a product. Obviously, exponential growth cannot be sustained indefinitely so use this model with caution. The model works well with relatively stable, well-behaved data. If the growth rate in the data changes frequently or if the data contain a great deal of randomness or noise, a better choice is the trend exponential smoothing model from Chapter 4. With the trend modifier set above 1.0, this smoothing model produces an exponential trend. Growth Models 51 Figure 5- 7 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 52 A B C D E EXP IBM World-wide sales Exponential regression on time ln(Yt) = a + bt Select Tools, Data Analysis, Regression. Enter ranges as follows: Y range includes column D, X range includes column C, Output range = O1. Ensure number of data selected for Y and X ranges agree with the nbr. of warm-up data entered in cell C10. INPUT: Nbr. of warm-up data Last period to forecast OUTPUT: Number of data Nbr. of outliers Warm-up MSE Forecasting MSE Warm-up MAD Forecasting MAD Mon/yr. 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 Data 144.54 161.98 183.46 214.91 266.79 333.72 409.98 461.35 563.54 734.34 1,000.40 1,171.70 1,309.70 1,436.00 1,694.20 1,925.20 2,059.60 3,239.30 3,572.80 4,247.70 5,345.20 F G H INTERMEDIATE CALCULATIONS: RMSE (Square root of MSE) 3 X RMSE Warm-up SSE Warm-up MSE Forecasting SSE Fcst SSE - Warm-up SSE Nbr. of forecast periods Forecasting MSE 18 21 Warm-up Sum abs. err. Warm-up MAD Forecasting Sum abs. err. Fcst Sum abs. - Warm-up Sum ab Nbr. of forecast periods Forecasting MAD Last forecast at period 21 1 19415.46 9752.74 81.77 89.89 Regression ranges X range Y range t ln(Y) 1 4.97 2 5.09 3 5.21 4 5.37 5 5.59 6 5.81 7 6.02 8 6.13 9 6.33 10 6.60 11 6.91 12 7.07 13 7.18 14 7.27 15 7.43 16 7.56 17 7.63 18 8.08 19 8.18 20 8.35 21 8.58 Est ln(Y) 4.91 5.09 5.27 5.45 5.64 5.82 6.00 6.18 6.37 6.55 6.73 6.91 7.10 7.28 7.46 7.64 7.83 8.01 8.19 8.37 8.56 Growth Models Est Y 135.30 162.39 194.90 233.92 280.75 336.96 404.42 485.38 582.56 699.19 839.17 1007.17 1208.81 1450.82 1741.27 2089.88 2508.28 3010.45 3613.15 4336.51 5204.69 e = Y - Est Y 9.24 -0.41 -11.44 -19.01 -13.96 -3.24 5.56 -24.03 -19.02 35.15 161.23 164.53 100.89 -14.82 -47.07 -164.68 -448.68 228.85 -40.35 -88.81 140.51 e^2 85.35 0.17 130.88 361.37 194.91 10.49 30.94 577.61 361.71 1235.63 25995.80 27069.63 10178.81 219.54 2215.99 27120.17 201315.52 52373.83 1627.81 7886.80 19743.61 I 139.34 418.02 349478.33 19415.46 378736.56 29258.23 3 9752.74 1471.82 81.77 1741.49 269.67 3 89.89 21 Figure 5-8 Exponential regression on time 6,000 Actual 5,000 Forecast 4,000 3,000 2,000 1,000 Growth Models 1967 1965 1963 1961 1959 1957 1955 1953 1951 1949 1947 0 53 5.4 Modified exponential growth (MODEXP) The modified-exponential growth model is widely used to model market penetration of consumer goods and services. A classic example of this type of growth is the penetration of TVs in U.S. households, shown in Figures 5-9 and 5-10. Penetration is defined as the percentage of households that own one or more TVs. Early percentage growth with this model is quite large, so the forecasts grow explosively at first. However, percentage growth declines continuously until the data reach a saturation level, where growth stops. From 1950 to 1960, TV penetration grew almost ten-fold, from 9% to about 87%. Later growth slowed dramatically as the market approached saturation. In 1981, penetration reached 98.1% and has been virtually constant since then. Data entry for this model is similar to the earlier models except that you must enter the expected saturation level in cell C12. The equation for the modified-exponential curve is: Yt = K - abt (5-3) K is the saturation level. As in the earlier models, t refers to time, a is the intercept, and b is the slope. After taking logs and rearranging, the modified exponential can be written as: ln (K - Yt) = ln (a) + t(ln b) (5-4) Fitting this equation by regression and forecasting the Y data works as follows: The Y variable in the regression is the log of (K - Y) in column D. The X variable is the series of integers in column C. Estimated values of the logs of (K-Y) are computed using the regression coefficients in column F. Column G takes antilogs of column F. Finally, in column H, forecasts are computed by subtracting column G from the saturation level. 54 Growth Models Figure 5-9 A B C 1 MODEXP 2 Modified exponential regression on time 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 D TV penetration E F t or ln (K - Yt) = ln (a) + t(ln (b)) Yt = K - ab Select Tools, Data Analysis, Regression. Enter ranges as follows: Y range includes column D, X range includes column C, Output range = O1. Ensure number of data selected for Y and X ranges agree with the nbr. of warm-up data entered in cell C10. INPUT: Nbr. of warm-up data Last period to forecast Saturation level (K) OUTPUT: Number of data Nbr. of outliers Warm-up MSE Forecasting MSE Warm-up MAD Forecasting MAD Mon/yr. 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 39 40 98.21 2.85 1.07 - 9.0 23.5 34.2 44.7 55.7 64.5 71.8 78.6 83.2 85.9 87.1 88.8 90.0 91.3 92.3 92.6 93.0 93.6 94.6 95.0 95.3 95.5 95.8 96.4 96.9 97.1 I 1.69 3 X RMSE 5.07 Warm-up SSE Warm-up MSE Forecasting SSE Fcst SSE - Warm-up SSE Nbr. of forecast periods Forecasting MSE 111.20 2.85 111.20 - Warm-up Sum abs. err. Warm-up MAD Forecasting Sum abs. err. Fcst Sum abs. - Warm-up Sum ab Nbr. of forecast periods Forecasting MAD Last forecast at period 39 0 Data G H INTERMEDIATE CALCULATIONS: RMSE (Square root of MSE) Regression ranges Y range X-range t LN (K-Y) 1 4.49 2 4.31 3 4.16 4 3.98 5 3.75 6 3.52 7 3.27 8 2.98 9 2.71 10 2.51 11 2.41 12 2.24 13 2.11 14 1.93 15 1.78 16 1.72 17 1.65 18 1.53 19 1.28 20 1.17 21 1.07 22 1.00 23 0.88 24 0.59 25 0.27 26 0.10 K-Y 89.21 74.71 64.01 53.51 42.51 33.71 26.41 19.61 15.01 12.31 11.11 9.41 8.21 6.91 5.91 5.61 5.21 4.61 3.61 3.21 2.91 2.71 2.41 1.81 1.31 1.11 Est LN K-Y 4.48 4.29 4.11 3.92 3.74 3.55 3.36 3.18 2.99 2.81 2.62 2.44 2.25 2.06 1.88 1.69 1.51 1.32 1.13 0.95 0.76 0.58 0.39 0.21 0.02 -0.17 Growth Models Est K-Y 88.14 73.20 60.78 50.48 41.92 34.81 28.91 24.00 19.93 16.55 13.75 11.42 9.48 7.87 6.54 5.43 4.51 3.74 3.11 2.58 2.14 1.78 1.48 1.23 1.02 0.85 Est Y 10.07 25.01 37.43 47.73 56.29 63.40 69.30 74.21 78.28 81.66 84.46 86.79 88.73 90.34 91.67 92.78 93.70 94.47 95.10 95.63 96.07 96.43 96.73 96.98 97.19 97.36 41.82 1.07 41.82 40 e = Y - Est Y -1.07 -1.51 -3.23 -3.03 -0.59 1.10 2.50 4.39 4.92 4.24 2.64 2.01 1.27 0.96 0.63 -0.18 -0.70 -0.87 -0.50 -0.63 -0.77 -0.93 -0.93 -0.58 -0.29 -0.26 55 Figure 5-10 Actual Modified exponential regression Forecast 100 90 80 70 60 50 40 30 20 10 56 Growth Models 1988 1986 1984 1982 1980 1978 1976 1974 1972 1970 1968 1966 1964 1962 1960 1958 1956 1954 1952 1950 0 It is important to understand that the modified-exponential curve is the best-fitting curve, given a prespecified saturation level. Thus an infinite number of curves exists, one for each saturation level. If you do not have marketing information that suggests a reasonable saturation level, experiment with entries in cell C12 until you find the saturation level that minimizes the warm-up MSE. This was done with the TV data and the best saturation level found to be 98.21. Because the saturation level is critical, it is always wise to do simulated forecasting to test the validity of the model. To demonstrate, set the warm-up data to 7 periods (covering the years 1950 - 1956) and run the regression again. The forecasts are not significantly different from those produced using all the data. You may protest the simulation on the grounds that in 1956 there was no way to know that 98.21 was the minimum-MSE saturation level. However, the point of this example is that forecasts for the next few time periods are often insensitive to the saturation level as long as it is somewhere in the ball park. It seems obvious from the early growth in TV data that saturation will occur somewhere in the high 90s. Any saturation level in the range 95 to 100 produces reasonable forecasts for 1957 - 1960. Growth Models 57 5.5 Logistic growth (GROWTH) Perhaps the most widely-used long-range forecasting model is the logistic curve, one of a family of S-shaped curves. In the example in Figures 5-11 and 5-12, the Y data are DVD penetration, the percentage of households that own one or more DVDs, in Fort Bend County, Texas. The curve is typical, with slow growth at first, followed by a period of steep growth, and finally by a gradual decline in growth to the saturation level. This type of forecasting model originated in the study of the spread of epidemics during the early part of this century. Today the logistic is used for the same purpose as well as in forecasting the development of markets for both industrial and consumer goods. Like the modified exponential, the expected saturation level K must be entered before running the model. The equation for the logistic curve is: Yt = K / (1 + a (exp (-bt)) (5-5) After taking logs and rearranging, the logistic can be written as: ln ((K/Yt)-1) = ln (a) – bt (5-6) The Y variable in the regression is the log of (K/Y)-1, column D in Figure 5-11. The X is column C. The remainder of the calculations are similar to the exponential. The same observations about choosing the saturation level for the modified exponential also apply to the logistic. There are an infinite number of logistic curves for each saturation level. Always do some sensitivity analysis before choosing a final model. 58 Growth Models Figure 5-11 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 30 31 32 33 34 35 36 37 38 39 40 A B C D E F LOG DVD penetration in Fort Bend Logistic regression on time Y = K / [1 + a exp (-bt)] or ln((K/Y)-1) = ln (a) -bt Select Tools, Data Analysis, Regression. Enter ranges as follows: Y range includes column D, X range includes column C, Output range = O1. Ensure number of data selected for Y and X ranges agree with the nbr. of warm-up data entered in cell C10. INPUT: Nbr. of warm-up data Last period to forecast Saturation level (K) 6 16 70.00 OUTPUT: Number of data Nbr. of outliers Warm-up MSE Forecasting MSE Warm-up MAD Forecasting MAD 6 0 0.69 0.00 0.61 0.00 Mon/yr. 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 Data 2.1 5.5 10.6 20.8 36.0 47.2 Regression ranges X-range Y range t LN (K/Y)-1 1 3.48 2 2.46 3 1.72 4 0.86 5 -0.06 6 -0.73 7 #N/A 8 #N/A 9 #N/A 10 #N/A 11 #N/A 12 #N/A 13 #N/A 14 #N/A 15 #N/A 16 #N/A (K/Y)-1 32.33 11.73 5.60 2.37 0.94 0.48 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Growth Models LN (K/Y)-1 3.39 2.55 1.71 0.87 0.03 -0.81 -1.65 -2.50 -3.34 -4.18 -5.02 -5.86 -6.70 -7.54 -8.38 -9.22 G H INTERMEDIATE CALCULATIONS: RMSE (Square root of MSE) 3 X RMSE I 0.83 2.49 Warm-up SSE Warm-up MSE Forecasting SSE Fcst SSE - Warm-up SSE Nbr. of forecast periods Forecasting MSE 4.14 0.69 4.14 - Warm-up Sum abs. err. Warm-up MAD Forecasting Sum abs. err. Fcst Sum abs. - Warm-up Sum ab Nbr. of forecast periods Forecasting MAD Last forecast at period 3.64 0.61 3.64 16 Est (K/Y)-1 29.74 12.82 5.53 2.38 1.03 0.44 0.19 0.08 0.04 0.02 0.01 0.00 0.00 0.00 0.00 0.00 Est Y 2.28 5.06 10.72 20.68 34.51 48.49 58.76 64.67 67.60 68.94 69.54 69.80 69.91 69.96 69.98 69.99 e = Y - Est Y -0.18 0.44 -0.12 0.12 1.49 -1.29 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 59 Figure 5-12 Logistic regression 100 90 Actual Forecast 80 70 60 50 40 30 20 10 60 Growth Models 2010 2008 2006 2004 2002 2000 1998 1996 0