Bom Calculation

[Anonymous]

Hi everyone!

I have these two tables:

  table1:

ItemNumber	Type	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A	202_Q	202_A	203_Q	203_A
A100	P	11	11	12	8	23	19	3	0	3	0	12	0	13	0
A101	P	13	12	13	13	26	25	11	0	4	0	2	0	11	0
A102	P	2	3	2	9	4	12	6	0	12	0	6	0	12	0
A103	P	7	10	9	12	16	22	14	0	8	0	15	0	2	0
B100	M	1	5	5	20	6	25	3	0	5	0	5	0	3	0
B200	M	2	10	5	30	7	40	2	0	4	0	4	0	3	0
B300	M	5	5	2	4	7	9	3	0	5	0	2	0	2	0
B400	M	5	4	5	5	10	9	3	0	4	0	4	0	5	0

table2:

ItemNumber	Type	ItemNumber	Q
A100	P	B100	1
A100	P	B200	2
A101	P	B100	2
A101	P	B200	1
A101	P	B300	3

the first task which I've done is to calculate the 104_A to 203_A. the second one which is complicated is to calculate the cost of P type through its BOM(table2) . the answer should be like this:

ItemNumber	Type	ItemNumber	Q	TotalA/TotalQ	Cost
A100	P	B100	1	4.166667	4.166667
A100	P	B200	2	5.714286	11.42857
A101	P	B100	2	4.166667	8.333333
A101	P	B200	1	5.714286	5.714286
A101	P	B300	3	1.285714	3.857143

my QV and excel files i is attached here, how to tackle this problem?

[sunny_talwar]

Try not to replicate threads for the same problem....

Bom Calculation