Spreadsheet: 679661d1590570368-avoiding-a-circular-reference-when-calculating-bonuses-based-on-profit-margin-bonusprofitcircularref_v3
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 (linked from Sensis sheet!) |  | 79811 | 8% of Revenues if Profit Margin < 20%
- OtherExpenses (linked from Sensis sheet!) |  | 62556 | [ 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 and [NAME]
Sensitivity
 | ExecComp | Profit Margin | CompShare | Check
Revenue
79811 |  | -0.09634010349450577 | 0.09397200887095764 | 0
 | 7500 | -0.08277338481147369 | 0.09280914726955489 | 0
 | 7500 | -0.06953832614196136 | 0.09167471366931097 | 0
 | 7500 | -0.05662291241501733 | 0.09056767820700148 | 0
 | 7500 | -0.04401570199615802 | 0.08948706017109925 | 0
 | 7500 | -0.03170579288064048 | 0.0884319251040549 | 0
 | 7500 | -0.01968279125053898 | 0.08740138210718905 | 0
 | 7500 | -0.007936782205020101 | 0.08639458133185887 | 0
 | 7500 | 0.003541697509423649 | 0.0854107116420494 | 0
 | 7500 | 0.01476168492641677 | 0.08444899843487856 | 0
 | 7500 | 0.02573181458841345 | 0.08350870160670743 | 0
 | 7500 | 0.03646034070762352 | 0.08258911365363227 | 0
 | 7500 | 0.04695515787868556 | 0.08168955789611267 | 0
 | 7500 | 0.05722382045231707 | 0.08080938681837282 | 0
 | 7504.88 | 0.0672215411838698 | 0.08 | -1.000444171950221e-11
 | 7584.88 | 0.07621605088017223 | 0.08 | -1.000444171950221e-11
 | 7664.88 | 0.08502280531462995 | 0.08 | -1.000444171950221e-11
 | 7744.88 | 0.09364762268750462 | 0.08 | 0
 | 7824.88 | 0.1020960832626188 | 0.08 | 0
 | 7904.88 | 0.1103735414073333 | 0.08 | 0
 | 7984.88 | 0.1184851369087577 | 0.08 | 0
 | 8064.88 | 0.1264358056164507 | 0.08 | 0
 | 8144.88 | 0.1342302894579172 | 0.08 | 0
 | 8224.880000000001 | 0.1418731458696055 | 0.08000000000000002 | 0
 | 8304.880000000001 | 0.1493687566828179 | 0.08000000000000002 | 0
 | 8384.880000000001 | 0.1567213365009399 | 0.08000000000000002 | 0
 | 8464.880000000001 | 0.1639349406016389 | 0.08000000000000002 | 0
 | 8544.880000000001 | 0.1710134723951654 | 0.08000000000000002 | 0
 | 8624.880000000001 | 0.1779606904675776 | 0.08000000000000002 | 0
 | 8704.880000000001 | 0.1847802152355921 | 0.08000000000000002 | 0
 | 8784.880000000001 | 0.1914755352378178 | 0.08000000000000002 | 0