Excel for Dental Practices
My dentist wife and myself are former Dental Practice owners ...
Excel solutions to help with Dental Practice Processes
Thats me hiding on the right in above! My wife, a Dentist, and I owned and operated a busy successful dental practice for 11 years. We developed many excel based solutions to everyday processes that occur in a dental practice that can be tailored quickly to suit other dental practices.
Excel can really help Dental Practice owners run their businesses with reporting of metrics and KPIs, fill gaps in their dental software’s functionality and reduce tedious work being done .
As mentioned elsewhere in my site, I come from a resource sector, large business background where financial (or business) modeling, forecasting, cash flows, budgets, KPI dashboards and complex reporting was my day in day out work.
But from mid 2005 I also added to my experience ownership of a busy four chair practice (Toothwise – Redland Bay Dental Surgery) with my dentist wife Dr Nupur Hackwood for over ten years in Redland Bay Queensland.
I came to know the dental business intimately and also applied these Excel advanced spreadsheeting skills and developed many Excel solutions for our dental practice.
I was at first surprised to find unexpected gaps in expensive dental systems but got to work filling these gaps with Excel based applications. I also found, unlike the corporate world, most users in the medical industry were not Excel proficient.
This taught me how to make Excel tools and applications more automated (often using VBA macros), friendlier and more robust for less sophisticated users. Dental practices tend to be high volume, transactional businesses so Excel can be used to automate tedious tasks to leave staff freer to focus on practice flow and scheduling patients.
So I am here to help you with Excel based solutions including advanced formulas, Pivot Tables, VBA (macros), User Forms, Power Query and Power Pivot to solve practice problems that I probably have had direct experience with myself.
There is also the amazing tool Microsoft Power BI which takes reporting, dashboards and KPIs to a whole other level which I discuss further below.
Also in the medical sphere, in my Excel consulting work, I developed a complex tracking application for UniQuest for one of their medical related businesses which needed to track provider related procedures, receipts and their remuneration.
Just Excel Dental Training & Consulting
My wife Dr Nupur Hackwood BDS, MDS, ADC, PhD and a Certified Trainer with 30+ years in the dental industry, operates a Dental Training & Consulting business called JustExcel.com.au and she has live, online and face to face training courses and seminars for the entire Dental Team including Dental Receptionists, Dental Assistants, Practice Managers, Practice Owners, including problem solving, process improvement and so much more. Below is a taste…
Just some of her courses for the dental team...
…for Practice Managers, Dental Receptionists, Dentist Graduates and more
Nupur's Practice Management course is structured as the 4Ps...
P1 People, P2 Process, P3 Purchases and P4 Production
An interactive Business Model for any business let alone a Dental Practice with high capital investment requirements so its a critical tool to help in making the big decisions like:
- Junior Dentist vs Hygienist?
- Commission renumeration vs Retainer or Salary for your new Dentist
- Are my providers contributing a satisfactory profit?
- Should I build another dental room?
The power of simulating your business in a easy to understand Business Model is easier than you think in Excel. To the left is one of my Dental Business Practice examples…
Getting the reports we wanted - combining multiple reports from the dental software to produce what we needed using advanced Excel skills
Having used 3 big dental software systems, we discovered quickly that there were gaps in the functionality of these reports.
For example we needed a tool that would sift through the Daily Transaction report and pick out the exception transactions we needed to know about. Such as, who didn’t get invoiced, who didn’t pay on the day and who who came for an appointment but the dentist missed raising a treatment code.
Using advanced EXCEL skills, we were able to extract, clean and organize .csv downloaded data in raw reports to Excel and create meaningful reports similar to this one for other things we needed including KPI Dashboard reporting see next.
Combining several reports from the dental software to extract KPI data using advanced Excel skills
Marry together data from various sources within Excel or Excel’s data model using Power Query and if needed Power Pivot to create an interactive Excel based reporting workbook or dashboard that can be easily and routinely generated so you can see what’s happening in your busy practice and focus on actions to continually improve your business.
Alternatively if you want more powerful interactivity and greater insights as well as easier collaboration this can also be done in Microsoft’s relatively new powerful Power BI application as in the next image.
An automated Stock Management system that helped reduce stock purchase costs
I developed a Stock Management system to help with more efficient
and price effective ordering. The key approach behind this application were as follows…
- Decide or calculate a base count for each stock item so that you know what to purchase after a stock take. This base count might be based on X units per patient or x unit per day or perhaps just an estimate.
- The stock in deficit to the base count level is extracted for sending to suppliers for quotation which we called RFQ (Request for Quotation).
- Quotation prices are imported back to the application so the workbook could choose the lowest price Supplier and facilitate a firm order report to be sent. If for some reason a preferred Supplier is required for technical reasons for a stock item this overrides the lowest price choice.
- Incoming stock is received to the workbook so that stocks represent the current stock levels.
.aving used 3 big dental software systems, we discovered quickly that there were gaps in the functionality of these reports. Using advanced EXCEL skills, we were able to extract, clean and organise data to convert raw reports into meaningful KPI reports that made sense to us.
We automated monthly reporting of key business metrics and were able to generate visual graphics of the health of the business easily in a dashboard. This allowed us to course correct each month, rather that wait for a P&L statement at the end of the year.
Automated Rostering System taking into account overtime and HPSS award rules
My roster approach was uncommon – my Excel based Roster application key advantage was the rostering of support staff against the income producing providers, so that the support staff cost is utilized wisely.
We also incorporated a matching of rostered hours against actual hours entered for payroll (we also had provision for reasons for going over or under to be provided by staff) so to have control over what is your 2nd or 3rd largest area of expenditure, staff costs.
I acknowledge there are now apps like Deputy to use for rostering that mean Excel’s role in this area has been overtaken somewhat. However I don’t think other applications match staff with the income producing person or machine concept so I have left the example here as an idea generator.