The R365 inventory system uses a usage per $1000 calculation to calculate theoretical on hand quantities and values.
Both usage per $1000 and theo on hand are visible in areas of R365 to serve as guidance for managing items, particularly when it comes to inventory counts and purchase orders.
R365’s usage per $1000 calculation is an approximation of an item’s usage over about 90 days. The calculation uses R365 inventory transactions and POS sales to average historical inventory consumption and predict future inventory item usage.
An item’s usage per $1000 is then used to calculate a reasonable assumption of the quantity of an item that a location has on hand. This is R365’s theoretical on hand amount.
Theoretical on Hand—or Theo on Hand—values represent the quantity of an item that a location should ideally have on hand. This calculated value is based on an item’s transactions since the item’s last inventory count and its system-calculated usage per $1000.
Usage per $1000 and theo on hand are updated each night and an item’s most updated values can be found on the purchased item record. These values are used to suggest quantities on orders, generate inventory quantities, and shown on some reports to make comparisons.
Usage Per $1000
In R365, usage per $1000 refers to the quantity of an item used per $1000 in sales in about a 90 day time frame. It is calculated using the base unit of measure of the item, which is the lowest U of M for any Measure Type (Each, OZ-wt, OZ-fl).
The system uses the following calculation:
In this calculation, to calculate the item usage, the system takes the beginning count quantity of the item, adds and subtracts the quantities based on the item’s activity, then subtracts the ending count quantity of the item. The result represents the total usage for the item during for the 90-day time-frame. This item usage is then divided by the quotient of total sales by 1000.
The variables of the equation can be described as follows:
EndCountQty | The item quantity as of the most recent inventory count. This is the ‘ending count.’ | ||||||||||||||||
StartCountQty | The item quantity from the count approximately 90 days before the date of the count used for the EndCount Quantity. This is the ‘beginning count’.
| ||||||||||||||||
CountToCountTotalInvQty | This is the item quantity when all the inventory transactions between counts have been totaled. This considers all the approved inventory transactions for the item that occur between the day after the StartCountQty date up to and including the day of the EndCountQty. The system assumes that the starting and ending inventory counts occur at the end of the day. Because of this, the item activity from the start date is excluded while the item activity from the end date is included.
The following inventory transactions are used:
| ||||||||||||||||
CountToCountSales | Refers to the total sales made from the date after the beginning count to—and including—the date of the ending count. These sales totals are pulled from all the approved Daily Sales Summaries that occur during the time frame. This CountToCountSales total is then divided by $1000. |
The system then takes the item usage and divides it by the sales to find the item’s actual usage per $1000 in sales.
Usage per $1000 is updated only once at the end of every day. Overnight, R365 will update the usage per $1000 that is shown on the Item Cost tab of the purchased item record.
Theoretical on Hand
R365’s theoretical on hand quantity reflects how much of an item that a location should currently have in stock.
The system uses the following equation:
The theo on hand equation approximates what a location has on hand by totaling quantities from the last inventory count, the item's most updated usage per 1000, and latest inventory transactions since that last count.
The variables of the equation can be described as follows:
EndCountQty | The item quantity as of the last, or most recent inventory count. | ||||||||||||||||||||
UsagePer1000 | The system calculated value for the item’s usage per $1000 in sales across an approximately 90-day time frame. | ||||||||||||||||||||
EndCountToAsOfSales | The total amount of sales since the last inventory count. This total sales is divided by $1000 to utilize the per $1000 structure.
| ||||||||||||||||||||
EndCountToAsOfTotalInvQty | All the item’s activity or approved inventory transactions that have occurred since the last inventory count.
The following item transactions are used:
|
An item’s Theoretical on Hand value is updated only once at the end of every day. Overnight, R365 will update the Theo On Hand quantity that is shown on the Item Cost tab of the purchased item record.
Purchased Item Record
An item’s most updated Usage Per $1000 and Theoretical On Hand values will be shown on the item’s purchased item record. Both values are shown on the Cost Summary card of the Item Cost tab.
These values are updated once each night, overnight. This means that an item’s most updated usage per $1000 or theo on hand will be based on approved item transactions and approved daily sales up to the end of the day prior to present day.
Usage per $1000 and Theo on Hand are location specific. Every location will have its own values for these for an item.
Areas in which Theo On Hand and Usage Per $1000 (also Actual Usage) are surfaced will use the values that appear here.
Using Theo On Hand
Calculations that use Usage Per $1000 or Theo on Hand will utilize the values shown on the purchased item record to determine suggested quantities. This includes the Suggest Qty button on a purchase order and the Generate Qty button on an inventory count.
Purchase Orders - Suggest Qty
Users with create/edit/submit purchase order permissions have the ability to use the Suggest Qty button when filling out an order.
This button generates a suggested quantity to order on a PO. When the Suggest Qty button is clicked, users are prompted to select when the product is going to be used (consumption and buffer days). In this case, the system will use forecasted sales, the item’s usage per $1000, and the theo on hand to determine a quantity suggestion. If there is a day that is forecast to be $0, users will be notified before continuing.
Learn more about entering a purchase order.
Inventory - Generate Qty
In an open inventory count, users with the Generate Using Theo On Hand permission have the ability to generate an inventory count using the theo on hand values generated by R365.
When clicked, all quantity values will be overridden and filled with the item’s theo on hand value. The storage location progress indicators will change to 100% and users can continue to review.
Learn more about generating an inventory count using theo on hand.
Theoretical on Hand Report
The Theoretical on Hand report report shows users how much product is on hand as of the selected date. This report shows the theoretical on hand count as well as the quantities for other transactions that make up the theo on hand. It allows users to select a date parameter to run the report by. The 90 day window starts from the date selected in the As Of parameter.
The theo on hand value on this report may differ from the value shown on the purchased item record. This is because when Today is selected as the As Of date, the report calculates a value that considers approved transactions/counts that have occurred ‘today’ or the day the report is being run.
