Inventory optimization for large hospital
Problem: for the given hospital, the end client desires to optimize its inventory. The end client has very high level of closing stock numbers and wanted to optimize the same using the Turbodata services.
Approach: Turbodata team had worked under a top 4 consulting firm. The end client had been given target Share of Business at the generic, strength and UOM level. The actual share of business versus target share of business was calculated.
The ETL team first looked at category ‘A’ items in terms of closing stock numbers at the generic strength and UOM level. Thereafter the percentage contribution in terms of sales and profit was calculated for these items.
The ETL team also found yearly and monthly trend analysis.
The ETL team then did a comparison between actual and target SOB on yearly basis and looked at the average procurement price by generic, strength and UOM.
Based on the same the ETL team looked at the vendor wise share and payables owed to the vendors in terms of ageing analysis(snapshot attached below):
Decision sheet: The final decision sheet had the following 7(seven) set of dashboards and graphs:
· Consumption(year to date) by patient category
· Consumption(year to date) by doctor
· Consumption(year to date) by package
· The team looked at inventory ageing and number of days inventory for the targeted stock items.
Based on the above decision criteria, the ETL team looked at actual SOB versus the Target SOB.
The top 3(three) doctors were sent an email about the excess consumption.
Result for the end client: immediate intimation of the excess stock by the doctor and package.
Process pre requisites:
Data consolidation: HIS and the SAP systems
Data cleansing: specifically from the HIS system
Data auditing and data profiling
Business Intelligence: Microsoft platform was used for the same.
Data extraction/data capture: from multiple source systems.
Further details on inventory optimization can be obtained from the given link.