I have been working on this for a while and I can't figure out how to solve this problem. I have a list of every contract signed by region for each month from October through December. Each contract has a discount % and a rebate %. We are tracking the Discount/Rebate ratio. For example if a contract offers a 10% discount and a 10% rebate, that is a 50% D/R ratio (10/(10+10)). I am trying to calculate the percent change in D/R for each region, and then show how that region's change effects the total change of all the regions during that period. I am using this to create a waterfall chart.
I have created two pivot tables to sum up the data. One is summing up the number of contracts each region has signed per month. The other is showing the average D/R by region for each month.
This is how I have tried to calculate it:
If region A3's D/R went from 72% in October to 70% in December, that regions change in D/R is -2%. To calculate how that change effected the company's total change, I found that regions weight by taking the number of contracts they signed in October and December and dividing it by the total number of contracts signed in October and December. A3 signed 3 contracts in Oct and 5 in December and the total contracts signed in Oct were 34 and 42 in December. Their weight would then be (5+3)/(34+42)=11%. To find A3's weighted effect of the company's change, I took -2%*11%.
The problem I have is that when I do this for all the regions, the sum of the weighted changes for each region does not equal the companies total change.
It is easier to understand if you can see it so I have attached the file. I would really appreciate someone's help!
I have created two pivot tables to sum up the data. One is summing up the number of contracts each region has signed per month. The other is showing the average D/R by region for each month.
This is how I have tried to calculate it:
If region A3's D/R went from 72% in October to 70% in December, that regions change in D/R is -2%. To calculate how that change effected the company's total change, I found that regions weight by taking the number of contracts they signed in October and December and dividing it by the total number of contracts signed in October and December. A3 signed 3 contracts in Oct and 5 in December and the total contracts signed in Oct were 34 and 42 in December. Their weight would then be (5+3)/(34+42)=11%. To find A3's weighted effect of the company's change, I took -2%*11%.
The problem I have is that when I do this for all the regions, the sum of the weighted changes for each region does not equal the companies total change.
It is easier to understand if you can see it so I have attached the file. I would really appreciate someone's help!