siddharthsopori
New Member
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
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