Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
reports:purchasing [2019/10/03 09:25] – [Suggested order quantities] Gary Willetts | reports:purchasing [2024/09/09 05:38] (current) – Louisa Wong | ||
---|---|---|---|
Line 1: | Line 1: | ||
{{indexmenu_n> | {{indexmenu_n> | ||
- | ====== | + | ====== |
- | + | ||
Purchasing reports (focused on what you have ordered and what you have received and what you want to order) are available at //Report// > // | Purchasing reports (focused on what you have ordered and what you have received and what you want to order) are available at //Report// > // | ||
Line 9: | Line 7: | ||
The first 2 options contain several different reports (see below). Select the required report type by clicking on it. | The first 2 options contain several different reports (see below). Select the required report type by clicking on it. | ||
- | ===== Purchase | + | ===== Purchase |
{{ : | {{ : | ||
Line 59: | Line 57: | ||
In the same way, you can also filter your reports on properties of the purchase orders involved: category, status and both purchase order custom fields. | In the same way, you can also filter your reports on properties of the purchase orders involved: category, status and both purchase order custom fields. | ||
- | ===== Purchases... ===== | + | ===== Purchases ===== |
{{ : | {{ : | ||
Line 74: | Line 72: | ||
===== Suggested order quantities ===== | ===== Suggested order quantities ===== | ||
- | <WRAP info center round 90%> | + | Use this report |
- | Not that there are currently 2 options | + | |
- | </ | + | |
- | Use this report | + | There are currently 2 options |
- | {{ :reports:reports2-11.png }} | + | |
+ | ==== Quantification ==== | ||
+ | |||
+ | Some logic behind the calculations involved in this report is described in detail on the [[purchasing: | ||
+ | |||
+ | As of version 7.15, mSupply supports a wide array of options for calculating AMC (Average Monthly Consumption), | ||
+ | |||
+ | The options are: | ||
+ | === Don't Adjust AMC === | ||
+ | This has been the default in mSupply up until this point. | ||
+ | The consumption (or distribution if not a service delivery point- we're using _consumption_ interchangeably here) is summed up for the number of months specified in the lookback field, and divided by the number of months. | ||
+ | This works well if the item was fully stocked for the whole time. It gives too low a number if stock was low or zero. | ||
+ | |||
+ | === Adjust by % of days out of stock === | ||
+ | This method takes each month' | ||
+ | We multiply the consumption for the month by (Days in month divided by Days in stock) to get adjusted AMC | ||
+ | Take this example: | ||
+ | * Item B was in stock for 10 days in the month | ||
+ | * Its consumption was 500 | ||
+ | * The adjusted AMC is 500 x 31 / 10 = 1,550 | ||
+ | |||
+ | |||
+ | === Only consider fully stocked months === | ||
+ | Here, we are attempting to address months with low stock by excluding them from our calculations, | ||
+ | There is a field shown **% of days in stock to be considered fully stocked** that defaults to " | ||
+ | Note that you need to set the lookback period long enough that mSupply will be able to find enough fully stocked months to run the calculation. You can also include more months by setting the **% of days in stock to be considered fully stocked** field to (say) 50%. | ||
+ | === mSupply' | ||
+ | |||
+ | We've tried to come up with a better algorithm here by: | ||
+ | * ignoring months that are in stock for < 33% of days. That is, if the item was in stock for less than 10 days in the month, the consumption on those days is too erratic to draw conclusions about what the consumption for the whole month would have been. | ||
+ | * For months with stock >= 33% of days, Multiply the consumption for the month by Square Root(Days in month/Days in stock) to get adjusted AMC. This adjusts the consumption up due to being out of stock, but by a factor up to 1.7 - so not as big an adjustment as the " | ||
+ | * We also ignore months where the mean stock on hand is less than "% of typical AMC that stock level is considered compromised:" | ||
+ | * Consider Item A - we have set the "when we calculate consumption there are 3 months that were "fully stocked" | ||
+ | * Now, there' | ||
+ | * But it turns out that the average stock on hand was only 50 - clearly they could never have had " | ||
+ | |||
+ | We then sum the adjusted consumption for all the eligible months & divide by the number eligible months to get the best AMC eva ;-) | ||
+ | |||
+ | =============== | ||
+ | |||
+ | Let's take an example of how this is done: | ||
+ | |||
+ | Lets say we are using: | ||
+ | |||
+ | {{:reports:pasted: | ||
+ | |||
+ | 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. | ||
+ | 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 as it is less than 90%. | ||
+ | |||
+ | {{: | ||
+ | |||
+ | Step 2: | ||
+ | We then calculate the typical AMC which excludes the months Nov 2023 (202311) and Oct 2023 (202310) from step 1. To do this, we use: | ||
+ | (Total consumption of only months included in Step 1)/(Number months usage basis) | ||
+ | = 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: | ||
+ | Next there is an automatic exclusion for days of low stock ≥33% in the month. Note: This is automatic and is not set by the user. In the example, this is Nov 2023 (202311) as it is the only month with less than 33% days in stock in the month. | ||
+ | |||
+ | Step 4: | ||
+ | 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: | ||
+ | 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 (which in this case, there is none). | ||
+ | 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. | ||
+ | |||
+ | {{: | ||
+ | |||
+ | Step 5: | ||
+ | We can now calculate the Adjusted AMC with the formula: | ||
+ | Square root[(Days in month)/ | ||
+ | We check this individually for each month that has not been excluded. | ||
+ | So, if the "Days in month" = "Days in Stock", | ||
+ | In Dec 2023 (202312) = 1 x 100 (consumption) = 100. | ||
+ | In Oct 2023 (202310) = Squareroot(31/ | ||
+ | |||
+ | Add these together: | ||
+ | 100 + 167.87 = 267.87 total for all included months that we will use to calculate adjusted AMC. | ||
+ | |||
+ | Step 6: | ||
+ | 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 ==== | ||
+ | |||
+ | You'll see this window: | ||
+ | |||
+ | {{ : | ||
If you want to report on all items, leave the item name field blank, otherwise enter an item name or code. For example, to report on amoxycillin stocks, you could enter " | If you want to report on all items, leave the item name field blank, otherwise enter an item name or code. For example, to report on amoxycillin stocks, you could enter " | ||
{{ : | {{ : | ||
- | |||
The drop down list, after the item name and code choices, lists several other fields, including any user defined fields, where the names that you have defined for custom item fields in the // | The drop down list, after the item name and code choices, lists several other fields, including any user defined fields, where the names that you have defined for custom item fields in the // | ||
Line 108: | Line 225: | ||
**Include items with no usage in the last 12 months:** If this box is checked, items which have not been used over the last year will be included. This check box will have no effect if the previous "days cover" field is filled in, as an item with no usage will have " | **Include items with no usage in the last 12 months:** If this box is checked, items which have not been used over the last year will be included. This check box will have no effect if the previous "days cover" field is filled in, as an item with no usage will have " | ||
- | **Order quantity for X months | + | **Months |
+ | |||
+ | **AMC Adjustment for Out of Stock:** See the " | ||
+ | |||
+ | **Exclude stock that will expire within 3 months of projected consumption date** is a new/updated checkbox to accurately predict amount of stock that is likely to be wasted due to expiry and current consumption rate. In this current example, mSupply will only aim to distribute stock if the expiry is more than 3 months away. This updated feature is only available | ||
+ | |||
+ | This topic is huge so it may be worth reading this topic [[faq: | ||
**Open report in Excel:** Checking this box opens Excel (or any other installed spreadsheet application) and displays the report as a spreadsheet. If you want to save the report, choose File > Save as... in your spreadsheet application. | **Open report in Excel:** Checking this box opens Excel (or any other installed spreadsheet application) and displays the report as a spreadsheet. If you want to save the report, choose File > Save as... in your spreadsheet application. | ||
==== Report output ==== | ==== Report output ==== | ||
- | **Cover | + | |
+ | A typical `Suggested Order` report will look something like below. | ||
+ | |||
+ | {{: | ||
+ | |||
+ | Definitions of each column | ||
+ | |||
+ | | ||
+ | |||
+ | | ||
+ | {{: | ||
+ | |||
+ | * Effective SOH : Calculated by (' | ||
+ | |||
+ | * Backorder : Quantity of stock that has been ordered by customers but not yet fulfilled due to insufficient stock. | ||
+ | |||
+ | * Stock on order : Quantity of stock that has been ordered from suppliers and is expected to arrive but not yet received. | ||
+ | |||
+ | * AMC 12 months : Average quantity of stock used per month over the last 12 months. | ||
+ | |||
+ | * AMC 24 months: Average quantity of stock used per month over the last 24 months. | ||
+ | |||
+ | * Monthly usage for the last (x) months : Calculated by (' | ||
+ | |||
+ | {{: | ||
+ | |||
+ | * Number of months considered for adjusted AMC : After the AMC adjustment is made (and months with low stock has been excluded), this value indicates the number of months that have been included for calculating the adjusted AMC. This value is derived from whichever formula has been chosen in the "AMC Adjustment for out of stock": | ||
+ | {{: | ||
+ | |||
+ | * Adjusted AMC : This adjusted value is based on the formula selected in the Report options: | ||
+ | {{: | ||
+ | |||
+ | * Number of months in stock : Calculated by (' | ||
+ | |||
+ | * 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 by: | ||
+ | * Calculate the "usage per day of stock" = AMC / 30.4375 | ||
+ | * Multiply "usage per day of stock" | ||
+ | * 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", | ||
+ | * If the value is a positive value and there is "Stock on order", if "Net stock difference to be used" is greater than "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 | ||
+ | |||
+ | * Usage per day = Adjusted AMC/30.4375 (which is from 365.25/12 which is days in the full year) | ||
+ | * Suggested order quantity = Usage per day x Days needed (which is ' | ||
+ | |||
+ | * This determines the quantity of stock that needs to be ordered to maintain the desired inventory levels after considering current stock, back orders, and stock already on order. | ||
+ | * 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 " | ||
+ | |||
+ | {{: | ||
+ | {{: | ||
+ | * Forecast used : Based on forecasting options under " | ||
+ | {{: | ||
+ | |||
+ | |||
+ | For a more detail guide click on this topic : [[faq: | ||
===== Suggest order quantities with prices and price extension ===== | ===== Suggest order quantities with prices and price extension ===== | ||
- | This report is an extension of the previous one, allowing you to see what the suggested order will cost. The filter choices you have are the same as for the suggested order quantities report. | + | This report is an extension of the previous one, allowing you to see what the suggested order will cost. The filter choices you have are the same as for the suggested order quantities report. |
+ | |||
+ | {{ : | ||
+ | |||
+ | Key Column headers : | ||
+ | * Unit Price : Calculated via the latest price or by supplier quotes or using average prices received during the look back period. The `look back period` is the same setting that is used to calculate average monthly stock usages. | ||
+ | * Price extension (Suggested) : This is the predicted cost of a future order based on Unit Price. | ||
+ | * Annual consumption (quantity) : This is the predicted stock usage over a year based on the latest average monthly consumption. | ||
+ | * Annual consumption (cost) : Potential annual cost based on unit price. | ||
- | // Previous: [[reports: | + | \\ |
+ | \\ | ||
+ | | | ||
---- struct data ---- | ---- struct data ---- | ||
+ | pagestatus.status | ||
---- | ---- | ||