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]

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

<tbody>
</tbody>

 

[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