Northeastern University College of Professional Studies Analytics Program
ALY 6050 Project 2
1. Solutions should consist of an Excel workbook and a Word document. Please attach both files when submitting the project.
2. In the Excel workbook, perform all calculations and simulations.
3. In the Word document, write a report summarizing the results obtained in your Excel workbook.
4. The project consists of two parts. Please complete each part in a separate worksheet, but in the
same workbook
Project:
University administers at a local University have recently launched several planning projects to determine
how effectively local emergency facilities such as local hospitals can handle natural disasters (weather, fire,
etc.). One of these projects focused on the transport of disaster victims from the campus to the five major
O bhispotials
hospitals in the area 1:
(i) Beth Israel Deaconess Medical Center
(ii) Tufts Medical Center
(iii) Massachusetts General Hospital
(iv) Boston Medical Center
(v) Brigham and Women Hopial
will be distributed to the hospitals in the event of a disaster situation:
HBO
The project team would like to determine how many victims each hospital might expect in a disaster, and ho0w long it would take to transport victims to the hospitals. However, because of lack of historical data on disasters in the area, the project team have decided to assume that the total number of victims is best approximated by a triangular probability distribution with a minimum and a maximum of respectively 20 and 300 victims, and with a peak of 80 victims.
victims
min20 Max 300 apeakof The emergency facilities and capabilities at the five hospitals vary. The following table describes how victims
𝑯𝒐𝒊𝒂𝒍
𝑨𝒍𝒍𝒐𝒄𝒂𝒊𝒐𝒏 𝒐𝒇 𝑫𝒊𝒂𝒆 𝑽𝒊𝒄𝒊𝒎
Beth Israel Medical
30%
Tufts Medical
15%
Massachusetts General
20%
Boston Medical
25%
Brigham and Women
10%
The proximity of the hospitals to the university campus also varies. It is estimated that the transport times to each of the hospitals is exponentially distributed with a certain average that depends on that hospital. The following table indicates the average of the transport time for each hospital.
transport time
For simplicity, we may assume that each hospital has 0two emergency vehicles, so that one leaves the university campus when the other one leaves the hospital. This means that one emergency vehicle arrives at the campus when the other arrives at the hospital. Therefore, the total transport time for each hospital will be the sum of transporting each victim to that hospital.
1. Perform a simulation analysis consisting of 5,000 simulations to determine:
(i) Average number of victims that can be expected at each hospital.
(ii) For each hospital, the average total time (in hours) needed to transport all victims.
(iii) For part (i) above, create a chart the displays the Law of Large Numbers in action for the
Beth Israel Medical. (Law of large numbers: As the number of trials becomes larger, the
observed averages approach to the theoretical average.)
(iv) For the Beth Israel Medical hospital, perform an exploratory data analysis of the total
transport time by:
a) Calculating a 95% confidence interval for the total transport time,
b) Determining a probability distribution that best fits the total transport time (in
hours).
c) Support your assertion in part (b) by creating a frequency distribution and
performing a Chi-squared Goodness of fit test.
(v) Let denote the average transport time (in minutes) per victim for the entire process of
transporting all victims. Perform an exploratory data analysis of .
2. Perform a simulation analysis consisting of 5,000 simulations in another worksheet of your workbook; however, assume that the total number of victims is normally distributed with a mean of 150 victims and a standard deviation of 50 victims. Furthermore, suppose that the transport time for each hospital is normally distributed with a mean and a standard deviation that differ for each hospital. The table below displays these quantities:
𝑯𝒐𝒊𝒂𝒍
𝑨𝒆𝒂𝒈𝒆 𝒊𝒏 𝑴𝒊𝒏𝒆
Beth Israel Medical
7
Tufts Medical
10
Massachusetts General
15
Boston Medical
15
Brigham and Women
20
𝑯𝒐𝒊𝒂𝒍
𝑴𝒆𝒂𝒏 𝒊𝒏 𝑴𝒊𝒏𝒆
𝑺𝑫 𝒊𝒏 𝑴𝒊𝒏𝒆
Beth Israel Medical
7
2
Tufts Medical
10
4
Massachusetts General
15
3
Boston Medical
15
5
Brigham and Women
20
3
Analyze the results of this simulation by repeating parts (i) (v) of part 1 using the above values.
3. In the word document, explain your methodologies and conclusions.
4. What are the major quantitative and qualitative differences between the outputs of simulations (1)
and (2) above.
5. Explain how the information obtained from your simulation can be used for planning purposes.
6. In your opinion, how can each simulation be changed to provide additional useful information?
Reference:
1. University Health and Counselling Services. Northeastern University. Emergencies. Retrieved from: https://www.northeastern.edu/uhcs/medical-services/emergencies/
2. Taylor III, B.W. (2011) Introduction to Management Sciences. Prentice Hall. Upper Saddle River, NJ