VBA Margin Reporting
A VBA-powered Excel workbook that calculates true landed cost and real-time % margin at item/container level, surfacing pricing issues fast.
Results
40+
hours/month saved
Automated reporting & reconciliation across shipment, cost, and inventory.
+5%
gross margin on new SKUs
Initial pricing informed by landed-cost visibility (freight + storage).
Real time
Report Cycle reduced from 4 weeks
Shipment Excel, Cost Excel, paper, Sage → single ERP entry with Sage sync.
Discover
-
Costs (freight, storage, trucking) were separate from inventory movements, so margin by SKU/lot wasn’t visible in real time.
-
Data was re-entered across shipment Excel, inventory Excel, and accounting, creating delays and drift.
-
Price changes weren’t consistently dated, making trend and audit reviews hard.
Develop
1. Link costs + sales at the source (single entry).
Previously, import costs and sales lived in separate files, and staff stitched them together for every report. I redesigned the workbook so cost and sales are entered in one place, keyed by container/SKU/lot/date. The model auto-joins the data and computes landed cost and real-time % margin as soon as entries are made—no ad-hoc VLOOKUPs or copy-paste.
2. One-click monthly reporting (for non-technical users).
Monthly summaries used to require manual filtering and consolidating into a separate sheet. I wrapped the logic in VBA buttons (e.g., Refresh Data, Generate Monthly Report) that rebuild pivots and export standardized views, so the team gets the same report in seconds without touching formulas.
Iterations
Deliver
-
40+ hours/month saved on reconciliation & reporting.
-
Faster data retrieval (no file-hunting). One-click refresh consolidates shipment, cost, and inventory into a single view.
-
Real-time % margin at item & container level (price vs landed cost).
-
Negative-margin SKUs flagged → targeted pricing/clearance actions.
-
Cost transparency: per-type breakdown (product, freight, storage, trucking, fees) in reports.
-
Less manual stitching: one workbook consolidates data needed for decisioning.
-
Set the stage for ERP: validated allocation rules & data model used in Phase 2.