Aims
To analyse a set of data (in Microsoft Excel), and write a brief report (in Microsoft Word), identifying and explaining your insights into the surgical operations of BestHealth Hospital.
BestHealthHospital: Analysing Surgical Services
Introduction
BestHealth Hospitalspecialises in providing a range of orthopaedic surgical services for both private and public patientsfromregional Victoria. The hospital currently has contracts with a number of surgeonsand anaesthetists (who are essentially private professional contractors)using two surgical theatres that are availablefor use, on average, 8 hours a day, Monday to Friday, and only these basic operational hours are used by the government to assess the total available surgical time for calculating minimum time to be made for public patients.
The hospital is required to apply 25% of available surgical time in each quarter to public patients in order to receive the full amount of reimbursable fees from the government. If the minimum amount of surgical time is not applied to public patients in each quarter, then the government penalises the hospital by reducing the total reimbursable fees by 5% for that quarter.
The hospital receives scheduled fees for each surgical service provided private patients from their health insurers and is also reimbursed by the government for surgical services provided to public patients, according to their respective schedules of coverage. However, there is nearly always a fee gap payable by the patient before the surgery is performed.
The hospital pays $60 per hour, as wages, to each staff member in a surgical team (i.e. nurses, technicians, etc.)for eachhour in surgery and an extra 50% of the hourly rate for all additional surgery time when a surgery takes longer than the allotted time scheduled by the government. For example, if surgery took 1.5 hours and only 1 hour was allocated by the government, then each staff member received $60 pay for the first hour and then $45 pay for the final half hour.
The hospital also acknowledges it incurs a fixed cost of $100 per hour (e.g. for electricity, water, depreciation of equipment and maintenance costs) for each hour a surgical theatre is used. The hospital also pays a fee to each surgeon and anaesthetist for each surgical procedureperformed, according to agreed schedules of professional fees for each.
The hospital's surgical services co-ordinator, Ms Sanus is interested in finding ways to improve operational efficiencies and increase the overall profitability of the surgical service. She has hired you as an analystto assist in developing an informational toolusing Microsoft Excel and poses some questions about the performance of thesurgical service. She also requiresyou to make recommendations on how to improve improving the surgical service.
Ms Sanus has provided you with surgical services information for the first quarter of 2014 in the"ITECH1005_201527_assignment_data.xlsx" (Excel workbook) file. The data needs to be analysed and visualised to help observations related to the surgicaloperations and its shortcomings.
The datasheet "surgeries" in the Excel workbook contains collected information of the hospital'ssurgical servicesinthe past year. Thesedetails included:
• date of surgery
• patient
• patient category (i.e. public or private)
• type of surgery (or surgeries) to be performed
• surgeon
• anaesthetist
• number of staff in surgical team
• theatre
• surgical time taken
The current fees charged private patients (incorporating all fees charged by the surgeon and anaesthetist) by BestHealth Hospital for surgical services,the percentage of these fees covered by the private health insurers, the government coverage of the surgery fee, the government-allocated hours for each surgery type, and the surgeons' and anaesthetists' schedules of feespayable by the hospital are:
|
Surgery Type
|
Hospital Fee charged to private patients ($AU)
|
Private Insurance coverage (% of hospital fee)
|
Gov't level of fee coverage for public patients ($AU)
|
Allocated hours for surgery
|
Surgeon's fee payable by hospital ($AU)
|
Anaesthetist's fee per hour payable by hospital ($AU)
|
|
Arthroscopy (knee)
|
3800
|
90
|
3600
|
0.5
|
2100
|
100
|
|
Arthroscopy (ankle)
|
3500
|
90
|
3200
|
0.5
|
2100
|
100
|
|
Reconstruction (knee)
|
6300
|
95
|
5900
|
1
|
2500
|
250
|
|
Reconstruction (ankle)
|
7200
|
95
|
6800
|
1.5
|
4000
|
250
|
|
Replacement (knee)
|
7800
|
90
|
7300
|
2
|
4500
|
300
|
|
Replacement (hip)
|
12500
|
90
|
11200
|
4.5
|
9000
|
300
|
|
Reconstruction (shoulder)
|
6500
|
95
|
5500
|
1
|
4100
|
250
|
|
Replacement (shoulder)
|
11000
|
90
|
9800
|
3.5
|
9000
|
300
|
|
Reconstruction (hand)
|
7600
|
95
|
5900
|
1.5
|
4300
|
250
|
|
Arthroscopy (spinal)
|
2800
|
90
|
2200
|
0.5
|
1500
|
150
|
|
Reconstruction (spinal)
|
9500
|
95
|
8700
|
3.5
|
5500
|
250
|
|
Reconstruction (fascio-cranial)
|
16800
|
85
|
15000
|
3.5
|
13000
|
300
|
Ms Sanusrequires you to use the Excel Workbook provided to convert it into a more useful informational tool to assist surgical services management.
Excel Workbook-based Informational Tool (All Students):
a) Create a new worksheetto manage all the variables noted in the information provided and use only these variables when performing calculations.
b) Create a new page each for providing tabular and appropriate graphical summaries of (a) sales, (b) costs, and (c) profitability. In each summary, break-downs must also be included for (i) surgical types, (ii) surgeons and (iii) patient categories.
c) Use named range(s)in the dataset,to provide scalability and dynamism in calculations.
d) Protect the spreadsheets by locking content not subject to modification.
e) Use appropriate data validation techniques to ensure reliability of all data in the dataset, and for all variables created.
f) Use appropriate conditional formatting to highlight significant data-points (e.g. highest or lowest values, highlighting negative values, etc.) in the calculations and observational summariesin the workbook.
Informational Tool (ITECH5005 Students Only):
g) Create a navigationsystem including a menu page that opens when the workbook is opened, with clickable images or shapes with appropriate test labels, to allow the user to move from one page to another, with all other pages being hidden and only visible when a menu item is chosen. (Note: the menu page should also hide when moving to view another page).
Ms Sanus requires you to make the following calculations from the dataset provided.
Calculations (All Students):
h) Income for each surgery performed.
i) Total costs for each surgery performed.
j) Difference between expected hours and actual hours of surgery.
k) Indicator as to whether a minimum level of public patients have been provided surgical services in the quarter.
l) Profitability of surgical services.
Ms Sanus requires you to make observations in a formal report regarding the following operational-level questions.
Observations (All students):
m) Chart changes to profitability of surgical services over the quarter, by total for each month. Use an appropriate chart to summarise.
n) Whatis BestHealth's most profitabletype of surgery, sub-categorised by the type of patient? Use an appropriate graph/chart to summarise this observation.
o) What is BestHealth'sleast profitable surgeon, sub-categorised by the day of surgery? Use an appropriate graph/chart to summarise this observation.
p) What would happen to profits if the minimum percentage of public patients was increased to30%or decreased to20%? Use an appropriate table and graph/chart to summarise this observation.
Observations (ITECH5005 Students Only):
q) What would happen to profits if the rate of pay to all staff in surgical teams (excluding the surgeons) was increased by 10% after 1stMarch this year? Use a ‘data table' to summarise these calculations and an appropriate graph/chart to compare these changed rates with the existing minimum rate.
r) What category of patient is required to pay the most ‘gap' fees, for what type of surgery and with which surgeon?
Ms Sanus also requires you to make recommendations in your report concerning the following business-level questions.
Recommendations (All Students):
s) What are your recommendation regarding other data that might be collected to improve decision making for BestHealth Hospital, and why?
Recommendations (ITECH5005 Students Only):
t) What changes do you recommendBestHealth Hospital make to any of its surgical services, and why?
Ms Sanus has asked you to provide her with the details of how you have accomplished these tasks. Therefore, you must include the functions and formulae you've used in your analyses, and not simply report the answers. As a paid consultant, your submission to Ms Sanusmust be professionally presented; all analyses, calculations and summaries in the Excel file must have headings and be supported with explanatory notes.Also, all recommendations in your report must be clearly justified (e.g. include appropriate charts/graphs/tables)and refer to specific analyses/summaries from the Excel workbook.