Help with Circular Reference
I am essentially trying to create a bonus structure for a CEO. Let's say his potential bonus is $100k and its based on how actual EBITDA performs against budget. For simplicity, its a sliding scale where if actual EBITDA at year end comes in at 70%, then he would get 70% of his bonus (with a hypothetical floor of 50% and ceiling of 100%). This circularity comes from the fact that EBITDA is determined by the bonus payout, and the bonus payout is determined by EBITDA, which is determined by the bonus payment, and so on. I would've structured it differently to be based on pre-bonus EBITDA, but I have to make it post-Bonus.
The issue I've run into is that unlike a circular reference on a debt balance/CF available to paydown debt, which we're all very familiar with, this one is completely blowing up my spreadsheet and won't iterate towards a value: its either $0 or a billion or negative billion. I'll preface that this isn't even a full model - its primarily just a few basic inputs that I copy over from their financials and then just lookup against a bonus scale.
Aware this question is a long-shot, but if anyone has any advice, would be much appreciated.
Are you sure you have iterations turned on? Should work if Bonus=Bonus Target*(Actual EBITDA/Target EBITDA) where Actual EBITDA=EBITDA-Bonus
Thank you for the comment - I sincerely appreciate it. Your formula gave me the idea to just embedd the everything that drives circularity only one cell. My previous model linked to the value across a few different places, which worked great in my initial non-circular attempt, but was causing too many issues when I introduced circularity. I believe its fixed now. Anyways, I appreciate your help!
Just do pre-bonus EBITDA and make the calc based on that
Architecto ut expedita perferendis velit dolorem quidem. Praesentium autem et minus eveniet ut voluptates ab sapiente. Temporibus magni illo tempore qui aut consectetur sit explicabo. Autem rerum voluptatem et velit velit unde. Quos at iste consequatur esse.
Ad omnis voluptatem ipsum itaque illo sit provident. Est in ratione odit. Totam dignissimos quod ea omnis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...