answer only question 3

AutoSave C - Glos Demand forecasting Fire Exercise NEF (1) Excel Scarch Rajesh Dip File Home Insert Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 12 A A == 89 Wrap Text Ceneral 4 Erill X Out [Copy Paste Format Painter Cupboard FH CA Insert Delete Format BIU Ideas Seng UVLY Merge Center $ % -8 Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number Cels dsas Sensity 013 2 H 1 L M N 4 A B C D E F G 1 OPM400 2 Student Name: 3 Student Number: 4 4 5 NOTE: Show and use excel formulas for each and all calculations and round to two decimals - 6 6 7 7 Q 1) Use the Historical demand data below to calculate THREE forecasts. (3 marks) 8 a) 3 Period Simple Moving Average 9 b) 3 Period Weighted Moving Average using weights of.65, 2,.15 10 c) Exponential Smoothing forecast using an Alpha of.25. and a Week 4 forecast of 396 units. 11 12 Week Demand SMA 3 Period WMA.65, 2,.15 EXP SM a 5.25 13 1 452 14 2 391 15 3 532 16 4 613 396 17 5 523 18 6 498 19 7 437 20 8 526 21 9 496 22 Instructions NEE + Select destination and press ENTER or chocs Paste Type here to search O 1209 ? 1:41 AM 2/4/2021 AutoSave C - Globo Demand forecasting Fire Exercise NEE (1) Excel Scarch Rajesh Dip File Home Insert Page Layout Formulas Data Review View Help Share Comments Calibri Auto Sum A A == 82 Wrap Text Ceneral 4 Erill Paste X out [ Copy Format Painter Clipboard PH CA Insert Delele Format BIU Ideas Seng UVLY Merge Center $ % -8 Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Numar Cols doas Sensitivity H37 2 D E F G H 1 L M N 4 31 32 33 27 A 4 5 B 65 79 81 C 68 80 79 6 34 35 36 37 MAD MSE MAPE 38 39 40 Q3 ) The actual demand for bagels from a large GTA retail chain is shown below for 2020. a) Calculate the Seasonal Index for last year for each of the four seasons. (2 marks) b) Use the seasonal Index from table #1 (2020) to calculate the demand for each of the seasons for this year Table #2 (2021) based on a new ANNUAL demand of 10,250 bagels. (2 marks) 41 42 43 This Year 2021 Average Seasonal Index Seasonal Index Forecast Sold 1245 2220 44 Last Year 2020 45 Season 46 Winter 47 Spring 48 Summer 49 Fall 50 51 Season Winter Spring Summer Fall 3120 2940 Instructions NEE 1209 Type here to search ? 1:43 AM 2/4/2021 AutoSave C - Globo Demand forecasting Fire Exercise NEE (1) Excel Scarch Rajesh Dip File Home Insert Page Layout Formulas Data Review View Help Share Comments X Out Calibri Auto Sum AA == 89 Wrap Text 48 Ceneral $ % Erill Paste la Copy PH CA BIU Merge Center Insert Delete Format Ideas Seng UVLY Clear Format Painter Cupboard Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number cols dsas Sensitivity H37 2 A B D E F G H 1 L M N Q2 ) For each of the forecasts and demand below, a) calculate the Mean Absolute Deviation, b) Mean Squared Error and c) Mean Absolute Percent Error. (3 marks) *Use the ABS formula for Absolute Value. 22 23 24 25 26 26 27 28 29 Error (e) Abs e en2 Abs e/A*100% week 1 2 3 30 31 Demand 102 83 53 65 79 81 Forecast 97 87 47 68 80 79 4 32 33 5 6 34 35 MAD 36 37 MSE = MAPE = 38 39 40 41 Q3 ) The actual demand for bagels from a large GTA retail chain is shown below for 2020. a) Calculate the Seasonal Index for last year for each of the four seasons. (2 marks) b) Use the seasonal Index from table #1 (2020) to calculate the demand for each of the seasons for this year Table #2 (2021) based on a new ANNUAL demand of 10,250 bagels. (2 marks) Instructions NEE + 42 1204 Type here to search ? 1:42 AM 2/4/2021 AutoSave C - Globo Demand forecasting Fire Exercise NEE (1) Excel Scarch Rajesh Dip File Home Insert Page Layout Formulas Data Review View Help Share Comments Calibri Auto Sum A A == 82 Wrap Text Ceneral 4 Erill Paste X out [ Copy Format Painter Clipboard PH CA Insert Delele Format BIU Ideas Seng UVLY Merge Center $ % -8 Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Numar Cols doas Sensitivity H37 2 D E F G H 1 L M N 4 31 32 33 27 A 4 5 B 65 79 81 C 68 80 79 6 34 35 36 37 MAD MSE MAPE 38 39 40 Q3 ) The actual demand for bagels from a large GTA retail chain is shown below for 2020. a) Calculate the Seasonal Index for last year for each of the four seasons. (2 marks) b) Use the seasonal Index from table #1 (2020) to calculate the demand for each of the seasons for this year Table #2 (2021) based on a new ANNUAL demand of 10,250 bagels. (2 marks) 41 42 43 This Year 2021 Average Seasonal Index Seasonal Index Forecast Sold 1245 2220 44 Last Year 2020 45 Season 46 Winter 47 Spring 48 Summer 49 Fall 50 51 Season Winter Spring Summer Fall 3120 2940 Instructions NEE 1209 Type here to search ? 1:43 AM 2/4/2021