Insurance Commissions Data Analyst — Excel & Power Query Pro

Avocado VA Inc.; United States; On-site; USD 60,000 - 90,000; Full time

Job summary

Avocado VA Inc. is seeking a detail‑oriented Insurance Commission Accountant to download, reconcile and match carrier and FMO statements to policies, members, and agents.

You will compute monthly agent/rep commissions using CRM data, identify discrepancies, and coordinate follow‑ups with carriers and FMOs. The role requires 2+ years in insurance commission accounting, strong Excel/Power Query skills, and experience with HubSpot or similar portals.

Qualifications

• 2+ years of insurance commission accounting (agency, FMO/IMO or carrier side). US health or Medicare commissions strongly preferred.
• Strong Excel and data skills (required).
• Power Query: combining multiple CSV/Excel statement files from a folder, cleaning and transforming data, refreshing queries
• Pivot tables: summarizing by carrier, agent, product and month; tying totals back to source
• Lookups (XLOOKUP / INDEX-MATCH, SUMIFS) and working comfortably with large files
• Experience reconciling policy-level data (policy/member IDs, effective dates, paid-through dates, chargebacks)
• Familiarity with a CRM (HubSpot preferred) and carrier/agent portals
• Very good written English for email follow-ups with US carriers and agents
• Organized and reliable with recurring deadlines; documents your work so others can follow it

Responsibilities

• Commission reconciliation: download and process carrier and FMO commission statements and match paid commissions to policies, members and writing agents.
• Sales rep commissions: calculate monthly commissions for ACA/OBC and DVH lines using CRM deal data, including new vs. renewal business, chargebacks and AOR changes.
• Discrepancy follow-up: identify missing, short-paid or misattributed commissions and follow up with carriers and FMOs until resolved.
• Independent agent program: prepare monthly override/IA computations, invoices and payouts from portal reports.
• Data consolidation: maintain consolidated commission workbooks (multi-carrier, multi-month, 30,000+ rows) and keep them reconciled to source statements.
• QuickBooks Online: create invoices, record payments and support monthly closing for related entities.
• Reporting: produce monthly commission summaries and P&L inputs for management.

Skills

Excel

Power Query

Pivot tables

Lookups (XLOOKUP/INDEX-MATCH/SUMIFS)

HubSpot familiarity

Tools

HubSpot

Carrier portals

Job description

Avocado VA Inc. is seeking a detail‑oriented Insurance Commission Accountant to download, reconcile and match carrier and FMO statements to policies, members, and agents.

You will compute monthly agent/rep commissions using CRM data, identify discrepancies, and coordinate follow‑ups with carriers and FMOs. The role requires 2+ years in insurance commission accounting, strong Excel/Power Query skills, and experience with HubSpot or similar portals.