This is the Excel exercises from Supply chain in Management.
Shelby4
ExcelAssignment.xlsxExcel Assignment #2
| This assignment counts towards 15% of your final score. It is graded on a scale from 0 to 10 points, 5 points for each question. For all answers, you must write a description of your work. Often, there is more than one correct answer, and sometimes different or additional assumptions may be accepted. Also, partial credit is given for incorrect answers if enough effort and commentary is shown. Bonus credit will be added to all, so you do not need an immaculate submission to get the ten points. Please, do not hesitate to email me questions. Asking is better than guessing. This is an individual assignment. Do not share your work, or points will be deducted at the grader's discretion. Follow Blackboard instructions to submit it. | |
Raw Data
| # | month | demand | price | ||
| 1 | jan | 38080 | $ 6.00 | For the past four years, a museum has collected monthly data about the number of visitors and the average ticket price charged. | |
| 2 | feb | 13920 | $ 5.00 | ||
| 3 | mar | 18260 | $ 5.00 | ||
| 4 | apr | 20100 | $ 6.00 | ||
| 5 | may | 23440 | $ 6.00 | ||
| 6 | jun | 26780 | $ 6.00 | ||
| 7 | jul | 41620 | $ 7.00 | ||
| 8 | aug | 45960 | $ 8.00 | ||
| 9 | sep | 37800 | $ 7.00 | ||
| 10 | oct | 13140 | $ 6.00 | ||
| 11 | nov | 20480 | $ 6.00 | ||
| 12 | dec | 41820 | $ 8.00 | ||
| 13 | jan | 33660 | $ 7.00 | ||
| 14 | feb | 11500 | $ 6.00 | ||
| 15 | mar | 14840 | $ 6.00 | ||
| 16 | apr | 18180 | $ 6.00 | ||
| 17 | may | 21520 | $ 6.00 | ||
| 18 | jun | 22360 | $ 7.00 | ||
| 19 | jul | 37200 | $ 8.00 | ||
| 20 | aug | 44040 | $ 8.00 | ||
| 21 | sep | 31380 | $ 8.00 | ||
| 22 | oct | 14220 | $ 6.00 | ||
| 23 | nov | 18060 | $ 7.00 | ||
| 24 | dec | 38655 | $ 9.00 | ||
| 25 | jan | 29240 | $ 8.00 | ||
| 26 | feb | 9580 | $ 6.00 | ||
| 27 | mar | 15920 | $ 6.00 | ||
| 28 | apr | 14760 | $ 7.00 | ||
| 29 | may | 17100 | $ 7.00 | ||
| 30 | jun | 23440 | $ 7.00 | ||
| 31 | jul | 35280 | $ 8.00 | ||
| 32 | aug | 39620 | $ 9.00 | ||
| 33 | sep | 31460 | $ 8.00 | ||
| 34 | oct | 10800 | $ 7.00 | ||
| 35 | nov | 16140 | $ 7.00 | ||
| 36 | dec | 38002 | $ 9.00 | ||
| 37 | jan | 30023 | $ 8.00 | ||
| 38 | feb | 10022 | $ 7.00 | ||
| 39 | mar | 15236 | $ 8.00 | ||
| 40 | apr | 16382 | $ 9.00 | ||
| 41 | may | 18341 | $ 9.00 | ||
| 42 | jun | 25199 | $ 10.00 | ||
| 43 | jul | 31933 | $ 10.00 | ||
| 44 | aug | 38555 | $ 11.00 | ||
| 45 | sep | 28847 | $ 9.00 | ||
| 46 | oct | 11584 | $ 7.00 | ||
| 47 | nov | 15239 | $ 8.00 | ||
| 48 | dec | 37200 | $ 9.00 |
Q1
| Please, graph in two separate line-plot charts time vs. raw demand and time vs. deseasonalized demand. Then create a regression-based time-series forecast for the next month of January. Describe in detail what each graph, trendlines, seasonal indexes and R^2 coefficients show. Discuss how the time-series forecast could be further improved. |
Q2
| Please, graph the raw price vs. raw demand scatter plot and the deseasonalized price vs. deseasonalized demand scatter plot. Then create a causal forecast for the next month of January, assuming you know the museum intentions for the next year, and comment it. Describe in detail what each graph and their trendlines show, what the museum should learn from them and what should be its future strategy. Discuss any possible confounding factors and how the causal forecast could be further improved. |
