ADM3301 X & Y Assignment 2  

Instructions

• ADM3301 X & Y Assignment 2 submission date: Saturday June 12, 2021 at 11:30 PM Total marks: 60 marks
• This assignment includes two cases about applications of forecasting techniques. The corresponding data are provided in the attached
• This is an individual assignment and must be completed individually and not in collaboration with other students.
• Please use MS Excel to perform forecasting and requested analyses. Please show your detailed answers and
• For this assignment, please upload a solution file containing a summary of your forecasting analysis and answers to the questions, as well as MS Excel files with the details of your forecasting analysis. The solution file must be typed and preferably uploaded in a Pdf
• You may upload several files, but only the most recent submission prior to the deadline will be graded.
• Please note that all answers must be only the result of your own work without consulting any online services or websites that assist with finding solutions. Using such services to answer the assignment questions is a violation of academic integrity.
• Sufficient time is given to complete the assignment and no extension will be granted for any reasons.

Case 1. Forecasting Air Passenger Traffic (30 marks)

 The spreadsheeted contains data on international air passenger traffic for a major airport in the United States for two years prior to COVID-19 pandemic. The goal in this case is to forecast the volume of international passengers for year 3. Ignore the impact of the COVID-19 pandemic on air travels answer the following questions.

1. What forces seem to be at work here in terms of trend and seasonality? Explore the data and comment on the trend and seasonality patterns present in the data. (3 marks)
2. Use the “seasonality forecasting technique with no trend” to forecast the international passenger volume for each month of year 3 in this (Use the actual number of passengers as the initial value for exponential smoothing. Use smoothing constant 0.3) (10 marks)
3. Create a graph comparing the actual number of passengers versus forecasted volume from part (b) and comment on how they compare. (2 marks)
4. Use the “seasonality forecasting technique with trend (multiplicative decomposition)” to estimate the international passenger volume for each month of year 3 in this airport. (10 marks)
5. Create a graph comparing the actual versus forecasted volume from part (d) and comment on how they compare. (2 mark)
6. What other variables do you expect to influence the number of international travelers? Comment on at least three variables. (3 marks)

Case 2. Forecasting Emergency Department (ED) Visits (30 marks)

 The spreadsheet contains data on the number of visits to the Emergency Department (ED) of a hospital in our region for the last three weeks. The data excludes arrivals by ambulance and provides breakdown of the total number of patient visits by day of the week and 6-hours shifts. Based on this last three weeks of data, the ED manager is seeking help to forecast ED demand in terms of the number of patient visits for each day of the week and shifts over the next week. To help the ED manager achieve his goal, perform the following analyses, and assume the effect of seasonality to be a multiplicative effect.

1. Explore the data, create graphs and comment on the seasonality patterns you observe in the data. Explore the seasonality patterns by “day and the week” as well as by “day of the week and ” (2 marks)
2. Use the “seasonality forecasting technique with no trend” to forecast the volume of ED visits by “day of the week” only. (Use the actual number of ED visits as the initial value for exponential smoothing. Use smoothing constant 0.25) (8 marks)
3. Calculate MAD and MAPE measures of error on training data for the forecasting model in part (b). (3 marks)
4. Plot the actual volume of ED visits versus forecasted volume from part (b) and comment on how they compare. (1 mark)
5. Use the “seasonality forecasting technique with no trend” to forecast the volume of ED visits by “day of the week AND shift”. (Use the actual number of ED visits as the initial value for exponential smoothing. Use smoothing constant 0.25) (10 marks)
6. Calculate MAD and MAPE measures of error on training data for the forecasting model in part (e). (3 marks)
7. Plot the actual volume of ED visits versus forecasted volume from part (e) and comment on how they compare. (1 mark)
8. Compare MAD and MAPE measures of error for the two forecasting models used in part (b) and (e). Do these measures suggest that one method performs better on the training data? (2 marks)