QUESTION 2 (40 MARKS)

The data provided is 12-month sales data of a supermarket for a South African toilet 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, its a new product (in that/those specific month/s that product was not in existence).

Microsoft EXCEL works required

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	Aug-20	Sep-20
202038	WHITE 1 PLY 48'S	1350	1041	1609	1388	1357	1088	1178	1722	1205	1988	1597	1917	1677	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
202974	STORE BRAND T/PAPER 1 PLY 4S	830	820	352	285	175	578	625	685	769	596	368	415	537	479
202975	STORE BRAND T/PAPER 1 PLY 9S	702	475	178	250	198	375	281	526	102	345	780	198	356	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	48	590	-24	937	266	21	1	1392	304	785	578	2	1101	617
203352	WHITE 1 PLY 300 8'S	1663	1798	1081	1027	684	1427	1263	947	1100	1133	544	819	899	849
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	0	1870	390	1140	1170	430	1530	1320	830	930	1153	1058
204993	BIG WHITE 2PLY 150SHT 2'S	998	380	320	325	450	150	950	620	580	780	590	920	718	752
206999	HBTS LIGHT FOIL REFILL										296	435	652	461	461
207001	INSULCUPS 250ML	1080	680	700	1450	1390	730	590	560	505	510	370	556	485	480
207010	HBTS CASSEROLE DISH 2'S										340	280	130	250	250
207012	PAPER PLATES 235MM 50S	361	326	227	569	680	347	110	182	343	213	154	207	229	201
207023	BOWLS 250MM 10'S								30	350	640	310	580	470	450
245007	WIPES DISINFECT 10'S				25	55	84	120	250	900	1780	1870	1980	1633	1816
245008	WIPES DISINFECT 100'S				10	75	45	92	180	1080	1890	2010	1980	1740	1905

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]

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. [3 marks]

2.4 Calculate the MAE, MAD, TS and CFE for each forecasting model, and choose the best model based on this analysis. [10 marks]

2.5 Using the best model, make a new forecast for the next period. [2 marks)

2.6 Based on your analysis and the time series data context, do you believe that this forecast is reliable enough to base a business decision? justify your answer. [3 marks]

2.7 Evaluate the challenges you experienced in forecasting.[3 marks]