Question

Consider five years of monthly sales data for a company in the attached file (Q5.xlsx). a) Forecast monthly sales for the next year (2020). (10 marks) b) Discuss whether a simple exponential smoothing model works well with this data or not. (5 m

7 9 Month Sales 747 Feb-14 697 Mar-14 1014 Jan-14 Ap4 1126 May-14 1105 Jun-14 1450 Jul-14 1633 Aug-14 1711 Sep-14 1307 Oct-1223 Nov-14 9T5 Dec-14S53 4J-15 1024 Feb-15928 Mar-151442 7Apr-151371 May-15 1536 Ju15 2004 Jul-15 1854 Aug-15 1951 2 ep-15 1516 3Oct-15 1642 4 No-15 1166 Dec-151106 Jan-161183 Feb-16 1209 1754 Apr-16 1843 o May-1 1769 Jun-162207 2 Jul-16 2471 3Aug-16 2288 2 Sep-15 5 ar-16 ep16 1867 5 Oct-16 1980 1418 7Dec-16 1333 Jan-17 1333 6Nov-16 Feb171370 0 Mar-17 2142 p-17 2138 May-172078 Jun-1 2960 Ju-17 2616 5 Aug-17 2861 Sep-17 2237 Oct-172225 Nov-1530 2 May-1 9D11653 0 Jon-18 1613 1 Feb-18 1605 2 Mar-18 2343 Apr-18 2468 4 May-18 2532 5 Jun-18 3127 6Ju-18 3288 Aug-18 3285 Sep-18 2485 9Oct-18 2723 0 Nov-18 1835 Dec-18 1834

Answer 1

Month	Sales	t
Jan-14	747	1		1)
Feb-14	697	2		slope=	26.61	SLOPE(B2:B61,C2:C61)
Mar-14	1014	3		intercept =	975.42	INTERCEPT(B2:B61,C2:C61)
Apr-14	1126	4		regression equation: sale = 975.42 + 26.61*x
May-14	1105	5
Jun-14	1450	6		month	t	predicted sales	sales_predicted = 975.42 + 26.61*t
Jul-14	1639	7		Jan-20	73	2918.131	=$F$4+$F$3*F8
Aug-14	1711	8		Feb-20	74	2944.744	=$F$4+$F$3*F9
Sep-14	1307	9		Mar-20	75	2971.356	=$F$4+$F$3*F10
Oct-14	1223	10		Apr-20	76	2997.969	=$F$4+$F$3*F11
Nov-14	975	11		May-20	77	3024.581	=$F$4+$F$3*F12
Dec-14	953	12		Jun-20	78	3051.194	=$F$4+$F$3*F13
Jan-15	1024	13		Jul-20	79	3077.806	=$F$4+$F$3*F14
Feb-15	926	14		Aug-20	80	3104.419	=$F$4+$F$3*F15
Mar-15	1442	15		Sep-20	81	3131.031	=$F$4+$F$3*F16
Apr-15	1371	16		Oct-20	82	3157.644	=$F$4+$F$3*F17
May-15	1536	17		Nov-20	83	3184.256	=$F$4+$F$3*F18
Jun-15	2004	18		Dec-20	84	3210.869	=$F$4+$F$3*F19
Jul-15	1854	19
Aug-15	1951	20
Sep-15	1516	21
Oct-15	1642	22
Nov-15	1166	23
Dec-15	1106	24
Jan-16	1189	25
Feb-16	1209	26
Mar-16	1754	27
Apr-16	1843	28
May-16	1769	29
Jun-16	2207	30
Jul-16	2471	31
Aug-16	2288	32
Sep-16	1867	33
Oct-16	1980	34
Nov-16	1418	35
Dec-16	1333	36
Jan-17	1333	37
Feb-17	1370	38
Mar-17	2142	39
Apr-17	2138	40
May-17	2078	41
Jun-17	2960	42
Jul-17	2616	43
Aug-17	2861	44
Sep-17	2237	45
Oct-17	2225	46
Nov-17	1590	47
Dec-17	1659	48
Jan-18	1613	49
Feb-18	1605	50
Mar-18	2349	51
Apr-18	2468	52
May-18	2532	53
Jun-18	3127	54
Jul-18	3288	55
Aug-18	3285	56
Sep-18	2485	57
Oct-18	2723	58
Nov-18	1835	59
Dec-18	1894	60

2)

The method of exponential smoothing is not preferred because it cannot predict the future values for the year 2020. The method of simple linear regression analysis is preferred over exponential smoothing. Because the method of linear regression analysis is helpful in prediction of future values for the year 2020.