I can help large, medium or small businesses build capability quickly using advanced Excel & VBA programming skills and implement Excel Solutions for common business problems. I enjoy writing Excel based applications or Excel solutions and financial modelling for any area of business or industry drawing on my long finance /marketing career experience in Resources. I am very interested in the exciting new field of Business Intelligence & Power Pivot projects.
I have a special interest in assisting Dental or Medical and Veterinary Practices having had direct experience in owning a dental practice and understanding what a practice owner needs. Since 2005, I have worked on many projects that facilitated management of practices effectively and more profitably by cutting costs on automating repetitive tasks and producing the right reports at the right time.
Let’s look at some practical Excel Solutions (or Excel Applications) I have implemented using advanced Excel in different business settings. Note due to client confidentiality reasons I am a bit hamstrung on what I can show you so some images are small and redacted…
- Dashboard for KPIs
- Upload from Excel to your Accounting Packages such as MYOB
- Mine Database
- 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
There is a lot of information out there and whole books on writing dashboard style KPI reporting. Basically this is because managers want to see the overview so they can then focus on the significant areas needing their attention. It facilitates management by exception. In a lot of cases a manager is more interested in the trend rather than the absolute values so these graphs can be shrunk down and more can be reported on one page. Colour and symbols can be added for further clarity. In Excel 2010 onwards you can Insert a Sparklines graph. Great stuff. I recommend Chandoo’s site (click here) if you want to see what’s possible.
On one project (above) I developed an interactive dashboard for Health & Safety statistics for a multi site construction company with conditional formatting to highlight positive or negative Target versus Actual results.
In our dental practice, through our own understanding of our business and also from book and seminars we decided on approximately on a few graphs to track monthly business and staff performance.
Our practice dashboard that I created was a workbook with numerous worksheets to hold the raw data to be updated monthly then a tabular report to bring the data in to one place using advanced formulas like SUMIF, SUMPRODUCT and INDEX/MATCH. I created simple easy to read graphs that could be moved around the page as needed. These graphs are interactive so that KPIs for different dentists or can be easily viewed. Just about anything is possible in this area.
Go Back to Top of Page
Automate upload of data to an Accounting Package e.g. MYOB journals & eliminate tedious keying
I used to do MYOB accounting entries for our practice but this relates to Quickbooks and Xero or any accounting package that imports data within it’s controls. The sales data had to be split by dentist on the P & L side of the entry and then by payment type on the Balance Sheet side. About 8 lines of entry per working day. At first, I did this task quarterly, so it was about 8 lines x 20 work days per month x 3 months = approx 500 lines in one session of entry! This took about 5 hours of very tedious work that had to be highly accurate. That’s no good for me, I hate repetitive work.
So I built an Excel cash book style entry sheet where our staff could easily understand and enter the day’s figures, one line per day with columns for the details of dentist and payment type.
I then built a hidden sheet with advanced formulas (INDEX) to pull in this data to a strict MYOB dictated and unfriendly format that MYOB would import.
The solution also validates the data checking for things like unbalanced entries or data entered for a weekend not a work day – this could be expanded for any error that might concern you. Then like magic a 5 hour job became 30 seconds of upload. Fantastic, not only saves time but saved me from some really, boring, un-enjoyable work.
Mine Database Example
A client from a large mining company had hundreds of legacy spreadsheets containing detailed global competitor data related to mining operations. They needed this unwieldy data combined into one simple database so they could use the power of Excel’s filtering and advanced filtering to efficiently access data and produce graphs and reports. So the Excel solution here was a huge complex task that required a VBA to do the hard consolidation work and then advanced formulas to bring in forecast data from other sources matched to each mine. The main worksheet ended up being 50K lines!
Daily Transactions Audit
The dental software used in the practice didn’t tell us which patients hadn’t paid for their dental work on the day. An owner needs to know that. Nor did it tell us who came in and paid for a previous day’s work done so we could ensure this is receipted correctly. And also it also didn’t tell us who came for an appointment but the dentist missed raising a treatment code which communicates to our front desk staff exactly what to invoice the patient. A practice owner needs to know this as in the worst case staff might be not charging certain patients, in the best case they are forgetting to charge out. Basically our dental software didn’t give us exception reporting which is the smartest way for any business to have control over it’s transactional processes.
I built the functionality we needed into a tool I have called Front Desk Close so we could do the basic checks plus more at the end of each day. Staff could then take corrective action whilst the issue was fresh in everyone’s minds including the patient’s. This tool works from data exported from our dental system, manipulates the data, matches and categorises transactions and makes decisions on what are exceptions using advanced formulas including array formulas and VBA. Businesses need exception reporting for all important areas of their business and Excel can do this from your base data or reports tailored to your need. You can design any exception you like that’s important to you and have VBA robotically do the checking & exception reporting work.
We found rostering to be a very, challenging, time consuming task for our practice of 14 or so staff when we did it manually or in basic spreadsheets. It was hard to know who was available at any given time with all the commitments staff have day to day such as kid’s cricket practice or ballet lessons. We needed a tool where we could set up non availability of staff and then roster only available staff and also have checks during this process such as:
- we don’t roster shifts that are too long or without meal breaks
- staff don’t exceed maximum hours they can work
- staff receive the minimum hours they want where we can
- we don’t over roster and match dental assistants hours with dentist’s working hours
- we could efficiently print our rosters and distribute or email to staff
- the roster data produced could dovetails with another application where actual hours are entered and basic payroll calculations made – this provides us the ability to see actual vs rostered hours for supervising staff and minimises payroll errors
- Our new Excel roster means this fortnightly task is down to a 1 hour job, is more satisfying and we can react to changing circumstances such as a staff member needing emergency time off etc.
[Stock management showing stock on hand, stock minimum level based on number of patients or procedures and automated request for quote, price entry and ordering] At our practice we had quite a long list of very particular dental supplies we need on hand to cover the many dental procedures we need to be ready to do on a day to day basis. The last thing you want in any business is running out of the stock you need to deliver the service or product you provide. In dental practices this is a high opportunity cost of missed work as a busy practice is paying for staff to be there and patients are too busy to come back later because you don’t have your materials. So I created what I called Stock Manager which:
- defines our standard stock items, allows us to enter our stock count
- helps us calculate what level of stock we should have on hand based on a sensible assumptions
- flags which items need re-ordering
- produces a Request for Quote form for suppliers
- automatically import supplier prices and tells us who had the lowest price
- produces a Order Form we can email to the suppliers chosen for our next round of ordering.
Basically this application has become an tool as well as an efficient procedure to follow that saves time and gives us reasonable control for our stock ordering process.
The benefits are:
- Minimises opportunity cost of lost business because of stock outs
- Saves about 1 day’s time per month for 1 staff member not having to react to stock shortages and placing emergency orders
- a further 1-2 days every 3 months for one staff member because it so much more efficient in preparing our major 3 monthly order
- it is an efficient means of comparing supplier prices quoted
- our staff who manage stock simply enjoy their job more
Staff Anniversaries & Birthdays
We found it hard to keep track of anniversaries & birthdays and downloading from our MYOB payroll system always Americanised the date. It’s embarrassing when you get it wrong. So I developed a simple but important little tool that gives you a noticeboard style Schedule of these dates. Over coffee we could see who had a special date coming up that we needed celebrating. We wanted this to be instantly updatable for new staff as well.
In a dental practice you need to keep control and have visibility over your external laboratory work. They do the specialised jobs like make dentures, crowns and bridges that are expensive. You need to see the Gross Profit you are making on this work, check the patient has been charged for the work and check a dentist isn’t getting work re-done because of his own mistakes that is costing you your profit margin. I came up with what I have called Lab Log which allows easy data entry of the lab bills and the other information that gives you control and visibility over this area. Additionally the data can be re-used to save data entry in other tasks
- at payroll time when you pay the dentists and have to detail the lab charges charged to them and
- when paying the bills in MYOB you can populate an Excel form that is uploaded to MYOB’s disbursement transaction you can then use to pay the Lab’s monthly statement.
Payroll hours entry sheet with analysis checks against Roster
At our business staff had an Excel sheet to enter their hours of work and lunch break. However this entry sheet also had their rostered hours showing by day brought in by VBA from the roster, so that the variance is known at time of entry and a section for explanation of being over or under roster. This entry sheet also had conditional formatting so that if the time of starting is such that a lunch break should be there and it’s not then it indicates red to force a check. This sheet also highlights overtime where hours worked are greater than 10 hours.
Basically this entry sheet not only is a tool for collecting hours for the payroll process but also acts as a control on this process so staff know that variances have to be explained and also we don’t miss paying someone who was rostered. This solution had another practical feature given it was a workbook that about 12 people had to use on a daily basis so there was a danger someone would open a copy of the workbook if the original was opened somewhere else – some event code ensured if a copy was opened then a message told the user this would close and to find the original.
Payroll processing entry sheet
We use the accounting package MYOB to process our payroll. So how can Excel help in this process? After the above Payroll hours entry sheet is finalised ready for payroll, a worksheet uses this information to split a payroll hours into a form required by MYOB which aids in accuracy and speed of entry. How many times have I done a payroll from a kitchen table overseas or on holidays and having the spreadsheet in the format of entry makes the payroll process quick and less error prone. Other accounting packages like QuickBooks or Xero will require modified format but the same principle applies.
Automated staff, supplier & emergency contact list
In the past, key contact information was not efficiently captured in one place. Staff had phone numbers important for their jobs in spreadsheets or even worse Word documents and this caused havoc when they were away. We now have one workbook to capture all key contacts related to the practice and this is accessible by all staff and maintained by all staff. This workbook uses VBA to toggle and autofilter that makes searching for a contact quick and easy. VBA is also used to produce handy small prints of Staff contacts and where contacts are marked as emergency, these contacts also appear in a small handy printout to be placed near phones and in wallets and purses. This is a simple but very necessary application. It has protection to ensure there is no accidental overwriting of important formulas.
My wife is a big believer in 360 deg Performance Appraisals for healthier work environments. But 14 or so people filling out forms on each other and answering say 20 or so work performance questions means to create a summary of the results is quite an exercise. So I developed a solution involving a Part A template questions about each team member and a Part B for writing the Strengths and Areas for Improvement for key work areas about each team member. Using VBA, the workbook first creates in a specified folder copies of the template forms and filenames of cities which are password protected. Later staff drew these names ‘from a hat’ with the password so they knew which workbook to open and complete all at the same time. VBA then consolidated results from all the workbooks in the folder creating a tab for each staff members Part A and Part B results.
This meant each team member could see on one page what others thought about their work performance and on anther page what each team member saw as their strengths and areas for improvement. This was a profoundly important tool.
Pivot Table Reports
I can’t emphasise enough how valuable and important Pivot Tables are to my work these days and anyone working with data and reporting. Why are they so powerful? They are fast for visualising data and dynamic and interactive. Once I hated Pivot Tables as I found in my chaotic, corporate world with little time to play around with them they were frustrating and would revert to using complex, sumifs or sumproduct formulas. But all the Pivot Table wants is for you to feed it true data in columns & rows and remember to refresh it when needed and it will reward you with instant solutions to problems. If you get in the habit of arranging your data in a database like format with individual transactions going down the page and unique simple one row headings above the data (if you need more detail for a column heading put it above the Pivot Table heading), you will find the Pivot Table can work for you miraculously and save the day again and again.
So my solution example here is I had to get data related to about 600 patients onto a Statement letter to go out to each of their referring doctors who may have referred between 1 and hundreds of those patients. At first I thought this might take me several few days to get these statements out as being dynamic reporting ie the number of rows will vary depending on the doctor’s volume of patients, and so will need some VBA. But I completed any missing data like addresses of referring doctors and set up a report format incorporating a pivot table that could filter by referring doctor and show patient details including their dentist. I was able to simply manually loop through each doctor’s name and produce and print a professional looking statement with their name and address and patient details for mail out within just a morning. If this weren’t a one off, I would have written some VBA to do the looping and printing. The Pivot Table is your friend!
If you want to learn about Pivot Tables I think one of the best places to start is with Debra Dalgleish’s www.contextures.com site. It’s a treasure trove of Pivot Table learnings as well as other Excel and VBA.
Contact me to discuss a project and make a start. I can also guide you on training and developing Excel skills over time. I am based in Casuarina NSW so close to the Gold Coast QLD and near Brisbane QLD, Australia.