The Challenges of Hospital Inventory - slicing a data cleanup project by 57 days.

New Zealand Health Board

Let’s consider the case of a large hospital that noticed its inventory figures were not truly reflective of how many necessary items were available. When preparing for an operation, it’s important to overstock with required items for surgery, and in this instance, staff would constantly find that the number of syringes the computer said were in the cupboards, were there but in very different quantities. This led to manually ordering extra stock instead of relying on auto-replenishing. The problem fully unravelled when it was also discovered the average costing figures were completely wrong. This left the various departments in the hospital with stock budget blowouts.

With an item like syringes which are packed in boxes of 100 units, part of the problem comes about when there’s a discrepancy between whether one item vs one box of said items is counted into or out of stock. If when preparing for surgery a number of items are taken out of stock for use and then some are returned as items without noting the difference between boxes vs individual items, then chaos sets into the inventory system. In this instance, it was taking one staff member a whole day at a time to do returns, and stock was starting to pile up – further affecting the inventory reality. 

The hospital in our case study was having exactly these issues. Once they identified the problem it seemed a relatively simple matter to resolve it, but the options before them to bring about real change were complex. They agreed they did not wish to hire consultants or write new code – both of which would be expensive, time-consuming, and require a lot of testing. 

THE RANGE OF TECHNOLOGY OPTIONS EACH HAD ITS OWN CHALLENGES: 

  • Keyboard Emulation Software – Slow, lack of validation and required babysitting 
  • Scripted Loading Software – Not end user friendly, takes consultants to setup and control. Requires additional language skills (such as PHP) for better functionality 
  • WebADI / SQL*Loader – Custom development / consultants 
  • Toad / SQL Developer – Very flexible but high expert knowledge required 
  • More4Apps Tools – Prebuilt end user excel based solution for downloading / uploading data 

Their ideal outcomes were to have updated items with correct units of measure, correct average costs per items, use min / max options, and reduce open purchase ordering. But they also found that for changes to be made effectively, many items had to be disabled, and then removed or added to Blanket Orders and Bill Of Materials. 

Using Manual, Macro, Queries – the loading time was predicted to be as high as 76 days, including 6 and a half days for set up:

New Item Creation New Blanket lines Blanket Changes Item Desc Updates Bill of Material Changes Stock Updates Average Cost Update
Item Subinv Create/ Update
# of Records 2842 2842 936 1439 342 3800 1800 6230
Time/record (second) 300 120 120 60 120 45 45 60
Setup Data/Development Time (hrs 0 d.p.) 4 4 2 4 16 12 4 8
Load Time (hrs 0 d.p.) 237 95 31 24 11 48 23 104
 

Even if Macros where forms based rather than keyboard emulation there is a lot of babysitting and setup days would increase. Assume setup to increase by 10% and implementation to drop by 30%.The final decision came down to simplicity of the software, and time savings predicted.

More4apps estimated they could SAVE 57 DAYS on the project:

Without More4apps

With More4apps

Days Saved

Percentage Saving (Rounded)

Setup

6.75

2.25

4.5

66.67%

Implementation

71.65

18.75

52.9

73.8%

Total Days

78.40

21.00

73.2%

Total Days Saved

57.4

They found that by using More4apps Wizards, they were able to update descriptions, disable old Items and create new items with the correct unit of measure quickly and accurately. Incorrect Items could be end dated and new lines added to Blanket Purchase Orders – with the correct Unit of Measure easily using Purchase Order Wizard. Because of the low item value, the average cost was simplified by using the last purchase price. The stock movements were reversed out of the old items and reloaded into the new items using the Material Transaction Wizard. The priorities of getting the stock right with the correct average costs were achieved using a combination of three More4paps Wizards; Item Wizard, Material Transaction Wizard, and Bill of Materials Wizard.

Actual times proved to be much quicker in the end, and this hospital has since repeated this process six times where they found they had incorrect units of measures.  

 

Featured Products
Share
Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on print