Excel for Dental Practices
That’s me hiding on the right! As mentioned elsewhere in my site, I come from a resource sector large business background, but I also understand small business and Dental Practices having owned 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 which we sold in early 2016. For that matter, I suspect other medical practices will also have similar issues that I may have worked with in the dental industry such as matching payments from Medicare or Veteran Affairs to outstanding amounts. Anyway, being a Dental Practice Owner was great experience for me from a financial modeling and Excel application viewpoint implementing many Excel solutions for the dental business and in many way rounded out my big corporate experience because:
- of gaps in core dental systems – these just had to be filled by good Excel applications
- users were mostly not Excel proficient – this taught me how to make Excel tools and applications more automated (often using VBA macros) and more robust for less sophisticated users
- dental practices tend to be high volume transactional businesses – we needed to look for ways to automate tedious tasks to leave our staff freer to focus on practice flow and scheduling patients.
On the core dental systems that dental practices use 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.
The purpose of this page is to show you I have considerable experience in advanced Excel Solutions for dental practices. I also did a major project for UniQuest for another medical related businesses. I don’t have any off the shelf solutions as much depends on the availability of the raw data itself and the scale of your business, but do I have a head start on your project from past work I have done.
As a general guide I do think having the following implemented would be good practice worth considering:
- Reporting database – Marry together data from various sources to create an Excel based reporting database so that a KPI dashboard style routine report can be easily generated so you can see what’s happening in your busy practice and focus on actions to continually improve your business.
- Exception reporting – this is a tool that can ‘look’ at your daily transactional data so you can identify the exceptions that need your attention 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 – whether you call it a 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.
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 functionality – 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