Accurate financial reconciliation is the cornerstone of trust and efficiency in logistics and supply chain management. For users of the CNFANS Spreadsheet, cross-verifying payment records against freight logs is an essential procedure to ensure fair and accurate billing. Discrepancies, if undetected, can lead to significant financial losses and operational friction. This guide outlines a systematic approach to identify and resolve such inconsistencies.
The Critical Importance of Cross-Verification
Payment logs and freight logs serve as two vital narratives of the same transaction. The freight log details the physical movement and contractual terms—weight, dimensions, origin, destination, and agreed rates. The payment log records the financial execution—invoices, amounts paid, dates, and payment methods. Discrepancies between them can arise from:
- Data Entry Errors:
- Rate Misapplication:
- Accessorial Charge Omissions:
- Delivery Status Mismatch:
- Currency or Unit Discrepancies:
- Rate Misapplication:
Step-by-Step Detection Process in CNFANS Spreadsheet
Step 1: Data Preparation and Alignment
Ensure both your payment and freight logs are within the same CNFANS workbook or can be linked. Create a common key identifier for each transaction, such as a Booking Reference Number, Pro Number, or a combined field of Shipper Date + ID. This key will be the anchor for all comparisons.
Step 2: Core Field Comparison Using Formulas
Leverage spreadsheet functions to flag differences automatically.
| Field to Verify | Recommended Formula/Method | Purpose |
|---|---|---|
| Total Charge | =IF(ABS(Payment_Log!C2 - Freight_Log!F2) Tolerance_Value, "CHECK", "OK") |
Flags monetary differences beyond a set tolerance (e.g., $1 or 0.5%). |
| Date Matching | =IF(Payment_Log!A2 < Freight_Log!B2, "Date Mismatch", "") |
Highlights inconsistencies in invoice date vs. shipment date. |
| Shipment Status | =IF(AND(Freight_Log!H2="CANCELLED", NOT(ISBLANK(Payment_Log!C2))), "BILLED CANCELLED", "") |
Catches invoices raised for cancelled shipments. |
Step 3: Identify Missing or Duplicate Entries
Use the VLOOKUPXLOOKUP
Step 4: Audit Accessorial Charges
Create a subtotal for base freight and a separate sum for accessorial charges in both logs. Compare these subtotals individually. A common discrepancy is accessorial charges billed but not documented in the freight log's service notes.
Best Practices for Ongoing Accuracy
Standardize Data Entry
Use dropdown menus and data validation lists in CNFANS for rates, service types, and currencies to minimize manual errors.
Implement a Regular Schedule
Perform this cross-verification weekly or bi-weekly to prevent backlogs and make issue resolution faster.
Document Discrepancy Resolution
Add a "Resolution Notes" column in your CNFANS sheet to log the cause and action taken for each flagged issue, creating a valuable audit trail.
Use Conditional Formatting
Apply color scales to highlight high-value discrepancies, making them instantly visible during your review.
Conclusion
The CNFANS Spreadsheet