Formula of cost amount adjustment

(1) Share

Report

Posted on by Phuong Pham

I have a question about the weighted average costing method. How about AX calculate the weighted average for an item? How is the closing inventory for weighted average cost item? In my case, P.O received but not invoiced and our formula for cost price = Physical amount/ Physical Qty. I can’t understand the formula of cost amount adjustment when I run re-calculation.
Help me!
Please.

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: Formula of cost amount adjustment AX 2012 R3

Hi,

Probably take a look at the following site that describes the costing issue in detail.

fedotenko.info

Hope this helps.

Best regards,

Ludwig

Was this reply helpful? Yes No

Hien Hoang

40 on at

Like (0)

Report

RE: Formula of cost amount adjustment AX 2012 R3

Thanks Ludwig,

My test case:

As you can see, this Item model group is called WA with Inventory Model: Weight Average.

Cost price: Checkbox Include Physical Value.

I tested with Item Number "test_5" and my result:

Item	Q.ty	Type	Unit price	Physical cost amount	Physical date	Financial cost amount	Financial date	Adjustment
test_5	100.00	Receipt	9,000.00	900,000.00	5/19/2017
test_5	-50.00	Sold		-450,000.00	5/20/2017	-450,000.00	5/20/2017	-100,000.00
test_5	50.00	Receipt	10,000.00	500,000.00	5/21/2017
test_5	-70.00	Sold		-665,000.00	5/23/2017	-665,000.00	5/23/2017
test_5	40.00	Purchased	11,000.00	440,000.00	5/25/2017	440,000.00	5/25/2017

when P.O received but not Invoiced, I have only Physical cost amount, Financial amount = 0.

5/31/2017, I run recalculation (Inventory management/ Periodic/ Closing and Adjustment) and I received a Voucher that have adjustment amount = 100,000. It allocation for the first S.O

I can't understand the logic to get the item cost price in AX when adjustment.

As adjustment amount that Ax calculation, Total Cost of goods in S.O = 450,000+100,000+665,000= 1,215,000

Formula of cost price in my company: (900,000+500,000+440,000)/(100+50+40)= 9,684.21

Q.ty sold = 50+70=120 >> Total Cost = 9,684.21*120= 1,162,105.26.

Could you tell me the resolve for my issue.

Item number	Warehouse	Site	Quantity	Unit price	Physical cost amount	Physical date	Financial cost amount	Financial date	Adjustment
test_5	K0001	M&E	100.00	9,000.00	900,000.00	5/19/2017
test_5	K0001	M&E	-50.00		-450,000.00	5/20/2017	-450,000.00	5/20/2017	-100,000.00
test_5	K0001	M&E	50.00	10,000.00	500,000.00	5/21/2017
test_5	K0001	M&E	-70.00		-665,000.00	5/23/2017	-665,000.00	5/23/2017
test_5	K0001	M&E	40.00	11,000.00	440,000.00	5/25/2017	440,000.00	5/25/2017

Was this reply helpful? Yes No

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: Formula of cost amount adjustment AX 2012 R3

Hi Hien Hoang,

Based on your calculations, I would expect also an adjustment different from the 100,000 that you got.

Do you have any other item transactions recorded before or after 19-25 May that might influence your outcome?

In addition, can you please open the closing and adjustment form, select your recalculation and then investigate the settlement button/form and print the adjustment report? Those forms you provide you the details how the adjustment was calculated.

Maybe you can post this form/Report here?

Best regards,

Ludwig

Was this reply helpful? Yes No
Hien Hoang 40 on at

Like (0)

Report

RE: Formula of cost amount adjustment

Hi Ludwig,

I don't have other item transactions recorded with "test_5'' in May.

Adjustment Report:

Can you please help me about this.

Thanks!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Formula of cost amount adjustment

Hi all,

I've faced same issues with my customer.

Please help us in this case

Was this reply helpful? Yes No
Suggested answer

Muthusamy 4 on at

Like (1)

Report

RE: Formula of cost amount adjustment

Hi Hien,

Its look like latest cost price (11000) is updated in item master based on the item set up and the same amount is adjusted while doing inventory recalculation for May 31st.

Thanks

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: Formula of cost amount adjustment

Hi Hien Hoang,

Thank you for sharing this Information.

Can you select the Settlements button in the closing and adjustment form, then select the settled transaction / line and then click on the transaction button?

What do you see there?

Best regards,

Ludwig

Was this reply helpful? Yes No
Guy Terry 28,911 Moderator on at

Like (2)

Report

RE: Formula of cost amount adjustment

Hi Hien,

Check here: technet.microsoft.com/.../gg230988.aspx

'When you use the weighted average or weighted average date inventory model, inventory close settles only financially updated transactions, regardless of whether you select the Include physical value check box.'

Inventory close (and, I think, recalculation) cannot use the value of the un-invoiced receipts for costing your sales. The only items which you have that are financially updated receipts cost 11,000 each.

If the 50 you sold cost 11,000 each = 550,000. Which means AX needs to adjust the estimated cost amount (which was -450,000) by -100,000.

Since you actually only financially received 40, if you invoice another receipt for 10 and then run recalculation again, I imagine that AX will revalue that sale of 50 once more.

Was this reply helpful? Yes No
Hien Hoang 40 on at

Like (0)

Report

RE: Formula of cost amount adjustment

Hi Muthusamy,

I used inventory recalculation for May 31st and cost price is updated. But it haven't been adjusted as my expectation. My customer have a lot of P.O which received but not Invoiced.

Thanks.

Was this reply helpful? Yes No
Hien Hoang 40 on at

Like (0)

Report

RE: Formula of cost amount adjustment

HI Ludwig,

The amount is adjusted for the first S.O while running inventory recalculation.

How to setup calculation cost price based on Physical Value when I didn't have Financial amount? I would like cost price be calculated as while having financial cost amount.

Many Thanks.

Was this reply helpful? Yes No