Would you like to use all of your Excel knowledge on your practice's data? I use SQL to help practices mine their existing data in PM, EHR, and other systems to get better information to run their practice. I also help organizations use Excel as a reporting tool for the data mining. Once you've seen your data in Pivot Tables, your practice will never be the same. When you're ready to start analyzing your data, here's how to contact me.
Here are some recent projects I've worked on.
Wrote custom code to analyze how many days until the third next available appointment for each provider. We chose the third next available appointment to smooth out unusual fluctuations due to appointment cancellations and other factors. The data is calculated every morning and stored in a table which is linked to a Pivot Table for analysis of trends by provider and specialty.
Created a custom spreadsheet that reads Centricity appointment schedules and allows users to track which providers are in which location. Created a second spreadsheet that uses the providers' schedule to track and schedule support staff based on the providers' locations. I also added a custom routine that summarizes each supporting staff member's schedule for distribution, saving the scheduling staff hours each week.
Designed an Excel table that automatically loads patient balances by bucket (0-30 days, 31-60 days, etc.) into an Excel table. The table also tracks upcoming patient appointments and locations so financial counselors can contact patients before their next appointment to confirm payment arrangements. The table can filter by patient balances, which member of the A/R team is responsible for the claim, aging buckets, upcoming appointments, and more.
Created a Payer Performance Matrix that compared a practice's top payers on a variety of factors, including receivables over 120 days, payments not a contracted amount, reimbursement per RVU, appeal rates, and more.
Linked specific appointment information from a practice management system to Excel and created a daily appointment report to send to a transcription group. The report pulls just the information the transcription company needs in the format they requested to ensure dictation isn't missed.
Produced a custom report for a medical billing company that showed billed charges by the month the claim was billed and collections by the month(s) when the payments on the claim were received. Their clinics can now look at each months' collections and analyze when the services were rendered that generated those collections. The billing company uses the report to show practices how efficiently their claims are being collected. Practices use the report to forecast cash flow.
Used a practice's appointment cancellation reasons to find and analyze patient no-shows. Created a Pivot Table to track no-shows by appointment date, appointment time, primary insurance, referral source, referring physician, patient's city and Zip Code, location, scheduled physician, and more. Discovered that morning appointments cancelled more often than afternoon appointments, confirmed that Medicaid patients were more likely to no-show than commercial patients and quantified the difference, looked at trends in no-shows by location, and much more. That practice now has an ongoing tool, not just a one-time report, to track and manage no-shows.
Took the same analysis described above and added a calculation of the time between the date the appointment was scheduled and the actual date of the appointment. Discovered that appointments scheduled 1 week out, 2 weeks out, etc. were much more likely to no show than other appointments. We charted the data and you could learn your 7 times tables from the spikes in no shows every 7 days. It turns out doctors were telling patients, "Come back in x weeks if it still hurts." For some doctors, x was 1 week. For other doctors, x was 3 or 4 weeks. The appointments were made, the patients didn't hurt, so the patients didn't show up. The practice is now changing their approach to reduce the number of no shows. I did a similar analysis for another group. You'll still see spikes, especially for weeks 3-8. Also notice that if an appointment is scheduled within 2-3 days, the no show rate is less. As soon as patients have to wait longer for an appointment, the no show rate jumps. Those first few days are a sweet spot for reducing no shows.
Designed a custom analysis for a group of anesthesiologists. They were negotiating overtime rates with a hospital and needed to know how many cases ended after 3:30 and how many cases ended after 5:00 but before the group of anesthesiologists' call obligations started. We started with a huge list of cases and then divided the overtime rates into two buckets. I created some pretty complicated Excel formulas to determine which cases hit overtime and how much overtime fell into each rate bucket. It took some fairly involved Excel functions, but at the bottom of the spreadsheet was some very valuable data about how much time was spent in each bucket. With that data, it was easy to apply a value to the overtime and prepare something to present to the hospital.
Reviewed a practice's dashboard and suggested ways to better automate and communicate the practice's information. I added a variety of Excel tips and tricks including conditional formatting, custom formats, and advanced formulas to provide more information more efficiently to the doctors.
Helped a group of anesthesiologists perform an in-depth analysis of hospital data to determine the first start time and the last end time of each provider each day. We used these times to calculate the total hours each provider spent at each location. We put this information into a Pivot Table to analyze total hours by location, by provider, by day of the week, by week of the month to track vacation, and more.
Produced a spreadsheet for an orthopaedic practice that had three automatically updating Excel tables. The first table tracked upcoming appointments that needed pre-certification. The second table tracked expired pre-authorizations based on either an expiration date or on a certain number of visits. Finally, we analyzed pending pre-certifications to make sure insurance companies responded timely and before the patient's next scheduled appointment.