Citrus Coast Foods — Quarterly Compliance Reporting

The problem

A regional food distributor was required to submit quarterly compliance reports to AFS Technologies as part of its Holiday Inn Express franchise supply agreement. The reports needed to conform to a strict 21-column pipe-delimited schema — vendor, manufacturer SKU, operator address, unit price, quantity, and extended price per line item, per property, per quarter. QuickBooks, the distributor's accounting system, produced no output that matched this format. Every quarter, raw sales and item data had to be manually extracted, transformed, and validated before submission.

The transformation was error-prone without a systematic approach. Items missing from the vendor lookup table produced silent failures — fields populated with #N/A rather than required values. Submissions with unresolved fields were rejected by the compliance system, requiring correction and resubmission. There was no repeatable process, no validation step before submission, and no management-level visibility into what was being sold where, at what volume, quarter over quarter.

The system

SCVdata built and operated a workbench-driven quarterly reporting process centered on a master Excel workbook with VLOOKUP-driven enrichment logic connecting raw QuickBooks exports to a vendor lookup table and item master. The operating procedure: run two QuickBooks exports (Item Listing and Sales By Customer Detail), paste into the workbench, allow formulas to resolve, filter to Holiday Inn Express accounts, validate required fields, copy as pipe-delimited output, and upload to the AFS submission target. A second compliance program introduced in 2016 — Compass / Foodbuy — expanded the required schema to 57 columns, adding manufacturer IDs, pack-size breakdowns, distributor address blocks, and category codes not present in the original AFS format.

The demo reconstructs this workflow as five working surfaces:

• Quarterly Compliance Workbench — four-step interactive reconstruction: source data load, enrichment with live #N/A display, HIE filter and field validation, pipe-delimited output preview
• Validation Queue — issue classes drawn from actual rejection and correction patterns
• Submission Preview — flat-file output viewer with toggle between the 21-column AFS/IHG schema and the 57-column Compass/Foodbuy schema
• Analytics Console — modernized reporting layer with account velocity, product movement by category, period comparison, account drilldown, and a supplier-safe review mode
• Quarter History — operational record across the documented quarters in the 2014–2016 window

Before / After

Before: raw QuickBooks exports with no path to compliance submission, no validation step, and no quarter-over-quarter visibility into account or product performance. After: a repeatable quarterly workbench delivering accepted flat files, with a structured exception-handling process and a basis for management reporting.

Technical context

The demo at citruscoast.scvdata.com is a frontend reconstruction — a self-contained HTML/CSS/JavaScript application with no backend, no live data connection, and no external API integration. The demo is seeded from anonymized structures derived from historical source files: QuickBooks exports, AFS submission flat files, and operational records from the 2014–2016 engagement. The distributor client is anonymized as Citrus Coast Foods; Holiday Inn Express property names appearing in the demo are public-facing hotel properties, not client-identifying. Margin figures shown in the Analytics Console are illustrative estimates only — units and revenue are the authoritative metrics derived from the source submissions. Public-safe credibility anchors: six documented quarters across the 2014–2016 operational window, approximately 3,200 line items per quarter in accepted submissions, seven Holiday Inn Express accounts at peak account base (five in the final documented quarter).