MSCI 342 Individual Coursework A. Hindle 2022 AMBULANCE MODELLING Data has been provided by the NHS for the 26 districts in Northern Ireland, covering the 5022 Census Output Areas (COAs). Your task is to analyse this data and produce an interactive model to investigate how changes to delivery modes influence coverage and response times. Part A – Data Analysis by District [30 marks] Produce a dynamic1 summary table which summarises the data by district, with the following outputs: 1) the number of COAs, for each of the 26 districts 2) the population, for each of the 26 districts 3) the average COA sparsity2, for each of the 26 districts 4) the expected annual demand, for each of the 26 districts 5) the average expected demand per person, for each of the 26 districts 6) the maximum demand from a COA, for each of the 26 districts 7) the maximum population of a COA, for each of the 26 districts Based on the original data and/or summary table produce the following specific results: 8) the average number of COAs per district (rounded to 1 decimal place3) 9) the average COA sparsity per district (rounded to 2 decimal places3) 10) the average annual demand per district (rounded to the nearest integer3) 11) which district has the lowest total population 12) how many people live in this district 13) which district has the highest total demand 14) how much demand is this 15) produce a chart to show the distribution of COA populations 16) produce a chart to show the relationship between district population and demand per person 17) what is the population of the COA with the highest demand 18) what is the COA Code for the COA with the lowest demand per person 19) what is the size (in hectares) of the biggest COA (by area) in the district with the highest average sparsity 20) what is the COA Code for the biggest COA (by population) in the district with the fewest people 1 dynamic in the sense that if the numerical data changes (populations, demands and so on), then the summary table would update automatically. Static values are not dynamic. 2 sparsity is hectares per person 3 not simply formatted in Excel to show a certain number of decimal places Part B – Analysis of the 11 Primary Stations [20 marks] There are currently 11 ‘primary’ stations operating in Northern Ireland, and 10 ‘spare’ stations which are not currently in use. The location of all 21 stations are listed in your dataset. Produce a dynamic1 summary table which summarises the data by station, with the following results: 21) the total demand allocated to each of the 11 primary stations4 22) the number of people living in each catchment, for each of the 11 stations 23) the number of COAs in each catchment, for each of the 11 stations 24) the average response time4, in minutes, for each of the 11 stations 25) the maximum response time size, in minutes, for each of the 11 stations 26) for each of the 11 stations, calculate the number of people in each catchment that are within 12 minutes or less of the station 27) for each of the 11 stations, calculate the percentage of people in the catchment that are within 12 minutes or less of the station 28) which station has the ‘worst’ (lowest) percentage of people with 12 minutes 29) for the whole of Northern Ireland, how many people are within 12 minutes or less of an ambulance station 30) for the whole of Northern Ireland, calculate the percentage of people that are within 12 minutes or less of an ambulance
station. Part C – Further Investigations by Station [20 marks] Develop your model such that the ‘spare’ stations can be included in the allocation process, with the summary table for Part B updated/expanded to accommodate this. Ensure the user interface to select an additional station is well thought-out and designed. 31) If one spare station is added to the allocation, making 12 stations in total, then which of the spare stations would produce the highest value for Q30 Provide a table of results to compare all possible additional stations. 32) If two spare stations could be introduced into the allocation, making 13 stations in total, then which two stations would you recommend, based on the highest value for Q30 Provide a table of results to compare all the possible pairs of additional stations. 33) If one spare station is added to the allocation, and one of the current ‘primary’ stations is removed, making 11 stations in total again, then which station do you recommend to be added and which primary station should be removed Again, use the value for Q30 to base your recommendation on. How many people in Northern Ireland would now be closer to an ambulance station, compared to previously with the original set of 11 primary stations Part D – Travel Times and Speeds [10 marks] 34) If the allocation of demand to a station is based on the quickest travel time5, rather than the shortest distance, what would your results and recommendations be for Q31, Q32 and Q33 Highlight any recommendations and values for Q30 that would change compared to the allocation approach using shortest distances. 35) What is the highest speed observed for any individual ambulance journey6 4 allocation of COA demand to a station should be based on proximity, based on straight-line distances. This allocation creates 10 ‘catchments’ surrounding the 10 stations. 5 see the Travel Time Matrix for all the journey times from COA centroids to ambulance stations 6 for journeys directly involved in the allocation process, not simply any journey that could take place Model Design [20 marks] High marks will go to a model which follows the design principles of the module, as laid out in the documentation and described during the lectures. The model should be well-designed, easy to use and navigate, with no unnecessary complexity. The model should also be flexible, transparent and be able to reproduce all the answers to the tasks should the data be altered or modified in the future. The model should also aid the user in locating the answers to the various tasks. Data Collection The data for each student is on moodle in a folder called ‘Ambulance Data’. Each student will receive a unique dataset, based on a code number. The code numbers will also be listed on moodle. You MUST use the correct dataset, as all are unique. Failure to do so will result in inaccuracy. Submission You are required to upload to moodle a single Excel 2019 model addressing the tasks. The model rules are: The model should operate successfully on a University-spec. PC All answers/links should be easily accessible and intuitive to use/find Any macros must run in a reasonable amount of time (i.e. <2 minutes) The model should not be password protected or contain links to external files The model should not have any significant bugs or problems, such as circular references No sheets should be hidden Failure to adopt these rules will result in a mark penalty