MSCI242L & 242M Spreadsheet Modelling for Management PART II 2022-23 EXAMINATIONS

This exam contains 3 sections. You must answer “ll of the questions in each section.

Section A contains 15 questions, and is worth 45 marks.

Section B contains 8 questions, and is worth 24 marks.

Section C contains 10 questions, and is worth 31 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 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 should not be password protected or contain links to external files

•   If you use a macro for Section C then you must include the macro in your file to get the marks

•   The model will be assessed on a University-spec. PC-version of Excel 2021

•   The model will be assessed using a sample of different SpecAd numbers, so all answers must be dynamic

Data Collection

All Excel datasets are held in a folder on the 242 exam Moodle called ‘Exam Datasets ’

The filenames are 3-digit code numbers, for example 123.xlsx

The file you must use is the one with the 3-digit code that corresponds to the LAST THREE DIGITS of your student ID number. For example, if your ID number is 12345678, then you should use 678.xlsx

All the datasets are different, sousing 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 SpecAd list has been modified and is now listed in alphabetical order

4. The SpecAd costs have been updated with new values

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

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

Below shows an illustrative screenshot of the correct values for SpecAd number 2 (ANDC):

Note 1: Total episodes for each District are in cells H11:H38

Note 2: Total costs for each District arepresented in cells N11:N38

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 3 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. [Note: if any cell in the table produces any type of error, then return a blank cell instead]

Question 3

Insert a formula in cell G3 which returns the name of the SpecAd, 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 Belfast City hospital, for any selected SpecAd?

[For example, for SpecAd number 2 this would be 5 episodes]

Question 6

What is the maximum number of episodes for a District, for Belfast City hospital, for any selected SpecAd? [For example, for SpecAd number 2 this would be 3 episodes]

Question 7

What is the total cost for any selected SpecAd, summed across all 28 Districts?

Question 8

What is the median District cost, including zeros, for any selected SpecAd?

Round the answer to 2 decimal places, using a suitable Excel function, not simply format the value.

Question 9

What is the average Hospital cost, including zeros, for any selected SpecAd?

Round the answer to 2 decimal places, using a suitable Excel function, not simply format the value.

Question 10

What is the name of the District with the highest number of episodes at Belfast City hospital, for any selected SpecAd?

[For example, for SpecAd number 2 this would be Armagh district]

[Note 1: if there is atie for the highest number, then present the first District alphabetically] [Note 2: if the highest number of episodes is zero, then return a blank cell]

Question 11

How many Districts have anon-zero number of total episodes, for any selected SpecAd? [For example, for SpecAd number 2 this would be 5 districts]

Question 12

What is the name of the hospital with the highest total episodes, for any selected SpecAd? [For example, for SpecAd number 2 this would be Belfast City hospital]

[Note 1: if there is atie for the highest number, then present the first hospital alphabetically] [Note 2: if all four hospitals have zero episodes, then return a blank cell]

Question 13

What is the smallest number of episodes at Belfast City hospital from any District (ignoring zeros), for any selected SpecAd?

[For example, for SpecAd number 2 this would be 2 episodes]

[Note: if all 28 Districts have zero episodes, then return a blank cell]

Question 14

What is the name of the District with the highest number of episodes at an individual hospital, for any selected SpecAd?

[For example, for SpecAd number 2 this would be Ballymena, with 4 episodes]

[Note 1: if there is atie for the highest number, then present the first District alphabetically] [Note 2: if all 28 Districts have zero episodes at all four hospitals, then return a blank cell]

Question 15

What is the name of the District with the lowest non-zero total episodes, for any selected SpecAd? [For example, for SpecAd number 2 this would be Antrim with 1 episode]

[Note 1: if there is atie for the lowest number, then present the first District alphabetically] [Note 2: if all 38 Districts have zero episodes, 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 number of Districts with zero episodes, for each hospital, in cells U11:U14.

Question 18

Calculate the total number of Districts with anon-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 answer to 2 decimal places, using a suitable Excel function, not simply format the value.

Question 20

Calculate the average number of episodes, excluding zeros, for each hospital, in cells X11:X14.

Round the answer to 2 decimal places, using a suitable Excel function, not simply format the value. [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.

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 28 District costs are zero for a hospital, then return a blank cell]

Section C

Using Excel formulas and/or a VBA macro, 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 primary answer cells.

All questions in this section are worth 3 marks each, except Q33 which is worth 4 marks.

 

Note 1: SpecAd number 6 through to 95 are hidden in the screenshot above, just for presentationpurposes

Note 2: The number episodes for SpecAd no.2 (ANDC) have been shown just for illustrative purposes

Question 24

For the Belfast City hospital, what is the name of the SpecAd with the lowest number of episodes, ignoring zeros?

[Note: if there is atie 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?

Question 26

What is the name of the SpecAd with the highest number of episodes at any individual hospital? [Note: if there is atie for the highest number, then present the first SpecAd alphabetically]

Question 27

Which hospital has the smallest number of total episodes?

[Note: if there is atie for the smallest number, then present the first hospital alphabetically]

Question 28

For the Belfast City hospital, how many SpecAds produce anon-zero number of episodes?

Question 29

How many of the 100 SpecAds produce non-zero episodes at all four hospitals?