Help with Product costing formula

[ned1559]

There are 2 separate product groups A and B, a known quantity of each group, and a known combined total cost of both groups.

I need to allocate the total cost accross the product groups.
What would a formula be in order to determine an average cost of product A that is 1.2 times the average cost of product B?


Product Qty
A 60
B 120
Total 180

Total Cost $ 1,500,000

 

[JeffM]

There are 2 separate product groups A and B, a known quantity of each group, and a known combined total cost of both groups.

I need to allocate the total cost accross the product groups.
What would a formula be in order to determine an average cost of product A that is 1.2 times the average cost of product B?


Product Qty
A 60
B 120
Total 180

Total Cost $ 1,500,000

What kind of course is this for, Cost Accounting?

 

[Deleted member 4993]

There are 2 separate product groups A and B, a known quantity of each group, and a known combined total cost of both groups.

I need to allocate the total cost across the product groups.
What would a formula be in order to determine an average cost of product A that is 1.2 times the average cost of product B?


Product Qty
A 60
B 120
Total 180

Total Cost $ 1,500,000

Assume that the average cost of A is = x

then the average cost of B is = ?? (as a function of 'x' , i.e. , in terms of 'x')

 

[ned1559]

What kind of course is this for, Cost Accounting?

No, this is actually a modeling concept to determine the viability of a financed operation. The product, purchased in bulk, is refined into two distinct groups, one carrying a much more significant market value than the other. To assign the overall average cost accross all groups will result in losses in the lower market value goods which we want to avoid. The multiple of the average cost of the lower group +20% in this case will be varied but I need a formula based upon the known information in order to drive a cost allocation.

 

[JeffM]

No, this is actually a modeling concept to determine the viability of a financed operation. The product, purchased in bulk, is refined into two distinct groups, one carrying a much more significant market value than the other. To assign the overall average cost accross all groups will result in losses in the lower market value goods which we want to avoid. The multiple of the average cost of the lower group +20% in this case will be varied but I need a formula based upon the known information in order to drive a cost allocation.

If I understand what you are doing, you are playing games to fool yourselves or potential investors. Here is the point. I buy the input at cost c and use it to produce two outputs, X, which sells at a, and Y, which sells at b. I buy p units of input and sell q and r units respectively of X and Y.

Ignoring fixed costs, profit = aq + br - c(a + b) = a(q - c) + b(r - c). If q - c < 0, you can increase profits by not selling X at all.

Now perhaps I do not understand your question, but if I do, your books will be intentionally misleading. If you are the sole owner, there is no law against fooling yourself, but there are laws against fooling outside investors.

If I do not understand your question, please clarify it.

 

[ned1559]

The reason behind this excercise has no bearing on whether someone can help with providing a formula.

If anyone can provide a simple formula example that would great.
I can get the answer by using a solver utility in excel but I would like a formula that will be dynamic to changing known values so I dont have to use the utility.

Known Values:
Qty
Group A 60
Group B 120
Total Dollars to be assigned to both Groups $1,500,000
Desired result: Group A Avg $ per Qty = (Group B Avg$ per Qty) * 1.2

The answer will be Avg $ per Qty for Group A = 9,374.94
while the Avg $ per Qty for Group B = 7,812.53

Thanks in advance

 

[ned1559]

Thanks Denis !!