Excel for Dental Practices

That’s me hiding on the right!  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 am I 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.

Dental Courses & Seminars   JustExcel.com.au 

Please note 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 Dental Team including Dental Receptionists, Dental Assistants, Practice Managers, Practice Owners, including problem solving, process improvement and so much more…check it out! 🙂

Business Modeling

An interactive Business Model for any business is a critical tool to help in making 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. Below is one of my Dental Business Practice examples…

Power BI

One of the exciting more recent developments in the Excel workd is Microsoft’s awesome visualisation tool Power BI.  If you don’t know what is Power BI it’s BI for Business Intelligence and it’s there to visualise and gain insights from your data using Excel if you need to and tools common to Excel.  Here is an introduction video.  Dental practices like any business can benefit from the insights that Power BI can bring if you can overcome the data difficulties such as joining data from multiple sources including your major dental software packages such as Exact, Dental4Windows, Practiceworks and Dentrix.   PowerBI reports and dashboards can help you focus in on the areas of your practice that need corrective attention and can be a part of your management team’s day to day focus.

Fill gaps in dental systems

With core dental systems that dental practices use (#5 below) such as Exact, Dental4Windows, Practiceworks and Dentrix, whilst they might be doing a good job with patient records, appointments, over the counter transactions and X-Rays, clinical charting etc, it was my experience at the time that they didn’t do much for dental practice owners.  Maybe they are getting better I am not sure, but their development seemed to be driven by features for dental assistants and dental assistant practice managers more than for dental practice owners. 

Apart from reporting our dental software didn’t help us with stock management, rostering, accounting or controlling what we called day end close activities.  Why are so many patient amounts not being collected on the day and going into debtors?   Why did we see patients and no invoice even being raised let alone receiving payment?  I developed a Day End Close exception reporting process to highlight these types of issues.  I also developed a Stock Management system to help with more efficient and price effective ordering as well as a roster system to efficiently match staff with changing fortnightly needs and also help with payroll checking because being up or down on a rostered time is an important business control for your labour costs and helps minimise overtime.

Key Dental Processes Excel or Power BI can help with:

  1. KPI Reporting / Dashboards – Marry together data from various sources within Excel or Excel’s data model using Power Query and 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 insigts as well as easier collaboration this can also be done in Microsoft’s new powerful Power BI application.
  2. Exception reporting – this is an Excel based tool of my own that is built to ‘look’ at your daily transactional data so you can identify the exceptions that need your attention on a daily basis e.g. who didn’t get invoiced, who didn’t pay on the day, is your system in sync with your Dental software, Hicaps and banking.
  3. Rostering tool/form and process – Have a sound rostering tool and process ideally matching needs ie dentists hours with assistant and front desk staffing and be able to match the 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 you have control over what is your 2nd or 3rd largest area of expenditure.  My roster approach was uncommon – my application has the user rostering support staff against the income producing providers, so that the support staff cost is utilised wisely.  I acknowledge there are now apps to use for rostering that mean Excel’s role in this area has been overtaken somewhat.
  4. Business Model – As already mentioned above whether you call it a Forecast, Budget, The Plan or whatever, you need an Excel business model of your business that is high level and contains all your facts, understandings, assumptions, presumptions so you effectively force yourself to understand the drivers of your business.   See the example in the video above
  5. Gaps in Dental Software / Time saving functionality – As already mentioned above think of areas that are not done or done well by your core dental software that are labour intensive and expensive to maintain.  Maybe it’s getting data into your accounting system, or stock ordering, controlling lab bills and recovery from your dentists.  What ever it may be. 

Other Dental Practice related examples which are also on my Excel Solutions page listed below…