Question

3) Monthly sales at a coffee shop have been analyzed. The seasonal index values are Month Index

Jan 1.38 Feb 1.42 Mar 1.35 Apr 1.03 May 0.99 June 0.62 July 0.51 Aug 0.58 Sept 0.82 Oct 0.82 Nov 0.92 Dec 1.56 and the trend line is 74123 + 26.9(t).

Assume there is no cyclical component and forecast sales for year 8 (months 97 - 108).

PLEASE NOTE

I AM USING EXCEL FOR THIS HOMEWORK PLEASE SHOW FORMULAS AND WORK

THANKS

Answer 1

Answer:-

Given That:-

3) Monthly sales at a coffee shop have been analyzed. The seasonal index values are Month Index Jan 1.38 Feb 1.42 Mar 1.35 Apr 1.03 May 0.99 June 0.62 July 0.51 Aug 0.58 Sept 0.82 Oct 0.82 Nov 0.92 Dec 1.56 and the trend line is 74123 + 26.9(t). Assume there is no cyclical component and forecast sales for year 8 (months 97 - 108).

Given,

Year 8 should have months 73 to 96 , not 97 to 108

but you can do the same procedure accordingly

t	deseasonal forecast	seasonal index	forecast
97	76732.3	1.38	105890.6
98	76759.2	1.42	108998.1
99	76786.1	1.35	103661.2
100	76813	1.03	79117.39
101	76839.9	0.99	76071.5
102	76866.8	0.62	47657.42
103	76893.7	0.51	39215.79
104	76920.6	0.58	44613.95
105	76947.5	0.82	63096.95
106	76974.4	0.82	63119.01
107	77001.3	0.92	70841.2
108	77028.2	1.56	120164

Formulas

t	deseasonal forecast	seasonal index	forecast
97	=74123+26.9*A2	1.38	=B2*C2
=1+A2	=74123+26.9*A3	1.42	=B3*C3
=1+A3	=74123+26.9*A4	1.35	=B4*C4
=1+A4	=74123+26.9*A5	1.03	=B5*C5
=1+A5	=74123+26.9*A6	0.99	=B6*C6
=1+A6	=74123+26.9*A7	0.62	=B7*C7
=1+A7	=74123+26.9*A8	0.51	=B8*C8
=1+A8	=74123+26.9*A9	0.58	=B9*C9
=1+A9	=74123+26.9*A10	0.82	=B10*C10
=1+A10	=74123+26.9*A11	0.82	=B11*C11
=1+A11	=74123+26.9*A12	0.92	=B12*C12
=1+A12	=74123+26.9*A13	1.56	=B13*C13

Thank you for your supporting. please upvote my answer...