Xen
Member
Hello,
This is not really an Excel question, but rather general logic/math question.
Say, I have few categories of goods, each of them has it's cost and sales. Of course, things are always changing - sometimes costs of production are higher, sometimes selling prices are higher, sometimes figures are just bigger or smaller. So this results in variety in added value percentage.
It's easy to see how much profitable single category is, or total profitability of all of them. Or how it changes between different months. However, what I need to do is to calculate how much each category contributes to change between two months.
Say, if overall profitability decreased by -10%, I want to know that A caused -5%, B caused -7% and C caused +2%.
Problem with formulas that I came up with is that they measure relative differences. Say, if sales and costs are increased in only one category proportionally, and this category is below average, it logically means that our overall profitability will decrease. But my formulas tell me that this category contributed the most + and all the others are to blame for -.
Can anyone help me with advice? I've had like 10-15 different formulas and all of them were flawed.
This is not really an Excel question, but rather general logic/math question.
Say, I have few categories of goods, each of them has it's cost and sales. Of course, things are always changing - sometimes costs of production are higher, sometimes selling prices are higher, sometimes figures are just bigger or smaller. So this results in variety in added value percentage.
It's easy to see how much profitable single category is, or total profitability of all of them. Or how it changes between different months. However, what I need to do is to calculate how much each category contributes to change between two months.
Say, if overall profitability decreased by -10%, I want to know that A caused -5%, B caused -7% and C caused +2%.
Problem with formulas that I came up with is that they measure relative differences. Say, if sales and costs are increased in only one category proportionally, and this category is below average, it logically means that our overall profitability will decrease. But my formulas tell me that this category contributed the most + and all the others are to blame for -.
Can anyone help me with advice? I've had like 10-15 different formulas and all of them were flawed.