Forecasting_for_DynaSol

FORCEASTING MODULE CASE ASSIGNMENT Based on an example from Introduction to Management Science with Spreadsheets, W. Stevenson and C. Ozgur, McGraw-Hill, FORECASTING FOR DYNASOL INDUSTRIES DynaSol Industries is a rapidly growing regional firm that manufactures, sells, and installs alternative power equipment for residential and commercial applications. When DynaSol was founded in 1998, it sold active solar water-heating equipment. The company has since expanded to three main product lines, each based on a different technology: wind turbines, solar heating equipment, and waterwheel generators. DynaSol has seen its overall sales increase by more than 200 percent during the last 24 months and wishes to obtain a reliable estimate of its sales during the next few months. Although sales for waterwheel generators (the least important of the three product lines) have remained stable, demand for wind turbines and solar heating equipment have increased dramatically. Historical sales data for these two product lines are provided in Tables 1 and 2. The company management expects that demand for wind and solar technologies will increase substantially because of competing energy costs, tax-credit availability, and fundamental shifts in the attitudes of the regional population toward so-called exotic energy systems. The firm also faces increasing competition within the burgeoning energy equipment market. The situation requires the company’s management to make important decisions concerning DynaSol’s supplier contracts, since many of these contracts are due to expire in the next few weeks. In order to negotiate new contracts, DynaSol’s management must have an accurate estimate of demand for its solar and wind products. Table 1: Historical Sales Figures for Wind Turbines Month Sales (hundreds ) Month Sales (hundreds ) Month Sales (hundreds ) Month Sales (hundreds ) 1 40 7 55 13 79 19 96 2 47 8 63 14 82 20 100 3 50 9 68 15 80 21 100 4 49 10 65 16 85 22 105 5 56 11 72 17 94 23 108 6 53 12 69 18 89 24 110 Table 2 : Historical Sales Figures for Solar Heating Equipment Month Sales (hundreds ) Month Sales (hundreds ) Month Sales (hundreds ) Month Sales (hundreds ) 1 13 7 90 13 36 19 230 2 7 8 93 14 21 20 201 3 23 9 63 15 47 21 122 4 32 10 39 16 81 22 84 5 58 11 37 17 112 23 74 6 60 12 29 18 139 24 62 Page 1 of 11

Michael Wagner, DynaSol’s Director of Forecasting and Strategic Planning, is responsible for developing demand forecasts. He wishes to examine several different forecasting methods to determine which is most accurate. Our Excel model should facilitate this analysis. Solution We will investigate three forecasting methods: simple moving average (SMA), exponential smoothing (ES), and linear regression. These methods are demonstrated below, and you should be able to build the model if you follow along. Let’s first define some notation:  t is the time index. For example, if we have three months of demand data, t will take the values 1, 2, and 3.  D t is the historical demand in time period t . For example, if we have monthly historical demand values for the first quarter of this year, then D 1 will be the demand from January, D 2 will be the demand from February, and D 3 will be the demand from March.  A t is the average demand, computed using historical demand up to period t . For example, A 1 is the average computed using just the first period’s demand value. A 2 is the average computed using the first and second periods’ demand values.  F t is the forecasted demand value for period t . For example, if we have monthly historical demand values from the first quarter of this year, we may want to compute F 4 , which is the forecast for month beginning the second quarter. We next summarize the forecasting techniques to be considered. Method I : n -Period Simple Moving Average (SMA) This technique averages the n most recent demands to find the average for the current period: The parameter n , representing the size of the demand “window”, is an input to the method and must be chosen by the analyst ahead of time. The average A t is used as the forecast for the next period: Method II : Exponential Smoothing (ES) This technique considers all of the historical data (as opposed to only the most recent n periods), but places more weight on the recent data. The average A t is computed using an iterative formula: A t =  D t + (1-  ) A t -1 . Interpret A t as a weighted average of the current demand ( D t ) and last period’s average ( A t- 1 ), where the weights are  and (1-  ). The constant  is a smoothing parameter which can take values between 0 and 1. This parameter controls how much weight is placed on more recent data:  A high  (> 0.7) puts more weight on recent demand information (like a simple moving average with an n of 1 or 2);  A low  puts more weight on demand from previous periods (like a simple moving average with a high value for n ). The value of  is an input to the method and must be chosen by the analyst. Page 2 of 11

This worksheet is set up so that the number of periods in the moving average ( n ) can be varied between 1 and 5. A few comments:  As usual, the cells with red corners have comments containing hints about what formula to enter. To read a comment, position the mouse pointer over the cell.  The values in the Average column (column D) are computed using Excel’s AVERAGE() function. For example, the formula in cell D11 is: “=AVERAGE(C7:C11)”. This formula can then be copied down to the bottom of the column.  Since the worksheet is set up to compute moving averages with up to five months of data, the first entry in the Average column is in month 5, i.e. A 5 .  The forecast for each period is the demand average from the last period, rounded to the nearest whole number. (It’s a good idea to round the forecast to the same number of decimal places as the original demand data.) This is accomplished using Excel’s ROUND() function.  The columns for MAD, BIAS, and MSE are initialized in row 12. For example, the MAD for month 6 is computed with the formula: “=ABS(F12)” and you can copy it down to the bottom of the column.  The forecast for month 25 is shaded yellow. Since there is no demand data for this month, we do not compute MAD, BIAS, and MSE for this month. Then create an XY Scatter chart which plots the historical demand and forecasted demand against the month indices. (Hint: Select cells B6:C31 and E6:E31 before choosing to insert the chart.) This chart is shown in Figure 1. Figure 1: Chart of Historical and Forecasted (SMA) Demand for Wind Turbines Next set up the formatting and equations for the SOLAR HEATING EQUIPMENT section of the MSA worksheet. Create a chart of the historical and forecasted demand for solar heating equipment as shown in Figure 2. Page 4 of 11

