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.
Learning Objectives
In the process of this assessment task you will:
• plan, schedule and execute project tasks with a view to improving your personal productivity;
• gain awareness of some typical issues related to the operation of a small-to-medium size business;
• use the functionality of Microsoft Excel 2007/10/13 to manipulate data, analyse it and visualise it in tabular and chart form; and
• use the functionality of Microsoft Word to write a brief report of your business observations and recommendations.
Due date: Week 11, Thursday, 5pm
Submit the individual work file, named 'a1- Surname_ID .doc', by Week 11, Thursday, 5pm via Moodle.
Cutoff date: Week 12, Friday, Midnight
Any submission after the due date and time will receive a deduction of 10% per day, this includes weekends.
Marks: This assignment has a total 30 marks for ITECH1005 students and 36 marks for ITECH 5005 students. It is worth 30% out of the total assessment.
Extensions: An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor's certificate, with a maximum limit of one week.
Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee.
BestHealth Hospital : Analysing Surgical Services
Introduction
BestHealth Hospital specialises in providing a range of orthopaedic surgical services for both private and public patients from regional Victoria. The hospital currently has contracts with a number of surgeons and anaesthetists (who are essentially private professional contractors) using two surgical theatres that are available for 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 each hour 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 procedure performed, 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 analyst to assist in developing an informational tool using Microsoft Excel and poses some questions about the performance of the surgical service. She also requires you 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 surgical operations and its shortcomings.
The datasheet “surgeries” in the Excel workbook contains collected information of the hospital’s surgical services in the past year. These details 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 fees payable 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 Sanus requires 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 worksheet to 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 summaries in the workbook.
Informational Tool (ITECH5005 Students Only):
g) Create a navigation system 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) What is BestHealth’s most profitable type of surgery, sub-categorised by the type of patient? Use an appropriate graph/chart to summarise this observation.
o) What is BestHealth’s least 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 to 30% or decreased to 20%? 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 1st March 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 recommend BestHealth 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 Sanus must 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.
Some Hints
The options you will likely identify and address are linked to a number of questions posed by Ms Sanus. Your role is to answer these specific questions and to support your answers with your data analyses, as presented in appropriate tables and charts. Ensure that your recommendations address the hospital’s objectives, and that they are clearly linked to your observations.
There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on your insight of the data, on your ability to make observations by analysing (with formulae) and visualising (with charts/graphs/tables) your data. Each graph/chart must have suitable labelling of each axis and of any significant data-point(s), a suitable heading and a legend. Each table must have an appropriate heading as well as any explanatory notes, as appropriate or necessary, to make the information comprehensible.
There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your Excel workbook, and especially the observations, you may wish to review the tutorials on the related topics (e.g. Excel functions, absolute and relative cell referencing, formatting, chartings and managing large worksheets, as well as on the analysis of Excel data and drawing recommendations).
Check that the dataset is complete and correct. You may have to correct ‘dirty’ data, and if you do, then you will need to report to Ms Sanus what data was changed and why.
Calculating revenue is complicated: revenue from surgery for public patients comes only from the government; revenue from surgery for private patients comes from the insurer and the fee ‘gap’ comes from the patient; while total revenue may be affected by the government requirement for a minimum amount of surgery time being made for public patients.
Profitability calculations for each surgery must take account of whether the hospital has met the minimum level of public patients in the quarter, as well as the other cost and reimbursement factors noted in the summary above. This is a complex calculation and requires considerable thought to include all factors (including any ‘gap’ fee payable by the patient). Note: The minimum allocation of time for public patients will be a fraction of the full year’s allocation for the provided dataset, which is only for the first quarter.
ITECH5005 students will find Macros useful for creating the functionality of ‘buttons’ (i.e. shapes with appropriate text labels) for the menu system (i.e. for hiding/un-hiding worksheets to give the appearance of moving from one page to another). Note: you might also consider using a button on each page that when clicked returns the user to the main menu.
You will likely need to perform intermediary calculations or additional analyses beyond the assessable tasks to gain sufficient insight into the surgical operations to provide recommendations to Ms Sanus. Include any additional analyses or summaries in the Excel workbook you submit.
Make sure you review the marking guide, to ensure you complete all required tasks before submission.
The questions posed to you in this assignment range from easy, through to challenging. However, it is expected that all students will succeed in all the tasks. Some of the tasks are similar to those covered in tutorials and the associated exercises. Some of the challenging tasks may rely on insight that requires self-directed study (e.g. using help facilities in Excel to explore in depth some of the topics covered in the tutorials) as sometimes new Excel skills must be developed that are not covered in tutorials.
Assignment Assessment
This assignment is worth 30% of total marks for this course and will be marked on:
• your demonstrated understanding of the problems;
• completeness of the tasks;
• exploration of the features in Microsoft Office; and
• quality of the submission (Excel workbook & Word document).
Submission
All students are required to submit two (2) files for this assignment (preferably in a single .ZIP file), via Moodle, by the due date and time.
Marking Guide
Tasks Marks
Informational Tool (All Students):
a. Create a new worksheet for 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 summaries in the workbook. 15
1.5
9
0.5
1
2
1
Informational Tool (ITECH5005 Students Only):
g. Create a navigation system 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). 3
3
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 for whether a minimum level of public patients have been provided surgery in the quarter.
l. Profitability of surgical services. 6
1.5
1.5
0.5
1
1.5
Observations (All students):
m. Chart changes to profitability of surgical services over the year, by total for each month. Use an appropriate chart to summarise.
n. What is BestHealth’s most profitable type of surgery, sub-categorised by the type of patient? Use an appropriate graph/chart to summarise this observation.
o. What is BestHealth’s least 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 to 30% or decreased to 20%? Use an appropriate table and graph/chart to summarise this observation. 6
2
1
1
2
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 1st March 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? 2
1
1
Recommendations (All Students):
s. What are your recommendation regarding other data that might be collected to improve decision making for BestHealth Hospital, and why? 1
1
Recommendations (ITECH5005 Students Only):
t. What changes do you recommend BestHealth Hospital make to any of its surgical services, and why? 1
1
Submission Presentation:
u. Report is well presented and well-written, containing:
• Title page
• Table of Contents
• Appropriate use of headings within report
• Appropriate use of figures (i.e. graphs, summary tables) and reference to calculations and summaries to justify all observations and recommendations 2
0.5
0.5
0.5
0.5
Possible Deductions:
Late submission (deduction - 10% per day)
MS Excel workbook and Word document not named correctly (deduction - 0.5 marks each)
Calculations, Tables and Graphs/Charts not used in analyses as required (deduction – up to 5 marks)
Analyses and summaries in workbook do not have required headings, labels and legends (deduction – up to 2 marks)
Total marks ITECH1005 students: 30
Total marksTECH5005 students: 36
Contribution to total marks 30%