In-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Data Review View Arial General pave Insert Defter Condition format Formatting Table Styles A1 D G H 14 - A A 26 Wrap Teri Merge & Carter $ % Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or Sign in to activate an existing subscription fx Excel Objectives A C E 1 Excel Objectives: 2 1. Learn how to name cells. 3 2. Learn how to create and copy formulas 4 3. Leam how to use Solver 5 4. Learn how to use the regression program in the Excel Data Analysis ToolPak 6 Forecasting Objectives: 7 1. Learn how to prepare moving average forecasts in Excel 8 2. Learn how to prepare weighted-moving average forecasts in Excel 3. Learn how to prepare exponentially-smoothed forecasts in Excel 10 4. Learn how to prepare double-exponential-smoothed forecasts in Excel 11 5. Learn how to use linear regression to prepare forecasts in Exco 12 6. Learn how to do time-sorios decomposition in Excel 137. Learn how to calculate the forecast error measures MAD in Excel 14 8. Learn how to use Excel Solver to find the optimal exponential-smoothing constanta, 15 16 17 Problem 1: Three-Period Simple Moving Average 18 Simple-Moving Average Absolute 19 Period Demand Forecast Deviation 1 109 21 2 102 3 106 4 23 115 105.6667 9.3333 108 107.6667 24 0.3333 109. GRAZ ARRA7 Sheets 20 36 Sheet2 MacBook Air Formulas Data Review View EE Arial 14 + 2 Wrap Text General Paste - Av Merge & Center $% Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription AL fx Excel Objectives D F 12 6. Learn how to do time-series decomposition in Excel 13 7. Learn how to calculate the forecast error measures MAD in Excel 14 8. Learn how to use Excel Solver to find the optimal exponential-smoothing constant a. 15 18 17 Problem 1: Three-Period Simple Moving Average 18 Simple-Moving Average Absolute Period Demand Forecast Deviation 20 1 109 21 2 102 22 3 106 23 4 105.6667 9.3333 24 5 107.6667 0.3333 25 6 100 109.6667 9.6667 7 117 107.6667 9.3333 MAD = 7.1667 19 115 108 25 ANRANNO&86 NB 27 28 31 32 Problem 2: Four-Period Weighted Moving Average with Weights 4,3,2,1 33 Weighted- Moving Average Absolute Sheet MacBook Air esc 80 F3 . 90 16 FS 16 @ & 1 # 3 2 $ 4 % 5 6 7 8 Q W E tab R T Y U In-Class Forecasting Spreadsheet SOLUTIONS Home Insert Page Layout Formulas Data Review View Arial 14 + 25 Wrap Text General Pasta BT U Merge & Center $% Al Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription fx Excel Objectives B D E 27 MAD = 7.1667 28 29 30 31 32 Problem 2: Four-Period Weighted Moving Average with Weights 4,3,2,1 33 Weighted- Moving Average Forecast Absolute Deviation 34 35 36 Period 1 2 3 4 5 6 7 8 Demand 509 508 511 526 525 512 529 519 40 516.2000 520.8000 518.6000 522.8000 MAD = 8.8000 8.8000 10.4000 3.8000 7.9500 42 43 44 45 46 47 48 Problem 3: Simple Exponential Smoothing 49 Alpha = 0.5420619 50 Sheet1 Sheet2 Sheets MacBook Air esc 36 80 F3 888 F1 12 F4 56 92 ! 1 # 3 & 2 $ 4 % 5 6 7 Home Insert Page Layout Formulas Data Review View Arial 14 - A A Wrap Text General III III lili Paste lil Merge & Center $ % 9 Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscript Al XVfx Excel Objectives: D E 45 46 47 48 Problem 3: Simple Exponential Smoothing Alpha = 0.5420619 49 50 Exponential- Smoothed Absolute 51 Period Demand Forecast (Ft) Deviation 52 1 213 220.0000 7.0000 53 2 221 216.2056 4.7944 54 3 231 218.8044 12.1956 55 4 240 225.4152 14.5848 56 5 234 233.3211 0.6789 57 6 213 233.6891 20.6891 58 7 228 222.4743 5.5257 59 8 208 225.4696 17.4696 60 9 216 216.0000 0.0000 61 10 211 216.0000 5.0000 11 213.2897 63 64 65 66 67 Problem 4: Excel Solver to Find a to Minimize MAD 68 Optimala 0.542061911 Sheet1 Sheet2 Sheet 3-****** MacBook Air esc Bo 888 FI F2 F3 F5 16 $ A ! 1 2 # 3 4 % 5 & 7 6 U. In-Class Forecasting Spreadsheet SOLUTION.xls (Compatibi Home Insert Page Layout Formulas Data Review View Form Arial 14 - A A Wrap Text General Paste Merge Center $% * Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription Al fx Excel Objectives: B D 68 67 Problem 4: Excel Solver to Find a to Minimize MAD ca 69 Optimal a 0.542061911 70 71 Problem 5: Double Exponential Smoothing with a = 2 and 8 = 2 72 D_alpha 0.2 73 D delta 0.2 74 Exponential Exponential- Smoothed Smoothed Trend Forecast including 75 Period Demand Forecast Ft T_t Trend FIT_ 76 1 13 10.0000 2.0000 12.0000 77 2 15 12.2000 2.0400 14.2400 78 3 18 14.3920 2.0704 16.4624 79 4 21 16.7699 2.1319 18.9018 09 5 22 19.3215 2.2158 21.5373 6 25 21.6298 2.2343 23.8642 82 7 24 24.0913 2 2798 26.3711 8 28 25.8969 2.1849 28.0818 84 9 28 28.0655 2.1817 30.2471 85 10 30 29.7977 2.0918 31.8895 60 11 31.5116 2.0162 33.5278 Absolute Deviation 81 33 87 88 MacBook Air * 3 esc 80 F3 99 92 F2 6 A ! 1 # 3 $ 4 % 5 2 6 7 8 Q W E R T Y U tab In-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Data Review View X Wap Text General Paste a. Au Merge & Center $% Conditional Form Formatting as atite Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription Al Xfx Excel Objectives: D 90 91 Problem 6: Linear Regression Demand S2 Period (X) (Y) Predicted Value 93 1 16 94 2 19 SUMMARY OUTPUT 3 22 96 4 27 Regression Statistics 5 31 Multiple R 0.999709179 98 6 36 Rsque 0.997420025 99 7 40 Adjusted R Square 0907102027 100 8 43 Standard Emor 0.751145202 101 9 46 Observations 12 102 10 50 103 11 55 ANOVA 104 12 58 105 13 62 3030303 Regression 100 Residual 10 107 Ya+bX Total 11 108 109 b. Cool 190 a= Intercept 11.53030303 111 Predicted Value (Forecast) for Period 13 - 3.906594400 X Variable 1 112 113 114 She MacBook Air 30 esc 97 F2 6 FS A ! 1 # 3 $ 4 % 5 & 7 2 6 8 W Q E R T T Y U tab SE3 16 A A A ab Wrap Text General 3-2-2 00 Merge Center $ % 9 19 Conditional Format Formatting as Table Styles In Delete Format Start your free one month trial of Microsoft 365 or sign in to activate ang scription Cew Ives: H M SUMMARY OUTPUT Regression Statistics Multiple R 0.999709179 R Square 0.997420025 Adjusted R Square 0.997102027 Standard Emer 0751145202 Observations 12 ANOVA of SS F Significance 2.81600E-14 1 MS 2181.274476 0.564219114 Regression Residual Total 386600590 2181 274476 5.642191142 2186 916657 10 11 ST Pau Intercept X Variable 1 Coefficients 11.53000303 3.906594406 Standard Emer 0.452297914 0.062813917 24.94128283 62.1772144 245505.10 2.81509E-14 Lower ON Upper 95 .ower 95.0 95.0% 10.50023909 12.580367 10.500239 12.580367 3.765836278 40456525 37658383 40456525 MacBook Air 00 DO SO F3 888 F Du B FS + e $ 4 % 5 & 7 ) 0 6 8 3 9 Y T R Y U 1 E 0 H G F J D J L ain-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Review View Arial - A A General Pesta Conditional Format Cal Formatting Table Styles SUMMARY O 105 Merge & Center Subscription Required to edit and save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription A1 Excel Objectives: D 133 134 Problem 7: Time Series Decomposition Deseasonalized 135 Period (X) Quarter Demand (Y) Seasonal Factor Demand (Y_d) 138 1 1 77 1.3003 59 2167 137 2 72 1.1889 60.5625 13A 3 52 0.8421 61.7500 199 4 IV 42 0.6687 62.8056 5 1.3003 80.7500 141 6 96 1.1889 80.7500 142 7 III 68 0.8421 80.7500 143 8 IV 54 0.6687 80.7500 144 9 133 1.3003 102.2833 145 10 120 1.1889 100.9375 145 11 III 84 0.8421 99.7500 147 12 IV 66 0.6687 98.6944 148 13 149 14 969.0000 150 b= 151 as 152 Seasonally Adjusted Forecast for Period 13 = 142.1961 153 Seasonally Adjusted Forecast for Period 14 = 135.2398 154 155 14 Reg Multiple R R Square Adjusted R S Standard Error Observations = E 2 - = - = = - = ANOVA Regression Residual Total Intercept X Variable 1 Sheet1 Sheet2 Sheet MacBook Air BO P3 esc 56 1 F1 Fa F2 A ! 1 W# $ 4 & 7 6 8 9 2 W Q E R U T Y C c . C Data Review View -A A P 20 Wrap Text General - 3-9-6- Merge & Center $% 9 WE 8 Conditional Format Cell Formatting Table Styles Delte format your free one month trial of Microsoft 365 or sign in to activate an existing subscription G H J M N P SUMMARY OUTPUT Regression Statistics Multiple R 0.943916176 R Square 0.890977748 Adjusted R Square 0.880075523 Standard Error 5.821423488 Observations 12 ANOVA SS 1 Regression Residual Total MS F gnificance F 2769.55566 81.724314 3.970E-06 338897142 2769.55506 338.8897142 3108.445374 10 11 Intercept X Variable 1 Coefficients 52.14444444 4.400854701 Standard Error 35243 0.486811888 t Star P-value Lower 95% Upper 95% 28 Oppor 95.0 14.55394785 4.671E-08 44.161383 60.127506 44.161383 60.127500 9040154546 3.97E-06 3.3161702 5.4855392 3.3161702 8 4056392 PI MacBook Air 80 09 DW FB bb 59 F3 F7 F& FS & * $ 4 ( 9 3 5 6 7 8 0 m R T Y P U 0 1 In-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Data Review View Arial General pave Insert Defter Condition format Formatting Table Styles A1 D G H 14 - A A 26 Wrap Teri Merge & Carter $ % Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or Sign in to activate an existing subscription fx Excel Objectives A C E 1 Excel Objectives: 2 1. Learn how to name cells. 3 2. Learn how to create and copy formulas 4 3. Leam how to use Solver 5 4. Learn how to use the regression program in the Excel Data Analysis ToolPak 6 Forecasting Objectives: 7 1. Learn how to prepare moving average forecasts in Excel 8 2. Learn how to prepare weighted-moving average forecasts in Excel 3. Learn how to prepare exponentially-smoothed forecasts in Excel 10 4. Learn how to prepare double-exponential-smoothed forecasts in Excel 11 5. Learn how to use linear regression to prepare forecasts in Exco 12 6. Learn how to do time-sorios decomposition in Excel 137. Learn how to calculate the forecast error measures MAD in Excel 14 8. Learn how to use Excel Solver to find the optimal exponential-smoothing constanta, 15 16 17 Problem 1: Three-Period Simple Moving Average 18 Simple-Moving Average Absolute 19 Period Demand Forecast Deviation 1 109 21 2 102 3 106 4 23 115 105.6667 9.3333 108 107.6667 24 0.3333 109. GRAZ ARRA7 Sheets 20 36 Sheet2 MacBook Air Formulas Data Review View EE Arial 14 + 2 Wrap Text General Paste - Av Merge & Center $% Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription AL fx Excel Objectives D F 12 6. Learn how to do time-series decomposition in Excel 13 7. Learn how to calculate the forecast error measures MAD in Excel 14 8. Learn how to use Excel Solver to find the optimal exponential-smoothing constant a. 15 18 17 Problem 1: Three-Period Simple Moving Average 18 Simple-Moving Average Absolute Period Demand Forecast Deviation 20 1 109 21 2 102 22 3 106 23 4 105.6667 9.3333 24 5 107.6667 0.3333 25 6 100 109.6667 9.6667 7 117 107.6667 9.3333 MAD = 7.1667 19 115 108 25 ANRANNO&86 NB 27 28 31 32 Problem 2: Four-Period Weighted Moving Average with Weights 4,3,2,1 33 Weighted- Moving Average Absolute Sheet MacBook Air esc 80 F3 . 90 16 FS 16 @ & 1 # 3 2 $ 4 % 5 6 7 8 Q W E tab R T Y U In-Class Forecasting Spreadsheet SOLUTIONS Home Insert Page Layout Formulas Data Review View Arial 14 + 25 Wrap Text General Pasta BT U Merge & Center $% Al Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription fx Excel Objectives B D E 27 MAD = 7.1667 28 29 30 31 32 Problem 2: Four-Period Weighted Moving Average with Weights 4,3,2,1 33 Weighted- Moving Average Forecast Absolute Deviation 34 35 36 Period 1 2 3 4 5 6 7 8 Demand 509 508 511 526 525 512 529 519 40 516.2000 520.8000 518.6000 522.8000 MAD = 8.8000 8.8000 10.4000 3.8000 7.9500 42 43 44 45 46 47 48 Problem 3: Simple Exponential Smoothing 49 Alpha = 0.5420619 50 Sheet1 Sheet2 Sheets MacBook Air esc 36 80 F3 888 F1 12 F4 56 92 ! 1 # 3 & 2 $ 4 % 5 6 7 Home Insert Page Layout Formulas Data Review View Arial 14 - A A Wrap Text General III III lili Paste lil Merge & Center $ % 9 Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscript Al XVfx Excel Objectives: D E 45 46 47 48 Problem 3: Simple Exponential Smoothing Alpha = 0.5420619 49 50 Exponential- Smoothed Absolute 51 Period Demand Forecast (Ft) Deviation 52 1 213 220.0000 7.0000 53 2 221 216.2056 4.7944 54 3 231 218.8044 12.1956 55 4 240 225.4152 14.5848 56 5 234 233.3211 0.6789 57 6 213 233.6891 20.6891 58 7 228 222.4743 5.5257 59 8 208 225.4696 17.4696 60 9 216 216.0000 0.0000 61 10 211 216.0000 5.0000 11 213.2897 63 64 65 66 67 Problem 4: Excel Solver to Find a to Minimize MAD 68 Optimala 0.542061911 Sheet1 Sheet2 Sheet 3-****** MacBook Air esc Bo 888 FI F2 F3 F5 16 $ A ! 1 2 # 3 4 % 5 & 7 6 U. In-Class Forecasting Spreadsheet SOLUTION.xls (Compatibi Home Insert Page Layout Formulas Data Review View Form Arial 14 - A A Wrap Text General Paste Merge Center $% * Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription Al fx Excel Objectives: B D 68 67 Problem 4: Excel Solver to Find a to Minimize MAD ca 69 Optimal a 0.542061911 70 71 Problem 5: Double Exponential Smoothing with a = 2 and 8 = 2 72 D_alpha 0.2 73 D delta 0.2 74 Exponential Exponential- Smoothed Smoothed Trend Forecast including 75 Period Demand Forecast Ft T_t Trend FIT_ 76 1 13 10.0000 2.0000 12.0000 77 2 15 12.2000 2.0400 14.2400 78 3 18 14.3920 2.0704 16.4624 79 4 21 16.7699 2.1319 18.9018 09 5 22 19.3215 2.2158 21.5373 6 25 21.6298 2.2343 23.8642 82 7 24 24.0913 2 2798 26.3711 8 28 25.8969 2.1849 28.0818 84 9 28 28.0655 2.1817 30.2471 85 10 30 29.7977 2.0918 31.8895 60 11 31.5116 2.0162 33.5278 Absolute Deviation 81 33 87 88 MacBook Air * 3 esc 80 F3 99 92 F2 6 A ! 1 # 3 $ 4 % 5 2 6 7 8 Q W E R T Y U tab In-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Data Review View X Wap Text General Paste a. Au Merge & Center $% Conditional Form Formatting as atite Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription Al Xfx Excel Objectives: D 90 91 Problem 6: Linear Regression Demand S2 Period (X) (Y) Predicted Value 93 1 16 94 2 19 SUMMARY OUTPUT 3 22 96 4 27 Regression Statistics 5 31 Multiple R 0.999709179 98 6 36 Rsque 0.997420025 99 7 40 Adjusted R Square 0907102027 100 8 43 Standard Emor 0.751145202 101 9 46 Observations 12 102 10 50 103 11 55 ANOVA 104 12 58 105 13 62 3030303 Regression 100 Residual 10 107 Ya+bX Total 11 108 109 b. Cool 190 a= Intercept 11.53030303 111 Predicted Value (Forecast) for Period 13 - 3.906594400 X Variable 1 112 113 114 She MacBook Air 30 esc 97 F2 6 FS A ! 1 # 3 $ 4 % 5 & 7 2 6 8 W Q E R T T Y U tab SE3 16 A A A ab Wrap Text General 3-2-2 00 Merge Center $ % 9 19 Conditional Format Formatting as Table Styles In Delete Format Start your free one month trial of Microsoft 365 or sign in to activate ang scription Cew Ives: H M SUMMARY OUTPUT Regression Statistics Multiple R 0.999709179 R Square 0.997420025 Adjusted R Square 0.997102027 Standard Emer 0751145202 Observations 12 ANOVA of SS F Significance 2.81600E-14 1 MS 2181.274476 0.564219114 Regression Residual Total 386600590 2181 274476 5.642191142 2186 916657 10 11 ST Pau Intercept X Variable 1 Coefficients 11.53000303 3.906594406 Standard Emer 0.452297914 0.062813917 24.94128283 62.1772144 245505.10 2.81509E-14 Lower ON Upper 95 .ower 95.0 95.0% 10.50023909 12.580367 10.500239 12.580367 3.765836278 40456525 37658383 40456525 MacBook Air 00 DO SO F3 888 F Du B FS + e $ 4 % 5 & 7 ) 0 6 8 3 9 Y T R Y U 1 E 0 H G F J D J L ain-Class Forecasting Spreadsheet SOLUTION (Compatibility Mode] Home Insert Page Layout Formulas Review View Arial - A A General Pesta Conditional Format Cal Formatting Table Styles SUMMARY O 105 Merge & Center Subscription Required to edit and save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription A1 Excel Objectives: D 133 134 Problem 7: Time Series Decomposition Deseasonalized 135 Period (X) Quarter Demand (Y) Seasonal Factor Demand (Y_d) 138 1 1 77 1.3003 59 2167 137 2 72 1.1889 60.5625 13A 3 52 0.8421 61.7500 199 4 IV 42 0.6687 62.8056 5 1.3003 80.7500 141 6 96 1.1889 80.7500 142 7 III 68 0.8421 80.7500 143 8 IV 54 0.6687 80.7500 144 9 133 1.3003 102.2833 145 10 120 1.1889 100.9375 145 11 III 84 0.8421 99.7500 147 12 IV 66 0.6687 98.6944 148 13 149 14 969.0000 150 b= 151 as 152 Seasonally Adjusted Forecast for Period 13 = 142.1961 153 Seasonally Adjusted Forecast for Period 14 = 135.2398 154 155 14 Reg Multiple R R Square Adjusted R S Standard Error Observations = E 2 - = - = = - = ANOVA Regression Residual Total Intercept X Variable 1 Sheet1 Sheet2 Sheet MacBook Air BO P3 esc 56 1 F1 Fa F2 A ! 1 W# $ 4 & 7 6 8 9 2 W Q E R U T Y C c . C Data Review View -A A P 20 Wrap Text General - 3-9-6- Merge & Center $% 9 WE 8 Conditional Format Cell Formatting Table Styles Delte format your free one month trial of Microsoft 365 or sign in to activate an existing subscription G H J M N P SUMMARY OUTPUT Regression Statistics Multiple R 0.943916176 R Square 0.890977748 Adjusted R Square 0.880075523 Standard Error 5.821423488 Observations 12 ANOVA SS 1 Regression Residual Total MS F gnificance F 2769.55566 81.724314 3.970E-06 338897142 2769.55506 338.8897142 3108.445374 10 11 Intercept X Variable 1 Coefficients 52.14444444 4.400854701 Standard Error 35243 0.486811888 t Star P-value Lower 95% Upper 95% 28 Oppor 95.0 14.55394785 4.671E-08 44.161383 60.127506 44.161383 60.127500 9040154546 3.97E-06 3.3161702 5.4855392 3.3161702 8 4056392 PI MacBook Air 80 09 DW FB bb 59 F3 F7 F& FS & * $ 4 ( 9 3 5 6 7 8 0 m R T Y P U 0 1