Case Study · Healthcare — Medical Case Review & Utilization Management
Automated Two-Sided Payroll and Billing Reconciliation
Two rate structures, dozens of providers, several insurers, years of accumulating history — reconciled by hand, every month, with limited ways to catch errors.
At a Glance
Client / Sector
Confidential (NDA) — medical case-review and utilization management network
Engagement Type
Excel-based payroll and billing engine, run monthly on an ongoing basis
Duration
Live production system, in active monthly use since November 2024
My Role
Sole consultant — requirements, architecture, development, testing, ongoing monthly execution
Tools & Techniques
Excel 365, Power Query, VBA macros, LAMBDA functions, PIVOTBY, structured Tables, data validation, slicers
Key Deliverables
Provider log workbook, centralized payroll and billing summaries, detailed reports, searchable database, dual-engine audit reporting, executive dashboards
Structured
A separate provider log workbook feeds one centralized database; a small set of rate and rule tables governs every dollar the system calculates.
Automated
Power Query rolls up logged activity across months and providers, auto-classifying every review and activity for accurate billing and compensation.
Tailored
Summary and detail reports use two independent calculation engines, cross-checking each other automatically.
The Challenge
Physician reviewers log completed case reviews and billable activities each month, work billed to insurers at one rate and paid out to providers at a separate, lower rate. The correct rate depends on review type, practice, and whether the work fell on a weekday, weekend, or holiday. The log had no data validation to keep entries clean, and both billing and payroll had to be manually assembled every month.
How It Was Built
What data is needed?
Primary data
Per-provider case-review entries (review type, date, insurer/region) and hourly activity entries (rounds, meetings, training), logged monthly by each provider.
Metadata and rules
A provider roster with default practice type and special compensation rates; billing and payroll rate tables by review type, practice, and day type; a holiday calendar.
Where does it come from?
Each provider logs cases and activities on their own page of a separate log workbook, inside two structured Tables, with data validation catching entry errors at the source.
Because copy-paste can bypass validation, it’s reapplied to the source log every month before import, doubling as an error check.
A monthly Power Query import rolls every provider’s entries into one centralized database, auto-filling default practice type when unspecified, and matching raw data against metadata to price each record before it lands on the worksheet.
Providers paid hourly have their cases flagged automatically, so pay calculates from logged hours instead of a per-case rate.
What do you need to do with it?
Billing reports for each insurer, broken out by case type, practice, and provider.
A summary report of activity by provider, showing at a glance which providers, case types, and insurers drive revenue.
A top-line report showing billing totals by insurer and payroll by provider in the same place.
Rolling results showing billing totals by insurer for the past year or more.
Two independent LAMBDA-based reporting engines calculate the same monthly totals two different ways — a mismatch automatically flags a data anomaly to reconcile.
What Was Built
This system runs the client’s actual monthly payroll and billing cycle today — not a pilot or proof of concept.
- A standard per-insurer monthly report, billing or payroll view, generated on demand — a macro loops through every report type, producing the full monthly package in one pass.
- A one-page summary rolling up every standard report, built from a second, independently-calculated LAMBDA engine that cross-checks the per-insurer reports rather than just re-displaying them.
- A Final Totals view, reusing the summary LAMBDA, showing billing totals by insurer and payroll totals by provider with period-over-period change built in.
- A 12-month rolling view by insurer, and two dashboards, case-level and provider-level, pairing slicers with PIVOTBY.
A Note on Confidentiality
This engagement is covered by an NDA. The client has approved anonymized use of this work in presentations, and this page was shared with them prior to publication. Client, insurer, and provider identities have been anonymized. Dollar values shown are fictitious but structurally representative of the real output. The system architecture and methodology described are accurate.