Change in Weighted Contribution

[mvparker79]

I am trying to figure out what seems like a very simple problem which excel gets quite close, but being excel, if it's not exact...then I'm missing something.

Boiling my problem down to something simple, I basically have 3 products (A,B, and C). I know what their revenues, costs and margins are in period 1 and period 2. In my simplified example, total margins increased 22.1% from period 1 to period 2. I know that this is due to product A increasing in margin by 38.2%. Product B decreased by 0.6% and product C increased by 3%. Product A makes up roughly 57% of sales in both periods (slight increase though) and product be is about 30% and product C is about 14%.

The question is how do I determine product A's contribution to the 22.1% margin increase?

I have included a spreadsheet to hopefully make it easier.View attachment Change in Weighted Contribution.zip

 

[Deleted member 4993]

I am trying to figure out what seems like a very simple problem which excel gets quite close, but being excel, if it's not exact...then I'm missing something.

Boiling my problem down to something simple, I basically have 3 products (A,B, and C). I know what their revenues, costs and margins are in period 1 and period 2. In my simplified example, total margins increased 22.1% from period 1 to period 2. I know that this is due to product A increasing in margin by 38.2%. Product B decreased by 0.6% and product C increased by 3%. Product A makes up roughly 57% of sales in both periods (slight increase though) and product be is about 30% and product C is about 14%.

The question is how do I determine product A's contribution to the 22.1% margin increase?

I have included a spreadsheet to hopefully make it easier.View attachment 6686

What is the definition of margin as related to revenue and cost?

Copy and paste the x-cel file - most of us will not open an "unknown" zipped file.

 

[Ishuda]

I am trying to figure out what seems like a very simple problem which excel gets quite close, but being excel, if it's not exact...then I'm missing something.

Boiling my problem down to something simple, I basically have 3 products (A,B, and C). I know what their revenues, costs and margins are in period 1 and period 2. In my simplified example, total margins increased 22.1% from period 1 to period 2. I know that this is due to product A increasing in margin by 38.2%. Product B decreased by 0.6% and product C increased by 3%. Product A makes up roughly 57% of sales in both periods (slight increase though) and product be is about 30% and product C is about 14%.

The question is how do I determine product A's contribution to the 22.1% margin increase?

I have included a spreadsheet to hopefully make it easier.View attachment 6686

See below. Two ways to look at it: (1) All of the increase is due to A since B and C decreased as a percentage. (2) But I think the proper way is the increase was 470 and of that A's part was 400, B's part was ...

	Period 1	Period 2
	A	B	C	A	B	C
Income	850	450	200	1,000	500	250
Costs	750	200	50	500	225	55
Gross Profit	100	250	150	500	275	195
	11.8%	55.6%	75.0%	50.0%	55.0%	78.0%