Excel for Dental Practices
My dentist wife and myself are former Dental Practice owners ...
… so we are well placed to help you with dental spreadsheet solutions, reporting, KPI Dashboards & problems.
My wife also has a Dental Training & Consulting business called Just Excel, see details below
That’s 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 dental practice processes that can be tailored quickly to suit other dental practices.
Excel for Dental Practices can provide a host of solutions to help 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 did many owner type tasks for our four chair practice (Toothwise – Redland Bay Dental Surgery in South East Queensland) with my dentist wife Dr Nupur Hackwood. I did the payrolls, the accounting and various tasks on my precious weekends. By 2010 our practice grew and became so busy, I joined our practice full time and so by 2016 when we sold I was involved for over ten years.
I came to know the dental business intimately and also applied these advanced Excel spreadsheeting skills and developed many Excel solutions for our dental practice. I got deeply into VBA programing because at the time that was the best tool for working with data from different dental software reports and other stand alone spreadsheet data.
I was at first surprised to find large gaps in expensive dental systems, data was splintered and inconsistently reported. Revenue in one report was gross and in another something different because discounts were excluded. So I 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, so any spreadsheets had to be user friendly, spreadsheet protection and have checks and balances to indicate there was any problems.
So this experience really 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 for Dental Practices including advanced formulas, Pivot Tables, VBA programing (macros), User Forms, Power Query, Power Pivot & Power BI to solve practice problems that probably I have had direct experience with myself.
The amazing tool Microsoft Power BI which takes reporting, dashboards and KPIs to a whole other level I discuss further below.
Also in the medical space, in my external Excel consulting work which also started in 2010, 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…
Having used 3 big name dental software systems, we discovered quickly that there were gaps and inconsistencies 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 (as in this screenshot showing the flag “No Invoice!”)
- who didn’t pay on the day
- who paid on the day for past or future treatment and
- who came for an appointment but the dentist missed raising a treatment code
In big business, these are called Exception Reports driven by internal and external audit compliance I suppose but our dental software didn’t provide this.
Using advanced EXCEL skills, we were able to extract, clean and organize .csv downloaded data in raw reports marrying the different data together in Excel and create meaningful reports similar to this one for other things we needed including KPI Dashboard reporting see next. Basically I wrote code in VBA to sift through daily transaction reports and test for these defined problems we needed to know about and keep track of.
These days Power Query would have made this task so much easier!
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 second image.
Automated Stock Management system
I developed a Stock Management system for our practice to help with more efficient and price effective ordering – reducing the risk of a stock out is critical to a dental practice as without the materials no fees and cheesed off patients.
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.
Having used 3 big dental software systems over the years, we discovered there large were gaps in the functionality of these systems in particular the 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 I think. My first job was in plastics manufacturing so it was all about scheduling direct labour to each machine when it was operating and not down for planned maintenance. Dental rooms are a bit like this too – each room is an income earner so rostering to each room’s production capacity seemed yo make sense to us.
So 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 this provided good 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 and time keeping so this may mean Excel’s role in this area becomes less common. However I don’t think other applications match staff with the income producing person or dental room concept, and this roster quite honestly reduced our staff costs by many thousands per year.
If you are interested in this application let me know – my wife the dentist does the implementation and training.