Need help with calculation of weights

[Xen]

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.

 

[Faseeh]

Hi Xen,

I think what you are getting in Col H is the actual answer. Don't you think we are over complicating things??

 

[Xen]

No, here's the example: if neither price of cost of any category changed, but we get increase in some category with low profit, our overall % will go down, but % by category will be zero.

In this case, column O works just as needed. But as soon as prices are starting to change (which is always), it does not work anymore. And all the other formulas show that all the categories but this one are to blame, which is false. Nothing changed to others.

With lots of ever changing data, I'd like to have helper column that might give a quick hint where to look and start further analysis.

 

[NARAYANK991]

Hi ,

See if this helps.

Narayan

 

[Xen]

Narayan, sorry, this it not what I was looking for

Correct overall result is in H9 and is -11,7%, what I want to do is for each row to calculate how much it contributes to -11,7%. Say, A is -1%; B is -2% and D -8,7%

Problem here that added value% is relative and change is relative, and I want to calculated another relative value, I can't seem to get my head around so many relations

 

[NARAYANK991]

Hi ,

I don't think so ; the result in H9 is from the formula :

=G9-F9

where :

G9 : =E9/D9-1

F9 : =C9/B9-1

What this is doing is subtracting ratios , which is never to be done. You can subtract absolute values , but you cannot subtract percentages , since the base for each percentage can be different.

Just to illustrate , suppose the total profit in 2013 was 4 on a cost of 4 , which will give a profit percentage of 100 %. Suppose the total profit in 2014 was 10 on a cost of 100 , giving a profit percentage of 10 % ; your formula , as it stands , will give a result of 90 % , which is obviously not correct.

You need to rethink.

Narayan

 

[Xen]

What this is doing is subtracting ratios , which is never to be done. You can subtract absolute values , but you cannot subtract percentages , since the base for each percentage can be different.

Just to illustrate , suppose the total profit in 2013 was 4 on a cost of 4 , which will give a profit percentage of 100 %. Suppose the total profit in 2014 was 10 on a cost of 100 , giving a profit percentage of 10 % ; your formula , as it stands , will give a result of 90 % , which is obviously not correct.

You need to rethink.
Narayan

No, the whole point is subtracting ratios, because bases are always different. That way we can compare how much more/less return we get on on investment. It's not measure of profit increase, but efficiency.

Basically, added value formula (Price/Cost-1) shows how much more we charge than it cost us. If we had 100% last month, this means we produce for 1$ and sell for 2$. And if we get 115% this month, we can safely say that efficiency increased, difference is +15%, or 15 cents more for each dollar earned.

In your example result would be not 90%, but -90%. The only thing to keep in mind here is that -90% doesn't mean '10 times less', but literally -90%. Our usual ratio is way above 100% (like 250%), so in case of -90% this would mean 250 -> 160. While absolute profit could grow, say 500%, this only means that we sold more, but overall earned less per dollar spent, i.e. were less efficient.

While things are logical when you look at the table, you can tell that less profitable category increased in volume, or profitability of certain category decreased and that caused decline in total ratio. But I can't figure out exact numbers, how much each row contributed to total ratio This should be possible somehow, because you can see it with your own eyes, but... uh... exact numbers.

The main problem is not only that bases are different, but mainly because values I'm comparing are ratios. In case of absolute values measuring contribution to change is easy, we just take last year total as base:

2013  2014  Change Contribution
40      55   38%    15%
60      65     8%       5%
100    120    20%     20%

This somehow doesn't work with ratios, I've tried everything I could, be logical or not


Sorry if some terms I use are incorrect, english is not my native. I hope you understand what I am trying to say

 

[NARAYANK991]

Hi ,

I am still not convinced that what you have described will in any way give a correct picture of changes.

Let us consider item E.

There has been no change in any figure relating to E ; both the costs and sales have remained the same in 2013 and 2014. But is the change 0 % ?

If you consider that other products have performed better and increased absolute profits , then the fact that E has not shown any relative increase is itself a sign of inefficiency.

Secondly , you say you are more interested in dollars earned per dollar spent , and not the absolute profits ; I find this difficult to digest. In the ultimate analysis , all stakeholders in a company are rewarded through the absolute dollars earned ; if you are extremely efficient ( you give an example of 500 % ) , but if this efficiency is on 1 % of the turnover , how much of absolute money is this going to contribute ?

10% on a million dollars is far more important than 10000% on a 100 dollars.

In the file I had uploaded earlier , can you explain which column is incorrect ? As far as I can see , columns K and M correctly show the contribution percentages of each item to the overall profit figure.

Narayan

 

[Xen]

While absolute profits are very important, and in fact the only thing that matters, there is physical limit to how much we can sell, which brings us efficiency - the more efficient we are, the more absolute profit we are going to have. Of course if it was possible to go from 250% to 160% efficiency, but get 500% more absolute profit, that'd be awesome. But in reality sales change +/-10% from average depending on season, so we must keep an eye on how much profit we get per item sold. Simply speaking, we can only have, say, 900$ to 1100$ turnover, and what part of it is left to us is important. The higher is efficiency, the more profit we have.

I have re-made example values, so total turnover is the same, and it's only efficiency that matters. In both examples all the changes made are driving total efficiency down, problem with all the formulas is that they are showing that product D is actually boosting efficiency, while in fact it is not. Yes, it is contributing more to absolute value, since it has more sales, but in reality we should avoid it and offer other options if possible.

Result for first table, how I imagine it, should look something like (numbers are random guesses):
A -1,5% (because of increased cost)
B -2,5% (because of decreased price)
D -3,6% (because of increased sales of low-profit item)
Total -7,6

 

[NARAYANK991]

Hi ,

I have uploaded a file ; can you go through it , and comment ?

For simplifying matters , I have considered only 2 products.

Narayan

 

[Xen]

Hi ,

I have uploaded a file ; can you go through it , and comment ?

For simplifying matters , I have considered only 2 products.

Narayan

Thank, Narayan
It is slightly different from what was needed, but in conjunction with other calculations it gives enough information on what went wrong/what was good.

I'll try to make and upload final file when I am back home.