Projects — Alexis Kelly | accordanalyst.com

Advanced Excel Case Study

International Freight Invoice Audit Automation

Ocean (LCL · FCL 20' · FCL 40') & Air · 7 Accounts · Per-Account Tolerance Rules · Demurrage · Spot Quotes

A multi-sheet Excel automation tool that replicates the core logic of international freight bill auditing — without exposing any real client data. Built to demonstrate the exact workflow used in production: rate verification, demurrage calculation, per-account tolerance classification, short-pay detection, and management dashboard reporting across Ocean and Air freight modes.

60

Simulated Invoices

7

Sheets Built

7

Account Tolerance Rules

1

The Problem — Manual rate verification across Ocean + Air lanes, no standardized demurrage tracking, no tolerance logic per account, inconsistent exception reporting across analysts

2

Data Architecture — Invoice Log (60 invoices), Contracted Rate Table + Spot Quote Register (by carrier/lane/container type), Account Tolerance Rules table (flat dollar vs percentage-based)

3

Automation Logic — XLOOKUP composite key rate matching, IFS-based per-account tolerance classification ($0.01 strict to $100 flexible), dynamic demurrage calculation against free-day allowances, short-pay and balance due detection with reason codes

4

Outputs — Exception Report (filtered overcharges, underbills, rate-not-on-file, short pay), Executive Dashboard (KPI cards, carrier breakdown, mode/container type exception rates, per-account summary)

5

Real-World Parallel — Mirrors the live audit workflow at Cass Information Systems across 20+ Fortune 500 international accounts, recovering $450K+ in carrier overcharges annually

XLOOKUP IFS / Nested IF IFERROR SUMIFS / COUNTIFS Conditional Formatting Composite Key Logic Demurrage Calculation Tolerance Classification Ocean Freight Air Freight LCL / FCL 20' / FCL 40' Spot Quotes Short-Pay Detection Balance Due Tracking Executive Dashboard

SQL Revenue Ops

Revenue Reconciliation Query Set

Aging Receivables · Short-Pay Detection · Duplicate Charges · Contract vs Billed Variance · Executive Leakage Dashboard

Five production-ready SQL queries that surface the financial pain points every company above Series B is actively trying to solve — revenue leakage, unpaid invoices, duplicate charges, short payments, and billing gaps against signed contracts. Industry-agnostic schema. Written clean, commented, and framed as business questions first.

5

Queries

4

Leakage Vectors

$100K+

Target Role Range

1

Aging Receivables — Buckets all open invoices by days past due (1–30, 31–60, 61–90, 90+) and flags ESCALATE / PRIORITY / STANDARD per account

2

Short-Pay Detection — Compares payments + credit memos to invoiced amounts, surfaces exact balance due and percentage paid per invoice

3

Duplicate Charge Detection — Uses window functions to fingerprint invoices by account + product + billing period, flags any account billed twice

4

Contract vs Billed Reconciliation — Compares actual YTD billing to contracted ARR, flags over-billing, under-billing, and renewal risk simultaneously

5

Executive Leakage Dashboard — UNION ALL aggregates all four leakage vectors into one prioritised summary with % of total risk — the output you present to a VP

CTEsWindow Functions CASE WHENCOALESCE / NULLIF UNION ALLDATE_TRUNC STRING_AGGAging Receivables Short-Pay ReconciliationARR Variance Duplicate DetectionRevenue Leakage

Python Analysis & Forecasting

Revenue Anomaly Detector

Rolling Z-Score Detection · Erosion Trend Analysis · Severity Scoring · Executive Triage

A statistical anomaly detection script that flags accounts whose revenue deviates from their own historical pattern — catching sudden drops, billing-error spikes, and slow erosion before they show up in a quarterly report. Unlike the Excel and SQL projects, which audit revenue that's already been billed, this one looks forward: it's the signal that tells a Revenue Ops team which account needs a conversation this week.

720

Revenue Records

48

Anomalies Flagged

6.7%

Clean Exception Rate

1

The Problem — Flat company-wide thresholds produce false positives on volatile accounts and miss slow decline on stable ones; manual review across 30+ accounts doesn't scale

2

Simulated Dataset — 30 fictitious SaaS accounts × 24 months, with five seeded anomaly scenarios: churn-style drop, billing-error spike, slow erosion, sustained expansion, and drop-with-partial-recovery

3

Detection Logic — Rolling 6-month baseline per account, z-score floored against std blowup, secondary erosion check for gradual decline, severity score unifying both onto one rankable scale

4

Outputs — Ranked anomaly report (CSV) with SPIKE/DROP/EROSION classification, HIGH/MEDIUM/LOW priority tiers, and a four-panel gradient chart of the top flagged accounts

5

Result — 4 of 5 seeded scenarios surfaced at the very top of the ranked report; Sterling Capital's new higher baseline self-corrected once the rolling window caught up, with no manual threshold reset needed

Pandas NumPy Matplotlib Rolling Window Statistics Z-Score Anomaly Detection groupby + apply Vectorized Conditional Logic Severity Scoring Executive Triage Churn Signal Detection Billing Error Detection Trend Erosion Analysis

Advanced Excel Interactive Dashboard

Pre-Audit vs Post-Audit Recovery Dashboard

222,900 Invoices · 6 Error Types · Live D3.js Visualization · Month-by-Month Drill-Down

An interactive dashboard simulating 12 months of freight invoice audit activity — the "necessary evil" of post-audit recovery in freight billing, quantified. Modeled in Excel with 245 live cross-sheet formulas and visualized on the case study page using D3.js: clickable month buttons, animated stacked area charts, per-month error type breakdowns, and a live cost-efficiency comparison showing that Post-Audit recovery costs 6.5x more per dollar than Pre-Audit prevention.

222,900

Simulated Invoices

$6.6M

Annual Error Pool

6.5x

Post vs Pre Cost Multiple

1

The Problem — Pre-Audit prevents errors before payment (cheap). Post-Audit recovers what was missed after payment (expensive). The dashboard quantifies exactly how much worse the "necessary evil" channel costs per dollar recovered.

2

Data Model — 30 fictitious SaaS-style freight accounts, 10,800–28,100 invoices/month with a realistic seasonal curve, 6 distinct error types each with their own Pre-Audit catch rate and monthly mix.

3

Excel Workbook — 245 live formulas across a hidden Data sheet, a hidden Calc sheet (every KPI as a formula), and a visual Dashboard sheet with a dropdown month selector driving all displayed values via INDEX/MATCH.

4

Interactive Web Dashboard — The same data model rebuilt in JavaScript on the case study page: clickable month buttons, stacked area chart, catch rate trend line, error type breakdown, investigative flag for October, and full-year FY summary.

5

The October Insight — Pre-Audit catch rate drops to 59% in October (vs. 69–71% the rest of the year) while the error pool spikes to $955K — a simulated carrier rate table update that the audit system hadn't synchronized, flooding the expensive 28% contingency recovery channel.

Advanced Excel INDEX / MATCH Data Validation Dropdowns D3.js SVG Charts Animated Transitions Pre-Audit Avoided Cost Post-Audit Contingency Recovery Catch Rate Analysis True Leakage Quantification Cost Multiple Seasonal Volume Modeling

Featured Projects