Help Needed: Average Costing Logic – How to Carry Forward Updated Unit Cost — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Help Needed: Average Costing Logic – How to Carry Forward Updated Unit Cost

edited May 14, 2025 7:28AM in Reporting and Analytics for SCM 1 comment

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

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!