Purchasing reports (focused on what you have ordered and what you have received and what you want to order) are available at Report > Purchasing. The following report types are available:
The first 2 options contain several different reports (see below). Select the required report type by clicking on it.
There are seven reports currently available:
Note that the number of lines received may not match the number ordered as a single Purchase Order line may be supplied in split deliveries, as in example shown, or an ordered line may not be supplied at all.
A date option, for selecting which items/purchase orders to include in the report must be selected; options are:
The report will include items/purchase orders with the selected type of date falling between two specified dates entered in the From and To fields. The drop-down list on the right affords many quick-choose preset date range options such as “today”, “yesterday”, “this week”, “last week”, “this month” etc.
The report may be confined to suppliers meeting specific criteria by selecting one of several supplier properties (“Name”, “Name code”, “analysis”, “category”, “Price code” etc.) in the left dropdown list, “equals”, “starts with” or “contains” in the centre dropdown list and making an appropriate entry in the field on the right. If this field is left blank, the report will include purchase orders from all suppliers within the chosen date range.
Similarly the report may be confined to items meeting specific criteria; you choose an item's property to filter on in the left dropdown list followed by the comparator in the centre dropdown list and finally making an appropriate entry is made in the field on the right. Again, leaving this field blank will produce a report on all items within the chosen date range.
You can also report on items that have a certain category and/or department. By default these options are set to “Don't care”, which means the item categories and departments will be ignored.
Note that only reports that report on invoice lines will produce meaningful results when you choose item criteria. Reports that report on whole transactions (e.g. “Each invoice grouped by name” or “Each invoice grouped by date”) will not produce meaningful data, as any one invoice can contain multiple items. Choose reports that report on invoice lines (e.g. “Each invoice line by item” or “Totals for each item grouped by month”) for these filters to have an effect.
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. For more information on purchase order categories see here
This report finds all purchasing transactions over a certain value within a specified time period
You should enter:
It may be good for your organisation to have a purchasing policy that stipulates that transactions over a certain value must be authorised by a second party (that is, someone other than the purchaser). This report allows you to monitor such a policy easily.
Use this report for mSupply to analyse your current stock and, based upon a required number of months of stock cover which you enter, suggest how much of each stock item you should consider ordering.
There are currently 2 options for the suggested order quantities report: Suggested ordering report and Suggested ordering [Excel] report. They both have the same options as defined below but the second Excel only report will run much more quickly than the first one, even if the first one is exported to Excel. This is a temporary situation: the first option will be removed in a later version of mSupply and the Excel option will be the only option available.
Some logic behind the calculations involved in this report is described in detail on the 6.01. Ordering stock from suppliers page.
As of version 7.15, mSupply supports a wide array of options for calculating AMC (Average Monthly Consumption), which is then used to forecast needs and then to calculate a requested order quantity.
The options are:
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.
This method takes each month's consumption, and adjusts it for the number of days in the month the item was out of stock. We multiply the consumption for the month by (Days in month divided by Days in stock) to get adjusted AMC Take this example:
Here, we are attempting to address months with low stock by excluding them from our calculations, rather than adjusting their consumption. There is a field shown % of days in stock to be considered fully stocked that defaults to “90%” 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%.
We've tried to come up with a better algorithm here by:
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:
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” which we entered in the Report options. 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, this means we will be looking at 90% stock or more of the typical AMC (12.82) and anything less than this will be excluded.
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)/(Days in the stock)] * Consumption 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. In Dec 2023 (202312) = 1 x 100 (consumption) = 100. In Oct 2023 (202310) = Squareroot(31/11) = 1.6787. Then 1.6787 x 100 (consumption) = 167.87.
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
Step 2: Calculate Typical AMC
Step 3: Automatic Exclusion for Low Stock
Step 4: Compromised Stock Level Check
Step 5: Calculate Adjusted AMC
Step 6: Final Adjusted AMC Calculation
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.
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 “amox” into the field.
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 Preferences are displayed.
If you wish to use item categories, then use the Item category list to choose the one on which you want to report.
You can use the drop-down list to find Items with a particular code, or to use the values you have entered into one of the custom user fields. If this isn't powerful enough, use the Complex Find button to display the full query editor.
Click on the refresh button (double orange arrows) to show the number of items these basic parameters will find.
Only include items whose stock cover is less than…: If you want to report only on items whose stock is low, enter the threshold for days of stock on hand into the second field. For example, entering 90 into this field will only include items in the report whose stock is not sufficient to cover average usage for the next 90 days.
Enter “0” (zero) for this field to report on items with any amount of stock remaining.
Expected delivery: Enter an appropriate date based on previous delivery times.
Include usage for build ingredients: If you manufacture items, you can choose whether the use of ingredient lines in manufacturing is included in the report or not.
Basis of analysis: Enter the number of months of data (form the current date) mSupply should use to calculate your consumption.
Exclude transfers from calculations: Check this box if you wish transfers within your organisation to be excluded from the calculations.
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 “infinite” cover- any stock you do have is going to last you forever
Months stock required: In this field, enter the number of months stock cover you want to have available. The report will automatically take into account any stock you already have, so all you need to do is enter how many months cover you want.
AMC Adjustment for Out of Stock: See the “Quantification” discussion above on the different options available.
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 in mSupply v7.12 or later.
This topic is huge so it may be worth reading this topic Stock wastage prediction for ordering
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.
A typical `Suggested Order` report will look something like below.
Definitions of each column after item properties:
For a more detail guide click on this topic : Stock wastage prediction for ordering.
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. You get an option to price your suggested order.
Key Column headers :
Previous: 13.03. Transaction reports | | Next: 13.05. Requisition reports |