Medical Services Summary and Payroll Calculator

Executive Summary

I previously worked for a medical group with three full-time doctors (one owner and two associates).  Like most associate contracts, these provided for a base plus bonus salary structure, with the bonus dependent upon the receipts from his/her services.  The contracts also provide for an open-book policy entitling the associates to monthly reports about charges, receipts, billing performance metrics, and payroll calculations.

The nature of healthcare is such that receipts from services performed often lag months (sometimes, even years) behind the date of service.  The production reports (produced by the billing software) show a detailed list of all charges made, adjustments by insurance, and payments received (from insurance and patients), all organized by date of service.  For example, if a doctor performs a surgery on 12/18/X1, and the claim is delayed for several months, the practice may receive payment months later in 20X2.  When payment is finally received, it will be reflected in the billing report for December 20X1.  Thus, the only way to calculate an associate’s salary is to run a report for their entire tenure within the group.

My VBA code automates the associate reporting/payroll process by doing the following:

1. Generating a summary report (based on the detailed billing report) showing charges, receipts, adjustments, and other pertinent information broken down by month.
2. Creating a chart for the respective period showing charges vs. receipts.
3. Giving the user an option to save a copy of the report, email a copy to (1) the associate, (2) the owner, and (3) the user.
4. Sending a text message with the payable bonus amount to the bookkeeper.

*Notes:

1. Certain aspects of the “real code” have been changed so as to operate on another computer.  If you want to run the code, either select “No” at all the prompts, or enter a valid file path where indicated in the VBA code. 
2. For the code to execute, it should be run on data in a separate workbook.  Because the VBA causes files to be saved, closed, opened, emailed, etc. it is necessary to prevent the code from shutting down when a file is closed.  Thus, I have included one workbook with the VBA, and another of “dummy data” on which the code would operate. 

http://files.gove.net/shares/files/16w/benjamir/Final_Project_write-up.pdf 
http://files.gove.net/shares/files/16w/benjamir/IS_520_Final_Project-VBA.xlsm 
http://files.gove.net/shares/files/16w/benjamir/IS_520_Final_Project-dummy_data.xlsx