Monday, February 25, 2019

Saved Search to Calculate Fill Rate Percentage on Orders Placed by Customers


As an overview; Fill Rate Percentage is a logistics measure of inventory effectiveness in meeting demands.  The latter is calculated as the total units in stock and available against the total units ordered by customers over a period of time.

Currently, there is no standard report that provides this information but the above data can be calculated by creating a custom Transaction Saved Search.

Here are the steps: 

-Navigate to Reports>Saved Searches>All Saved Searches>New

-Select Search Type = Transaction

-Navigate to Criteria tab>Standard subtab>Filter column and add the following filters:

a. Type = is Sales Order

b. Account Type = is Income

c. Main Line = is false

d. Tax Line = is false

e. Shipping Line = is false

f. Status = none of; Sales Order: Billed, Sales Order: Cancelled, Sales Order: Closed, Sales Oder: Pending Approval, Sales Order: Pending Billing

g. Date = is within today

-Navigate to Results tab>Columns subtab>Field column and add:

Field: Formula (Percent) | Summary Type: Sum | Formula: round(sum(nvl({quantitycommitted},0))/nullif(sum(nvl({quantity},0) - nvl({quantityshiprecv},0)),0),4)

-Hit Save & Run button


-If you want to calculate the Fill Rate % per Item or per Customer; navigate to Results tab>Columns subtab>Field column and add Item or Customer, set the Summary Type: Group under Results tab>Sort By: select the latter field added (either Item or Customer if calculation will be based by customer)

-Take note that since there is no field available that can be pulled through a Saved Search that stores historical data pertaining to either the Quantity Back Ordered or Quantity Committed of an Item; the system can only calculate the Fill Rate % relative to the current date.  This is the reason why in the search a filter is set for the Date field: is within today

No comments:

Post a Comment