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