Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
reports:purchasing [2024/07/26 03:59] – [mSupply's better algorithm] Louisa Wong | reports:purchasing [2024/09/09 05:38] (current) – Louisa Wong | ||
---|---|---|---|
Line 124: | Line 124: | ||
Step 1: | Step 1: | ||
First we are adjusting by % days in stock to be considered fully stocked. This calculation will be used in Step 2 to calculate the typical AMC. | First we are adjusting by % days in stock to be considered fully stocked. This calculation will be used in Step 2 to calculate the typical AMC. | ||
- | So if this is set as 90%, only months with >90% stock will be considered. | + | So if this is set as 90%, only months with ≥90% stock will be considered. |
- | In this case, Nov 2023 (202311) and Oct 2023 (202310) are excluded. | + | In this case, Nov 2023 (202311) and Oct 2023 (202310) are excluded |
{{: | {{: | ||
Step 2: | Step 2: | ||
- | To calculate the typical AMC after the first exclusion criteria, we are using: | + | We then calculate the typical AMC which excludes |
(Total consumption of only months included in Step 1)/(Number months usage basis) | (Total consumption of only months included in Step 1)/(Number months usage basis) | ||
= 100 / 7.8 = 12.82. Red coloured boxes indicate exclusion. | = 100 / 7.8 = 12.82. Red coloured boxes indicate exclusion. | ||
+ | (Note: We have 7 full months, and 0.8 of a month due to July 2024 (202407) as 26 days is 0.8. | ||
{{: | {{: | ||
Step 3: | Step 3: | ||
- | Next we are excluding months that have 33% days of low stock - this is done automatically | + | Next there is an automatic exclusion for days of low stock ≥33% in the month. Note: This is automatic |
Step 4: | Step 4: | ||
- | Since we have entered | + | Now we consider the criteria of "100% of typical AMC that stock level is considered compromised" |
+ | Since this is set as 100% by us, we now take the typical AMC (12.82) and check the Mean SOH of each month to make sure that this is equal or higher than 12.82. If stock level is not higher than 12.82, that month will be excluded from the Adjusted AMC calculation (Step 5). Note: If we had selected 90% of typical AMC to be considered compromised, | ||
To now obtain the number of months to be considered for the adjusted AMC: | To now obtain the number of months to be considered for the adjusted AMC: | ||
- | We will exclude the 33% low stock from Step 3. | + | We will exclude the 33% low stock month from Step 3. |
- | We will exclude any months with (x)% typical AMC stock level that is considered compromised. | + | We will exclude any months with (x)% typical AMC stock level that is considered compromised |
+ | We will be including the month with 11 days in stock however, as it is more than the 33% exclusion step and the first step where it was excluded was just for calculating what months to use for typical AMC. | ||
- | From the example, we will now have 8.8 months that we will be considering for the Adjusted AMC. (Note: 0.8 of the months comes from July 2024 (202407) as 26 days is 0.8 of a month. | + | From the example, we will now have 8.8 months that we will be considering for the Adjusted AMC. |
{{: | {{: | ||
Line 152: | Line 155: | ||
Step 5: | Step 5: | ||
We can now calculate the Adjusted AMC with the formula: | We can now calculate the Adjusted AMC with the formula: | ||
- | Square root[(Days in stock)/(Days in the month)] * Consumption | + | Square root[(Days in month)/(Days in the stock)] * Consumption |
We check this individually for each month that has not been excluded. | We check this individually for each month that has not been excluded. | ||
- | So, if the Days in month = Days in Stock, the square root of this = 1. | + | So, if the "Days in month" |
- | In Dec 2023 (202312) = 1 x 100 = 100. | + | In Dec 2023 (202312) = 1 x 100 (consumption) |
- | In Oct 2023 (202310) = Squareroot(31/ | + | In Oct 2023 (202310) = Squareroot(31/ |
- | Add these together = 267.87 total units for all included months | + | |
+ | Add these together: | ||
+ | 100 + 167.87 | ||
Step 6: | Step 6: | ||
- | For the Adjusted AMC = 267.87/8.8 months = 30.34 | + | For the Adjusted AMC = 267.87/8.8 months = 30.34 units per month. |
+ | |||
+ | **Steps Summarised: | ||
+ | |||
+ | Step 1: Adjusting by % Days in Stock | ||
+ | * Set threshold for fully stocked (e.g., 90%) | ||
+ | * Exclude months with < 90% stock | ||
+ | |||
+ | Step 2: Calculate Typical AMC | ||
+ | * Use months not excluded in Step 1 | ||
+ | * Calculate Typical AMC = (Total consumption of only months included in Step 1)/(Number months usage basis) | ||
+ | |||
+ | Step 3: Automatic Exclusion for Low Stock | ||
+ | * Exclude months with < 33% days in stock | ||
+ | |||
+ | Step 4: Compromised Stock Level Check | ||
+ | * Set threshold (e.g., 100% of typical AMC) | ||
+ | * Exclude months below threshold | ||
+ | |||
+ | Step 5: Calculate Adjusted AMC | ||
+ | * Apply formula to each included month = Square root[(Days in month)/ | ||
+ | * Sum the results | ||
+ | Step 6: Final Adjusted AMC Calculation | ||
+ | * Divide total consumption by number of months | ||
+ | * Calculate Adjusted AMC | ||
+ | Note: If all months have been excluded due to the first or second exclusion criteria, then the typical AMC calculation will automatically be used instead. | ||
==== Report options ==== | ==== Report options ==== | ||
Line 240: | Line 270: | ||
* Number of months in stock : Calculated by (' | * Number of months in stock : Calculated by (' | ||
- | * Ordered Quantity used : Calculated | + | * Ordered Quantity used : This is how much of the stock you have ordered that you will actually need to use, based on how much you use each day and how much stock you already have (Note: forecast calculation will affect this). This is determined |
+ | * Calculate the "usage per day of stock" = AMC / 30.4375 | ||
+ | * Multiply "usage per day of stock" with the "total number of days in your months stock required" | ||
+ | * Calculate your " | ||
+ | * Subtract "Total usage required" | ||
+ | * This value ("Net stock difference to be used") now determines the " | ||
+ | * If the value is a negative value and there is "Stock on order", add the "Net stock difference to be used" with "Stock on order" = "Net stock difference to be used" + "Stock on order" | ||
+ | * If the value is a positive value and there is "Stock on order", | ||
+ | |||
+ | * Suggested Order calculated by: | ||
+ | * If expected delivery date has not been adjusted: (' | ||
+ | * If expected delivery date has been adjusted, this will be accounted for as it will only look at the stock required from date of expected delivery to the end of months stock required: | ||
+ | |||
+ | * Usage per day = Adjusted AMC/30.4375 (which | ||
+ | * Suggested order quantity = Usage per day x Days needed (which is ' | ||
+ | |||
+ | * This determines | ||
+ | * Note: if you are using the " | ||
+ | * Note: if you are using forecasting this can affect suggested order quantity. Please check forecasting settings on mSupply if it is indicated as being used in the spreadsheet. | ||
+ | * Note: The " | ||
- | * Suggested Order : Calculated by (' | ||
{{: | {{: | ||
{{: | {{: |