BIS Assignment work

· 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:

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

8