شرح عمل مستشفى بالإكسل شيت

MA7444 Excel for Data Science

Coursework 1 - 2025/26

Coursework Weight: 30% of the overall module mark.

Use of AI: The work submitted must be entirely your own work. You may use AI to assist in deciding approaches and suitable Excel functions, but AI must not be used to produce your final submitted output (including written analysis or descriptive commentary).

Marks will also be awarded for good spreadsheet practice, including appropriate use of formulas, documentation, and clarity.

Submission:

• Submit your workbook by Friday 31 October at 12:00.

• The file should be named ID_MA7444_CW1.xlsx, where ID is your university login ID (e.g. ha427).

• Submit a separate audit trail (PDF) documenting your methods, calculations, and checks.

• Incorrect file naming or poor audit documentation will result in mark deductions.

Academic Integrity

Before submitting your work, you will also be asked to confirm that you have completed the Academic Integrity online tutorial available here (you only have to do this once for MA7444 and MA7441):

https://uniofleicester.sh...

Overview of the Task

You are working for a hospital trust analysing patient admission and discharge data to support healthcare planning.

The trust wants to estimate bed occupancy risks, identify seasonal variation in admissions, and evaluate the effect of average length of stay (ALOS) on capacity planning.

You are required to construct an Excel workbook that models these risks, supports decision-making, and helps forecast capacity.

Data Provided

Download the file ‘MA7444_CW1_data_and_template_2025.xlsx’ from the Assessment and Feedback section on the module blackboard site. The Excel contains the following sheets:

1. Patient_Admissions – A dataset of 250 hospital admissions (date of admission, department, length of stay, patient age, gender).

2. Admissions_Distribution – A reference table showing historical monthly admissions percentages (used for comparison).

3. Hospital_Beds – Current number of beds available in three departments (Medicine, Surgery, Paediatrics).

Instructions

1. Data Cleaning

o Check for any missing/erroneous values in patient data and correct them.

o Standardize age groupings (e.g., 0–18, 19–40, 41–65, 66+).

2. Admissions Analysis

o Construct a frequency distribution of monthly admissions and compare it to the historical percentages given.

o Comment on any differences (e.g., seasonality, anomalies).

3. Bed Occupancy Forecasting

o Calculate average length of stay (ALOS) for each department.

o Using historical monthly distribution and ALOS, project expected bed occupancy for the next 12 months.

o Identify which months/departments are likely to exceed bed capacity.

4. Scenario Testing

o Model the impact if average length of stay increases by +1 day in each department.

o Recalculate occupancy and identify additional risks.

5. Risk of Shortage

o Calculate the number of projected months where capacity is exceeded.

6. Checks and Validation

o Include built-in checks at each stage (e.g., totals match, percentages sum to 100%).

o Ensure your workbook is logically structured and error-free.

7. Audit Trail

o Produce a comprehensive audit trail (pdf) giving full details of all calculations and checks.