Supplier reconciliation Excel template and sample files
Download a free Excel template that lays out supplier statement and AP ledger columns. Save your own XLSX file and reconcile it in SupplierRecon. The files stay in your browser and are not uploaded.
Preview the workbook
These rows are the fictional rows stored in supplier-reconciliation-template.xlsx. The sheets, in order, are Supplier Statement, AP Ledger, and Instructions. Replace the sample rows, keep the header, and save the file as XLSX.
Supplier Statement
AP Ledger
Instructions
No transaction rows. This sheet explains which worksheet to select and how each column is used.
Download the Excel template
The workbook is an input layout, not a finished reconciliation. Sample rows are marked SAMPLE / FICTIONAL. Keep the header row when you replace them.
It does not perform the reconciliation itself. SupplierRecon calculates Exact matches only after you confirm the worksheet, field mappings, formats, and supplier scope.
What is included
- Instructions — which columns SupplierRecon can map, and which sheet to select for each file.
- Supplier Statement — header row plus fictional invoice and credit rows.
- AP Ledger — the same columns, with fictional AP rows you can replace.
Both data sheets use one header so the field mapping stays consistent. Column names match the labels SupplierRecon already recognizes, including Invoice No., Gross Amount, Invoice Date, Currency, Supplier Name, Type, and Description.
Supplier statement fields
On a narrow screen, scroll each field table sideways. Amount, currency, supplier, and the other columns stay in the table.
| Column in the template | Mapped field | Role | Handling note |
|---|---|---|---|
| Invoice No. | Reference | Required | Invoice, credit, or document identifier. Keep long values as text. Scientific notation can block a row. |
| Gross Amount | Amount | Required, unless you use debit and credit | Signed amount. Parentheses or a trailing minus can represent a credit. Confirm the decimal separator, grouping, and sign direction. |
| Invoice Date | Date | Commonly used | Used for the date window. A missing date creates a warning. When both dates exist, a date outside the window cannot be Exact. |
| Currency | Currency | Commonly used | Stops cross-currency matching. A confirmed three-letter default can fill a missing mapped column. |
| Supplier Name | Supplier | Commonly used | Confirms that the statement belongs to the supplier for this run. |
| Type | Transaction type | Optional | Review context, such as Invoice or Credit note. |
| Description | Description | Optional | Review context. Similar text can rank a candidate. It never creates an Exact match. |
If the statement export uses separate debit and credit columns, replace Gross Amount with Debit and Credit and map both. When those cells are filled, SupplierRecon uses them instead of a single amount column.
AP ledger fields
The AP Ledger sheet uses the same columns. Reference and amount are still required. Supplier Name is required when the AP export contains more than one supplier: map it and confirm the supplier for this run. Rows for other suppliers are excluded with a visible warning and cannot become Exact matches.
| Column in the template | Mapped field | Role on an AP export |
|---|---|---|
| Invoice No. | Reference | Required. Compared with the statement reference after text normalization. |
| Gross Amount | Amount | Required, or replace it with Debit and Credit. |
| Invoice Date | Date | Commonly used. Document date or posting date, confirmed as one date format. |
| Currency | Currency | Commonly used. Control totals never combine currencies. |
| Supplier Name | Supplier | Required for a multi-supplier AP file. Exact matching stays off until scope is confirmed. |
| Type | Transaction type | Optional review context. |
| Description | Description | Optional. Used only to rank a review candidate. |
How to use the template
- Prepare the supplier statement. Open the Supplier Statement sheet, delete the sample rows, and paste or type one row per invoice, credit, or other statement line. Leave the header in row 1.
- Prepare the AP ledger export. Do the same on the AP Ledger sheet, using the open-item or ledger export for the same supplier and period.
- Open SupplierRecon. Choose the workbook once for the statement and once for the AP ledger. Select the matching worksheet each time. Do not select Instructions.
- Confirm field mappings and formats. Check the header row, amount format, date format, currency, and supplier scope. Exact matching stays disabled until that scope is confirmed.
- Review Exact matches and review candidates. Unique one-to-one results that meet the confirmed rules are Exact. Amount differences, grouped rows, conflicting groups, and similar text stay in review.
- Export the reconciliation workpaper. Download the separate eleven-sheet workbook. It records source rows, rules, decisions, exceptions, and control totals.
How SupplierRecon reads XLSX and CSV
If both sheets are in this workbook, open it twice: once as the supplier statement, with Supplier Statement selected, and once as the AP ledger, with AP Ledger selected. Do not import Instructions. A CSV file is one table. The sample CSV files use these same column names. Parsing stays in the browser. SupplierRecon does not upload the template or the exports you reconcile, and it does not read PDF statements.
What SupplierRecon considers an Exact match
An Exact match is only EXACT_UNIQUE_1_1. The normalized reference, the amount in integer minor units, and the currency must occur once on the statement and once on the AP ledger. Supplier scope must be confirmed and compatible. If both dates exist, they must fall inside the confirmed date window. A missing date creates a warning, and that missing date alone does not block an otherwise unique exact key.
These results are review candidates, not Exact matches:
RULE_UNIQUE_1_1— same reference and currency, with an amount difference inside the tolerance you confirmed.SUGGESTED_1_N— one statement row and one AP group whose amounts add up exactly.AMBIGUOUS_MULTIPLE_CANDIDATES— more than one AP group produces the statement amount.SUGGESTED_FUZZY— same amount and currency, ranked by similar reference or description text.
Exact means the loaded rows satisfy that deterministic rule. It is not payment approval, an accounting conclusion, or an audit opinion. Each result code is defined in the reconciliation methodology. This template only prepares the columns.
Template vs generated workpaper
The Excel template prepares input. The generated workpaper is the reconciliation output. SupplierRecon creates that second file only after a run, and it is not included in this download.
- Summary
- Rules & Parameters
- Exact Matches
- Reviewed Matches
- Unmatched Statement
- Unmatched AP Ledger
- Data Warnings
- Exceptions
- Source A Reconciled
- Source B Reconciled
- Run Manifest
Before that download, the export step reopens the workpaper and checks sheet names, row classifications, and control totals. A control total that does not balance stops the export. The check confirms the file structure. It does not approve the accounting content.
When the template is useful
- An AP team already has a statement and a ledger export in Excel or CSV and wants one shared column layout.
- The source files use different headers, and someone needs a readable copy before mapping.
- The working file should stay on the computer that runs the reconciliation, without sending transactions to an upload service.
Limitations
- No PDF, image, or OCR intake. No ERP connection and no invoice retrieval.
- No macros, XLSM, ODS, encrypted workbooks, or legacy Excel containers.
- CSV files can be up to 25 MB and 200,000 data rows. XLSX files can be up to 10 MB and 50,000 data rows.
- Uncertain items still need a person. Tolerance, one-to-many groups, and similar text are not accepted automatically.
Optional CSV samples
Two smaller fictional CSV files show a complete local run: one unique exact invoice, a one-cent amount difference, one statement invoice split across two AP rows, and a credit absent from the AP file. They use the same column names. The supplier, Northshore Office Supply, is fictional.