average cost/unit

[roykana]

Good Morning master excel semuannya, I am a beginner in using excel application.

Please help to determine the average cost / unit (in marking with yellow) in the data
sales, sales returns and purchase returns with excel formulas. If I analyze the data determine the unit average cost / unit with manual way it will spend time. and also if data consists of several items and also a lot of transactions then it will be difficult for me.

An excel file sample is attached to this message. I'm sorry if I've disturbed the activities of master2 excel in this group. Please solve my problem with urgent.

Thank you

Kanahttp://www.mediafire.com/file/3569ayjbn1tjhmr/average_cost-unit..xls

 

[hennahairgel]

Try not linking to an externally hosted file. No one sensible will open it.

 

[roykana]

[TABLE="width: 1072"]
<tbody>[TR]
[TD]Purchase
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[TD]Qty
[/TD]
[TD]
Purchase price
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Calculation
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]03-03-18
[/TD]
[TD]item A
[/TD]
[TD]5
[/TD]
[TD]10000
[/TD]
[TD]50000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
Unit cost value
[/TD]
[TD]10000
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]05-03-18
[/TD]
[TD]item A
[/TD]
[TD]5
[/TD]
[TD]8000
[/TD]
[TD]40000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Qty
[/TD]
[TD]
Purchase price
[/TD]
[TD]Total
[/TD]
[TD]Remark
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]06-02-18
[/TD]
[TD]item A
[/TD]
[TD]4
[/TD]
[TD]8500
[/TD]
[TD]34000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/03/2018
[/TD]
[TD]4
[/TD]
[TD]10000
[/TD]
[TD]40000
[/TD]
[TD]
Purchase
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]40000
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10000
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[TD]Qty
[/TD]
[TD]Selling price
[/TD]
[TD]Unit Cost
[/TD]
[TD]total Unit Cost
[/TD]
[TD]Total sales
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
Unit cost value
[/TD]
[TD]8333.33
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]customer
[/TD]
[TD]03-03-17
[/TD]
[TD]item A
[/TD]
[TD]4
[/TD]
[TD]15000
[/TD]
[TD]10000
[/TD]
[TD]40000
[/TD]
[TD]160000
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Qty
[/TD]
[TD]
Purchase price
[/TD]
[TD]Total
[/TD]
[TD]Remark
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]customer
[/TD]
[TD]05-03-17
[/TD]
[TD]item A
[/TD]
[TD]3
[/TD]
[TD]15000
[/TD]
[TD]8333.33
[/TD]
[TD]24999.99
[/TD]
[TD]74999.97
[/TD]
[TD][/TD]
[TD]03/03/2018
[/TD]
[TD]1
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]
Remaining stock
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]customer
[/TD]
[TD]06-03-17
[/TD]
[TD]item A
[/TD]
[TD]7
[/TD]
[TD]15000
[/TD]
[TD]8428.57
[/TD]
[TD]59000
[/TD]
[TD]413000
[/TD]
[TD][/TD]
[TD]05/03/2018
[/TD]
[TD]5
[/TD]
[TD]8000
[/TD]
[TD]40000
[/TD]
[TD]
Purchase
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]50000
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]8333.333
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sales returns
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]customer
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[TD]Qty
[/TD]
[TD]Selling price
[/TD]
[TD]Unit Cost
[/TD]
[TD]total Unit Cost
[/TD]
[TD]Total
sales returns
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
Unit cost value
[/TD]
[TD]8428.57143
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]customer
[/TD]
[TD]06-03-17
[/TD]
[TD]item A
[/TD]
[TD]1
[/TD]
[TD]15000
[/TD]
[TD]8333.33
[/TD]
[TD]8333.33
[/TD]
[TD]15000
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Qty
[/TD]
[TD]
Purchase price
[/TD]
[TD]Total
[/TD]
[TD]Remark
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]05/03/2018
[/TD]
[TD]3
[/TD]
[TD]8333.33333
[/TD]
[TD]25000
[/TD]
[TD]
Remaining stock
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/03/2018
[/TD]
[TD]1
[/TD]
[TD]8333.33333
[/TD]
[TD]8333.333
[/TD]
[TD]
sales returns
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]purchase return
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/03/2018
[/TD]
[TD]-1
[/TD]
[TD]8333.33333
[/TD]
[TD]-8333.33
[/TD]
[TD]
purchase return
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[TD]Qty
[/TD]
[TD]
Purchase price
[/TD]
[TD]Unit Cost
[/TD]
[TD]total Unit Cost
[/TD]
[TD]total
purchase return
[/TD]
[TD][/TD]
[TD]06/02/2018
[/TD]
[TD]4
[/TD]
[TD]8500
[/TD]
[TD]34000
[/TD]
[TD]
Purchase
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier
[/TD]
[TD]06-03-17
[/TD]
[TD]item A
[/TD]
[TD]1
[/TD]
[TD]8000
[/TD]
[TD]8333.33
[/TD]
[TD]8333.33
[/TD]
[TD]8000
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]59000
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]8428.57
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Stock
[/TD]
[TD][/TD]
[TD]QTY IN
[/TD]
[TD="colspan: 2"]QTY OUT
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item A
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[roykana]

Good Evening dear all master excel, I am a beginner in using excel application.

Please help to determine the average cost / unit in the data
sales, sales returns and purchase returns with excel formulas. If I analyze the data determine the unit average cost / unit with manual way it will spend time. and also if data consists of several items and also a lot of transactions then it will be difficult for me.

An excel file sample is attached to this message. I'm sorry if I've disturbed the activities of master2 excel in this group. Please solve my problem with urgent.

Date Code Type Qty Out Cost/Unit Out (Average Cost/Unit)
03 Mar 2018 A Sales Invoice 4.00 10,000.00 (WITH FORMULA)
03 Mar 2018 A Sales Invoice 5.00 9,583.33 (WITH FORMULA)
05 Mar 2018 A Sales Invoice 3.00 8,263.89 (WITH FORMULA)
06 Mar 2018 A Sales Return -1.00 8,263.89 (WITH FORMULA)
07 Mar 2018 A Sales Invoice 7.00 8,398.81 (WITH FORMULA)
18.0
Date Code Type Qty In Cost/Unit In
03 Mar 2018 A Purchase Invoice 5.00 10,000.00
03 Mar 2018 A Purchase Invoice 5.00 9,500.00
05 Mar 2018 A Purchase Invoice 5.00 8,000.00
06 Mar 2018 A Purchase Return -1.00 8,263.89 (WITH FORMULA)
06 Mar 2018 A Purchase Invoice 4.00 8,500.00
18.00

Thank you

Kana