With its unique abilities and affordability, Monarch software is well placed to assist those working in the healthcare industry, an area in which I must say that I don’t have much firsthand experience. But while information is information, and data is data, and I hope that much of what is available here is both useful and applicable, there’s a certain something about having direct involvement in an industry.
On that note, I’m very pleased to welcome Mr. Joe Berry as a guest contributor! Joe’s entire career has been spent in the business offices of hospitals, and he now shares his knowledge and expertise by providing consulting services to his hospital clients.
Please join me in welcoming Joe to the site, and enjoy his story, which I’ve titled…
Seeding Information Solutions for Healthcare
This week it has been unseasonably warm – around 65 degrees – and sunny in central Illinois. Even though it is a little early in the season, it makes me think of gardening.
Gardening is one of my favorite pastimes. I spend time each year planning the types and varieties of vegetables that I will grow. I decide where I will plant each variety and how much seed to purchase. The planning helps me make sure I have enough room for what I want to grow and helps me reduce the possibilities of disease by rotating my vegetables. Planning makes me a better gardener.
What does gardening have to do with excelling with Monarch? Like gardening, a good Monarch model/project should include a good plan. Planning not only helps us be better gardeners but also helps makes us become better at creating actionable data.
I have worked in hospital business offices for 35 years. During most of those years, I have produced reports to analyze data and improve the quality of processes. Currently I am an independent consultant helping hospitals get the data they need to make data based decisions.
I find the reporting capabilities of most healthcare information systems to be somewhat limited. They usually have many standard reports and a simple Ad Hoc report writer, but few have the ability to provide in-depth analysis, dashboard, or flash reports.
Excel and Monarch work well in healthcare. Alone, each is very powerful, but together they offer a vast array of solutions that do not require the assistance of IT professionals.
Define the requirement
I was recently consulting for rural hospital. The Chief Financial Officer wanted a way to summarize their payroll data – a one page flash report for each department. The data would be stratified by pay code with totals by pay period and year-to-date. He also wanted the flexibility to be able to add budget totals in the future. His request was to have the output in Excel using one workbook per pay period. Each department would be reported on its own sheet inside of that workbook.
The CFO shared a copy of the spreadsheet he had created as an example of the final report. This really helps me to get a visual of what the end result should look like – an expectation from the user. This jump-started my planning and gave me a map to follow. This is a huge help and a time saver.
From my review I noticed that the formatting of the report was much more than basic and included bolded text, underlining, line drawing, and inverse printing. At this point, I had no idea how I was going to replicate the formatting, but I knew that Monarch and Excel were up to the task of gathering, manipulating, stratifying, and reporting the data.
Determine the data sources
We spent some time discussing what source data he used for his report. He told me that he had obtained the data from several different reports, from two staff members. He gave me the details and reasoning for his formulas, and explained that his sample report had taken over ten man hours to produce. While his sample report was for only one department, his hope was that automation could separate the data by department and make it cost effective to produce this valuable report.
Since I had very little experience with the payroll data from this hospital’s information system, I needed to browse the manual, talk to users, and get to know the system in order to establish what data was available. Determining the best way to gather data is part of a good plan. What reports or data sources would contain the data needed and what calculations should be used? What filters were needed? Were any summaries going to be used and what was going to be exported?
Set the schedule
Another part of a good plan is a timeline. The CFO was very flexible with this. As part of my plan I would need to determine what was realistic and build in a couple of emergency buffers. My goal is to always exceed the expectation, so a realistic goal with room for emergencies is vital to maintain that goal.
Gardening requires many steps in the plan. For example a gardener must prepare the soil, fertilize, sow the seeds, cultivate, and harvest. With this report, it was apparent that multiple models/projects would be required to get the data in the format desired with the desired totals and stratifications.
Document, document, document
After talking to different staff members and reading a few pertinent pages in the manual, I was ready to start documenting the plan. I determined that it was going to be easier to create the year-to-date totals as the year progressed instead of running multiple reports to get that data on an ad hoc basis. Interestingly, the hospital’s information system did not have a single report with totals for hours or dollars by pay code.
Indentifying each step and along with what needed to be accomplished, I determined the need for eight different models/projects. I usually create a project with each model as it makes it easier to add exports later if needed.
I also separate complex tasks into smaller, easier to manage models. Since most of what I create will be used by others, this practice helps with subsequent revisions and improvements as they arise.
Build a process
The department name was not available through the Ad Hoc Report Writer, but it was part of the sample report. Getting it was an easy fix. First, create a simple model (Model1) that would trap the department number and the department name from a standard report. Now export those two data elements to Excel (Payroll1.xls) and use that file as the source for an external lookup.
Another bit of missing data was a pay period calendar: a file that has the calendar year, the beginning date and the ending date of each pay period along with the pay period number. Again, there was a simple solution. First, create a model (Model2) to trap the needed data from a standard report, and then export the data to Excel (Payroll2.xls) and use it as an external lookup data source. This file would only need to be created once per year as the pay period calendar never changes during the year.
Two different Ad Hoc reports from the hospital information system would be needed. Each report was for a single pay period. One would contain hours and dollars by pay code for non-productive hours; such as, vacation and jury duty. The other would contain hours and dollars for productive hours; such as, regular and overtime. Unfortunately, the system had a limit on the number of bytes of data that could be extracted in one report; therefore two reports needed to be used.
The data elements, pay period and calendar year, were available as filters in the ad hoc reporting system, but were not available to export from that system. Two calculated fields, defined as runtime parameters, were added to the next two models to capture these two fields for the final report.
The Monarch model built to process the Excel file created for the non-productive hours and dollars is named Model3. It used Payroll1.xls as an external lookup to get the department name field. It would format the data and rename the fields so that they could be more easily understood. This practice is especially helpful when others will be using your models and projects. The next model (Model4) would process the Excel file for the productive hours and do much the same with that data.
Both models required a filter to exclude the records that had zero hours and dollars since the ad hoc report writer had no way of excluding those. Both would export their in Excel format. For simplicity’s sake, the output for Model3 was named as Payroll3.xls and the output for the Model4 as Payroll4.xls.
The next model (Model5) would open the database file Payroll3.xls and use Payroll4.xls as an external lookup file. To make sure we were combining like data, the link was on department number, pay period, and calendar year. This would allow the entire dataset of pay period data to be combined into one file for easier processing. The export would be to Excel – Payroll3.xls.
The next model (Model6) would open the database file Payroll3.xls and use Payroll4.xls as an external lookup file. To make sure we were combining like data, the link was on department number, pay period, and calendar year. This would allow the calendar year data to be combined into one file and create a summary for the current pay period totals using department as the key field.
The export would be to an existing Excel file named Payroll6.xls and the summary data would be appended to existing data. This would be the file used for year-to-date totals and would contain a single record by department for each pay period.
Yes, model5 and model6 could have been combined into a single model/project with two exports. When I completed the original plan, keeping pay period and year-to-date data separated would facilitate any possible troubleshooting. In retrospect it wasn’t necessary, but this doesn’t create unwanted effects and adds minimal time to the process.
The next model (Model7) would open Payroll6.xls and create a summary of year-to-date data for the entire calendar year. It exports to Excel – Payroll7.xls. The exported data has a single record by department containing year-to-date totals for all pay codes.
The final model (Model8) would open Payroll5.xls which is the pay period specific data. It uses Payroll7.xls, the year-to-date data by department, as an external lookup. It also uses Payroll2.xls, the pay period calendar fields, as an external lookup. This model gathers only the data that will be used in the sample report, but it will contain all of the departments and creates a summary for the entire organization by pay period. This export would be named Payroll8.xls.
Produce the best presentation, easily
So far, success has been achieved. However, I hadn’t researched the “pretty” formatting in Excel quite yet. I toyed with the idea of going back to the CFO and explaining I could get his export in Word using a Mail Merge process and have the data populate a pretty Word form, but decided to seek an Excel-based solution first.
It was time to do an Internet search and see if any add-ins for Excel that might work. On the first search, I saw the words “mail merge entirely in Excel”. I’d found an add-in that fit the task perfectly, fittingly named “Mail Merge for Excel”. It takes a row of data per merge, which was exactly what was needed. They even allow you to try before you buy, so I downloaded the software.
As I started to learn the add-in, I found that a shell or a blank form of the final report would need to be created. That would be simple since the sample was supplied to me by the CFO. I decided that I would use the shell as a sheet in the Payroll8.xls file. After testing and making a minor change to Model5 to export a new calculated field – one that would trigger the mail merge – I was ready to purchase the add-in. Costing about $36 USD, this excellent tool is truly a bargain.
Automate for optimal execution
After testing the entire process from start to finish, running everything manually and making sure all of the numbers balanced, I began automating the process. While, the two reports from the hospital’s information system would still need to be created manually, the eight Monarch projects were automated using a command file. For this task I used the handy tool, ExcelWithMonarch Monarch Batch File Generator, found on this website.
The mail merge portion was left as a manual process.
I was almost ready to present the final version to the CFO, but first I had to prepare the final documentation of the Monarch models/projects and write a “how to” document to instruct the staff that would use these processes to create the reports. I do this for almost all of the processes that I supply to my customers as it helps them when they have staff changes and it reduces the number of support calls that I receive. The documentation also benefits me, at it makes it very easy to quickly help if I am called upon to troubleshoot or revise the process.
Delivering the solution
When I presented this to the CFO, he was pleased with the final product. His only question was how long it took to create for each pay period. Since we were already on pay period three when I was asked for the report, four pay periods needed to be processed when it was finished. That gave a great opportunity to provide a solid number based using the report multiple times. The final answer was “less than 30 minutes”.
Since the CFO was pleased with both the final report and the time required to produce the data, I considered this project a success.
Like gardening, once in a while you find a way to improve your skills. In gardening it may be a different variety or a better fertilizer. In creating actionable data it may well be a tool that you have never used. Indeed, I found an additional tool for my toolkit to continue Excelling with Monarch.
Thanks Joe!
With 35 years worth of experience in the healthcare sector, Joe Berry can help you to diagnose and cure your information challenges. Contact him and learn more by visiting www.bbshc.com.
Joe has a special reason for his continuing interest in healthcare: “I am just as passionate about education and awareness for organ donation and transplantation as I am about Monarch. I received the Gift of Life on October 17, 2007 and will spend the rest of my life helping save more lives.”
As Joe wrote, by giving your work good strong roots as a result of planning, documenting, and organizing projects into easily manageable parts, you’ll be certain that you and your business will grow, succeed, and even excel, with Monarch.
