The data provided Is 12-month sales data of a supermarket for a South African tollet paper...

  

Transcribed Image Text:

The data provided Is 12-month sales data of a supermarket for a South African tollet paper brand. The supplier has a lead time of 2 weeks and 30 days payment term. NB; any negative sales figures mean there was more product returns than the actual sales for the month. Any empty cell means, It's a new product (In that/those specific month/s that product was not In existence). Requirements: 2.1 Create a line chart and identify the time series components in the time series. [5 marks] 2.2 Analyse your data and state any assumptions. [2 marks] 2.3 Create the following forecasts using as much of the data as possible: [15 marks] 2.3 Create the following forecasts using as much of the data as possible: [15 marks] [3 marks] a) 2-period moving average. [3 marks) b) 6-period moving average. [3 marks] c) 12-period moving average. [3 marks) d) Exponential smoothing forecasts with alpha = 0.3, alpha = 0.6, and alpha = 0.9. [3 marks] e) Trend forecast (whether or not there is a trend). Use the =TREND() function in Excel. 2.4 Calculate the MAE, MAD, TS and CFE for each forecasting model, and choose the best model based on [10 marks] this analysis. Aug-20 1677 Product Code Product Description Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Jul-20 Sep-20 202038 WHITE 1 PLY 48'S 1350 1041 1609 1388 1357 1088 1178 1722 1205 1988 1597 1917 1795 202423 SERVIETTES POLY 50'S 1460 1042 798 2278 1665 610 552 698 535 498 585 822 610 629 202973 STORE BRAND T/PAPER 1 PLY 48S 1198 1252 398 852 596 1215 998 1482 1082 1582 642 785 1023 1008 769 537 356 202974 STORE BRAND T/PAPER 1 PLY 4S 830 820 352 285 175 578 625 685 596 368 415 479 202975 STORE BRAND T/PAPER 1 PLY 9S 702 475 178 250 198 375 281 526 102 345 780 198 420 203297 2 PLY LUX WHITE 9S 1542 2504 1621 1799 1041 572 1424 764 1287 1147 907 839 1045 985 203302 2 PLY LUX WHITE 18S 2592 902 906 2180 1426 1657 1710 1505 2400 2265 1745 2263 2168 2110 203344 2 PLY WHITE MIN 8'S 200 735 1125 1025 1245 1425 845 1985 223 171 458 586 985 758 697 203349 STORE BRAND T/PAPER 2 PLY 18'S 590 21 1392 304 785 578 2 1101 617 48 -24 937 266 203352 WHITE 1 PLY 300 8'S 1798 1081 1027 684 1427 1263 947 1100 1133 544 819 899 849 11 1663 12 203989 TISSUES WHITE 180'S 1190 730 1060 650 480 1180 1650 940 710 1000 900 905 879 921 204988 ROLLER TOWEL M/DEW 2'S 1150 930 1870 390 1140 1170 430 1530 1320 830 930 1153 1058 13 204993 BIG WHITE 2PLY 150SHT 2'S 998 380 320 325 450 150 950 620 580 780 590 920 718 752 14 296 435 652 461 461 15 206999 HBTS LIGHT FOIL REFILL 207001 INSULCUPS 250ML 1080 680 700 1450 1390 730 590 560 505 510 370 556 485 480 16 340 280 130 250 250 17 207010 HBTS CASSEROLE DISH 2'S 361 326 227 569 680 347 110 182 343 213 154 207 229 201 18 207012 PAPER PLATES 235MM 50S 30 350 640 310 580 470 450 19 207023 BOWLS 250MM 10'S 25 55 84 120 250 900 1780 1870 1980 1633 1816 20 245007 WIPES DISINFECT 10'S 10 75 45 92 180 1080 1890 2010 1980 1740 1905 21 245008 WIPES DISINFECT 100'S 22 The data provided Is 12-month sales data of a supermarket for a South African tollet paper brand. The supplier has a lead time of 2 weeks and 30 days payment term. NB; any negative sales figures mean there was more product returns than the actual sales for the month. Any empty cell means, It's a new product (In that/those specific month/s that product was not In existence). Requirements: 2.1 Create a line chart and identify the time series components in the time series. [5 marks] 2.2 Analyse your data and state any assumptions. [2 marks] 2.3 Create the following forecasts using as much of the data as possible: [15 marks] 2.3 Create the following forecasts using as much of the data as possible: [15 marks] [3 marks] a) 2-period moving average. [3 marks) b) 6-period moving average. [3 marks] c) 12-period moving average. [3 marks) d) Exponential smoothing forecasts with alpha = 0.3, alpha = 0.6, and alpha = 0.9. [3 marks] e) Trend forecast (whether or not there is a trend). Use the =TREND() function in Excel. 2.4 Calculate the MAE, MAD, TS and CFE for each forecasting model, and choose the best model based on [10 marks] this analysis. Aug-20 1677 Product Code Product Description Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Jul-20 Sep-20 202038 WHITE 1 PLY 48'S 1350 1041 1609 1388 1357 1088 1178 1722 1205 1988 1597 1917 1795 202423 SERVIETTES POLY 50'S 1460 1042 798 2278 1665 610 552 698 535 498 585 822 610 629 202973 STORE BRAND T/PAPER 1 PLY 48S 1198 1252 398 852 596 1215 998 1482 1082 1582 642 785 1023 1008 769 537 356 202974 STORE BRAND T/PAPER 1 PLY 4S 830 820 352 285 175 578 625 685 596 368 415 479 202975 STORE BRAND T/PAPER 1 PLY 9S 702 475 178 250 198 375 281 526 102 345 780 198 420 203297 2 PLY LUX WHITE 9S 1542 2504 1621 1799 1041 572 1424 764 1287 1147 907 839 1045 985 203302 2 PLY LUX WHITE 18S 2592 902 906 2180 1426 1657 1710 1505 2400 2265 1745 2263 2168 2110 203344 2 PLY WHITE MIN 8'S 200 735 1125 1025 1245 1425 845 1985 223 171 458 586 985 758 697 203349 STORE BRAND T/PAPER 2 PLY 18'S 590 21 1392 304 785 578 2 1101 617 48 -24 937 266 203352 WHITE 1 PLY 300 8'S 1798 1081 1027 684 1427 1263 947 1100 1133 544 819 899 849 11 1663 12 203989 TISSUES WHITE 180'S 1190 730 1060 650 480 1180 1650 940 710 1000 900 905 879 921 204988 ROLLER TOWEL M/DEW 2'S 1150 930 1870 390 1140 1170 430 1530 1320 830 930 1153 1058 13 204993 BIG WHITE 2PLY 150SHT 2'S 998 380 320 325 450 150 950 620 580 780 590 920 718 752 14 296 435 652 461 461 15 206999 HBTS LIGHT FOIL REFILL 207001 INSULCUPS 250ML 1080 680 700 1450 1390 730 590 560 505 510 370 556 485 480 16 340 280 130 250 250 17 207010 HBTS CASSEROLE DISH 2'S 361 326 227 569 680 347 110 182 343 213 154 207 229 201 18 207012 PAPER PLATES 235MM 50S 30 350 640 310 580 470 450 19 207023 BOWLS 250MM 10'S 25 55 84 120 250 900 1780 1870 1980 1633 1816 20 245007 WIPES DISINFECT 10'S 10 75 45 92 180 1080 1890 2010 1980 1740 1905 21 245008 WIPES DISINFECT 100'S 22

Answer rating: 100% (QA)

Here Trend forecastInterceptslope Period I have calculated Intercept slope Coefficient of determinat... View the full answer