Inventory Management Advice

Making better use of the data in your inventory system

Replenishment decisions rely on forecasts of lead times amongst other things. If the mean lead time for an item is not expected to change much then the average of recent lead times for the item can be used. Unless that item is purchased frequently, there won’t be many lead times to use for averaging purposes. Under these circumstances, you might like to find the average of recent lead times for the supplier, excluding items for which the lead times tend to differ substantially from other items obtained from that supplier. There will probably then be sufficient data to, not only obtain a good estimate of the mean lead time, but also to see if there are any trends.

Transaction history held in your computer could be used for the lead time analysis if it is sufficiently reliable. Unfortunately, in most companies, it is not. Many ERP systems do not keep records of order dates and do not match up goods received with the purchase orders. It is essential for the records of order dates to be reliable if they are to be used for lead time calculations. Unless the dates of order placement and of receipt into store are reliable, I suggest that sample data be used. The sample should be small enough to enable it to be checked. Use of a small amount of reliable data is better than use of a large amount of unreliable data.

For the above-mentioned reasons, a spreadsheet analysis is appropriate. I have developed a spreadsheet for this purpose. There are four versions. The appropriate version should be downloaded by clicking on the appropriate link below. When the spreadsheet appears, click on the three dots near the top right hand corner and then click on “Download”. You will probably find that an “Enable editing” button appears, in which case, click on it.

Open document format with UK dates
Open document format with US dates
Excel 97 format with UK dates
Excel 97 format with US dates

These were updated on 19 February 2017.

Almost all modern spreadsheet software should be able to handle the open document format versions. It is recommended that you use that version unless you are using a pre-2013 version of Microsoft Excel, in which case, it is recommended that you use an Excel 97 version of the spreadsheet. All of the spreadsheets were developed using Libre Office.

The yellow cells are where you can enter data. The spreadsheet calculations cannot work with fewer than three rows of data. Some cells will show errors until three rows have been entered. The supplier (Cell B3) is just for your information. The item code (product code) can be entered in Columns A and B respectively for your information. Enter the order date and date received in Columns C and D respectively. It is important for these dates to be correct. In Column F, enter the quantity received in the shipment concerned. There is no need to enter the “order quantity” (Column E) if it is the same as the quantity received.

The “Comment” column (Column I) indicates action which needs to be taken.

The “rejection confidence” (Column H) is an estimate of the confidence with which it can be said that the row concerned contains an incorrect date. If it is greater than 90% then the dates in that row should be checked thoroughly. This situation is indicated by the word “Check” in the “Comment” column (Column I). The most important things to check are that

  • the order date as shown in Column C is correct and
  • the date received as shown in Column D is correct.

Use Column J (“Checked”) to indicate that the data in the row concerned has been checked thoroughly. Any corrections which you make will result in recalculation of all of the rejection confidences. As a result, some more rows might need to be checked. For that reason, it is important for you to use the “Checked” column (Column J) so that you know which rows have already been checked.

The results of the analysis are shown at the top of Columns K to Q. The spreadsheet is concerned with the supplier lead times (i.e. from order placement until receipt) which are only one component of the lead time. The lead time starts when the inventory position of the item concerned falls below the reorder point and ends when the replenishment stock is available for picking. The coefficient of variation is the standard deviation of the supplier lead times divided by the mean supplier lead time. However, when setting safety stocks, it is the standard deviation of the errors in forecasting lead times which should be used. This is higher than the standard deviation of the lead times. Consequently, the number in Cell L5 or Q5 should be used instead of the coefficient of variation in practice. The number in Cell Q5 is for use in relation to items for which the mean lead time has been set manually rather than using the mean supplier lead time in Cell L1. If less historical data is used in manual setting of a lead time than the amount of data in the spreadsheet then the number in Cell Q5 will be lower than it should be. When using the online Monte Carlo simulator which is accessed by means of the “Simulator” tab in the menu, the number in Cell L5 or Q5 as appropriate should be entered in place of the coefficient of variation.

Part 2 of this article will contain information concerning the mathematics involved in the spreadsheet and is intended for readers who have some knowledge of statistical mathematics.