MSCI 242 Spreadsheet Modelling for Management EXAMINATIONS 2022 PART II

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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

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:

CombonGn!

DGzcL!b!!on

SpecAd number

an input cell (yellow) showing a number from 1 to 100.

SpecAd name

the name of the SpecAd. A combination of the 2bGcialty and the Vqmission 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 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

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]

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.

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?

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.


2023-05-10