I currently have a business that takes in medical specimens from various marketing firms. All the specimens get entered into the software that we use in the lab. I'll refer to this software as the Lab Information System (LIS).
Then we drop claims for the work we do, this is entered into the Billing Software (Billing). Right now, the two systems do not talk to each other, which makes things a bit difficult.
My marketers ask for regular reports. Any report we generate from the LIS will show ALL specimens received, but it doesn't tell them how much has been collected. We can pull that data from a report on the Billing software, but it doesn't show all specimens - only the ones which have been billed so far.
What I'd like you to do is build an excel model that will do the following:
1) Takes Data from LIS of all specimens as well as Data from Billing system and just uses a lookup function to match payments to the appropriate patient accounts.
2) Creates a report for each marketer by pulling from the report the data that has his/her Account number on the specimen.
3) Does some basis analysis on each marketer report (# of specimens for the period, average pay per specimen, and anything else you think we would want to see, etc.)
4) Creates a rollup summary page for us to see all our marketers and how each of them are performing, etc.
We'd like this whole thing to be pretty automated so all we have to do is feed the excel program with data dumps each time we need to run the report and it can run it all for us so we can send reports to each marketing partner.