Meet CGW: Our Laboratory inventory management system, its pitfalls and glories
Laboratory inventory management is a critical task, but still a neglected and overlooked in every laboratory. Managing the lab inventory properly can be significant in reducing costs and saving time, by increasing your lab efficiency and increase productivity.
As every startup lab, many of them, starts running their inventory on paper or classical spreadsheets, making it almost impossible to track the stock levels or material location. This can result in loss of expensive reagents, antibodies, cell lines due to the stock outs to execute a procedure or, even to expired dates.
At Genomika Diagnósticos, we also encountered the same problems in our inventory control. To gain control and keep track of everything, manage the stock levels, the orders and share the inventory quantities for each reagent with all the lab up to date, we needed to bring information technology to the problem.
In this post I will share our experiences with our stock management process, specially how we understood our lab inventory managers pain points at our multiple customer requirements meetings, which resulted on several flaws at our first attempt to solve the inventory management, and therefore, our final and current solution that helped the lab technician teams and managers work happily together.
Who are our customers ?
Before developing a solution for the a stock management, we needed to realize who were our clients and what were their pains when running day-by-day orders with stock ins and stock outs.
First goal: Keep track of the stock levels
At our earlier days of a startup lab, with low volume of exams, and a small team with overlaid responsibilities (i.e. lab technician is also a QC inspector, the Purchasing team is also responsible for the warehouse) it is usually normal to start your inventory based on a low cost solution.
We looked after some inventory control management systems at the market, but we didn't close a deal, specially because the systems at the time were excessively far away from the lab staff team (SAP systems for a local inventory control in a lab — quite expensive the customisation at time) or otherwise.
We chose to run our inventory on spreadsheets, focusing on the main actors at time: the lab tech and the purchasing/warehouse team. At the time, the stock out flows was controlled by a simple spreadsheet including the lab consumables used and their respective quantity removed from the inventory.
A simple solution, though as we started to grow up , more types of consumables and distributors were incoming, and our lab team has started to loose efficiency and productivity to track tock levels and keep log of all expiration dates of consumables at the inventory.
Furthermore, the financial team requested further information about our stock lab items such as lots, entry dates and average prices. The spreadsheet had suffered the overwhelming of columns.
New goal: Stock out orders, inventory current levels and quality control over the consumables
Our customers weren't happy anymore with spreadsheets, and our warehouse/purchasing and financial team came after our IT team to come with a solution to their inventory management process.
We conceived a solution that could help both teams : lab, financial and warehouse/purchasing staffs. We started to map the inventory management pipeline, which I resumed at this flowchart below:
At the time our purchasing and financial team started ahead by implanting a financial/fiscal system named JIVA, which had a basic system workflow for purchasing orders and financial invoices with distributors. Since the negotiation and purchasing were theoretically solved, we took our attention to the lab and quality team.
Our first MVP started with several design/development sprints based on continuous deliveries with our first operational MVP in almost two months after the first specs meetings.
It was developed in our web-base laboratory management system, named Gensoft as module available for the lab team.
A web-based lab inventory management system can help laboratories manage their inventory in a productive and cost-effective manner. The advantages of such a system over using paper are clear — You can gain control and keep track of everything in one place, manage your stock levels and new orders, create stock alerts, easily share inventory locations with all lab members- so everyone is up to date. The automation of the processes would benefit our team.
For a quick prototyping , we decided to develop all the features using the stack Django + Django Admin + Django Suit. Django Suit is alternative theme/skin for the Django admin app (administration interface) . How does it work ? I will explore this subject in another post when we explore more about our LMS Gensoft.
Our stock started to flaw…
Our inventory MVP ran at our lab for about two years, but as the features requests for new metadata's item to keep track were coming over time , the financial team needed to modify the business logic for the average prices of our products. This began to overflow our stock solution with several average prices calculated in multiple ways, which didn't match against the financial ERP systems at our inventory routines. It was a long day when the inventory days came.
We also had limitations with our interface, a choice for django-admin, whose search components were not quite defined, becoming difficult to find a lab product in the inventory.
The stock registering of consumables were also a painful task for the warehouse team, since there wasn't any nomenclature standardisation and a poor categorisation.
Time to refactoring!
In 2018, our lab Genomika Diagnósticos started a process improvement program organised by several cross-sector teams (teams from several areas working together) in order to select (define) a recurring problematic process. The basic idea was to analyse, explore, improve and measure it. We applied the Six Sigma program guided by our Black Belt CFO.
Several proposals for our stock management workflow were discussed by the team through the program with actionable plans:
- Data cleaning and normalisation of itens in order to help all the teams in removal of duplicates and misleading itens to improve the search and track of the itens in the inventory system.
- Definition of responsibilities and key dates for purchasing orders;
- Request for a new interface for our web-based inventory management system;
At the time we also changed our ERP from JIVA to OMIE. Our IT team discussed with the team and decided to refactor and remodel our database schema and improve the ERP integration by using RESTful APIs instead of uploading flat files. The integration with the ERP allowed us to reduce any manual stock entries which could result in an inventory bad registering again.
We named our system CGW (Clinical Genomics Workflows). The screenshots below presents some steps of our new inventory management system.
One of the main critical improvements at our system were the redesign of our frontend interface, by changing to Vue.JS + Vue Paper Bootstrap.
Vue (pronounced /vjuː/, like view) is a progressive framework for building user interfaces. Unlike other monolithic frameworks, Vue is designed from the ground up to be incrementally adoptable. The core library is focused on the view layer only, and is easy to pick up and integrate with other libraries or existing projects. On the other hand, Vue is also perfectly capable of powering sophisticated Single-Page Applications when used in combination with modern tooling and supporting libraries.
On Vue.JS, we split our backend and frontend framework. It made possible our backend platform (Django + Django Rest Framework) integrate with our frontend, this means that it was useful for developing new web applications as well as altering the pre-existing applications. This integration is possible because Vue.js has components for everything.
Discrete mathematics also helped us
Discrete mathematics also came to us to solve one of the problems of stock levels update routine at our database. The ERP only returned the current stock level at their database, so any updates at our system could lead to a wrong stock in. The main issue is depicted at the figure below:
For a better solution , we used the recurrence relations theorem, which is a mathematical technique that enables series definitions, sets, operations or algorithms from particular problems to general ones, that is, by using a rule we can calculate any term in function of its immediate(s) antecessor(s).
We used the classic Arithmetic progression => Xn = Xn + T . This now can be a simple solution , but believe me , at the time, it consumed several hours to find an algorithmic solution.
CGW Go-Live and current results
CGW went live in 2018 and it stills runs our lab inventory . We already have been gone by more than two audit inspections, where our lab stock inventory matched against our ERP. Furthermore, with our redesigned interface, it enabled our financial and warehouse team to have an improved control over the stock inputs and outputs.
We have many features requests at our roadmap, such as:
- integration with the lab routine workflow: when the lab technician decides to start a DNA extraction, the system already checks the inventory system and returns the items available and its location to perform the procedure;
- the lab team could make stock an earlier reservation of consumables planned for new exam validation routines;
- Improvements at our interface enabling users to mark as used a batch of itens with few clicks.
- and much more!
The infographic and photos below presents a a review of our inventory management over those two years.
Closing remarks
Lab inventory control is a critical process in every laboratory. The discipline around the inventory management is an effective path to teach the staff members the importance of personal responsibilities in lab management and the need for systems and processes required for efficient lab operations.
As any startup, you will need to evaluate your current stage (whether you are beginning with small volume or a big lab with thousand workflows), so you can feel the need for the systems and refined processes. Automating inventory processes as your lab grows (exams, staff team) adds to your financial and quality processes several improvements, by reducing or eliminating inaccuracies in inventory control, more efficient ordering and product management and overall improvement in lab operations.
Let's review our main pain points:
- Other manual processes. Lab staffs spend inordinate amounts of time searching for and collecting reagents, and other lab supplies. This often means lab techs are losing productivity while increasing the risk of human error.
- Intuitive stocking. Many manual inventory systems rely on the hunches and best guesses for stocking products. Removing this component allows for more efficient and cost-effective inventory practices.
- Bad products. With manual systems, there is a far greater risk that expired, recalled or obsolete products and supplies are used, even duplicated or incorrect information.. This usage creates budgetary waste and may endanger patients via inaccurate results or dangerous procedures.
- Faulty charge capturing. With manual processes, chargebacks are rife with error potentials, either from misplaced, mischarged, or omitted charges. Capturing charges manually exposes labs to the impact of human error.
- Lack of analytics. With manual systems, creating accurate, real-time reports that can be used to analyze costs, usage and efficacy are daunting if not impossible. Creating these reports with manual inventory is time-consuming and tenuous in terms of accuracy.
- Painful physical inventory days . When the lab often essentially shut down their stock to count and recount. With tracking systems, the flow of the labs are tracked on an ongoing basis, eliminating the need for multiple shut-down days and overtime costs.
And the future ?
What we believe which are the next steps or our (maybe) inventory management system, is to apply data-science techniques to plan and forecast. inventory demand. Our system would allow you to set alerts when supplies are running low to timely restock them and avoid stalling your lab’s processes. With an automated notification system like that, you can plan your inventory demand and feel secure about the availability of your lab supplies. As a more sophisticated way to forecast inventory demand, adding strong data analytics capabilities to uncover your supply consumption patterns and use them for future demand prediction.
Our product team were happy after one of our internal research surveys about the CGW. Some testimonials of our current inventory management system:
The inventory management system made a process that, in addition to being laborious, left room for many dynamic and fast failures. Today it is possible to trust the system and make purchasing decisions for example by the information in it. The checkout is easy to fill, speeding up the process. The consultation of materials is dynamic and several data can be used for research such as name, type, code, batch ... Batch validation records are easy and quick to be performed, this makes the process to be done immediately and not left for later and forgotten. For auditing purposes in conjunction with Gensoft it is a mirror of how good our traceability is. There are also alerts for items with a close due date, which is very important for deciding on obsolescence or triggering a new purchase. This is a small summary of the main functions of everyday life, CGW is beautiful! Facilitates the routine and does not take time from other activities.
With the system we can meet some technical requirements of the sectors, such as the tracking and control of each product through the registration of the date of entry, batch, validity, temperature control and packaging on receipt. We also meet accounting and tax specifications by controlling average price, consumption and purchases by period, in addition to disposal. The system has optimized our activity as all control requirements have arisen. It is very good to have a personalized system and the support is at our side. Although, this ease of customization and access to support can leave the system fragile, since a previously defined rule can change at any time. Probably all the divergences of reports that we found are due to this aspect.
With the new system, from the perspective of software maintenance, everything became easier. We have a system with robust data consistency and precise mapping of the laboratory's business rules. These points led us to the condition of very low bug rate in 2 years of use and 100% of the reported bugs are not related to the consistency of the data in relation to the state of the physical stock.
Special Thanks
I would like to mention some people that assisted us in the development and evolution of our inventory management system: the old spreadsheets to CGW.
- NGS Genomika Diagnósticos Team
- Our stock keepers: Nidian Arão e Elaine Cardoso (Our Financial Team)
- Our sponsor CFO Carlos Saito
- Our lab guardians, specially the Improvement Stock Team: Geórgia de Oliveira, Ana Maria Menezes and Jobson Nascimento.
- Our Lead Senior Developer Danilo Soares and Lucas Eduardo (IT trainee)
