Case Mix Assignment Guidelines
Assignment: An emergency department wants to track all patient flow into their center. EMTALA requires centers that accept Medicare to MSE patients, regardless of ability to pay, citizenship and legal status. Operations reaches out to you and requests that you track all patients who come in and determine how many patients leave without being seen or are screened out during medical screening. They also want to differentiate between billable and unbillable patients to better gauge billing census. Use Type of Service_Student file 2022 to complete the assignment requests.
Submissions that are not Excel and/ or do not include formulas will earn 0 points. EDs can see thousands of patients a month and you will not be successful doing manual work. While reports can be automated, they may not provide what you’re looking for so you must be able to use raw data and create reports on what needs review.
If you do not know how to use Excel, consider the BATC courses. Schedule time to discuss how to do this during my office hours so that you get the help you need to be successful.
The following type of services were recorded:
Discharged: Patient was seen and had a full workup before being released.
Observation: The patient was observed to see if admission may be necessary. Workup and treatment were done during this time.
Left AMA: Patient left against medical advice. Patient may or may not have had work-up completed. We will assume the patient did not have services rendered to bill so expectations are not inflated.
MSE: Medically Screened Examination and no further workup was continued.
Transfer: Patient was transferred to another facility after stabilization.
Wound Care: Patient came in to remove stitches received in the ED previously. There is no charge as suture removals fall in a global period and is part of the initial charge.
Admissions: Patient was treated but was admitted into the hospital. Per contracts, all billing is done under in-patient stay.
Left without Treatment: Patient was deemed emergent enough to seek further care after MSE but left after being triaged. No workup was completed, and patient did not sign AMA. No billing should occur per ED policy.
MVC: Patient sought care after being involved in a motor vehicle collision.
LWBS: Patient left without being seen. They checked in and left without notifying anyone and/or being triaged.
Learning Intentions:
· How to create a report with predefined data. Using a spreadsheet to organize numbers and data with formulas and functions to provide analysis and review of operations level requests within a healthcare business during a timeframe. (
· Using excel sum, average, and month functions, and combining functions to create necessary results for project objectives to find total patient, total billable, and average billable information.
· Using precalculated raw data to respond to management and operation questions quickly.
· Determine type of service billing status based on provided explanations.
Success Criteria:
Your submission must be in Excel (XLS, XLSX) format. OTHER FILE TYPES WILL NOT BE ACCEPTED FOR SUBMISSION. Correct formulas and selections of billable v. unbillable will allow for full credit. Correct formulas with incorrect visit types will receive half credit. Formulas must be used to earn credit. Partial formulas will receive partial points.
Excel:
· Create a formula that will find total patient count (15 points)
· Create a formula that will find total billable patient count (15 points)
· Create a formula that will tell you total unbillable patient count (15 points)
· Create a formula that will find average billable patient count by month (15 points)
· Used correct visit status types for each formula (40 points)
