Looking for the excel formula to calculate attached rate

[siddharthsopori]

Hello Excel Gurus,

I need help developing an Excel formula in the below sheet (Attached). I want to calculate the attached rate for an Accessory as compared to various Finished Goods.
Here Accessory ACC1 is going into 2 different finished goods FG1 and FG2. Typically the attached rate for an Accessory for a Finished Good would be calculated as Sales history FG / Sales Hist ACC.
To calculate the Forecast for Accessories, we multiply the Sales history of Each FG with the Attached rate of each FG-ACC combination (For e.g. For FG1 50 x 0.33 = 17) and add them together.

The problem with this approach is if the FG combinations are a lot, the resulting Accessory forecast would also become very huge which is not correct, instead attached rate average should reduce if the number of FGs increases.
Is there a way to normalize the Attached Rate Avg so that Accessory Foecast @acc is roughly equal to Sales Hist ACC, irrespective of the number of FGs


Regards,
Sid

 

[BobBridges]

Maybe it would be obvious to someone else, but I'm not sure what I'm looking at. Are these numbers:

1) Units sold during those three months? No, because a) I decline to believe you sold exactly the same number of FG1 and FG2 in each month (in the first two rows), and b) you did not sell a fraction of one unit (in two rows further down).

2) Price per unit? Maybe you sold FG1 at $50/unit, $120/unit for FG2, and...but what would that imply about the rest of the sheet?

Guess I need you to explain better.

(This isn't actually a question about Excel, it's about cost accounting or some other kind of math. But it's interesting even so.)