Benefits / census file
A census or enrollment spreadsheet sent to a carrier or broker, wrongly read as a clean one-row-per-employee headcount with cost attached.
The shape, how the file is really laid out
Usually an Excel workbook on a broker template, opening with instruction and
legend rows and color-coded headers above the data, and often split across
tabs (an employee/subscriber tab and a dependents tab). Granularity is the
core ambiguity: some files are one row per member, the employee plus each
covered dependent, where dependent rows have blank salary and hire date, while others are one row per employee with dependent counts in columns. Rows
carry DOB, gender code, hire/effective date, a coverage-tier code (EE,
EE+Spouse, EE+Child, Family), plan/tier elections, salary, and often a full
SSN. A census lists everyone eligible; an enrollment lists only the enrolled
and may include Waived rows and COBRA/retiree members.
The traps
Member-level rows mean one person appears multiple times, once per dependent, and again per line of coverage (medical, dental, vision) if the file is per-enrollment, so a raw row count is not people and not subscribers.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Employee benefits census / enrollment template conventions from carrier and broker onboarding guides · ACA reporting eligibility definitions