Healthcare Almanac Documentation

Overview

The Healthcare Almanac provides complete detailed information for all Medicare/Medicaid healthcare providers. The providers include Hospitals, Skilled Nursing, Home Health, ESRD, Hospice, Clinics and CMHCs. Healthcare Almanac is for cost report preparers, financial analysts, consultants, marketing, public relations and contracting. The information covers up to nine years of history. The information is obtained from CMS - Center for Medicare/Medicaid Services. This information is the Medicare Cost Reports and Provider of Service information. Other information sources that maybe included in the future.

Database Processing and Design

Healthcare Almanac uses publicly available information from government agencies and reformats the information into an easy to use format. The information consists of the following.

  • Cost Report data from Center for Medicare/Medicaid Services.
  • Provider of Services data from Center for Medicare/Medicaid Services.

This information is retrieved either by download from websites or ordered and received on CDs. The information comes in two types of formats. Vertical data (Cost Reports) has a single data element with a description like worksheet, line number and column. This format is typical of database storage systems. Horizontal data (POS/Stock Market) has many data elements in a single record like a hospital record with information on bed counts, FTEs, services offered, etc. This format is typical of flat file storage. These formats are large files (over 2 gig) and are difficult to process for the average user.

The information is processed by the following steps.

  • The raw information is loaded into a database storage container like Microsoft SQL Server 2014.
  • The information is validated for completeness and format. Control counts are established.
  • Documentation is used to create meta data to describe the data in the tables.
  • Tables are created to decode the data in the tables. For example, in the POS tables, a hospital type field may have a value of '1' to describe this hospital as 'Acute Care'. A table is created to decode this information. There maybe a number of tables required to decode and describe all the information.
  • The converted table, supporting meta data tables and decoding tables are packaged together into a Microsoft SQL Server 2014 database. This database is prepared for distribution to Microsoft Azure and downloadable databases for the user.

Once the information is stored an SQL Server, SQL scripts are used to create summary tables and selections tables. The summary tables are used to create the reports for the website. The selection tables are stored in the user database that is stored on your desktop computer. This provides rapid selection of information.

Report Design

To achieve the maximum value from the Healthcare Almanac data, software is used to extract the information from database tables. There are up to five types of reports.

  • Reference Reports in summary and detail format. In this report, the user selects the provider and a system query is created to extract all available information in the detail format. The system saves the information in a table for the user on the local user computer.
  • Custom Reports In this report, the user selects the providers and data elements. The data elements are based on the information in the meta data tables. A user could select 5 providers and 10 data elements. The system will create a query to extract the information and save the data in a table for the user. This report shows the data item by provider.
  • Analysis Reports In this report, the user selects the providers and data elements. The data elements are based on the information in the meta data tables. A user could select 5 providers and 10 data elements. The system will create a query to extract the information and save the data in a table for the user. This report show the provider information by data item.
  • Comparison Reports In this report, the user selects the providers, data elements and two masters or time periods for comparison. The results of the query are saved in a table for the user.
  • Special Reports Based on the type of information, special reports are prepared for the user based on user data selection. The results of these queries are saved for the user.

In addition to the reports, Healthcare Almanac has Excel spreadsheets to create OLAP cubes or pivot tables. These cubes will be used for beds, FTEs and financial information. The cubes are an excellent way to look at data. Excel can be used to print reports from the pivot tables.

Healthcare Almanac has two types of databases. The main database type is the Permanent Database. The permanent database is stored in the Azure Cloud. The permanent database and also be downloaded or received on CD/DVD. The permanent database is read only and password protected. The passwords are available to owners/subscribers to Healthcare Almanac. The other database type is the User Database. This database is used to save the results of each query and provide the selection files for the user. This database is stored on the local user computer.

In summary, this is the general design of Healthcare Almanac. The development of this product has required great effort. We believe that purchasing Healthcare Almanac should be based on the value you will receive from using the product. The time you would spend to get and process this information would be substantial. We hope this product will save you time and effort and will improve the quality and quantity of your work.

Cost Report Descriptions

The cost reports consist of worksheets prepared by healthcare providers. These cost worksheets are sent to a fiscal intermediary for processing. After validation and desk checking, the cost reports are transmitted to CMS. At CMS, the cost reports are placed in the HCRIS database. Healthcare providers are required to report five months after their fiscal year end. The cost reports today are used to reimburse healthcare providers for cost mandated by acts of Congress. The cost reports are divided into many worksheets that have letter codes. Worksheet A is the adjustment trial balance used by the fiscal intermediary for payment processing. Worksheet S is the statistical worksheet that provides compliance information, case counts and a number of elections made by the healthcare provider. Worksheet G is a financial statement. Worksheet B is for cost allocation by departments. Worksheet C and D contains the cost to charge ratios. There are other worksheets to support the financial statements.

Sample reports are available to show the Provider Reference Report, Provider Custom Report, Provider Comparison Report and Provider Status Report. These reports can be previewed, printed or exported to a PDF, DOC or Excel format.

Provider of Service/UPIN

Healthcare Almanac has a directory of healthcare providers based on publicly available information. The almanac uses information from the Provider of Service file obtained directly from the Center for Medicare/Medicaid Service. The POS file contains information on the following types of providers

  • Hospitals
  • Skilled Nursing Facilities and Nursing Facilities
  • Home Health Agency
  • End Stage Renal Disease
  • Hospice
  • Ambulatory Surgery Centers
  • Rehabilitation Centers
  • Rural Clinics
  • Therapy Facilities
  • Mental Health Facilities
  • Labs

UPIN/NPI

NPI information was released in October, 2007. The NPI data is an enumeration of all Medicare Providers. This data replaced the UPIN information. The NPI file is very large and we are currently looking at the data for a way to provide information similar to the UPIN information. The NPI file consists of NPI code, Name, demographic information and a very limited amount of provider information on services. There are currently over 2 million providers in the file. Our current plan is to divide the information into files based on the type of Provider. This would be Hospitals, Physicians, SNFs, etc.


Please contact us for more information

Robert Neal, CPA
Healthcare Almanac
Lake Worth, FL 33463
Voice 561-594-7551
Email robeneal@halmanac.com

Copyright © 2018 - Robert Neal, CPA