Freight Quote Automation

A multi-mode VBA tool that turns a manual freight-quote workflow into a structured, auditable process — seven freight modes, sequential quote IDs, an automatic audit log, and one-click PDF export.

---

The problem

Freight forwarders issue dozens of customer quotes per week across multiple product lines — ocean FCL, ocean LCL, multi-drop sea, air import/export, and domestic road. In most small and mid-size branches, the workflow looks like this:

• Sales executive opens a spreadsheet from a shared folder
• Overwrites the previous quote with new customer details
• Emails a PDF that nobody ever sees again
• No central record of what was quoted, to whom, at what price

The consequences compound quickly: no win/loss visibility, no pricing trends, duplicate quote numbers, stale rate cards, and a pricing manager who spends their Monday morning asking seven people "what did you quote last week?"

The solution

A single macro-enabled workbook (.xlsm) that standardizes seven freight modes behind the same workflow:

1. Sales user opens the sheet for the relevant mode (e.g., Air Export)
2. Fills in customer, routing, and rate details
3. Clicks Save Quote

The VBA behind the button does five things atomically:

1. Generates a sequential quote ID scoped to mode and date (MAAAXP20260117-03 = Chennai Air Export, 17 Jan 2026, 3rd quote that day)
2. Stamps the quote number onto the form
3. Appends a row to the central Quote Log with customer, origin, destination, prepared-by, and total
4. Exports the form to PDF in a cloud folder under the same quote number as filename
5. Adds a clickable hyperlink in the log row linking straight to the PDF

The result: pricing has a single source of truth, sales has zero extra effort, and managers get a live running log with clickable evidence.

What's in the workbook

Seven mode-specific quote sheets, each with its own save macro:

Sheet	ID Prefix	Purpose
SEA	MAASIP	Sea Import (FCL default)
SEA Export (FCL)	MAASXPF	Sea Export, full container
SEA Export (FCL) Multi	MAASXPM	Sea Export, multi-drop
SEA Export (LCL)	MAASXPL	Sea Export, loose cargo
Air Export	MAAAXP	Air Export
Air Import	MAAAIP	Air Import
Domestic	MAADOM	Domestic road freight
Quote Log	—	Auto-populated audit trail

Each mode has its own Save_Quote subroutine in a dedicated VBA module (see src/) so the sheets can evolve independently without side effects.

Quote ID scheme

MAA + [Mode code] + yyyymmdd + -NN

• MAA — Chennai origin (IATA / port code)
• Mode codes chosen to be unambiguous at a glance: SXPF reads as Sea Export FCL, AIP as Air Import, etc.
• Date ensures uniqueness across modes on a given day
• Two-digit sequence resets each day, incrementing by looking up the last matching ID in the log

The ID is not cosmetic — it's the primary key linking the PDF filename, the log row, and the form content.

Tech stack

• Microsoft Excel (xlsm, 2016+)
• VBA — 7 modules (Module1.bas through Module7.bas), plus form-level code
• Data Validation for Incoterms, mode selection, and Y/N flags
• ExportAsFixedFormat for PDF generation
• Environment variables (OneDrive) so the destination folder resolves per user without hard-coded paths

Repo contents

freight-quote-automation/
├── README.md               ← you are here
├── templates/
│   └── Quote_Generator_Template.xlsm    ← sanitized, ready to customize
├── src/
│   ├── Module1_SEA_Import.bas
│   ├── Module2_Air_Export.bas
│   ├── Module3_Air_Import.bas
│   ├── Module4_Domestic.bas
│   ├── Module5_SEA_Export_LCL.bas
│   ├── Module6_SEA_Export_FCL.bas
│   └── Module7_SEA_Export_FCL_Multi.bas
└── docs/
    ├── ARCHITECTURE.md     ← how the pieces fit together
    ├── CUSTOMIZATION.md    ← adapting the tool for a different branch
    └── screenshots/

How to use

1. Download templates/Quote_Generator_Template.xlsm
2. Open in Excel and enable macros when prompted
3. Go to the mode you need, fill in the form
4. Click the Save Quote button — quote number, log row, and PDF are generated automatically
5. The Quote Log accumulates every quote issued, with clickable links to the PDF

To customize for a different branch (e.g., Mumbai = BOM prefix), see docs/CUSTOMIZATION.md.

Outcomes in production

When deployed on a freight forwarding sales desk, the tool delivered measurable improvements:

• ~8 minutes saved per quote (manual numbering, filing, logging eliminated)
• Zero duplicate quote IDs — previously a recurring issue
• 100% quote retrieval rate via searchable log + hyperlinked PDFs
• Pricing visibility at a glance — manager can filter the log by customer, lane, or mode in seconds

Case study context

Built and deployed during my role as Strategic Account & Analytics Manager at a global freight forwarding company. All customer names, rate data, and branch-specific references in this repo have been anonymized; the logic, structure, and VBA code are original.

---

_{Part of my supply chain analytics portfolio · Built by Harshaa Nandakumar}