Spreadsheet: 679263d1590301546-avoiding-a-circular-reference-when-calculating-bonuses-based-on-profit-margin-bonusprofitcircularref_v2
Sheet: McFormula

 | January |  |  |  |  | February |  |  |  |  | March |  |  |  |  | April
 | Actuals | %ofRevenue | Hypothetical | %ofRevenue | Manual | Actuals | %ofRevenue | Hypothetical | %ofRevenue | Manual | Actuals | %ofRevenue | Hypothetical | %ofRevenue | Manual | Actuals | %ofRevenue | Hypothetical | %ofRevenue | Manual
Revenue | 108248 |  |  |  |  | 80901 |  |  |  |  | 95331 |  |  |  |  | 89811
Compensation of Officers | 10115 |  |  |  | 0.1 | 11395 |  |  |  | 0.08 | 10359 |  |  |  | 0.09 | 9171 |  |  |  | 0.08
Health Insurance
Base Comp
Monthly "Bonus"
OtherExpenses | 63564 |  |  |  |  | 68094 |  |  |  |  | 60782 |  |  |  |  | 62556
Total Expenses | 73678 |  |  |  |  | 79491 |  |  |  |  | 71142 |  |  |  |  | 71727
EBITDA | 34570 |  |  |  |  | 1410 |  |  |  |  | 24190 |  |  |  |  | 18084
Officer Share of EBITDA
Officer [NAME]
[NAME]
 |  |  | Compensation Ratchet:
Revenue | 160 |  | 8% of Revenues if Profit Margin < 20%
- OtherExpenses | 100 |  | [ 4% + 20% * Profit Margin if between 20% and 30% ] of Revenues
- ExecutiveCompensation |  |  | 10% of Revenues if Profit Margin > 30% |  |  |  |  |  |  |  | Profit Margin | [NAME] defined structure
= Profit |  |  | (formulas intersect at 20% and 30% profit margin) |  |  |  |  |  |  |  | 0 |  | apply min here to 8%
Profit Margin |  |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
Compensation Check |  |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
Check Profit |  |  |  |  |  |  | (must always hold true = equal zero) |  |  |  |  |  | apply min here to 8%
 |  |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
Executive Compensation = |  |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
(min/[NAME] nature applied here) |  |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
Solving equations: | Profit = Revenue-OtherExpenses-ExecutiveCompensation |  |  |  |  |  |  |  |  |  |  |  | apply min here to 8%
(ignore min/[NAME] here for the moment) | ExecutiveCompensation = (20%*Profit/Revenue + 4%)*Revenue |  |  |  |  |  |  |  |  |  |  |  | correct here
 | Profit = Revenue-OtherExpenses-(20%*Profit/Revenue + 4%)*Revenue |  |  |  |  |  |  |  |  |  |  |  | correct here
 | 1.2*Profit = Revenue - OtherExpenses - 4%*Revenue |  |  |  |  |  |  |  |  |  |  |  | correct here
 | Profit = (96%*Revenue - OtherExpenses)/1.2 |  |  |  |  |  |  |  |  |  |  |  | correct here
 |  |  |  |  |  |  |  |  |  |  |  |  | correct here
Solution for P before Min/Max |  |  |  |  |  |  |  |  |  |  |  |  | apply [NAME] here to 10%
(this result is only correct if 20% ≤ Profit Margin ≤ 30%) |  |  |  |  |  |  |  |  |  |  |  |  | apply [NAME] here to 10%
Solution if Profit Margin < 20% |  |  |  |  |  |  |  |  |  |  |  |  | apply [NAME] here to 10%
Solution if Profit Margin > 30% |  |  |  |  |  |  |  |  |  |  |  |  | apply [NAME] here to 10%
 |  |  |  |  |  |  |  |  |  |  |  |  | apply [NAME] here to 10%
Final solution = include Min/Max
Sensitivity
 | ExecComp | Profit Margin | CompShare | Check
Revenue
100 |  | -0.08 | 0.08 | 0
 | 8.2 | -0.055609756097561 | 0.07999999999999999 | 0
 | 8.4 | -0.0323809523809523 | 0.08 | -8.881784197001252e-15
 | 8.6 | -0.01023255813953483 | 0.08 | -5.329070518200751e-15
 | 8.8 | 0.01090909090909093 | 0.08 | -3.552713678800501e-15
 | 9 | 0.03111111111111111 | 0.08 | 0
 | 9.200000000000001 | 0.05043478260869575 | 0.08000000000000002 | -1.243449787580175e-14
 | 9.4 | 0.06893617021276603 | 0.08 | 0
 | 9.6 | 0.08666666666666671 | 0.08 | 0
 | 9.8 | 0.1036734693877551 | 0.08 | 0
 | 10 | 0.12 | 0.08 | 0
 | 10.2 | 0.135686274509804 | 0.08 | 0
 | 10.4 | 0.1507692307692308 | 0.08 | 0
 | 10.6 | 0.1652830188679246 | 0.08 | 0
 | 10.8 | 0.1792592592592593 | 0.08 | 0
 | 11 | 0.1927272727272727 | 0.08 | 0
 | 11.33333333333334 | 0.2047619047619048 | 0.08095238095238097 | 0
 | 11.83333333333333 | 0.215204678362573 | 0.08304093567251461 | 0
 | 12.33333333333333 | 0.225287356321839 | 0.0850574712643678 | 0
 | 12.83333333333333 | 0.2350282485875706 | 0.08700564971751412 | 0
 | 13.33333333333334 | 0.2444444444444445 | 0.0888888888888889 | 0
 | 13.83333333333333 | 0.2535519125683061 | 0.0907103825136612 | 0
 | 14.33333333333333 | 0.2623655913978494 | 0.09247311827956989 | 0
 | 14.83333333333333 | 0.2708994708994709 | 0.09417989417989417 | 0
 | 15.33333333333333 | 0.2791666666666667 | 0.09583333333333333 | 0
 | 15.83333333333334 | 0.2871794871794872 | 0.09743589743589745 | 0
 | 16.33333333333333 | 0.294949494949495 | 0.09898989898989899 | 0
 | 16.75 | 0.3029850746268657 | 0.1 | 0
 | 17 | 0.3117647058823529 | 0.1 | 0
 | 17.25 | 0.3202898550724638 | 0.1 | 0
 | 17.5 | 0.3285714285714286 | 0.1 | 0