Calculating contribution of Percentages

M

mattalin

Board Regular
Joined
Mar 18, 2003
Messages
89
I am working with calculations that result in percentages (Rate = Commission / Sales) based on sales and commission for several products.


  1. I would like to take the results one step further by determining the contribution that each product makes to the total rate, i.e. I need to be able to calculate a rate for each product that adds up to the total rate (9.4% for Budget, 8.5% for Actual).
  2. Additionally, I'm looking to determine the contribution of the variance between the budgeted and actual rates against the total variance - assume I can simply subtract the budget from the actual results from #1.

I've been struggling to find the appropriate equation to accurately calculate the contribution percentages, yellow-shaded cells.

Below is an example of data:


<tbody>
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, colspan: 3, align: center"] Budget
 [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, colspan: 3, align: center"] Actual
 [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] B/(W)
 [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, colspan: 3, align: center"] Contribution Rate Variance
 [/TD]

[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] Sales [/TD]
[TD="bgcolor: #FFFFFF, align: right"] Commission [/TD]
[TD="bgcolor: #FFFFFF, align: right"] Rate [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] Sales [/TD]
[TD="bgcolor: #FFFFFF, align: right"] Commission [/TD]
[TD="bgcolor: #FFFFFF, align: right"] Rate [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] Rate [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] Budget* [/TD]
[TD="bgcolor: #FFFFFF, align: right"] Actual* [/TD]
[TD="bgcolor: #FFFFFF, align: right"] B/(W) [/TD]

[TD="bgcolor: #FFFFFF"] Product A [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $1,000 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $120 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 12.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $1,000 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $100 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 10.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] (2.0%) [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.0% [/TD]

[TD="bgcolor: #FFFFFF"] Product B [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $750 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $45 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 6.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $750 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $50 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 6.7% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.7% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.0% [/TD]

[TD="bgcolor: #FFFFFF"] Product C [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $1,200 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $144 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 12.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $1,200 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $125 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 10.4% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] (1.6%) [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.0% [/TD]

[TD="bgcolor: #FFFFFF"] Product D [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $800 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $56 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 7.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $800 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $50 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 6.3% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] (0.8%) [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.0% [/TD]

[TD="bgcolor: #FFFFFF"] Product E [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $900 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $72 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 8.0% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $900 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $70 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 7.8% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] (0.2%) [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 0.0% [/TD]

[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $4,650 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $437 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 9.4% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] $4,650 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] $395 [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 8.5% [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] (0.9%) [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"] 9.4% [/TD]
[TD="bgcolor: #FFFFFF, align: right"] 8.5% [/TD]
[TD="bgcolor: #FFFFFF, align: right"] (0.9%) [/TD]

[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="bgcolor: #D9E1F2"] Sum of Rate column: [/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"] 45.0% [/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"] 41.1% [/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"] (3.9%) [/TD]
[TD="bgcolor: #D9E1F2, align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: right"] 9.4% [/TD]
[TD="bgcolor: #D9E1F2, align: right"] 8.5% [/TD]
[TD="bgcolor: #D9E1F2, align: right"] (0.9%) [/TD]

[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="bgcolor: #FFFFFF"] Comments: [/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="bgcolor: #FFFFFF, colspan: 14, align: left"] Rate = Sales/Commission ; B/(W) = Actual - Budget
[/TD]

[TD="bgcolor: #FFFFFF, colspan: 14, align: left"] * Need to calculate percentage contribution that add up to 9.4% and 8.5% for Budget and Actual, respectively [/TD]

</tbody>
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
This?


Excel 2010
ABCDEFGHIJKLMN
1BudgetActualB/(W)Contribution Rate Variance
2SalesCommissionRateSalesCommissionRateRateBudget*Actual*B/(W)
3Product A$1,000$12012.00%$1,000$10010.00%-2.00%2.58%2.15%-0.43%
4Product B$750$456.00%$750$506.70%0.70%0.97%1.08%0.11%
5Product C$1,200$14412.00%$1,200$12510.40%-1.60%3.10%2.68%-0.41%
6Product D$800$567.00%$800$506.30%-0.80%1.20%1.08%-0.12%
7Product E$900$728.00%$900$707.80%-0.20%1.55%1.51%-0.04%
8$4,650$4379.40%$4,650$3958.50%-0.90%9.40%8.50%-0.90%
9
10Sum of Rate column:45.00%41.10%-3.90%9.40%8.50%-0.90%
11
12Comments:
13Rate = Sales/Commission ; B/(W) = Actual - Budget
14* Need to calculate percentage contribution that add up to 9.4% and 8.5% for Budget and Actual, respectively
15
Sheet3
Cell Formulas
RangeFormula
L3=D3*B3/$B$8
M3=H3*F3/$F$8
N3=M3-L3
 
Upvote 0
Thanks, I might have been over complicating this. My next step is to dive into the elements that make up rate variance for each product such as volume (quantity/mix), price, etc. that affect sales$ and this gives me something to compare against at the total variance level.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
110
FormulasOnly
F
T
  • Solved
Scaling a Data Set
Replies
6
Views
265
felixstraube
F
E
  • Question Question
Calculating Tiered Commission Structure based on Rolling Annual Contribution
Replies
1
Views
1K
Dave Patton
D
F
  • Question Question
Relative contribution to percentage change
Replies
1
Views
702
Joe4
Joe4
J
  • Question Question
Calculated Field - Not Consistent
Replies
2
Views
329
Jgordon
J

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top