EXAMINATIONS 2022
PART II (Second Year, Third and Final Year)
MSCI 242 Spreadsheet Modelling for Management
This coursework contains 4 sections. You must answer all of the questions in each section.
Section A contains 15 questions, and is worth 30 marks.
Section B contains 8 questions, and is worth 24 marks.
Section C contains 11 questions, and is worth 46 marks
Submission Rules
Deliverable is a completed Excel workbook uploaded to the exam Moodle, addressing all the questions
All answers must be presented in the correct answer cells on the Model sheet
The Model sheet should also include your ID number
All answers to the questions must be formula or function-based i.e. not simple/static values
Only correct results will receive the marks. No partial marks will be awarded
The model will be assessed for accuracy using a sample of different SpecAd numbers
The model should not be password protected and not contain any links to external files
If you have used a macro for Section C then include the macro in your submitted file
The model will be assessed on a University-spec. PC-version of Excel 2019
Data Collection
All datasets are held in a folder on the exam Moodle called ‘Exam Datasets’
The filenames are all 3-digit code numbers, for example 123.xlsx
To find the code assigned to you, see the section ‘Data Collection’ and use the one that corresponds to the
LAST THREE DIGITS of your student id number
All the datasets are different, so using the incorrect dataset will generate incorrect results
Background
This task is based on a modified version of workshop 5 – Modelling Patient Flows.
However, this task differs from the original workshop version in four distinct ways.
1. The number of hospitals is four: Altnagelvin, Antrim, Belfast City and Causeway
2. The number of SpecAds is 100
3. The SpecAds are listed in alphabetical order
4. The SpecAd costs have been updated
1
2
Data Structure and Sheets
there are 6 sheets in the supplied dataset
patient flow data for each of the four Hospitals, for all 100 SpecAds, is held on its own sheet, with the
following names: Altnagelvin, Antrim, Belfast City, and Causeway
the SpecAd Costs sheet has the reference cost for each of the 100 SpecAds
the Model sheet is where you must submit all your answers, in the correct answer cells
Some definitions:
Component Description
SpecAd number an input cell (yellow) showing a number from 1 to 100.
SpecAd name the name of the SpecAd. A combination of the Specialty and the Admission type
SpecAd cost the expected cost for one episode of the SpecAd.
Admission Type an acronym for the type of admission: day case (DC), inpatient elective (IPE) or
inpatient non-elective (IPNE)
Specialty a clinical unit within a hospital, in abbreviated form, e.g. AE, CAR, DENT and so on.
District where the patient lives. There are 28 Districts, arranged alphabetically.
Hospital the hospital where the patient was treated.
There are 4 hospitals; Altnagelvin, Antrim, Belfast City, and Causeway
3
Below shows an illustrative screenshot of the correct results for SpecAd number 1 (AEDC):
Note 1: Total episodes for each District are presented in cells H11:H38
Note 2: Total costs for each District are presented in cells N11:N38
4
Section A
For this section, all answers must be formula-based, not static/pasted values.
You may insert intermediate formulas and calculations to the Model sheet but do not move any of the
answer cells, or insert any rows or columns.
All questions in this section are worth 2 marks each.
Question 1
Using formulas, create the Summary Table (Episodes) shown on the previous page
The table must be dynamic and respond to a different SpecAd number being inputted in cell G2.
Question 2
Using formulas, create the Summary Table (Costs) shown on the previous page
Again, the table must be dynamic and respond to a different SpecAd number being inputted in cell G2.
Question 3
Insert a formula in cell G3 which returns the SpecAd name, based on the SpecAd number.
Question 4
Insert a formula in cell G3 which returns the SpecAd cost, based on the SpecAd number
Question 5
What is the total number of episodes for Antrim hospital, for any selected SpecAd
Question 6
What is the maximum number of episodes for a District, for Antrim hospital, for any selected SpecAd
Question 7
What is the total cost for any selected SpecAd, summed across all 38 Districts
Question 8
What is the median District cost, including zeros, for any selected SpecAd
Round the answer to 2 decimal places.
Question 9
What is the average Hospital cost, including zeros, for any selected SpecAd
Round the answer to 2 decimal places.
Question 10
What is the name of the District with the highest number of episodes at Antrim hospital, for any selected
SpecAd
[Note: if there is a tie for the highest number, then present the first District alphabetically]
[Note: if the highest number of episodes is zero, then return a blank cell]
Question 11
How many Districts have a non-zero number of total episodes, for any selected SpecAd
Question 12
What is the name of the hospital with the highest total episodes, for any selected SpecAd
[Note: if there is a tie for the highest number, then present the first hospital alphabetically]
[Note: if all four hospitals have zero episodes, then return a blank cell]
Question 13
What is the name of the District with the highest total episodes, for any selected SpecAd
[Note: if there is a tie for the highest number, then present the first District alphabetically]
[Note: if all 38 Districts have zero episodes, then return a blank cell]
5
Question 14
What is the name of the District with the highest number of episodes at any individual hospital, for any
selected SpecAd
[Note: if there is a tie for the highest number, then present the first District alphabetically]
[Note: if all 38 Districts have zero episodes at all four hospitals, then return a blank cell]
Question 15
What is the smallest number of episodes for Antrim hospital (ignoring zeros), for any selected SpecAd
[Note: if all the episodes are zero, then return a blank cell]
Section B
These tasks involve completing the following Summary Table by Hospital.
All the questions below require dynamic Excel formulas, rather than simple pasted values or text.
All questions in this section are worth 3 marks each.
Question 16
Calculate the total number of episodes for the selected SpecAd, for each hospital, in cells T11:T14.
Question 17
Calculate the total number of Districts with zero episodes, for each hospital, in cells U11:U14.
Question 18
Calculate the total number of Districts with a non-zero number of episodes, for each hospital, in cells
V11:V14.
Question 19
Calculate the average number of episodes, including zeros, for each hospital, in cells W11:W14.
Round the values to 2 decimal places (not simply format them in Excel).
Question 20
Calculate the average number of episodes, excluding zeros, for each hospital, in cells X11:X14.
Round the values to 2 decimal places (not simply format them in Excel).
[Note: if a hospital has zero episodes for a SpecAd, then return a blank cell]
Question 21
Calculate the total cost for this SpecAd, for each hospital, in cells Y11:Y14.
Format these cells as Currency type, formatted to show only integer amounts.
6
Question 22
Calculate the highest cost for a District for this SpecAd, for each hospital, in cells Z11:Z14.
Format these cells as Currency type, formatted to show only integer amounts.
Question 23
Calculate the lowest cost for a District for this SpecAd, for each hospital, ignoring any zero costs, in cells
AA11:AA14. Format these cells as Currency type, showing only integer amounts.
[Note: if all the costs for a hospital are zero, then return a blank cell]
Section C
Using either Excel formulas or a VBA macro (or both), complete the following table showing the
number of hospital episodes for all 100 SpecAds, for each of the four hospitals.
To answer the following questions, you may add intermediate calculations of your own but do not move
any of the answer cells.
All questions in this section are worth 4 marks each, except Q33 and Q34 which are worth 5 marks.
Note 1: SpecAd number 6 through to 95 are hidden in the screenshot above, just for presentation purposes
Note 2: The number episodes for the first SpecAd (AEDC) have been shown just for illustrative purposes
Question 24
For Antrim hospital, what is the name of the SpecAd with the lowest number of episodes, ignoring zeros
[Note: if there is a tie for the lowest number, then present the first SpecAd alphabetically]
Question 25
What is the highest number of episodes observed for a SpecAd, at any individual hospital
7
Question 26
What is the name of the SpecAd with the highest number of total episodes
[Note: if there is a tie for the highest number, then present the first SpecAd alphabetically]
Question 27
Which hospital has the highest number of total episodes
[Note: if there is a tie for the highest number, then present the first hospital alphabetically]
Question 28
At Antrim hospital, how many SpecAds produce a non-zero number of episodes
Question 29
How many SpecAds produce non-zero episodes at all four hospitals
Question 30
How many Day Case (DC) SpecAds register non-zero episodes at 3 or more hospitals
Question 31
How many SpecAds have a tie for the highest number of episodes at two (or more) hospitals, ignoring any ties
involving zero episodes
Question 32
How many SpecAds are there where one of the four hospitals has more than 50% of the total episodes for that
SpecAd
Question 33 (5 marks)
Which Specialty has the highest number of total episodes (all three admission types)
Question 34 (5 marks)
Which hospital has the most even-numbered SpecAd episodes, ignoring zeros
Note: if there is a tie for the highest number, then present all the tied hospitals in alphabetical order, separated
by a single forward slash, for example like this:
Altnagelvin/Causeway
Note: to receive the full marks this formula needs to be dynamic, such that if the episode data changes, the
result will update.
END OF PAPER
