Creating a dashboard to track TB lab consumable usage trends quarterly
Tuberculosis laboratories depend on a steady supply of reagents, cartridges, collection materials, personal protective equipment, and other consumables. A shortage can interrupt testing, delay results, and increase pressure on staff. Excess stock creates a different risk: expired materials, tied-up funds, and storage congestion.
A quarterly dashboard turns routine inventory records into a practical management tool. Instead of reviewing isolated purchase orders or counting shelves without context, laboratory leaders can see how consumption is changing, compare usage with testing volume, and identify supply risks early.
The dashboard does not need expensive software. A well-structured spreadsheet can support the same decisions as a more advanced system when definitions, responsibilities, and review dates are clear. Its value comes from consistent data and regular action.
Define the decisions the dashboard must support
Start by identifying the decisions that the dashboard should inform. These may include when to reorder a consumable, whether a change in testing volume explains increased use, whether a product is being wasted, and how much stock should be included in the next procurement request. Each metric should connect to one of these decisions.
A useful dashboard usually tracks opening stock, receipts, issues or usage, closing stock, losses, adjustments, tests performed, and days of stock on hand. Record the unit of measure carefully. A box, kit, cartridge, tube, pack, and individual item should not be mixed in the same field unless a conversion factor is documented.
Define a consistent reporting period, such as the calendar quarter or the laboratory’s financial quarter. Use the same cut-off date for every product. If stock counts are conducted on different days, note the count date so that apparent changes are not mistaken for actual consumption trends.
Build a reliable data structure
Create a master list for every consumable. Include the item name, catalogue number, manufacturer, supplier, unit size, storage requirement, shelf-life information, and the testing activity it supports. Separate products with different pack sizes or specifications, even when their common names are similar.
A transaction sheet should capture the date, item code, transaction type, quantity, unit, batch or lot number when relevant, and the staff member responsible for the entry. Transaction types can include receipt, issue, transfer, expiry, damage, adjustment, and physical count. This structure makes it easier to distinguish genuine use from losses or corrections.
The dashboard should also connect consumption with workload. For TB laboratories, useful denominators may include molecular tests performed, microscopy examinations, cultures initiated, drug-susceptibility tests, or specimens processed. Consumption per test is often more informative than total consumption because quarterly testing volumes can vary substantially.
Before publishing a result, reconcile the formula:
Opening stock + receipts − issues − losses ± adjustments = closing stock
The calculated closing balance should be compared with the physical count. Investigate material differences rather than silently overwriting the figures. Repeated discrepancies may point to undocumented transfers, inconsistent units, data-entry errors, or weaknesses in stock control.
Select indicators that reveal change
A concise dashboard should highlight trends rather than reproduce every inventory transaction. For each priority item, calculate quarterly consumption, percentage change from the previous quarter, average monthly use, stock on hand, months or days of supply, expiry exposure, and consumption per test.
Use a clear rule for percentage change. When the previous quarter has zero consumption, display “new use” or “not applicable” instead of generating a misleading percentage. Where testing volume changes, interpret the consumption rate alongside workload. A 20% increase in cartridge use may be reasonable if testing increased by 25%, while a 5% increase may still signal inefficiency if testing remained unchanged.
| Indicator | Calculation | What it helps reveal | Suggested review signal |
|---|---|---|---|
| Quarterly consumption | Issues plus approved losses | Overall use during the period | Large unexpected rise or fall |
| Consumption per test | Quantity used ÷ tests performed | Efficiency and workload-adjusted use | Persistent increase |
| Days of stock | Closing stock ÷ average daily use | Time before replenishment is needed | Below lead-time requirement |
| Stock variance | Physical count − recorded balance | Accuracy of inventory records | Repeated or material difference |
| Expiry exposure | Quantity expiring within defined period | Risk of unusable stock | Stock exceeds likely use |
| Supplier lead time | Request or order date to receipt date | Procurement reliability | Increase affecting reorder timing |
Present trends with simple visuals. A line chart can show quarterly consumption, a bar chart can compare usage with testing volume, and a conditional-formatting rule can flag low stock or imminent expiry. Avoid using too many colours. Green, amber, and red should have written definitions so that different reviewers interpret them consistently.
Connect inventory data with laboratory quality systems
Consumable monitoring belongs within the laboratory’s quality management system, especially the Quality Systems Essentials related to equipment, purchasing and inventory, documents and records, assessment, and continual improvement. Assign an owner for data entry, a reviewer for quarterly analysis, and an approver for corrective actions.
The dashboard should be a controlled record. Include the reporting period, version date, data source, person who prepared it, reviewer, and any known limitations. Store source documents such as stock cards, issue vouchers, goods received notes, and physical count sheets according to the laboratory’s document-control practices.
Consumables are often linked to equipment performance. For example, an unexpected fall in reagent use may reflect instrument downtime rather than lower demand. A sudden rise in repeat testing may increase consumption and indicate a maintenance or quality problem. When interpreting these patterns, review equipment service records and the laboratory’s maintenance schedule. Guidance on equipment maintenance planning can help laboratories relate supply trends to operational reliability when resources are limited.
Temperature-sensitive reagents require particular attention. A dashboard can include storage incidents, temperature excursions, or quantities placed on hold alongside expiry and usage data. A sound freezer temperature system supports more accurate decisions about whether stock remains usable and whether a loss should be recorded.
Review quarterly patterns and investigate exceptions
A quarterly review should begin with the largest changes. Compare current consumption with the previous quarter, the same quarter in the prior year when available, and the number of tests performed. Consider changes in case detection, outreach activity, testing algorithms, instrument availability, reagent substitutions, staff practice, and reporting completeness.
Investigate high consumption and low consumption differently. High consumption may result from increased workload, repeat testing, spillages, expired stock, incorrect dispensing, or unrecorded transfers. Low consumption may indicate lower demand, stockouts, equipment downtime, delayed data entry, or a shift to another testing method. The dashboard identifies the signal; records and staff discussion establish the cause.
Set thresholds that reflect the laboratory’s context. For example, an item might be flagged when consumption changes by more than 15%, days of stock fall below supplier lead time plus safety stock, or physical variance exceeds a defined percentage. Thresholds should be reviewed after several quarters because initial limits may produce too many false alerts or miss meaningful risks.
Document the action taken for each significant exception. Actions could include recounting stock, correcting a unit conversion, rotating near-expiry items, adjusting the order quantity, reviewing a standard operating procedure, or escalating a supplier delay. Recording the response turns the dashboard into evidence for continual improvement rather than a passive reporting display.
Use the dashboard for forecasting and procurement
Quarterly data can support a rolling forecast, but it should not be treated as a simple multiplication exercise. Estimate future demand using recent consumption, expected testing volume, seasonal patterns, campaign activity, and planned changes to the diagnostic algorithm. Exclude exceptional losses from routine consumption, while retaining them as a separate risk indicator.
A basic reorder calculation is:
Reorder quantity = expected use during lead time + safety stock − usable stock on hand − confirmed incoming stock
“Usable stock” should exclude expired, damaged, quarantined, or incorrectly stored materials. Apply the calculation separately to each item and pack size. For products with long lead times or uncertain supply, use a larger safety margin and review the forecast more frequently.
Procurement teams need more than a red or green status. Give them the item code, current balance, average use, projected requirement, expiry profile, supplier lead time, and recommended order quantity. This creates an auditable link between laboratory evidence and purchasing decisions.
Make the review routine and actionable
A dashboard works best when it becomes part of the quarterly quality meeting rather than a report prepared only for external requests. Keep the discussion focused on exceptions, risks, and decisions. Record agreed actions, responsible staff, due dates, and the next review point.
Practical steps for implementation include:
- Start with high-value, high-risk, or frequently used consumables before expanding the scope.
- Use item codes and standard units to prevent duplicate records and pack-size errors.
- Compare stock consumption with testing volume every quarter.
- Define red, amber, and green thresholds in a controlled procedure.
- Archive each approved dashboard with its source records and action log.
A spreadsheet can be sufficient at the beginning, provided that access is controlled and formulas are protected. As the system matures, laboratories can add automated imports, barcode scanning, supplier lead-time fields, or links to laboratory information systems. Downloadable quality management resources and implementation materials are available through the GLI Quality Tool downloads, which can support the broader documentation and assessment process.
Use the next quarter as a baseline, validate the figures with a physical count, and discuss the first set of exceptions with laboratory, procurement, and quality staff. Then make the dashboard part of the regular management cycle so that stock decisions are based on evidence, risks are addressed earlier, and TB testing services remain dependable.