How to Use MDS Data to Calculate Case Mix

How to Use MDS Data to Calculate Case Mix PowerPoint PPT Presentation


  • 668 Views
  • Uploaded on
  • Presentation posted in: General

A step by step outline of how to calculate nursing home case mix by using MDS data.. List of StepsDiscussion of Each StepExamples of Each StepSample DatabaseInteractive. Perspective of This Presentation. State Medicaid AgencyCase mix is used as part of rate settingQuarterly calculation of adjustmentsOther components in the rateThis presentation just focuses on the case mix part of the rate calculationIntended to show example of calculationSample Database.

Download Presentation

How to Use MDS Data to Calculate Case Mix

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


1. How to Use MDS Data to Calculate Case Mix Presentation by Randy K. Baker Case Mix 2003 Conference September 11, 2003

2. A step by step outline of how to calculate nursing home case mix by using MDS data. List of Steps Discussion of Each Step Examples of Each Step Sample Database Interactive

3. Perspective of This Presentation State Medicaid Agency Case mix is used as part of rate setting Quarterly calculation of adjustments Other components in the rate This presentation just focuses on the case mix part of the rate calculation Intended to show example of calculation Sample Database

4. Before and After

5. What is Case Mix? RUG Case Mix Index (CMI) is a resource allocation methodology developed for continuing care residents. Each of the RUG groups has an associated CMI value that represents the relative cost of caring for an average continuing care resident within a group compared to the average resident in the population. Uses: CMI can be used for resource allocation in continuing care facilities

6. Steps to Calculate Case Mix Gather MDS data in a manner that can be used in a PC database Load Data into a Microsoft Access Database Calculate the number of days each patient was on a RUG Calculate Facility Case Mix score Facility summary tables Reports of patient detail Reports of patient detail Administrative notifications

7. Gather MDS data in a manner that can be used in a PC database Needed Fields (from MDS Form) Patient Identifier Facility Name / Number MDS Assessment Type Assessment Date RUG Score Other Identifiers

8. Load Data into a Microsoft Access Database Why Use MS Access? Not Promoting a product, rather features needed to perform functions Personal experience has shown MS Access to be a product that does the job

9. Load Data into a Microsoft Access Database (Continued) Text Files Comma Delimited Tab Delimited Other Delimiter Fixed Length Other databases Direct links to data sources though ODBC connectivity

10. Why Use MS Access? Use large datasets Multiple steps needed to perform analysis Queries saved to be used again on new data Macros for performing multiple sequential tasks Calculations that cannot be done in a spreadsheet

11. Load Data into a Microsoft Access Database (Continued) Create reusable template for loading data Automate load process Once routine has been validated, no longer need to do repetitive tasks

12. Load Data into a Microsoft Access Database (Continued) Remember to include only those records which you want to include in the case mix (Medicare, Medicaid, Self-Pay, etc). “Other” identifiers are used to filter and select on for analysis and other data needs than just calculation of the case mix Data base demonstration

13. Load Data into a Microsoft Access Database (Continued) Demonstration Screens Text File Layout File Delete Query Append Query Edit Query Calc Days Query Calc Case Mix Query

14. Calculate the number of days each patient was on a RUG Calculating days involves comparing two dates Dates are in different records (i.e. each record has assessment date)

15. Calculate the number of days each patient was on a RUG (continued) Technical Explanation of how to do a calculation when numbers are in different rows Referring to a Field in the Previous Record or Next Record Microsoft Knowledge Base Article http://support.microsoft.com/default.aspx?scid=kb;en-us;210504 DLookup() Usage, Examples, and Troubleshooting Microsoft Knowledge Base Article http://support.microsoft.com/default.aspx?scid=kb;en-us;208786

16. Calculate the number of days each patient was on a RUG (continued) Summary Explanation of how to do a calculation when numbers are in different rows Data must be physically in the order that calculation will be done in (first by facility and then by recipient identifier) Records must have a sequential ID number Reference to next or previous record refers to sequential ID number and uses the domain aggregate function “Dlookup()”

17. Calculate the number of days each patient was on a RUG (continued) The DLookup() function in an expression (or calculation) that returns a field value in a domain, or specified set of records. It limits the records examined when performing calculation “If the person’s ID is the same as the persons ID of the previous record, then calculate date difference, otherwise do nothing.”

18. Calculate the number of days each patient was on a RUG (continued) Microsoft Access Demonstration of date calculation Append data from table to a new table in sequential order Calculate days between dates with “update query” Select RUG to be used in days calculation Calculate Days only when assessment is other than admission assessment

19. Case Mix Calculation Formula Days IIf( TableToCalcDays!SSN=(DLookUp("[SSN]","[TableToCalcDays]","[ID]=" & [ID]-1)), Switch( TableToCalcDays!AssessmentTypeCode="01",Null, TableToCalcDays!AssessmentTypeCode In ('06','07','08'), TableToCalcDays!AssessmentDate-(DLookUp("[AssessmentDate]","[TableToCalcDays]","[ID]=" & [ID]-1)), TableToCalcDays!AssessmentTypeCode Not In ('01','06','07','08'), TableToCalcDays!AssessmentDate-(DLookUp("[AssessmentDate]","[TableToCalcDays]","[ID]=" & [ID]-1))), Null) RUG IIf( [TableToCalcDays]![SSN]=(DLookUp("[SSN]","[TableToCalcDays]","[ID]=" & [ID]-1)), Switch([TableToCalcDays]![AssessmentTypeCode]="01","", [TableToCalcDays]![AssessmentTypeCode] In ('06','07','08'),(DLookUp("[StateCalculatedMedicaidRUG]","[TableToCalcDays]","[ID]=" & [ID]-1)), [TableToCalcDays]![AssessmentTypeCode] Not In ('01','06','07','08'),[TableToCalcDays].[StateCalculatedMedicaidRUG]), "")

20. Calculate the number of days each patient was on a RUG (continued) Reminder! Don’t get caught up in technical details if they don’t make sense at first Remain focused on the basic concepts of what you are doing

21. Calculate the number of days each patient was on a RUG (continued)

22. Facility Summary Tables Summarize data by the number of days for each RUG by facility RUG Weight Days X Weight Total Facility Days Total calculated days

23. Facility Summary Tables (Continued) Demonstration of Summary queries

24. Calculate Facility Case Mix Score Case Mix Calculation = Sum of the days for each RUG X RUG Weight Divided by Total Facility Days

25. Calculate Facility Case Mix Score (Continued) Demonstration of queries with calculations and formulas

26. Administrative Notifications Presentation has focused on how to calculate a case mix Don’t forget to complete work by notifying those affected of the results of the calculations

27. Summary of Database Steps Load data from external source into table called [MdsData] Step 1 Macro Copy data in [MdsData] table to [TableToCalcDays] Step 2 Macro Orders data to perform calculations Run update query to calculate days Step 3 Macro Query = [UpdtDaysRugs] Total days each resident was on a RUG Query = [DaysAndRugs-Detail] Total the number of days for each RUG by facility Query = [DaysAndRugs-Sum] Total the number of days for each facility Divide the (weight X days) by total days Query = [DaysAndRugs-Total]

28. Summary of Case Mix Calculation Steps Presentation focus on basic steps Can have variations based on scope of desired calculation Brief exposure to another tool Assists with documentation

29. Review: What is Case Mix? Case Mix Index values represents the relative cost of caring for an average continuing care resident within a group compared to the average resident in the population. Uses: CMI can be used for resource allocation in continuing care facilities I now know how to calculate it myself!

30. Copies of Database and Presentation http://www.estrat.net/casemix/

  • Login