28.15. Wastage Prediction due to expiry when ordering

Prior to mSupply v7.12, when ordering or reporting on suggested ordering, you could simply tell mSupply to ignore stock that was going to expiry in three or four months time. mSupply would then lower it's stock level and order according to usages.

However due to popular demand with mSupply v7.12 we have tried to do a better job of predicting stock wastage due to expiry. Using the consumption data that we have, we have travelled into the future and worked out the likely wastage stock. This page describes the workflow.

Lets look at a report in mSupply v7.12 or later.

From mSupply activate the menu :

• Report > Purchasing > Suggested Order quantities…

The Suggested ordering report now has a new/updated expiry filter checkbox : Exclude stock that will expire within 3 months of projected consumption date

This checkbox accurately takes into account the likelihood of current stock expiring in the future based on the monthly calculated consumption for an item while determining the suggested order quantities. Previously you could tell mSupply to ignore stock that was close to expiry by say 3 or 6 months when calculating quantities, however this is approximate.

With mSupply version v7.12, mSupply has the option to accurately analyse the stock quantities that can’t be distributed due the consumption rate and the expiry date of the stock. This results in a more accurate estimate of the suggested order quantities.

The workflow :

Imagine we have the following batches for an item.

Item Batch Stock Expiry Amoxycillin/ Clavulanic acid 30mg b12-2023 500 12/2023 Amoxycillin/ Clavulanic acid 30mg b06-2024 550 06/2024 Amoxycillin/ Clavulanic acid 30mg b06-2025 100 06/2025

Imagine the current date is 1/1/2024 Imagine the AMC is 50 Imagine you need stock for 12 months. When running the report this is checked : Exclude stock that will expire within 3 months of projected consumption date

When the above report is run, mSupply to immediately ignore the batch `b12-2023` as it has already expired as the current date is `1/1/2024`.

For batch `b06-2024` : mSupply works out that you can only dispense this batch till the end of April 2024, due to the set 3 months cutt-off date for expiry. So the months that this stock cane be dispensed are : January 2024 February 2024 March 2024 April 2024

So the stock that can be used from batch `b06-2024` is : 4 * 50 = 200. ( in the reports that you will see in the future, this is known as effective stock) So the wasted quantity for batch `b06-2024` is : 550 - 200 = 350 (This is expiring stock, 350 will need to be thrown away. )

For batch `b06-2025` : So from May 2024, mSupply will need to start using this batch for distribution. So this is 8 months remaining from May 2024 - to December 2024. However with a stock of just `100` this item will be used up during the following two months : May 2024 June 2024 So for batch `b06-2025` all the stock will be used up and they will be no wastage.

However we now have 6 months starting from July 2024 till the end of December 2024 where we don’t have any stock, so mSupply will suggest a immediate order of : 6 * 50 =300 to cope with the 12 months starting from `1/1/2024`