13.04. Purchasing reports

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.

Types of report

There are seven reports currently available:

  • The Ordered Item Report will list items that are on Purchase Orders according to criteria specified.
  • The Goods Received date vs Order date report lists each Goods Receipt for a particular Purchase Order and the number of lines received, then follows with details of the number of lines and date of the original Purchase Order, as in the example below. This is useful for tracking supplier delivery performance.

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.

  • The Goods Received date vs Order date - Show items report.
  • The Outstanding Purchase Order Lines report, which will display all Purchase Order lines for Purchase Orders provided that
    (1.) delivery is incomplete, and
    (2.) the Purchase Order has a Confirmed (“cn”) status.
  • Note that Purchase Orders whose status is Finalised (“fn”) will be ignored when producing this particular report.
  • The Goods Received date vs Order date - show Delivery days report shows the number of days taken to deliver items.
  • The Purchase order vs goods received broken down by batches report shows the dates particular batches were received.
  • The Purchase order cashflow planning report shows you the values and times of expected deliveries so that you can plan when money needs to be available to pay for them.
Date range and date type

A date option, for selecting which items/purchase orders to include in the report must be selected; options are:

  • creation date - the date purchase orders were created
  • sent date - the date purchase orders were confirmed
  • expected delivery - the expected date of delivery for items
  • actual delivery - the actual date that items were delivered

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.

Report on names...

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.

Report on items...

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.

Report on purchase orders...

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:

  • The date range of transactions on which to report
  • Four filters are available to refine the report - a name filter, and three item filters; all are optionally and will be ignored if nothing is entered in the textbox or drop down lists are left at “Don't care”.
  • The value of a transaction line above which the report will cover.

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.

The logic behind the calculations involved in this report is described in detail on the 6.01. Ordering stock from suppliers page.

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.

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.

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.

Important Columns and their meaning :

  • Stock on hand : This is your stock including expired or potentially un-usable stock.
  • Expiring stock : mSupply looks into the future and predicts the quantity of stock that is likely to be wasted.
  • Effective SOH : This is the Effective stock on hand. i.e `Stock on hand` - `Expiring stock`
  • Effective Months of Stock : This is the months cover calculated using the `Effective SOH` value.
  • Suggested order : This is the suggested order and will depend on `Effective SOH` and `Effective Months of Stock`

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 :

  • 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.

  • Last modified: 2024/02/16 11:33
  • by Ujwal Khatry