In: Operations Management
INFO 564 Homework Assignment 5 (100 pts)
This work must be done completely in EXCEL. Answer each question on a separate tab. Label each tab appropriately. You can copy and paste the data given into an Excel worksheet.
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.
Quarter |
Year 1 |
Year 2 |
Year 3 |
Year 4 |
Year 5 |
1 |
20 |
37 |
75 |
92 |
176 |
2 |
100 |
136 |
155 |
202 |
282 |
3 |
175 |
245 |
326 |
384 |
445 |
4 |
13 |
26 |
48 |
82 |
181 |
Question 1 (5 pts)
Plot this data with quarters from years 1-5 on the horizontal axis. What components do you see in this time series?
Question 2 (20 pts)
Ignore any trend or seasonality in the data.
Question 3 (20 pts)
Ignore any trend or seasonality in the data.
Question 4 (20 pts)
Again ignore any trend or seasonality in the data.
Question 5 (20 pts)
Now make adjustments for trend and seasonality.
Question 6 (15 pts)
Using the most accurate method of all of the above,
1)
Below is the screenshot of the chart -
It shows seasonal trend in data.
2)
a & b)
Below is the screenshot of the formula applied to get the result -
Below is the desired result -
c)
From above its is clear that 4 quarter moving average is better as it has the lower MAPD.
d)
Below is the chart-
3)
a&b)
Below is the screenshot of the formula applied to get the result-
Below is the screenshot of the result -
c)
4 Quarter weighted moving average is better as it has the lower MAPD.
d)
Below is the chart -
4)
a&b)
Below is the screenshot of the formula applied to get the result-
Below is the screenshot of desired result -
Alpha = 0.3 has the lower MAPD hence alpha = 0.3 will be the better.
c)
Below is the chart -
For rest of the questions, kindly initiate new thread