Help Needed: Average Costing Logic – How to Carry Forward Updated Unit Cost
Hi, I have a requirement to calculate the updated unit cost using the following formula and use the resulting value as the current unit cost for the next transaction row:
Updated Unit Cost =
[(Transaction Qty × Transaction Unit Cost) + (Current On-hand Qty × Current Unit Cost)] / New On-hand Qty
This is a rolling calculation where each updated unit cost becomes the base for the next transaction. Here's a sample dataset for context:
Transaction Type | Qty | Unit Cost | Current On-hand | Current Unit Cost | New On-hand | Updated Unit Cost |
---|---|---|---|---|---|---|
PO Receipt | 100 | 10.00 | 100 | 12.00 | 200 | 11.00 |
PO Receipt | 80 | 10.50 | 200 | 11.00 | 280 | 10.875 |
Misc Issue | -10 | 280 | 10.875 | 270 | 10.875 | |
Misc Receipt | 20 | 270 | 10.875 | 290 | 10.875 | |
Sales Order Issue | -40 | 290 | 10.875 | 250 | 10.875 | |
PO Receipt | 150 | 10.00 | 250 | 10.875 | 400 | 10.546 |
How can I implement this logic in