Excel Solution Examples

This page has some practical Excel Solutions I have implemented using advanced Excel in different business settings. Due to confidentiality reasons, its harder than you might think to showcase a lot of my work and why I am showing images and parts are redacted.

Also some examples are quite old so with the advent of Power Query in particular I would do these tasks with less VBA and more Power Query for sure.

Slider Menus

Slider Menus can be a great way to organize macro buttons without taking up more than a few cells (even one if you widen a cell to house it). Using VBA animation techniques a menu can slide from the left or slide downwards. Basically the VBA is hiding the shape that contains the menu buttons in a fancy way.

Financial Modelling - Dental Practice Business Model

Financial Modelling or Business Models simulate a business in a spreadsheet. They are interactive so that you can see the effect of changing assumptions from more conservative to optimistic on company valuation and cash flows or in making decisions on business plans for boards, restructuring options, major new equipment and sales and acquisitions of business units.

During the Corona Virus Crisis people everyone would be running these types models to ensure they have enough cash and deciding between options.

Medium and Small business also should be using these kind of models for the same reasons albeit on a smaller and less complex scale. It helps business owners and management clarify their planning and is very valuable to show investors and banks to demonstrate their business plans and assumptions.

Business Models focus discussions on key assumptions and business drivers which can then be refined rather than trying to pull together an understanding from disjointed reports.

It has also been said the process of business modeling is almost as important as the final product as it teaches insights in what drives a business and what it is sensitive to and so what needs prioritizing.

To illustrate further here is a short video of a relatively simple interactive business model I developed for a dental practice which may generate ideas.

Financial Modelling Corporate Model

This was about my first the Excel Factor job over 10 years ago so today with what I know better and with Power Query for the data cleaning and enhancement, I would do this project in a much better way. But it is still a worthwhile real world practical solution of data cleaning, data consolidating and enhancing I hope.

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 global mining 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 plus provide the categorization for the reporting.

The main worksheet ended up being about 50K lines and in the days before Power Query each row had complex formulas, so hence the huge workbook size.

Dashboards

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 which is a good thing. 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 if you want to see what’s possible in Excel.

So these days a Dashboard can be in Excel or it can be a Report or Dashboard (these names mean different things in Power BI as is the first example below and the second being Excel.

Mine Database Example

This was about my first the Excel Factor job over 10 years ago so today with what I know better and with Power Query for the data cleaning and enhancement, I would do this project in a much better way. But it is still a worthwhile real world practical solution of data cleaning, data consolidating and enhancing I hope.

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 global mining 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 plus provide the categorization for the reporting.

The main worksheet ended up being about 50K lines and in the days before Power Query each row had complex formulas, so hence the huge workbook size.

Daily Transactions Audit

This example is one of my favorite solutions even though the output may not look that exciting.

The dental software used in our dental practice didn’t tell us which patients hadn’t paid for their dental work on the day.

An owner needs to know that, right. 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 and focus on the things that needed attention.

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.

Using mostly VBA as well as array formulas, this tool works from data exported from our dental system, manipulates the data, matches and categorizes transactions and makes decisions on what are exceptions .

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. I still VBA is the ultimate tool for this as it can virtually do whatever check you like on long data sets if you give it a little time and it can create a report of exceptions.

You can design any exception logic process you like that’s important to you and have VBA robotically do the checking & exception reporting work.

Stock Management & 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.

I used advanced formulas, VBA and User Forms in this tool.

Basically this application became a tool as well as an efficient procedure to follow that saves time and gave 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 enjoyed doing this job more

Upload of data to an Accounting Package like MYOB

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.

Lab Work Log

In a dental practice you need to keep control and have visibility over your external laboratory work.

They do the specialized 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.

Pivot Table based Reports

I can’t emphasize enough how valuable and important Pivot Tables are to my work these days and would be to anyone working with data and reporting. Why are they so powerful? They are fast for visualizing data and dynamic and interactive.

I must admit to earlier on hating 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. It’s like a working dog, it knows what to do, just feed it and give it work to do.

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 in my Pivot Table data source, 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 in a crisis I think after this incident!

If you want to learn about Pivot Tables I think one of the best places to start is with Debra Dalgleish’s site www.contextures.com site. It’s a treasure trove of Pivot Table learnings as well as other Excel and VBA.

Prepping Data for Payroll processing

For our former small business Dental Practice, we used the accounting package MYOB to process our payroll.

So how did Excel help me in this process? You can download the hours entry data from your on line time keeping like Deputy (at the time our time keeping was from our own Excel hours entry spreadsheet) and then convert this hours related data to a form required by MYOB or Xero which aids in accuracy and speed of entry.

How many times have I done a payroll from a kitchen table or hotel room whilst overseas or on holidays and having the spreadsheet in the format of payroll entry makes the payroll processing quick and less error prone

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 in their Outlook contacts, Google contacts etc. and in a hectic, busy small business, this caused havoc when they were away.

So I developed one workbook to capture all key Practice related contacts accessible to all staff and maintained by all staff.

This workbook used VBA to toggle and autofilter that makes searching for a contact quick and easy for basically non proficient Excel users.

VBA was 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 printouts to be placed near phones and in wallets and purses.

This was a simple ok perhaps old school, but very necessary application.

It had protection to ensure there is no accidental overwriting of important formulas.

Roster

This is an area of Excel that has been somewhat overtaken by some clever apps out there nowadays like Deputy that handle the time tracking and rostering to an extent.

But Excel may still be required if you have unique circumstances with your business and staffing.

Perhaps Excel needs to be used to take the output from your timesheet app and do something you need that it doesn’t do.

In our Dental Practice ownership 2005-2015 there were no apps available and 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 most importantly roster support staff to the businesses need which in the case of a dental practice, is to the planned hours for the income producing providers.

It also had checks during this process such as:

we didn’t roster shifts that are too long or without meal breaks
staff didn’t exceed maximum hours they could 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 they being the income earners
we could efficiently print our rosters and distribute or email to staff
the roster data produced could dovetail with another application where actual hours were entered and basic payroll calculations made – this provided us with the ability to see actual vs rostered hours for supervising staff, keeping overtime under control and minimizing payroll errors

Our Excel roster meant the fortnightly task went down to a 1 hour job, was more satisfying to do and we could react to changing circumstances such as a staff member needing emergency time off or being down in dentists etc.

So although there are apps to do much of the above these days, I leave the example here as a good example of how Excel based solutions can be used to solve business problems particular to your business.