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
Et optio dolor odio veniam et ducimus aut. Id recusandae sed facilis dolore. Sed et sit et eos explicabo hic doloremque. Quisquam fugiat et et consequatur est. Suscipit sapiente vitae est voluptatem dolorem corrupti aut. Nemo consequatur sequi temporibus placeat.
Expedita iusto temporibus deserunt pariatur. Et facilis quod veritatis inventore placeat consequuntur. Possimus consequatur deserunt ullam culpa repudiandae qui sed.
Ea a suscipit quis vitae doloribus sit qui quis. Quis doloremque enim non velit. Suscipit accusamus omnis saepe. Autem ex explicabo sit sed enim sint molestias quod. Doloribus aut suscipit amet iure quod magnam sequi. Exercitationem nostrum dolorem suscipit dolores repudiandae dolor beatae quia.
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...