Figure 2: Chart of Historical and Forecasted (SMA) Demand for Solar Heating Equipment Now go to the ES worksheet and complete the calculations for the exponential smoothing forecasts. Some comments about this worksheet:  The smoothing parameter used for the exponential smoothing forecast, α, is an input to this model and is contained in cell B4 (assume a value of α = 0.3 to begin with).  The exponential smoothing calculations in column D are initialized in cell D7 with the formula: “=C7”. Once the initial average (A 1 ) is computed, the exponential smoothing formula is used for subsequent averages. For example, A 2 is computed in cell D8 with the formula: “=$B$4*C8+(1-$B$4)*D7”. The absolute referencing in this formula is set up so that you can copy it down to the bottom of the column.  Compute the forecast error measures (including MAD, BIAS and MSE) as before. Again create a chart of the historical and forecasted demand for wind turbines as shown in Figure 3. Page 5 of 11

Choose the “Input X Range” and “Input Y Range” as in the screenshot below: You should get the sumamry output as below: Page 7 of 11

Then using the formula y= ax+b, the forecast for month 2 (cell D8) will simply be “=Round((X Variable*(B8)+Intercept),0). You should get a forecasted demand for month 2 (cell D8) as 44. Simialrly compute the foreacst for the remaining months. and also compute the measures of forecast error for each month. Repeat the same process to compute the foreacst for months 2 to 24 for Solar Heating Equipment with the corresponding forecast error values. Again create a chart of the historical and forecasted demand for wind turbines as shown in Figure 5. Also set up the linear regression forecast for solar heating equipment and the corresponding chart (Figure 6) of historical and forecasted demand. Figure 5: Chart of Historical and Forecasted (Linear Regression) Demand for Wind Turbines Page 8 of 11

Now for which value for n will the MAD in month 24 be the lowest? Why? (Restore the original demand values before answering the other questions in this section.) Answer: Since the Demand is random and fluctuating (ups and downs), having a higher n (in my case 5) is the better solution because it relay more on the historical data. 2. To answer parts (a) and (b) of this question, go to the Solar Heating Equipment section of the Exponential Smoothing ( ES ) worksheet. a) Make the alpha smoothing parameter (α) equal to 0.5, and look at the BIAS column of the table. The numbers in this column form a repeating pattern: there are several negative numbers, followed by several positive numbers, followed again by negative numbers. What causes this alternating pattern? (Hint: Look at the XY scatter chart for demand.) Why do you suppose this pattern appears in the solar heating equipment data, but not the wind turbine data? (Hint: When do customers buy heating equipment?) Answer: This alternating pattern goes to the idea of method which keeps catching up the previous demand since its random and fluctuating. This happened to the Solar heating only because the demand in Wind turbine data is trending up. b) Try changing the alpha smoothing parameter in the Solar Heating Equipment section to the values 0.1 and 0.9. Which value for α results in the lowest MAD in month 24? Why? Again, restore the original demand values before answering the following questions. Answer: Going from 0.1 to 0.9 will decrease the MAD. The lowest MAD was at 0.9 is 24.65. As the data provided for Solar Heating Equipment is fluctuating. The method has to keep catching the demand fluctuating and the more recent data you include; better solution results you get. 3. Go to the Wind Turbines section of the Exponential Smoothing ( ES ) worksheet. Try changing the alpha smoothing parameter to the values 0.2, 0.5, and 0.8. Which value for α results in the lowest MAD in month 24? Why? Again, restore the original demand values before answering the following questions. Answer: The lowest MAD value was the value of alpha = 0.8, The more recent data you include in the trending demand pattern, the better results you get. Because you don’t need to include old data while your demand is increasing. Page 10 of 11

4. To answer this question, go to the Wind Turbines section of the Linear Regression worksheet model that you have developed. How does the best Linear Regression forecast for wind turbines you have developed compare with the best ES forecast for wind turbines from question 3? Compare the forecasting methods with respect to MAD, BIAS and MSE. Also interpret the value of the adjusted R 2 . Which forecasting model performs better? Why? Answer: Compared to ES forecast, Linear Regression model performs better. Because we have a dataset (Demand) which shows clear up -trend. The Regression model predicts better forecast numbers. As you can see from the table below. The LR MAD is 2.24. the R2 for the model is very high (0.9849) which determines the proportion of variance in the dependent variable that can be explained by the independent variable. ES at Alpha 0.8 Linear Regression model MAD 4.42 2.24 BIAS -3.58 -0.17 MSE 27.67 6.87 5. To answer this question, go to the Solar Heating Equipment section of the Linear Regression worksheet model that you have developed. Again, interpret the value of the adjusted R 2 ? Is the forecast for month 27 likely to be accurate? Why or why not? What is missing from the model and how would you fix it? Answer: The value of R2 is too low (0.327) which represents a poor forecasting model. Week 27 forecast value is almost not accurate. More data is required to fix the forecasting formula to predict precise numbers. 6. Which forecasting method would you choose to use for the Wind Turbines data? Which would you choose for the Solar Heating Equipment data? Why? Answer: I would choose the LR model for wind turbines since the error is minimized and we have the most accurate model as the demand is in up-trend I would choose ES for the Solar Heating since the data is fluctuating and we have to keep catching up with the demand uncertainty. Page 11 of 11