Fill Rate Calculations

MITS uses fill rate calculations in new versions of it's SALES cubes. The details of each ERP's implementation are different, but follow a similar formula. The following article will discuss how we define these calculations more generally.

Let's start by discussing the "Line Fill Rate." The line fill rate is designed to answer the question "What percentage of invoice lines are filled completely on the first attempt at shipping that line." There are a few calculations that go creating an accurate line count so we can answer this question.

We check this in two steps and derive a new line count at each point. The first step is to determine which invoice lines represent lines that require moving stock out of inventory. We rely on the flags set in your ERP to make this determination. We typically exclude:

1. Anything defined as a non-stock or direct ship
2. Anything defined as a credit or return
3. Anything with a 0 for quantity, or other miscellaneous charge items

After subtracting these, we create a new column we refer to as the "Stock Line Count". If you compare this to the "Line Count" column you'll notice the "Stock Line Count" will be slightly lower, or much lower, depending on how many of your invoice lines fit the criteria.

There's one more step required to correctly calculate fill rate, and that is to remove secondary attempts at shipping a line. The reason for this is that if a customer places an order with, for example, 10 units of product ABC, and 5 units of ABC are shipped on the first attempt, and 5 more units are shipped on the second attempt, we want the fill rate to be 0%, and not 50%. Most ERPs generate subsequent "sequence" or "suffix" numbers for those shipments. The logic varies by ERP, and most ERP reports do not make this distinction. MITS does not include those additional shipping attempts in it's calculation.

The number of lines remaining after this second exclusion can be found in the "First Stock Line Count." This is a hidden audit column in most implementations. If so, it can be added to report by directly editing the MQL to include FIRST.STOCK.INVOICE.LINE.CNT.M-1 (An example of the total first stock lines for last month).

Now that we have a base number to use we can bring up the columns used in the "Line Fill Rate" calculation. The "Shipped in Full Line Count" contains only lines from the "First Stock Line Count" that have a shipped quantity equal to the ordered quantity. If you divide the "Shipped in Full Line Count" by the "First Stock Line Count" the number will match the "Line Fill Rate".

MITS also has a configuration set when the cube was deployed for calculating whether these lines are on time or not. Typical options would be "Promised Date" or "Required Date", but vary by ERP system. We then remove any lines not shipped on or before that date which gives us the "Shipped in Full On-Time Line Count". Divide that by the "First Stock Line Count" and you'll get the "Shipped in Full On-Time Fill Rate."

A report of last months totals at the company level can be seen by pasting this into the Options --> EDIT MQL window: FLASH SALES LINE.CNT.M-1 STOCK.INVOICE.LINE.CNT.M-1 FIRST.STOCK.INVOICE.LINE.CNT.M-1 FULL.FIRST.INVOICE.LINE.CNT.M-1 FULL.INVOICE.LINE.PCT.M-1 FULL.ONTIME.FIRST.INVOICE.LINE.CNT.M-1 FULL.ONTIME.INVOICE.LINE.PCT.M-1

The order fill rate columns follow the same outline, but require that all lines of an order fulfill these requirements to count as full or ontime.

To view these columns at the order level, past this into the Options --> EDIT MQL window: FLASH SALES INVOICE.CNT.M-1 STOCK.INVOICE.CNT.M-1 FIRST.STOCK.INVOICE.CNT.M-1 FULL.INVOICE.CNT.M-1 FULL.ORDER.PCT.M-1 FULL.ONTIME.INVOICE.CNT.M-1 FULL.ONTIME.ORDER.PCT.M-1

Line level

Order level

The preceding three lines would produce:
Line fill rate - 100% (2/2)
Order fill rate = 0% (0/1)