The Core Verification Process

Effective discrepancy detection relies on a methodical comparison of key data points. Follow this structured approach using your CNFANS spreadsheet.

1. Step 1: Data Consolidation and Alignment

   Begin by importing or entering data from your Payment Log (incoming/outgoing invoices) and Freight Log (shipment details, weights, distances, carrier contracts) into a unified CNFANS Spreadsheet. Ensure both logs share a unique identifier for each transaction, such as a Shipment ID, Purchase Order Number, or Invoice Number.

   • Tip:VLOOKUPXLOOKUP

2. Step 2: Key Field Comparison

   Create adjacent columns to compare the following primary fields. Flag any rows where the values do not match exactly or fall outside acceptable tolerances.

   Field to Verify	Payment Log Source	Freight Log Source	Potential Discrepancy
   Total Charge	Invoice Amount	Calculated Rate × Quantity	Overbilling, underbilling, incorrect rate application.
   Quantity/Weight	Billed Weight/Units	Actual Shipment Weight/Units	Billable vs. actual measure mismatch.
   Service Level	Charged Service (e.g., Express)	Service Used (e.g., Standard)	Charged for a premium service not rendered.
   Accessorial Fees	Fuel Surcharge, Detention etc.	Documented Occurrence of Event	Fees applied without justification or documentation.
   Dates	Invoice Date / Service Period	Actual Shipment/Pickup Date	Billing for services outside the contract period.

3. Step 3: Automated Flagging with Formulas

   Leverage spreadsheet formulas to automate discrepancy detection. For example:

   • Use an IF=IF(ABS(PaymentLog_Charge - FreightLog_CalculatedCharge)     Tolerance, "CHECK", "OK").
   • Use COUNTIF
   • Conditional Formatting can be applied to highlight entire rows in red where any key field mismatch is found, making errors visually standout.

4. Step 4: Root Cause Analysis and Resolution

   Investigate flagged transactions. Common root causes include:

   • Data Entry Errors:
   • Contract Misapplication:
   • Carrier Billing Errors:
   • Missing Documentation:

   Document the cause and action taken (e.g., "Credit Note Requested," "Data Corrected") in a dedicated column in your CNFANS sheet.

5. Step 5: Regular Auditing and Reporting

   Make this cross-verification a standard monthly or quarterly audit process. Generate summary reports from your CNFANS Spreadsheet showing:

   • Total discrepancy count and total monetary value in question.
   • Most common error types.
   • Primary carriers or lanes prone to errors.

   This data is crucial for negotiating contracts, improving internal processes, and holding partners accountable.