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), 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.
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.
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…
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.
- 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.
- 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.
- 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 is uncommon – my application has the user rostering support staff against the income producing providers, so that the support staff cost is utilised wisely.
- 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
It’s like the process of having one is more important that the final result! But when you have one, you have a yardstick to compare with your accounting results, a plan to show your bank, partners and stakeholders and a base case against expansion plans e.g should I add on the 5th dental chair.
- 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.
Excel can be the perfect solution to these problems. I have listed examples below but a quick examples first – it used to take me 5 or more tedious hours to enter a quarter’s revenue entries to MYOB with the detail needed for dentist, type of payment source i.e. Cash, Cheque, HiCaps, EFTPOS or Medicare/Veteran Affairs. I set up a Cashbook spreadsheet that our staff could fill in daily which had checks and balances using error flags and conditional formatting to ensure it’s accuracy and then elsewhere in this workbook I set up the MYOB format and a way to automatically bring in the data I needed to upload. Once done it then took me 30 seconds to upload my quarter’s accounting data. That’s a beautiful thing.
- Dashboard for KPIs
- Upload from Excel to your Accounting Packages such as MYOB
- Daily Transactions Audit
- Stock Management
- Staff Anniversary and Birthday schedule for the noticeboard
- Lab Log (or register) showing costs and fees of lab related work
- Payroll hours entry sheet with analysis checks against Roster
- Payroll processing entry sheet
- Automated staff, supplier & emergency contact list
- Performance Appraisals
- Pivot Table driven reporting