Information systems are key to helping one hospital develop a stress test to successfully monitor its supply chain operations
By Gary McMann, Director Supply Chain Operations, Billings Clinic, and Steve Smith, Informatics Analyst in Supply Chain Operations, Billings Clinic
Supply Chain Operations (SCO) impacts every area within a hospital setting. Therefore, it is imperative that leaders keep a close eye on its pulse to ensure all systems are healthy and ready to meet the day-to-day challenges of the organization. Periodically missing a beat may or may not be a serious event depending upon the situation. As is the case with human-related maladies, early detection of systemic malfunctions typically results in greatly enhanced chances for best possible outcomes. Early detection begins with an understanding of the warning signs that trigger a closer look.
Overall supply chain fitness can be viewed similarly to the way a caregiver might initially assess patient fitness. The observation that a patient is breathing is indeed helpful but is usually not indicative of overall fitness. Determining the patient’s functionality of internal bodily systems is critical to understanding the quality of each breath they take. In other words, it’s always best to conduct a quick evaluation of key bodily functions (pulse rate, blood pressure, heart rate, etc.) to know that all systems are go. The categorical supply chain systems we have identified as being critical include those related to finance, logistics and personnel. Each of these systems has an impact on the next, so if the rhythm is off on one, it can and will place stress on the others.
The three broad areas of finance, logistics and personnel will only come to life when tied back to more detailed specifics. In our case, we decided to begin by limiting monitoring activities to three sub areas within each of the three major categories.
Finance
- Supply expense to adjusted patient day.
- Supply expense to operating expense.
- Supply expense to revenue.
Logistics
- Inventory turn rate.
- Open purchase orders.
- Warehouse slow movers.
Personnel
- Staff turn rate.
- Paid Time Off (PTO).
- On-time evaluation rate.
In each of the subcategories we could analyze data as we drilled down to root cause determinations. For example, if the ratio of supply expense to adjusted patient day had a significant variance, we could consider factors such as fluctuations in patient volume, patient severity, patient mix, etc.
Understanding the database systems
When considering a complex project such as this, the initial step was to actually understand the database systems involved in the process. How do they interact … or do they? What is contained in the database? Is it clean? Is the dataset complete? We mapped the SCO database in our materials management information system (MMIS) as well as the accounting database to better understand what we had to work with. Then we moved into the actual cleansing phase.
Though on-going, there were certain aspects of data cleansing that had to be accomplished prior to project implementation. Naming policies were created and then funneled through one person, giving limits and controls on the names entering the system. This also helped to bring like items together with an alpha sort. We also expanded the number of data fields to enhance the search and report writing capability. ABC product classifications were incorporated for all items in the system. In addition, United Nations Standard Products and Services Code(s) (UNSPSC) were loaded to help categorize items from various vendors/manufacturers. Lastly, our purchasing and accounts payable staff worked in concert to clear up outstanding purchase orders and associated invoices. All of this up-front effort helped ensure that we were starting with accurate, comprehensive and up-to-date information.
Data mining
Now that the data foundation was firmly in place, we could begin data mining. Whenever possible, we set up our queries in an automated format to facilitate real-time data inquiries. Most selection criteria were set up to be function-based in support of the automatic update operation. Queries for the operational stress test extracted data from our Item Master, Location Master and Purchase Order (PO) Header File. For the personnel test we took queries from the human resources database. We also added tracking that could be entered manually. For accounting our main queries we generated from the general ledger (GL) data files.
Graphical display of information was used to depict measurable limits. Both historical and national best practice ranges were established to give managers target information. Development of combination control charts and line graphs (chart 1, above) were deployed to facilitate easier visualization of control limits. The speedometer graphical display (chart 2, page 16) was also used to help accommodate quick comprehensive visualization of data. Also note that the color combinations on charts remained consistent, with green representing an acceptable range, yellow cautionary and red unacceptable.
Reporting and presentation played an integral role in bringing stress test results to life. Graphical display of information allowed for customization and could help the viewer quickly interpret data, as well as direct him or her to potential areas requiring closer evaluation. The ability to customize and quickly summarize data makes charts ideal for reporting.
Within the spreadsheet functions, macros and proper layouts allowed us to simplify updates and navigate through a relatively complex collection of data. A function is a small program written in the Visual Basic programming language that expands Excel functionality. A macro is either a program already included in Excel that is referenced by entering an “=” sign in the cell, or a sequence of repeat steps, such as copy and paste, that is memorized by Excel and used when needed.
Excel functions were used to create navigation and advanced calculations. Functions were also attached to graphs to link them back to other pages. Another function calculated the Paid Time Off (PTO) accrual, eliminating the need to gather the data manually.
The Excel database macros were critical to “operationalize” our stress test functions. Because the macros make Excel treat any array of cells like a database, we were able to easily fill in a spreadsheet with a set of data shared with several metrics. By using functions, macros and thinking through the layout of the program, we came away with an easy-to-update system that presents valuable information quickly and concisely.
Preferred Web-based reporting
While advanced spreadsheet functionality helped us develop our project, we are moving toward a preferred Web-based reporting software. The spreadsheet version was not truly a real-time system, and it requires involvement from various other departmental systems. The primary departments from which we obtain information for our stress test included Human Resources (HR), Accounting, and Information Systems (IS).
Our IS department has a system drive that is accessible throughout the computer network, so when our spreadsheet solutions were used, the program was stored on our shared drive.
In the end, our optimal system was visual and easy to access, navigate and update. The programs were browser-based and in spreadsheets. Our stress points were visible and measurable, and they facilitated drill-down of data to accurately analyze warning signs that could signal potential major supply system failures.
Billings Clinic continues to benefit from the notion that system stresses are opportunities for learning, as well as for prevention. We have come to realize that some stress is a good thing in that it leads to prevention of potential major systemic failure when discovered early in the game. Thoughtful and systematic supply chain stress testing can and will lead to critical action steps in support of a healthy bottom line.
Gary McMann is Director Supply Chain Operations at Billings Clinic. He has a BS in Radiologic Technology from Midwestern State University and an MBA from Golden Gate University. McMann completed a one-year Ambulatory Services Fellowship through the Air Force Surgeon General’s Office. He is a Fellow in the American College of Healthcare Executives and retired from active duty Air Force Medical Service Corps as a Lt. Colonel. He has over 37 years experience in healthcare, ranging from hands-on clinical patient care to various management, leadership and executive roles in healthcare delivery systems, hospitals and full service ambulatory care centers.
Steve Smith is an Informatics Analyst in Supply Chain operations at Billings Clinic. He has an MBA from Washington State University, where he worked in development and deployment of several online tools and studied extensively in database management and computer programming. He is also a Lean Six Sigma Green Belt. Smith has worked at Billings Clinic as an informatics analyst for more than two years, applying his knowledge and skills to the Med Series 4 and AS400 database.