Earnout Modeling
Looking for an elegant way to dynamically model a target hitting earnout targets.
Context: We want our target to hit a revenue target through the period. When they hit 25%, 50%, 75%, and 100% of the target they receive a fixed payment, these hurdles are not time dependent.
Problem: Assume the company generates $100m in FY2018 and we would like them to hit $200m by 2022 (payouts are b/w 2019-2022 are a fixed dollar amount). The issue is that this earnout is not time-based and has a range - i.e. assuming the targets are $125m, $150m, $175m, and $200min; in FY2019 the target could hit $125m in rev. and hit the first hurdle, they decrease to $115m in FY2020, and in FY2021 they hit $130m would trigger the first hurdle again (b/c it is below $150m and above $125m) - this is not how the transaction is structured, once the first hurdle is triggered the target should not be able to hit this earnout hurdle again.
Looking for an elegant way to model this without a ton of if statements.
Thanks! Som
I'd assume the earnout on each hurdle varies? For example, is the payout for hitting $125MM in revenue $XX, and the payout for hitting $150MM is $YY?
If so, I think I know a pretty straightforward way to model it with only a couple IF statements
Just a quick solution that popped up in mind, apologies if it doesn’t work/has some kinks:
I’d set four “cumulative” earnouts as an assumption table, so: @ 25% or $125mm - $a @ 50% or $150mm - $a+b @ 75% or $175mm - $a+b+c @ 100% or $200mm - $a+b+c+d
Set if functions or whatever you were thinking such that at each hurdle, respective earnout is paid —— I think up to here is what you are ok with already.
Then, 3. At the end of the formula, add a subtraction that subtracts all preceding years earnouts and also put a max(0,”formula”) to avoid negative earnouts.
To illustrate, when you reach $125mm in year 1, you get $a. Let’s say it goes down to $85mm subsequent year in year 2 and then goes back up to $130mm after that in year 3. Your earnout won’t repeat that year because your formula will subtract the $a you already received in year 1 when you reached $125mm. Then, let’s say you reach $150mm in year 4, you receive $b (your formula will grab cumulative amount of $a+b, but will subtract $a received in year 1). And so on...
The only reason why to add a max(0,”formula”) is to avoid a situation where...let’s say you hit $200mm in year 3, so you had received all the earnouts ($a+b+c+d), but then in year 4, it crashes to $100mm then in year 5 you get back up to $125mm. Your formula will grab $a from the table, and then subtract all the preceding earnouts (a,b,c,d), thus resulting in a negative earnout. So putting a max(0,”formula”) avoids it.
I think this is what you are looking for, but if not, oops
This is definitely cleaner than what I was going to suggest. +1
Explicabo facere officia consectetur et. Veritatis quae culpa vero odio ex inventore suscipit. Aut laboriosam incidunt earum.
Maiores rerum sunt et qui. Non amet eius expedita cum id. A explicabo assumenda ipsa alias sunt sed. Repellendus in necessitatibus incidunt cumque. Et qui doloribus aut doloremque dolore amet praesentium. Fugiat ut nesciunt dolores. Et minus aspernatur quia nobis 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...