A number of ERPs have the option run weighted average costing at a Stock Item level. While Acumatica has an average cost on the price/cost tab of a stock item, cost of sales on invoicing and stock valuation uses Item Warehouse average cost or Location average cost (if costing at location level). Is there a solution to achieve Item level costing?
I am considering running standard cost and using a business event triggered by IN Receipts to recalculate a stock level average cost; update the Pending cost ;and then use an import scenario to run the Update Standard cost process. What I need is a GI that does the right calculation. Unfortunately when standard cost is used the average cost stored on a stock item is the standard cost and the actual average cost is not recorded. If any one has tried and would share the GI, this would be appreciate.
Stock Item Level Average Costing
Best answer by chameera71
Achieving Item-level costing in Acumatica, where you calculate and apply a weighted average cost at the Stock Item level (instead of the default Item Warehouse or Location levels), presents some challenges. While Acumatica doesn't natively support weighted average costing at the stock item level, your approach using Standard Cost and a combination of Business Events and Generic Inquiries (GIs) is a creative way to work around it.
Challenges with the Standard Cost Approach:
- When using Standard Cost, the system stores the standard cost as the item's cost, and the actual average cost at the stock item level isn't recorded directly. This means that recalculating an accurate stock-level average cost can be tricky, but not impossible with some customizations.
Here’s a more detailed breakdown of how you can proceed and the challenges you'll face:
Approach Overview:
-
Run Weighted Average Cost Calculation: You plan to calculate a stock-level average cost based on IN Receipts (or another stock activity) and trigger updates using Business Events.
-
Update Pending Cost: Use the calculated average cost to update the Pending Standard Cost for the stock item via a business event.
-
Update Standard Cost Process: Use an Import Scenario to automate the Update Standard Cost process after recalculating the weighted average cost.
Steps for Implementation:
1. Business Event Triggering on IN Receipts:
- You can create a Business Event that triggers whenever an IN Receipt is processed. This will allow you to recalculate the average cost at the stock item level whenever inventory is received or adjusted.
2. Generic Inquiry (GI) to Calculate Weighted Average Cost:
- You need a GI to calculate the weighted average cost of all received quantities for the stock item.
- The GI should sum up the quantities and costs from all relevant transactions and divide the total cost by the total quantity to compute the average.
Here's a high-level idea of what this GI might look like:
- Table:
INTran
(Inventory Transactions) - Fields:
INTran.InventoryID
INTran.Qty
INTran.TranCost
INTran.TranType
(to filter out non-receipt types)
- Filters:
- Exclude transaction types that don't affect inventory receipts (e.g., adjustments, transfers).
- Filter by
InventoryID
to isolate transactions for the specific stock item.
GI SQL-Like Logic:
sql
Copy code
SELECT InventoryID, SUM(Qty) as TotalQuantity, SUM(TranCost) as TotalCost, (SUM(TranCost) / NULLIF(SUM(Qty), 0)) as WeightedAverageCost FROM INTran WHERE TranType = 'Receipt' -- Or relevant transaction types GROUP BY InventoryID
This GI will calculate the weighted average cost for each InventoryID
(Stock Item), which can then be used for updating the Pending Standard Cost.
3. Recalculating the Stock-Level Average Cost:
- Once the GI has the necessary data (total quantity and total cost), use the result to update the Pending Standard Cost field in Acumatica for the relevant stock items.
- This can be automated through a business event that captures the recalculated average cost and updates the pending cost fields for the stock item.
4. Updating Standard Cost via Import Scenario:
- After recalculating and updating the Pending Standard Cost, you can run the Update Standard Cost process to apply the new average cost as the stock item's Standard Cost.
- You can use an Import Scenario to automatically trigger the Update Standard Cost process after the cost is updated via the business event.
Considerations:
-
Cost Rounding and Accuracy: Depending on the volume of transactions, rounding errors can sometimes occur in average cost calculations, so ensure that your GI and recalculation logic accounts for that.
-
Performance: Depending on the volume of transactions processed, running a weighted average cost recalculation frequently might impact system performance, so you might want to carefully choose how often the recalculation occurs (e.g., on every receipt, daily, etc.).
-
Pending Cost Visibility: Ensure that users have visibility into the pending cost values before they're applied, in case any manual adjustments need to be made.
-
Historical Transactions: If you need to account for historical transactions, ensure that the GI looks at past receipts, or consider filtering by specific time frames.
Example of the Generic Inquiry Structure:
- Tables:
INTran
,InventoryItem
- Fields:
INTran.InventoryID
INTran.Qty
INTran.TranCost
InventoryItem.Descr
INTran.TranType
- Aggregations:
SUM(INTran.Qty)
for total quantitySUM(INTran.TranCost)
for total costSUM(INTran.TranCost) / SUM(INTran.Qty)
for weighted average cost
Once the GI is in place and providing the correct weighted average cost calculation, you can integrate it into your process for updating the Pending Standard Cost and running the standard cost update process.
Conclusion:
Your idea of using Standard Costing and calculating a custom stock-level average cost with a GI is a solid approach. If you can fine-tune the Generic Inquiry and link it with a business event to update the standard cost automatically, you should be able to achieve item-level costing close to the weighted average method.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.