Case Study

Database Creation for Statistical and Informational Reporting for Compliance Review


Our team was engaged to review and report findings for a comprehensive Compliance Review for six outpatient programs that offer behavioral health services. The project required the creation of a spreadsheet tool to effectively receive data from the review of the patient records and billing sheets to provide a final statistical and analytical report.


Initially, the Client intended for our team to review all 20,000 separate encounters based on visit dates between July 2014 and June 2015! The challenge was to determine a sampling process that was statistically sound and also met criteria set forth by the Client. Our administrative team provided the documentation to present to the Client that recommended using a sampling calculator to determine a sample size. The Client agreed with us that the sampling process would accept a 5 percent error rate, which is in line with the Office of Inspector General’s (OIG) methodology and set a 95 percent confidence interval.

Typically in these cases, HFS would use the OIG’s RAT-STATS statistical software tools to assist us in performing random samples and evaluating the results. Unfortunately, there was no way to enter the criteria needs (Program, Client, and Quarter) into RAT-STATS for our population of encounters, visit dates, quarter and by program, since there were a different number of quarters and visit dates in which they were seen, thus creating complexity for the sampling solution.


It was decided that the random sampling process could be done using formulas in Microsoft Excel. First the data provided by the Client was formatted for randomization. Using advanced and creative formulas, our team was able to use the criteria to extract randomized visit dates, producing a 7.89 percent sampling. A list was then provided to the Client to pull and scan the patient records, which were uploaded using our secure FTP server.


A spreadsheet was developed to reflect the numerous categories of items from the patients’ records and established 55 individual fields to gather the data needed to answer the Client’s specific questions. The main focus areas were incomplete documentation and the financial results of allowed or disallowed minutes, consistent differences, aligned with specific criteria and a consistent data collection tool, allowed our consultants to enter data from the records into the spreadsheet in a centralized location.


After some trial and error, a process of data entry and compiling information was established, and the auditing team was able to audit the patient records in a very short time span. Using the data entered by the review team, the administrative team was able to provide real time analytics and statistics.


The project manager was able to provide a detailed report to the Client, showing the overall error rate, in percentages and dollars, and broken down by category. This methodology provided specific recommendations to the Client based on this reliable data.

As a result, the Client was very appreciative and immediately asked HFS to conduct another review of encounters in January 2016 to compare error rates and demonstrate improvement in the system. Because of the detailed system already set-up, this follow-up review was completed and quickly delivered to the Client.


"The use of Excel tools can really make a difference to an analysis, especially as our team was able to create composite percentages, streamline the analysis of data, and transfer recorded data points to worksheets that could be easily interpreted by the client."

-Mary Lopez, MSN, RN, Clinical Operations Practice


More about the benefits of our Clinical Operations Services >>