New census 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            [40 marks]

Produce a dynamic1 summary table which summarises the data for all 26 districts, and use this summary table to present the following outputs on the user sheet:

1)   the number of COAs, for the Belfast district

2)   the population, for the Belfast district

3)   the average COA sparsity2, for the Belfast district (rounded to 2 decimal places3)

4)   the expected annual demand, for the Belfast district (rounded to 2 decimal places3)

5)   the average expected demand per person, for the Belfast district (rounded to 3 decimal places3)

6)   the maximum demand from a COA, for the Belfast district

7)   the maximum population of a COA, for the Belfast district

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 down to an 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 is the highest district demand?

15) produce a chart to show the distribution/histogram of COA populations

16) produce a scatterplot 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?

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 locations of all 21 stations are listed in your dataset.

Produce a dynamic1 summary table which summarises the data by station, and present the following outputs on the user sheet:

21) the total demand allocated to the Belfast station4

22) the number of people living in the Belfast catchment

23) the number of COAs in the Belfast catchment

24) the average response time4, in minutes, for the Belfast station (rounded to 2 decimal places3)

25) the maximum response time, in minutes, for the Belfast station (rounded to 2 decimal places3)

26) for the Belfast station catchment, how many people live in COAs which are within 10 minutes5 or less of this station

27) for the Belfast station catchment, calculate the percentage of people that are within 10 minutes or less of the station (rounded to 2 decimal places3, for example 70.34%)

28) which station has the ‘worst’ (lowest) percentage of people within 10 minutes or less?

29) for the whole of Northern Ireland, how many people are within 10 minutes or less of an ambulance station?

30) for the whole of Northern Ireland, calculate the percentage of people that are within 10 minutes or less of an ambulance station (rounded to 2 decimal places3, for example 70.34%)

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 designed and easy to use.

31) 12 Stations: If one spare station is added to the allocation, making 12 stations in total, then which

of the spare stations6 would produce the highest value for Q30?

Provide a table of results to compare all possible additional stations.

32) 13 Stations: If twospare stations could be introduced into the allocation, making 13 stations in total, then which two stations would you recommend6, based on the highest value for Q30?       Provide a table of results to compare all the possible  pairsof additional stations.

33) One In, One Out: If one of the current ‘primary’ stations is removed, and one ‘spare' station is       added to the allocation, making 11 stations in total again, then which station do you recommend to be removed and which spare station should be added6?

Again, use the value for Q30 to base your recommendation on.

For your recommendation, how many people in Northern Ireland would now be closer7 to an ambulance station, compared to previously with the original set of 11 primary stations?

Model Design                [20 marks]