California HCAI Hospital Financial Analysis – A Self-Guided Study of Healthcare Analytics
California’s Department of Health Care Access and Information’s (HCAI) mission is to expand equitable access to quality, affordable health care for all Californians through resilient facilities, actionable information, and the health workforce each community needs. They provide data for hospitals regarding Facilities, Financing, Workforce, Patient Outcomes, Infrastructure, and Affordability. The data is gathered by the Department via the System for Integrated Electronic Reporting and Auditing (SIERA), which utilizes hospital financial reports.
The purpose of this assignment is to learn how to navigate a publicly accessible hospital database, alleviate data integrity issues, and compute basic financial ratios that a healthcare executive may use for decision-making.
The dataset can be found here: https://data.chhs.ca.gov/dataset/hospital-annual-financial-disclosure-report-complete-data-set
Download the 2020-2021 Fiscal Year Hospital Annual Financial Data (December 2022 Extract) Excel file. Take a moment to familiarize yourself with the dataset – note that there are over 400 hospitals with a total of 12477 data attributes.
You may also choose to download “Page Column Line Labels for 2015-20xx”. This provides a much more compact version of the data attributes that you may find easier to read, sort, or search.
Once you have a grasp of the data, answer the following questions:
- Create a new column and compute the debt-to-income ratio for each hospital (Total Liabilities divided by total assets). Which hospital (with necessary data available) would be the least likely to be able to pay off its debts in the coming period?
- How many hospitals and hospital systems are operating at a reported loss for the fiscal year?
- In order to determine what type of patients a hospital is serving, hospital executives will examine the payer mix, which is often defined as the following: (Medicare Reimbursement + Medicaid Reimbursement)/Net Patient Revenue. Which hospital with the necessary data available serves the highest payer mix of government-funded patients? (Note: The Medicaid program in California is known as Medi-Cal)
- Many states, including the state of California, evaluate hospitals’ non-profit status by computing the ratio of charity care to net patient revenue. Hospital executives typically want to maintain this ratio anywhere from 1 to 5 percent. What is the charity care to net patient revenue ratio for Watsonville Community Hospital?
- Why do you think almost all Kaiser Hospital listings in the database are missing ALL balance sheet and income statement information?
- As a new data analyst that is unfamiliar with the database, lingo or jargon within your work environment may be a challenge you need to overcome. Your boss walks by and makes a quick and simple request – “Give me the phone number for CHOMP”. In the absence of asking other coworkers, what do you provide given your database?
- How many hospitals and hospital systems for the fiscal year were classified as non-profit hospitals (note: state and municipal hospitals would be excluded)?
- What single hospital likely has the largest physical size?
- How much did Sutter Amador Hospital receive in Covid stimulus payments during the fiscal year?
- Why does the USC Norris Cancer Center have two entries for the same hospital in this database? Is this a mistake or is it intended?
