Item Quantity Fields
There are a number of quantity related fields used for items. These are related to stock management and purchasing primarily. Some are set manually during item setup, others are calculated based upon the activity of the item.
On-hand is the total quantity that your company should have in stock for an item. It is not necessarily the amount available for sale because some of the on-hand may be committed to customer's orders or manufacturing work orders. Many things can and do update on-hand such as sales, returns, quantity adjustments, receiving inventory, physical inventory, manufacturing, and transfers between locations. The database figure for on-hand may not match the physical quantity on-hand for a number of reasons (human error, theft, damage, etc.). If this occurs, it's necessary to correct the figure using some form of adjustment. On-hand quantities can become negative if more of a material is used than the database has a record of.
Committed quantity is a branch specific quantity figure. It is gathered from unsold quantities for items ordered on open customer orders, items committed to open work orders (manufacturing), and pending or in-progress branch transfers. Committed quantity is reduced as items are sold from orders, after completion of manufacturing, or once transfer processing is completed. Committed quantity is not used directly for the purposes of automated PO builds. "Due to be Sold" is used for purchase order builds. Committed quantities are not impacted by negative quantities since the reason for most returns is due to damaged products, wrong color, etc.
The available quantity is the amount of an item that is in stock and not previously committed to other customers (or manufacturing/branch transfers). It is calculated using the on-hand less the committed figure.
Due to be sold is related to committed quantity. It is calculated based upon the current date, open customer orders for the branch, and the item's settings for days supply. The purpose of this figure is to provide a quantity that shows the amount of the item that is due to be delivered to customers within a specific date period. The date period is calculated based upon the current date and the item's days supply figure. For example, if today is June 6 and the item's day's supply is set to 14, the end of the "due to be sold" period would be June 20th. The quantity reported represents the unsold quantity committed to customer's orders that are due to be delivered prior to the determined period's end date (June 20th in the case of our example). Typically, this figure is used in purchasing to assist the purchaser with regard to ordering decisions.
The on-order quantity is determined based upon the quantities remaining as ordered on all open purchase orders --and-- any pending transfers for the branch. On-order quantities consider branch allocations on purchase orders (consolidated). If a consolidated purchase order exists, the allocations for the item are split out by branch. On-order quantities are not impacted by negative quantities since the reason for most returns is due to damaged products, wrong color, etc.
This figure is related to on-order. It's calculated based upon the current date, purchase order data, and item settings during purchasing. The purpose of the "due to arrive" figure is to ascertain the quantity expected to be delivered (received) from purchase orders within a time frame based upon the item's lead time and the current date. The following explains the conditions applied during the calculation of this quantity:
•Only open Purchase Orders are considered (not closed).
•Both regular and consolidated Purchase Orders for the current branch are included. Branch allocations on purchase orders are not split out by branch, so if consolidated purchasing is done, the ordering branch would show the quantity due to arrive on the consolidated purchase order. Again, these amounts won't appear for each branch.
•Only Purchase Orders with a status of "ordered" are considered (Purchase Orders can be just "saved" and not ordered).
•The item must be open on the purchase order (not received).
•The "due to arrive" quantity is set if to match the quantity of the item ordered on purchase orders with an "expected/due" date that falls within a period of time based on the current date adjusted for the item's lead time. If the item has a lead time setting of zero, the lead time is assumed to be 7 days.
In summary, the "due to arrive" is the quantity of the item that is expected to be delivered within the ordering period for that item if it were to be ordered today. This figure is calculated at the time items are being ordered and only appears in the Consolidated activity in Purchasing.
Order quantities are either manually set or they may be calculated when doing an automated purchase order build. Please see the topic Purchase Order Builds for more details.
For an item to be included on a build, the software calculates the short term available quantity and then determines if that quantity is above or below the item's minimum. By "short term," we mean that the quantities due to be sold within the item's days supply (aka. "Due to be Sold") and the quantities due to be received within the item's lead time (aka. "Due to Arrive"). The item's overall committed and on-order quantities are not considered directly because they may not reflect an immediate need to replenish stock.
Here's a breakdown of how the calculation works:
First, the software decides if the item should be ordered:
If the on-hand plus "due to arrive" minus "due to be sold" is less than (<) the item's minimum, the item is ordered.
On-hand - Due to Arrive (POQDue) + Due to be Sold (COQDue) < Minimum
If not, the item is not ordered.
POQDue and COQDue are the database field names used for "due to arrive" and "due to be sold."
In cases where an item has a zero minimum but has a maximum quantity, the minimum is considered a mistake and is modified automatically to a quantity of one (1). Items with a minimum of zero and a maximum of zero will not be included on automated builds.
Next, if the item is to be ordered, the quantity to order is calculated:
Order quantity is calculated using the item's maximum quantity using the following formula:
Maximum - (On-hand + Due to Arrive - Due to be Sold) = Order Quantity
Finally, the item's vendor purchasing settings are considered:
Settings such as Order U/M-Package Quantity, Order Multiple, and Order Break quantity are considered at this time. This can cause the prior "order quantity" to be adjusted either up or down.
Minimum
The intended purpose of the minimum quantity is to serve as the re-order point for the item. When quantities drop below the minimum, the item should be ordered. Items with a minimum of zero but a maximum figure above zero are considered to have a minimum quantity of 1 instead. Items with a zero minimum and maximum won't be included on purchase order builds. Minimum can be set to be a percentage of the Maximum or a static number that's manually set.
Maximum
The intended purpose of the maximum quantity is to be the restock or shelf quantity for the item that should be maintained. Maximum quantity is primarily used with automated purchase order builds. In this case, it's used to determine the quantity needed. Maximum can be set to be calculated based on either the days supply or usage interval item settings. The Adjustments area in Purchasing must be used to process changes to the maximum quantity based on these calculations.
Average Daily Usage
This figure reflects the daily average quantity used over a period of time (usually a month). It's calculated by taking the total usage for the month and dividing that figure by the number of days in the month.
(Monthly Usage) ÷ (# of Days in Month)
When the period includes more than one month, the days remaining in each of the months would be used in the same calculation. Often the starting date is based on the current date less one year. This date won't necessarily include the entire month.
(Days Used in Month #1 * Average Usage for Month #1) + (Days Used in Month #2 * Average Usage for Month #2)
(Days Used in Month #1 + Days Used in Month #2)
As long as the usage period being considered wouldn't pass the end of the starting month, the average usage for the period would be the same as the average usage for the month.
Desired Days Supply
Days supply is the anticipated number of days that an item should be stocked for when using automated ordering (PO builds). It is sometimes used when determining the usage period for calculating daily average usage.
For example, if this number is 30, a purchase build will consider the quantity necessary to maintain a supply for that period of time based upon the quantity committed during that time. What this means literally, is that the day's supply determines the COQDue (Customer Order Quantity Due) figure used when calculating the quantity to order. The "COQDue" figure considers customer orders, manufacturing, and pending transfers, not just customer orders. "Committed" quantity is not used for build calculations. A seven (7) day period is used for calculating COQDue if the days supply figure is zero.
Days supply may also be used to recalculate the maximum quantity when its adjustment type is set to "system automated adjustment." This is only true if no Usage Interval is set (see below). The "Adjustments" transaction on the Purchasing menu must be used to apply "automated" adjustments to items. No default is used for when calculating average usage if neither the days supply nor usage interval has been set (if both are zero).
Usage Interval
This is an item setting that is used for calculating a new maximum when the System Automatic Adjustment option is used for the item. If a usage interval is entered (above zero), it will be used in place of days supply to calculate the average daily usage used for automatic adjustments made to maximum quantities. As long as the maximum's adjustment type is set to "system automatic adjustment," the Adjustments activity located under Purchasing can be used to update the maximum and other purchase related settings when needed. Although it may sometimes make sense to use a specific period for calculating a new maximum, it shouldn't be set too high. Seasonal changes in usage wouldn't really be considered appropriately and you lose some of the benefits of an automatic calculation.
For example, if this figure is set to 60 days and the current date is June 23rd, the prior year period used for determining average usage would be June 23rd - 30th (7 days), all of July (31 days), and the first 22 days of August.
Over/Under
This figure is calculated during a purchase order build as the difference between your item's maximum quantity and on-hand (plus "due to arrive" and less the "due to be sold"). For example, if you are under-stocked, the on-hand is less than the minimum. If you are overstocked, the on-hand is greater than the maximum.
Physical Count
Physical Count is the cumulative quantity counted during physical inventory. Until counts are posted (finalized), counts taken at different times accumulate in the physical count cell. At time of posting, counts are compared to the item's on-hand adjusted for any activity and an adjustment is made to correct any discrepancy (difference).
Lead Time
This is the length of time in days between when an order for a product is placed and the receipt of those goods from the vendor. For example, a lead time of 7 indicates that the item would usually be received within one week (7 days) of ordering. This figure may be either automatically calculated based on the last 4 receipts for the item or manually set. If lead time is being done as an automatic calculation, the figure is recalculated each time the item is received as long as a purchase order was used.