The Excel Dashboard that placed second best in the MicroCharts 2008 Excel Dashboard Competition was created by Jim Uden. His Outpatient Surgery Center Dashboard shows the metrics for the operations of a health care facility. Owned by a partnership that demands comprehensive reporting, the ambulatory center operations generate large volumes of data that must be digested and presented in a clear and concise manner.
We’ll examine the Excel dashboard itself in a moment, but I wanted to point out this dashboard received a special mention on account of the great documentation and project background that the developer provided with the dashboard. Take a look through this Dashboard Walk-through Document and you’ll be impressed as well. A nice document to emulate in our own dashboard projects, for sure.
Here is the screenshot of the Second Best Excel Dashboard of 2008. It’s a large screenshot, so please click on the image to bring it into full view.
We’ll pull some backgound material from that great project document that I mentioned earlier, so please join me in reading the rest of this post by clicking on the “read more” link below.
This dashboard was built for a company that owns and operates ambulatory (outpatient) surgery centers in partnerships with surgeons. We currently have a portfolio of 9 facilities that are located all over the US. Typically, we will own a majority interest (around 55%) of the individual business and the practicing surgeons will own the rest. A typical partnership includes our company as well as 10-12 physician partners. Each partnership is a separate business entity.
Ambulatory surgical centers, or ASCs, are facilities where surgeries that do not require a hospital admission are performed. Patients who elect to have surgery in an ASC arrive on the day of the procedure, have the surgery in a fully equipped operating room and recover under the care of the facility’s nursing staff, all without hospital admission.
ASCs have become the preferred venue for outpatient surgery for Physicians, Patients, and Payors. Reasons why include:
- Enhanced efficiency
- Increased income from distributions
- Greater influence over clinical decisions and management
- Non-Institutional environment
- Friendly & convenient
- Significantly lower cost structure
- Enables profitability at much lower costs to patients, insurance companies and government programs
For example, Medicare pays an ASC 65% of what it would pay a hospital for the same surgery
The dashboard relies on data that is pulled from each center’s billing system as well as our corporate accounting platform. This data is updated automatically and the dashboard utilizes drop downs to choose a particular facility to view. Due to the time required to remove all of the sensitive data, I have unlinked the model and I am using data from only 1 facility. In addition, I am using fictitious names. By removing the dynamic capability of the model, I have limited its power but the report should provide a good example of how the information is displayed.
Objective and Audience:
The goal of this report was to provide a one page snapshot that can be reviewed and presented to each individual partnership to assess the operations of the business. This business is driven by physician case volume and that is why it is so prominent in the report. Understanding and analyzing individual physician volume trends is crucial. Due to government regulations, cash generated by the business is distributed to the owners (including the physicians) based on ownership, not on contribution.
A report is generated for each facility and then presented to each partnership individually. The audience includes the physician partners who are not familiar with financial analysis. Given this obstacle, the visualization of data is crucial to their comprehension. As a result, very little financial information is displayed. However, the business is driven by physician volume and by listing the production by partner, I am able to highlight the contributors as well as those who are not meeting expectations. This creates a competitive environment among the surgeons and serves as an excellent motivator for the partnership.
The next piece of this project involves creating a consolidated dashboard that summarizes each of the partnership reports in a 1 page corporate view.
Here are some explanations of the various dashboard charts:
And here is the physician ranking scorecard:
If you haven’t seen the Excel dashboard contest winner, it was an International Banking Dashboard.
Tags: Excel Dashboard for Medical Practice Partnerships, Microcharts Dashboard, Excel Dashboard Contest