The goal of this specification is to describe how to add support for average costing to xTupleERP. Currently xTupleERP supports standard and job costing which are typically good options for manufacturers. However, average costing can be a better fit for distributors and light manufacturers who require a lower maintenance solution. By definition, average cost inventory will not require periodic cost recalculations so maintenance of these items should be lower than standard cost items.
This Average Costing implementation will feature the following:
- The ability to define a system default for cost method
- The ability to specify costing method by item site
- Job (Job Items Only)
- Negative on hand balances will not be allowed for average cost item sites
- Inbound inventory transactions will debit inventory a value equal to the cost of the source transaction.
- Purchase Orders will use Purchase Order Price
- Work Orders will use cost allocation method used by Job items to calculate receipt values
- Inbound transfer orders will reference outbound transaction costs
- Adjustments in will be at average cost, unless an over-ride cost is specified
- Material Receipts will be at average cost, unless an over-ride cost is specified
- Transformations will apply the source item cost as the target item cost
- Outbound inventory transactions will credit inventory by an average value
- Transaction value = Inventory Value / Quantity on Hand * Quantity Transacted
- Inventory History will include cost method and before and after inventory values
- Quantity on Hand reports will support mixed costing
- New "Posted Value" option will reflect inventory cost value based on method
- New column will be added to show cost method
New Terms and Definitions
- The method that determines costing calculation used to value inventory transactions
- Costing where cost distribution is based on an average of all costs accumulated
- Costing where cost distribution is determined by actual accumulation of costs on a work order. Currently the cost method used by Job Items.
Related Existing Functionality
xTupleERP currently supports three cost valuations methods Actual, Standard, and Job. Actual costs are a value calculated on the last cost of an item. Standard and Job costing are used to post inventory transaction values to the general ledger.
As of 3.0 costing method already exists as a combo box on the itemsite.ui file. However, it is being hidden since the supporting functionality does not exist yet.
Similar and Related Requests
There is a lengthy discussion topic on weighted average costing on the xTuple forums here.
6789 Add average costing as a cost method (The issue driving this specification).
3600 "Average Cost" Posting when Vouchering.
Currently standard cost items are allowed to drop to negative quantities and values on hand. Negative quantities and value for average cost items presents some difficult problems. To avoid these problems, the sponsor of this feature has requested that we disallow negative quantities for average cost items. This behavior will work well for the task at hand, but will introduce inconsistent behavior if users have items with both types of costing at the same site.
Job costing is currently defined at the item level by the item type "Job." This specification calls for costing to be defined at the item site level. In order to support cost control at the site level, a cost method of "Job" will have to be made there, and "Job" costing will have to be hard coded as a cost method exclusively tied to Job items.
The view costs privilege may be problematic here because some transactions will give users the option enter a cost of the item, even though they should not be granted the privilege to view or maintain standard costs.
Users will be able to define whether an item uses "standard" or "average" costing at the item site level. If average cost is chosen cost calculations and valuations are handled automatically by the user with no further intervention. The source cost of an inbound average cost transaction will be added to a perpetual inventory value maintained by item site. The average unit cost of item will be the the total perpetual value divided by the current quantity on hand. Negative quantities on hand will not be allowed for average cost items.
Updates to standard cost will behave as before, except that no G/L transaction will be recorded for item sites flagged as average cost. Standard item cost is strictly informational relative to average cost item sites. If the cost method is changed from average to standard on an item site, the perpetual inventory value of the item will be adjusted to be equal to the quantity on hand multiplied by the current standard cost, and a corresponding general ledger transaction will be recorded against the designated inventory variance account to capture the change in value. A change from standard to average costing will cause no G/L transaction.
Set Up Changes
UI files used to generate mock ups pictured below may be downloaded on this page.
The Inventory Configuration window will be modified to allow users to specify which cost methods are available.
At least one of Average and Standard cost must be selected. If any item site records exist flagged to use either Standard or Average cost, those respective check boxes will be flagged as checked and disabled. A Job cost option will available as a place holder for a time when job costing can be separated from the Job item type. In the mean time Job will be checked and disabled at all times. An upgrade script will be created that sets all upgraded databases to have Standard and Job costing flagged as true, and Average costing flagged as false.
The item site window will be modified to display a Costing Method combo box beneath control method as pictured:
The costing method combo box should include the following values: Average, Standard, Job, and None. The following rules apply:
- 'None' is only to be the only costing method available when the control method is 'None,' or the item type is Reference.
- 'Job' is only to be the only costing method available when the item type is 'Job.'
- All other item type and control method combinations should allow the user to select 'Average' or 'Standard' as the Cost method per the allowed options checked in Inventory Configuration. This selection can be changed at any time, however, an item site may not be changed to average costing if the quantity on hand is negative.
All existing databases should be upgraded so that item sites with control method of none have costing of 'None,' all item sites for Reference items should be set to costing method of 'None,' all Job items should be set to 'Job' costing and finally all other items sites should be set to 'Standard' costing.
All transactions that require specific changes are listed below. Transactions that are not listed require no modification and will default to the average cost calculation used by average cost items when no specific cost value is passed to the postinvtrans function.
Inventory Adjustment and Material Receipt
Inventory adjustment and Material Receipt must be modified so that they can optionally accept a cost for inbound transactions.
Notes will be moved to the second page of a tab widget.
The first page will be a cost tab, which will only be visible if average costing is enabled. It will only be enabled when the item site selected is average cost. By default, the "Adjust value" check on the group box pictured will be checked, in which case the user can select the value to be calculated or manual with calculated as the default. If calculated, the system will use the average cost calculation. If manual is selected, the user must enter a total cost for the transaction by which inventory value will be adjusted up or down depending on whether the quantity is positive or negative. If Adjust value is unchecked changes to quantity will not affect perpetual inventory value or the general ledger.
Transform and Warehouse Transfer
Transform transactions will use the average cost calculation of the outbound item to value both the source and target item site costs. For transform items if the source is an average cost item and the target is standard cost, the difference will be charged to the designated transform clearing is it currently is. If the same situation exists with warehouse transfers, the difference will be charged to the designated inventory variance account.
Purchase Receipts for average cost item sites will be valued based on the purchase price.
Receipt corrections will calculate a proportional value to reverse from inventory based on the original receipt value.
Purchase Returns will use an average of receipt values to calculate return values.
Inventory costs for average cost item sites would behave as follows:
Material returns will use an average issue cost based on values posted in the womatlpost table to determine value.
- When "To-Date" is selected on a Work Order all the costs accumulated against the work order should be debited to inventory and credited to WIP.
When "Proportional" is selected costs are recognize in proportion with the total costs posted and total items received to date. For an example of how this see the "Get Work Order Info" section of issuetoshipping.sql
When the work order is closed, any remaining WIP value is debited to inventory variance, the same as standard costing.
Correct Production will credit inventory and debit WIP based on an average calculated by: (Work Order Posted Value - WIP Value) / Qty Received * Quantity Corrected.
Misc Production will debit inventory for the value of all materials and labor back flushed against the transaction.
Work Order production posts will debit inventory using a value that is calculated the same way as Job Items, which is determined by a selection in a group box on the work order that is currently titled "Cost of Sales Recognition." The group box should be relabeled to use the more general term "Cost Recognition" and appear on work orders for average cost item sites as well as Job items. There are two cost recognition selections: "To Date" and "Proportional." The default for this selection is set in Manufacture Configuration under the group "Job Item COS Recognition Defaults" which should be re-titled "Cost Recognition Defaults."
Quantity On Hand
The quantity on hand by parameter and item reports will now have a new default cost option for "Posted Value."
When Show Inventory Value is selected and Posted Costs is the selected cost option, the report will show use the inventory value stored in the item site table as the basis for inventory value, and the unit cost will be calculated as that value divided by quantity on hand. A new column will be visible that shows the cost method: Standard or Average. If either of the other two cost methods are selected, the report will run exactly as before and the cost method column will be hidden.
The printed version of the report should be modified to display the same information.
The inventory history displays and reports should display three new columns: The cost method, the before value and after value of inventory.
Problems and Alternatives
There is a question of whether adjustments should generally change the value of inventory or not. If a user ordered, received and paid a $1,000 for a 1,000 widgets, then later came to find they were accidentally over shipped 50 widgets, if they make an adjustment should the inventory value increase $50? The adjustment window gives the user an option on how to handle this question. The physical count system, however, will always revalue the inventory when adjustments are made based on the average cost. Forcing users to make choices about inventory valuation for physical counts is likely to make the system overly complicated to manage. We could make a global setting for this behavior, but things still get ugly if inventory is completely zeroed out, but you aren't changing value. For this reason, it is probably best to assume value should change when physical count adjustments occur.
As mentioned in the Conflicts section, the notion of adding the ability to record negative quantities on hand for standard cost items, and disallowing this for average cost items is inconsistent. Ideally, a global setting could be created to allow both to work either way. However, this would add a significant amount of work to this development that goes beyond the scope of the requirements.
A new average cost function will be introduced called avgcost. When passed an item site Id, it will calculate and return the average cost for that item site.
Average Cost Function Get Itemsite Value If Itemsite Value is zero return zero Else return itemsite_value / itemsite_qtyonhand
The postinvtrans function will be modified to handle both an overload cost value and process its own valuation transaction. The overload cost will be fed to it by transactions that must calculate cost from external sources, such a P/O receipt value for example. Other transactions will expect the cost to be calculated automatically, such as an issue to shipping transaction. In those cases a null value for cost will be passed and the transaction will default to the value returned from the avgcost function. The overload cost value will be ignored if the item site is using Standard Cost.
Algorithm changes to the postInvTrans() function: -- On SQL where itemsite info is gathered and we get cost this way: SELECT CASE WHEN (itemsite_cost = 'S') THEN stdcost(itemsite) WHEN (itemsite_cost = 'A') COALESCE((Cost passed as parameter/ Trans. Qty.), avgcost(itemsite)) END AS cost, itemsite_costmethod, itemsite_value.... -- Check for negative ...If cost is average and after qty value is negative return -2.... -- Where inventory history is inserted For "unit cost" use _r.cost (defined above) Add "cost type" as _r.costmethod Add "previous value" as _r.itemsite_value Add "after value" as _r.cost * pQty * _sense
The PostInvHist(int) function will be modified to update the item site value based on unit_cost * qty on the inventory history record.
The Item Site table
The itemsite table should be altered to include cost method and a value that stores the perpetual value of the item site:
ALTER TABLE itemsite ADD COLUMN itemsite_costmethod CHAR(1) CHECK (itemsite_costmethod IN ('N','A','S','J')); ALTER TABLE itemsite ADD COLUMN itemsite_value numeric(12, 2);
The costing methods are limited to the four possible combinations: N=None, A=Average, S=Standard and J=Job. Legacy data should be populated according the rules described at the itemsite window.
Since all controlled and non-reference inventory items are currently standard cost, legacy data can be populated by simply multiplying the quantit on hand by standard cost. Reference and non-controlled itemsites would be populated with a value of zero. Once legacy data has been populated into itemsite_value, the itemsite_value column should be set to NOT NULL.
The invhist table should be altered to include values that record before and after value history:
ALTER TABLE invhist ADD COLUMN invhist_costmethod ALTER TABLE invhist ADD COLUMN invhist_value_before numeric(12, 2); ALTER TABLE invhist ADD COLUMN invhist_value_after numeric(12, 2);
Since all inventory items are standard cost, legacy data can be populated by simply multiplying the quantity by the unit cost column. Once legacy data has been populated into the new value columns, they should be set to NOT NULL.
Note the value columns in both tables are limited to 2 decimal places. This is designed to match ledger transactions.
Stored Procedure Changes
The following functions will need to be changed to support average costing. Each function needs to be expanded to be able to calculate or accept source cost data to pass to the postinvtrans function as described in Transaction Changes:
These additional functions will be added or modified as described in detail in Algorithms:
The great standout on this functional addition is the necessity of preventing average cost item sites from running into negative quantities. Errors can be trapped a number of ways, but there are trade offs involved. If trapped at its lowest level with table triggers the application is least likely to allow corrupt data, but error messages returned to users will be cryptic and impossible to translate to other languages. If checked pro-actively at the client application level error messages will be refined, translatable and decipherable to users, but this will require a great deal of work to develop and it will be more prone to developer oversight. A hybrid approach to this problem is probably best.
- A low level check should be imposed as a trigger on the itemsite and invhist tables that prevents a negative quantity on hand quantity to be recorded when cost on the item site is average.
The first method protects the database, but would lead to generally cryptic error messages. PostInvTrans returns -2 when the balance of an inventory transaction would result in a negative balance. Translatable entries should be made in the Stored Procedure lookup file for transactions errors of this type. (-2 may not be an appropriate error number depending whether it has already been used. Any number will work).
- Using the above method would be frustrating for users issuing batch quantities, because on a large batch issue that encountered an insufficient quantity error they would not know by the error message which item or items are short. Issue to Work Order by batch, Issue All in the Issue to Stock screen, and the backflush functions in Post Production and Post Operation should pro-actively check for sufficient quantities and tell the user which items, if any, have insufficient stock. This could be handled by concatenating a list of problem items and populating that concatenated list into an error message.
All inventory transactions should be tested for expected results in inventory history records, on hand values and G/L transactions.
Standard documentation update applies for item site and all the affected transactions. Much of this document can be used for reference documentation.
At this point a doc topic on costing is probably in order to address the different methods, their respective impacts and limitations.
This functionality is scheduled for release in version 3.1 of xTupleERP. It must be completed in its entirety. It should have almost no impact on legacy users.