Benefit Cost & Deduction Reconciliation

Three systems each hold a version of what benefits cost this month: the elections, the payroll deductions, and the carrier invoice. Any two can agree while the third is wrong. The report compares all three, per worker.

RuleExpected from elections, deducted from payroll, invoiced from the carrier: three columns per worker per month, variances explained by name.Nevertreat this as cleanup. Pre-tax deductions set taxable wages; a wrong deduction makes the W-2 wrong by definition.

◆ The three-way disagreement— what each source knows, and the timing signatures that explain most variances.

Pattern	What it looks like	What it means
New hire in transit	Deduction taken, no invoice line.	The enrollment has not reached the carrier yet; expect a retroactive bill next cycle. Tracked, not written off.
Termination leakage	Invoice line, no active election.	The termination never reached the carrier, and the company is paying the full premium for no one. The credit request starts here.
Leave arrears	Active election, no deduction.	Deductions skipped during unpaid leave accumulate as arrears to recover on return. A balance to carry, not a variance to close.
Mid-month change	Two rates in one month.	Qualifying life events change tiers mid-cycle. Decide once whether the month uses the month-end election or prorates, and state it on the report.
Misclassification	Everything matches, taxes are wrong.	A pre-tax deduction coded post-tax, or the reverse, reconciles perfectly and still misstates taxable wages. The classification audit in dim_pay_component is part of this report's job.

◆ The owned model, and the SQL— elections, deductions, and the invoice in one query, variances by name.

fct_benefit_enrollment lands one row per election version: plan, coverage tier, employee and employer costs, effective dates. Deductions already exist in fct_payroll_result; the invoice is staged per carrier line. One month, per worker:

-- May, medical plan: expected vs deducted vs invoiced, per worker
WITH expected AS (
  SELECT e.worker_key, e.monthly_premium, e.employee_share
  FROM fct_benefit_enrollment e
  WHERE e.plan_key = 3 AND DATE '2026-05-31' BETWEEN e.effective_from AND e.effective_to
),
deducted AS (
  SELECT f.worker_key, SUM(f.amount) AS deducted
  FROM fct_payroll_result f
  JOIN dim_pay_component pc ON f.pay_component_key = pc.pay_component_key
  WHERE pc.benefit_plan_key = 3 AND pc.class = 'Employee deduction'
    AND f.paid_in_period_key = 202605
  GROUP BY 1
)
SELECT COALESCE(x.worker_key, d.worker_key, i.worker_key) AS worker,
       x.employee_share            AS expected_ee,
       d.deducted,
       i.invoiced_premium,
       COALESCE(d.deducted,0)          - COALESCE(x.employee_share,0)  AS deduction_variance,
       COALESCE(i.invoiced_premium,0)  - COALESCE(x.monthly_premium,0) AS invoice_variance
FROM expected x
FULL OUTER JOIN deducted d USING (worker_key)
FULL OUTER JOIN stg_carrier_invoice i
       ON COALESCE(x.worker_key, d.worker_key) = i.worker_key
      AND i.coverage_month = '2026-05'

Sample output, three workers showing the clean case and the two timing signatures:

worker	expected_ee	deducted	invoiced_premium	invoice_variance
W-1042	210	210	645	0
W-1187	210	210		−645
W-0966			458	+458

W-1042 reconciles on both axes against a 645 monthly premium. W-1187 is the new hire in transit, deducting correctly with the carrier not yet billing. W-0966 is the leakage case, a terminated worker still invoiced at 458, and the FULL OUTER JOINs are why both appear at all: a worker present in only one source is the finding, the same principle as the GL reconciliation. Employer premium share lands as employer-cost lines and flows into fully loaded cost; benefits spend is part of labor cost even where gross-to-net never shows it. Sample values are illustrative, never client data.

Want the three-way reconciliation running monthly?

The election fact, the deduction classification, and the staged invoice, with variances explained by name, reconciled against Workday, and you own every line.

Talk to us

Terms on this page

election: A worker's plan choice at a coverage tier, effective-dated. The expected cost comes from it.
coverage tier: Employee-only, employee plus spouse, family. Sets the premium and the split.
three-way reconciliation: Elections against deductions against the carrier invoice. Two-way checks miss the third source's errors.
in transit: An enrollment sent but not yet billed by the carrier. Expect a retroactive bill.
premium leakage: Paying premiums for coverage that should have ended. The termination signature.
arrears: Deductions skipped during unpaid leave, recovered on return.
pre-tax classification: Whether a deduction reduces taxable wages. Wrong classification reconciles clean and still breaks the W-2.
employer share: The company's part of the premium. An employer-cost line, inside fully loaded cost.