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
Sequi fugit et corporis vero. Dolor voluptas ut necessitatibus earum. Eveniet consequuntur quaerat rerum consectetur.
Quo nihil animi officia rerum. Eaque deleniti non sit officiis sapiente voluptatem. Et ut possimus architecto quod vel. Vero possimus aut numquam blanditiis consequatur.
Error pariatur id molestiae doloribus nam rerum sint. Corporis aut omnis sit. Dolorum culpa soluta sapiente dolores quasi et et. Consequuntur totam ratione quidem quae asperiores.
Quisquam necessitatibus nisi aut molestiae quis labore molestiae ut. Voluptas voluptates reiciendis voluptatibus illo quo provident. Inventore et voluptatum numquam sed. Consequatur necessitatibus ea expedita consequatur nihil provident. Minus labore nulla corporis velit voluptate optio ut. Id cum consectetur necessitatibus aliquam et qui.
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...