Case study · Sales analytics & automation

A refund simulator for a 150,000-record portfolio.

A national insurance brokerage needed to calculate customer refunds accurately, at scale, with no BI platform in place. I built the macro-driven Excel tool that replaced a manual, error-prone calculation with a validated, auditable process — and rolled it out to the national sales force.

Advanced Excel / VBA Data reconciliation Pricing logic Process design

Try the interactive demo (fictitious data)

Context

Manual refunds, at national scale

Credit-linked insurance policies (unemployment, incapacity, and serious-illness / accidental-death riders) generated a constant stream of cancellations. Every cancellation triggered a premium refund that depended on the credit line, the policy term, the disbursement and cancellation dates, a product-specific rate factor, and whether a penalty applied.

Getting it wrong meant overpaying customers or losing revenue — and with more than 150,000 policy records, every error had to be found by hand.

What I built

One engine, three editions

1
Lookup
Enter an insured ID; pull every policy they ever took
2
Liquidate
Enter a policy number; get a validated refund figure
3
Rules built in
Product rate factors, audit checks and a 50% penalty case
4
Deployed
Three branded editions for different business partners

How it works

From insured ID to a frozen, reportable number

Clean the workspace

One button resets the form so each liquidation starts from a known state.

Look up the customer

The macro filters the 150,000-record production base and lists every associated policy.

Liquidate by policy

It retrieves status, credit value, term, credit line and key dates automatically.

Compute the refund

Applies the correct rate factor, calculates base amount, insurance value, earned days and premium — halved when the penalty rule applies.

Freeze the result

The calculated block is pasted as values, so the number cannot drift once reported.

Inside the tool

The actual workbook (interface in Spanish)

Screens from one edition, personal data masked.

Simulator lookup screen with insured ID input and policy table — Lookup screen: insured ID input, policy table and on-screen process instructions.

Liquidation panel with policy data and refund fields — The liquidation panel: the refund is built field by field and frozen as values.

Client and company details are removed for confidentiality.

Impact

What it changed

The manual refund calculation became a repeatable, auditable process used by the national sales force — with product rules encoded once instead of re-derived by hand each time.

Hours → minutes per refund Fewer errors National rollout

Skills demonstrated

Why it matters for your business

Σ
Data reconciliation at scale
Querying and trusting a 150,000-row base
％
Pricing & rate logic
The same rigor behind accurate price setup
⚙
Automation
Removing manual work with reliable tools
☺
Enablement
Documented steps a whole sales force could run

Have a process that needs this treatment?

If a calculation, report or workflow is slow and error-prone, that’s exactly the kind of thing I automate.

Get in touch