Write a brief report that identify surgical operations

Reference no: EM13963560

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.

Reference no: EM13963560

Questions Cloud

Concurrent access to objects : This question asks about synchronizing methods for stack and queue objects. (a) Bounded stacks can be de?ned as objects, each containing an array of up to n items. Here is apseudocode for one form of stack class.

Compounded annually, : A stock will have annual dividends of $0.65, $0.8, $0.78, $0.76, $0.82, and then grow by 0.02 a year. If the required return is 0.16 per year compounded annually, what should the price be?

Calculate the energy of the gas : There has recently been considerable interest in one-dimensional electrical conductors. In this problem, you are asked to calculate some free-electron properties for a system of length L containing N electrons. Thus, there are n =N/L electrons per..

Actual overhead costs for the year : Pinnacle Corp. budgeted $700,000 of overhead cost for the current year. Actual overhead costs for the year were $650,000. Pinnacle’s plant wide allocation base, machine hours, was budgeted at 100,000 hours

Write a brief report that identify surgical operations : 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.

Find general solution r(r) to radial schrodinger equation : Find the general solution R(r) to the radial Schrodinger equation for r r0. Use the fact that the wave function must be finite at 0 and infinity to simplify the solution as much as possible. (You do not have to normalize the solutions)

Prove the efficiency of two carnot engines : Prove that the efficiency of two Carnot engines operating in tandem, one going from a cold reservoir of temperature Tc to a hot reservoir ofTj, then the second going from a cold reservoir of temperature Tj to a hot reservoir of temperature Thmust ..

Problem regarding the actor computing : The actor mail system provides asynchronous buffered communication and does not guarantee that messages (tasks in actor terminology) are delivered in the order they are sent. Suppose actor A sends tasks t1, t2, t3,... , to actor B and we want acto..

What is the book value of aca assets today : ACA Computer Systems's balance sheet lists net fixed asset as $32 million. The fixed assets could currently be sold for $28 million. ACA's current balance sheet shows current liabilities of $12 million and net working capital of $8 million. What i..

User Account

All Pages