Several of my clients are New York-based social service agencies. As such, much of their income depends on Medicaid reimbursement. The problems they face are two fold: First, they need to make sure staff members document client work in a way it can be billed. That’s important, but it’s not something I deal with.
Second, they need to figure out how much they’re billing and collecting—and where the differences lie. This problem meshes well with my background in accounting and databases. The goal is to know how well (or poorly) billing/collecting is going—so they they can work with client-facing staff members to make sure all revenue is being captured.
The Challenge of Medicaid Billing for Social Service Agencies
As I see it, the challenge of Medicaid billing for social service agencies lies in the plethora of systems that don’t fully talk to each other.
For example, in billing for social services, you’ll most likely have an EHR (Electronic Health Record) or other system to track patient data. Then, you have a Medicaid billing system, often provided by a third party. Then, you’ll have a state system, such as the MAPP (Medicaid Analytics Performance Portal).
And finally, you’ll have an ERP where the final financial outcome may reside. (When dealing with revenue, the ERP is generally the least important piece of the puzzle. But for other reporting needs, it can be very useful to have more expense detail.)
Many people try to solve the problem with Excel. This is a bad idea for the many reasons I describe in this post. When accountants doesn’t know what to do, they often turn to Excel. It makes them feel like they’re accomplishing something. And if they work for a small agency, they can probably use Excel as a solution—for awhile. But as their agency grows, so do their Excel problems.
The Datamart Solution
My suggested alternative is a simple datamart. (This is the solution my team and I implement for clients. I might be biased, but I’m not wrong.) Creating a datamart can take anywhere from five to ten days to 12 months, depending on what you’re trying to do.
Here’s the broad-strokes process:
1. Identify the minimum amount of work you can do to solve a meaningful problem.
The amount of data in all these systems is voluminous. So it’s tempting to think, “Since I’m building a data mart, I might as well take all the data.” Don’t do it.
It’s better to have a partial datamart that solves a meaningful problem than the perfect datamart that solves none because it never went into production. Figure out which is the most important problem to solve and then go there.
2. Identify all relevant systems and determine how they can give you data.
This may sound basic. But getting access to systems can be a challenge. The key point, though, is to identify where you’re getting the data.
Once you identify the systems, you need to determine how to get data out of them. Best case scenario (albeit unlikely) is to connect directly to the database and create the necessary queries.
Next best scenario: The system has decent standard reports (or a reporting tool you can use to pull data into Excel or csv format).
After that, if you’re dealing with government systems, you hope they can provide standard output files with data in Excel or csv format.
3. Determine where you will store all the collected data.
We work with SQL Server and Microsoft tools. There are plenty of other database solutions on the market, but we focus on Microsoft for several reasons:
- Most of our clients already have SQL Server installed. Many accounting systems rely on SQL Server for a database. Generally, the datamarts we’re talking about can easily share the same server as the server with your ERP database.
- Microsoft offers highly discounted versions for nonprofits on TechSoup. If you don’t own SQL Server, that doesn’t mean you can’t get one at a decent price.
- Even if you have to buy a license, the cost is fairly reasonable. Even for a mid-sized solution datamart, the cost isn’t too bad.
- Create a process to upload the Excel and csv files into the database.
We generally estimate one to two days to upload each data set. (If we’ve done the feed before, it can take less time. Which is why our contracts specify that we grant you a license and don’t perform work for hire.) The goal is to have a “push a button and go” system.
5. Choose your initial output format.
Again, we generally use SSRS because it comes with SQL Server. It’s excellent for creating both detail and summary reports and distributing them widely.
6. Build your basic reports in the database.
The quantity of reports will always increase over time. Select the most critical things and start there. Don’t worry about trying to get everything done at once.
We understand that 10 to 20 days of consulting time is not an insignificant project for a nonprofit. But if you are dealing with hundreds of thousands—if not millions—in revenue, it’s worth knowing what’s being billed and what’s getting paid. Then, your finance department can work with the agency to make sure revenue isn’t being lost.