Bonus Accrual

The pool starts at the individual level. Each named employee has a base salary, a target bonus percentage, a performance rating, tenure, and a vested flag.

• A lookup table converts the rating into an individual multiplier, which is then capped by a scenario-driven limit. Gross bonus per employee equals base pay times target percentage times that individual multiplier, times a corporate multiplier, times full-time-equivalent and vested factors.
• This means the model reflects actual headcount and pay structure rather than an assumed average bonus. A forfeiture adjustment is applied row by row, reducing each gross bonus by the expected forfeiture rate before any department totals are calculated.

The employee-level detail then rolls up into five departments using conditional sums, producing department headcount, gross bonus, and forfeiture-adjusted bonus.

Once department totals are in place, the model adds employer burden. A separate detail sheet stacks statutory and benefit components, including Social Security and Medicare taxes, federal and state unemployment contributions, retirement match, and a benefits load.

• Several of these components are effectively zero on bonus pay because annual wage bases are already exceeded on base salary, so the model uses an effective-rate approximation rather than recomputing caps month by month. The derived burden percentage is applied to the forfeiture-adjusted pool to produce total burden dollars, and adding that to the adjusted pool gives the fully loaded pool.
• That fully loaded figure is what gets accrued, because employer taxes and benefits are real cash costs that belong alongside the bonus itself.

The accrual schedule runs three stacked rollforwards. The prior-year payable opens at a known liability and drains according to a payout-weight vector, so a multi-month payment pattern is visible rather than assumed to be a single lump sum.

• The current-year accrual builds from zero to the fully loaded pool over twelve months, either straight-line or, if a true-up trigger fires mid-year, as a revised remaining amount spread across the months left. Total balance sheet liability is the sum of the prior-year and current-year closing balances.
• Each month, the accrual is allocated across departments in proportion to their share of gross bonus, creating debit entries that tie exactly to the credit to accrued compensation liability. A pass-or-fail check confirms that debits equal credits every month.

A three-way scenario switch selects between floor, target, and stretch assumptions for the corporate multiplier, forfeiture rate, individual cap, and EBITDA realisation. The scenarios sheet compares all three side by side regardless of which one is active, and reports a probability-weighted expected pool so a compensation committee can see the range rather than a single point.

• A quarterly EBITDA capacity sheet tests whether the pool fits within a chosen percentage of earnings, showing headroom or shortfall at mid-year and full-year cutoffs. Sensitivity grids display how the pool-to-EBITDA ratio changes with the corporate multiplier, forfeiture, and EBITDA movement, with colour-coded status flags.
• The model is designed for a single operating entity and does not cover multi-entity consolidation, stock-based compensation, or currency translation.