Operations Management Computer assignments 1 The number of

Operations Management - Computer assignments 1. The number of American tourists coming to Cabot Trail, over the past five years is as shown below: Quarter Yea Year 2 Year 3 Year 4 Year 5 3140 3598 4593 1800 2589 3536 2589 2597 1987 4589 3625 3698 2698 2900 3783 3598 3000 3589 2360 3596 2 4 Show the four-quarter moving average values for this time series. Compute seasonal indices (index values) for the four quarters. Flot o graph of S.I against each qvarter in aline a. b. graph

Solution

USE ONLY EXCEL:

Formulas:

Moving average Values:

Add all the quarters in a particular year and divide by 4 (eg: In year 1, add all the 4 quarters and then divide the total by 4, repaeat the same for each year and you will have the moving average for each year)

Seasonal Indices:

Divide the value of every quarter by the average of that year (eg: Y1, Q1 = 3140 and the year 1 average is = 3474.75, divide 3140/3474.75 = 0.903662)

Line Graph:

Select all the quarters in a year and click on insert, then click on the image of line chart, slecet the first model and done.

Year	Quarter	Toursit Traffic		Average		Seasonal Indices
1	1	3140	Year 1	3474.75	Year	Quarter	Toursit Traffic	Seasonal Indices
1	2	3536	Year 2	3221.25	1	1	3140	0.903662
1	3	3625	Year 3	3369.25	1	2	3536	1.097711
1	4	3598	Year 4	2261.75	1	3	3625	1.075907
2	1	3598	Year 5	3639.25	1	4	3598	1.590804
2	2	2589			2	1	3598	1.116958
2	3	3698			2	2	2589	0.76842
2	4	3000			2	3	3698	1.635017
3	1	4593			2	4	3000	0.824346
3	2	2597			3	1	4593	1.363211
3	3	2698			3	2	2597	1.148226
3	4	3589			3	3	2698	0.741362
4	1	1800			3	4	3589	1.065222
4	2	1987			4	1	1800	0.795844
4	3	2900			4	2	1987	0.545992
4	4	2360			4	3	2900	1.282193
5	1	2589			4	4	2360	1.04344
5	2	4589			5	1	2589	0.71141
5	3	3783			5	2	4589	1.260974
5	4	3596			5	3	3783	1.0395
					5	4	3596	0.988116