Find the RM(Raw Material) cost from different purchase quantity

[Anbuselvam K]

Hi

I have RM1 (Raw Material) to RM10 (Raw Material) Purchase with different quantity and price. Also, Product1 to Product12 with different combination formulations of RM1 to RM10 as per attached sheet.

I want to calculate Each product RM (Raw Material) cost per kg from the purchase price.

Example
Product1 Production, 444kg is used of RM1
Product2 Production, 570kg is used of RM1

Purchase
RM1 450kg by 0.79$ price
Next RM1 purchase 895kg by 0.37$

So The RM cost must take 444kg with the price of 0.79$ in Product1
Then The RM cost must take 0.79$ for 6kg and 0.37$ for 569kg in Product2

I have added the expected results with manual formulations in the attached sheet.

I hope I can get some excel formula or VBA code to calculate the same in automatic.

Thanks in Advance.

 

[vletm]

Anbuselvam K
... based from Your X4-sample formula
Press [ Calculate ]-button ( range W1:X1 )

 

[Anbuselvam K]

Dear Vletm

Thanks for your valuable reply.

My apologies if I was wrong in the first post.

It is not calculating the cost as per purchase prices. below are the details for further modifications of the code.

Column Y:AR is the purchased quantity and it's price of RM1 to RM10

Column M:V is the Consumption of RM1 to RM10

To avoid confusion and for a better understanding, I used only one RM1 in these attachments. But actually it should consider all RM's purchase and consumption in the costing calculations.

RM1 purchase is 10kg by $1, 10kg by $2, 10Kg by $3 and 5kg by $4

RM 1 Price	RM 1 Qty in kg
$ 1.00	10​
$ 2.00	10​
$ 3.00	10​
$ 4.00	5​

RM Consumption
Product1 Consumed 20kg, Product2 Consumed 5kg and Product3 Consumed 10kg of RM1

Production Qty in kg	RM1
200​	20​
100​	5​
200​	10​

Expected results in the cell X4, X5 and X6 are as below

Cell X4	$ 30.00	(10*$1)+(10*$2)
Cell X5	$ 15.00	(5*$3)
Cell X6	$ 35.00	(5*$3)+(5*$4)

RM1 to RM10 Consumed from the RM1 to RM10 Purchases. So the RM cost must take the first purchase price to first consumption and then second, third so on...

My first post ecpected results was different because my first purchase is less than the first consumption. so it was not taking the second purchase price.

Now Adjusted the purchase and consumption for the better understanding.

Please do the needful. Thanks in advance.

 

[vletm]

Anbuselvam K
Please do the needful ... yes!
Have You ever hear about 'measure twice - cut once' -procedure ?
the better understanding ... if You've give details then what else someone else could try to understand?
... someone maybe would try to understand something again later, if You have still misunderstood something?
Please...

 

[Anbuselvam K]

there is no further change in the previous post. can you please modify the code.
Thanks in advance.

 

[vletm]

Anbuselvam K
Seems that Have You Never hear about 'measure twice - cut once' -procedure ?
As well as my other questions ( = sentences which ends with ? )
I cut once and ...
You have left there TWO different sets of 'Expected Results' ... just for clear, I show it for You both of those - check below.
... hmm?

 

[Anbuselvam K]

Below is the one I expected in column"X"

B is the result and C is the calculation for the results.

A	B	C
My expected results below
	Result	Calculation
Cell X4	$ 30.00	(10*$1)+(10*$2)
Cell X5	$ 15.00	(5*$3)
Cell X6	$ 35.00	(5*$3)+(5*$4)

You have left there TWO different sets of 'Expected Results' ... just for clear, I show it for You both of those

My expected result must be column"W". In my first post, I added the manual calculation and the results in Column"X" which should be the expected results of Column"W".

I want to find the RM Cost per kg for each product.

Again my apologies if I was conveyed wrongly and wasted your time.

Please do the needful if your time permits.

 

[vletm]

Anbuselvam K
a) Your 'My expected results below' seems to have 'fixed' -values in Your 'formulas' which shows ... something.
Should there be some kind of logic?
If You cannot figure 'how to solve something?' then how would someone else would guess - how?

b) Cell L 4 has value 200
Sum(Z4:Z15) ... is 35 ... would mean over 35kg ... all is free!

Still, You're using '... do the needful ... '-phrase.
... Okay, I see - I need to go to feed my pets now!

 

[Anbuselvam K]

Dear vletm

I hope the attached sheet will explain my expected results. Expecting your support.

 

[Anbuselvam K]

b) Cell L 4 has value 200
Sum(Z4:Z15) ... is 35 ... would mean over 35kg ... all is free!

To produce 200kg of Product1 is required only 20kg of RM1 and remaining it will take as distilled water. (This is the formulation of chemicals products)
Column B:K is the formulations of each product. Total formulation won't be 100% because balance water will consider automatically.

 

[vletm]

Anbuselvam K
You're expecting ... and ... better understanding...
Did I expected 28hrs to get something with some kind of clear logic? Yes.
I had one kind of logic ... but ... Your file 'messed' it totally.
As Your clear (for Yourself) solutions, which seems to have many variations ... would it more clear, that You could solve the rest too?
I found something positive too. L-column formulas are clear.

 

[Anbuselvam K]

Dear Vletm

I have added the notes on how I calculated the cost of RM1 to RM3 for your kind consideration.

 

[vletm]

Anbuselvam K
... something with some kind of clear logic ...
Did You change something? Maybe I missed that part?
There seems still be many variations ... only You will know.

This 'my' version would have 'some kind of logic' ... but ... it could be different than 'Yours'.
There are two [ Calculation ]-buttons - 'left slower / right faster'.

It solves:
1) total kgs per RM
2) Prices which reflect total kgs (eg RM1 kgs 0-450 are 0.79/kg, next 895kg are 0.37/kg and so on )
3) based kgs / Product and total Price of RM - it solves 'Expected Results' as well as 'PM Cost per kg'
>> then no matter of order of Products
>> above will do it same-way with all RMs and Products