• Review the Case Study video presentation on the Video Monitoring System (VMS) for financial data on the Sitter and VMS programs
  • Note you can see a transcript of the video pages by clicking on “Transcript” at the top right of the video screen.

1. Open a blank Excel workbook and name the file, starting with your last name: LastName Case Study 2 VMS
2. On the Fall Categories Tab, show the following items in Row 1:
   • a)Label Column A: “Sitter Program”
   • b)Label Column B: “Falls Category” and insert each category on a row below
   • c)Label Column C: “Number of Falls”
   • d)Label Column D: “Cost of Services”
   • e)Label Column E: “Total Cost of Services”
   • f)Select Row 1, Columns A-E: Wrap Text, bold & center text
   • a)Label Column A: “VMS Program”
   • b)Label Column B: “Falls Category” and insert each category on a row below
   • c)Label Column C: “Number of Falls”
   • d)Label Column D: “Cost of Services”
   • e)Label Column E: “Total Cost of Services”
   • f)Label Column F: “VMS Cost Reduction”
   • g)Select Row 12 , Columns A-F: Wrap Text, bold & center text

1. On the Fall Categories Tab, show the following items in Row 12:
2. Enter the revised patient fall data by severity provided by the CFO in this table:
3. Insert formulas in the appropriate cells to perform the required calculations.
4. Format appropriate data for currency with no decimal points; center data.
5. On the Cost-Benefit Tab, create a spreadsheet table, showing the following items:
   • a)Capital costs
     1. Assume that eight monitors and one central station will be purchased in first year of VMS program
     2. No capital costs are expected in the second year of the VMS program
   • b)Operating costs – personnel, software, and fall services for VMS program patients
     1. Assume FTE’s are paid at $19.10 per hour
     2. Assume FTE’s receive benefits at 12.5% of salary
     3. Assume each FTE works 2080 per year
   • c)Patient Falls – assume no change in volume or severity in first and second years of VMS program
6. Calculate Total First Year Costs
7. Calculate Total Second Year Costs
8. Insert last year’s Cost for Sitters and Cost for Services.
9. Calculate the Net Savings for the First Year and for the Second Year of the program
10. Format the appropriate columns for currency with no decimal points, and center data
11. IMPORTANT: Create a copy of the Excel Case Study-1 Data File and add ANALYSIS to the name of the file:
    • a)LastName Case Study 2 ANALYSIS VMS
    • b)You will use this file to answer questions in the written analysis.

PART II

• Provide a short, written analysis (2-3 pages in APA format) of the case study
• Review the grading rubric for additional required content and format. 1. Identify key stakeholders in the video and other stakeholders. 2. Discuss the important concepts, economic factors and theories, and environmental factors in the case study. 3. Answer these questions using the ANALYSIS file:a. Based upon the analysis, should the executive suite members approve the VMS program? Explain why, in economic terms.b. Calculate the average cost per patient for the Sitter Program and for the VMS Program (Hint: Include operating, personnel and services costs but do not include capital costs in both calculations). Show your work in paper or refer to a new Tab in the ANALYSIS file for spreadsheet formulas.c. The CFO did not project initial revenue charges but suggested $55 per day may be appropriate. Given the following facts based upon first year experience in the VMS program, would the VMS system generate enough revenue in the second year to cover costs of the program? If not, is there an appropriate and reasonable charge to cover the annual costs of VMS program? Show your work in paper or refer to a new Tab in the ANALYSIS file.· No changes to the number or severity of Falls patients· Falls patients are monitored an average of 6.7 days on the VMS program· Medicare pays 68% of charges; Private insurance pays 80% of charges; and Medicaid reimburses 50% of charges.· Patient mix is 60% Medicare, 30% Private Insurance, and 10% Medicaid.a. Bonus (5 pts): Based upon the payor mix and the patient mix, what would be the amount of the $55 charge that would be actually received? Express result as a ratio and percentage (XX/55 and XYZ%). Discuss the importance and role of such ratios in the economics of healthcare.· Review the grading rubric for additional required content and format.