Employee Benefits Budgeting: Plan Options, Enrolment by Tier, Employer Contribution Strategies, Renewals & Cost per Head

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn an open-enrolment list into a plan × tier grid with COUNTIFS, measure the waived share and the enrolled headcount of a department, and see which plan most people pick: the counts every benefits budget is built on.
• Look up an employee's premium in a plan × tier rate table with a two-way lookup, multiply a rate grid by an enrolment grid with SUMPRODUCT, and find the average premium per head and the tier that drives the bill.
• Split every premium between employer and employee under three strategies (a percentage, a fixed amount per tier, and a share of the base plan), and compare what each strategy costs the employer per year.
• Apply a renewal quote with different increases per plan, cost next year's bill on today's enrolment and on a migration scenario, and express the result as one percentage change for the budget.
• Grant flexible-benefit credits by work pattern, budget them at the expected take-up per department with SUMPRODUCT, split the spend across benefit options and express it per employee.
• Turn a two-tier match formula (100 % up to 3 %, 50 % of the next 2 %) into one spreadsheet formula with MIN and MAX, cost it per employee, find the maximum possible cost and price an auto-enrolment scenario.
• Total the benefits cost per department with SUMIFS, express it per head and as a share of payroll, and prorate a new hire whose coverage starts on the first of the next month with EOMONTH and DAYS.
• Compare next year's budget with last year's actuals by cost type, split the biggest change into a headcount effect and a rate effect, and draw budget against actual in a column chart for the budget meeting.