Use amount tolerances in data reconciliation (preview)

[This article is prerelease documentation and is subject to change.]

This article explains how to enable tolerances for transaction matching through reconciliation in Excel with Microsoft 365 Copilot for Finance.

The amount tolerance capability expands the matching range by enabling monetary keys to match not only through exact amounts but also through amounts that are within a user-provided range. After you specify a tolerance amount for a monetary key, Copilot for Finance confirms that the amount in the corresponding table and column is within the amount range for the tolerance.

For example, table A includes a transaction that has a value of $100 in the Invoice Amount column. This column has a tolerance of +5. Table B includes a transaction that has a value of $102 in the Ledger Amount column. Because the Invoice Amount column is mapped to the Ledger Amount column, and all other mapping keys for the two transactions match, the transaction in table B is matched to the transaction in table A. The match occurs because the $102 in table B is within the calculated range of $100–$105 that is defined by the tolerance of +5 that was applied to table A. (Note that if the specified tolerance was +/-5 instead of +5, the calculated range would be $95–$105.)

This capability supports scenarios where the amounts in mapped monetary keys might differ for reasons such as floating decimals, rounding, and exchange rates. When matching occurs as a result of a tolerance, and all other selected mapping keys and criteria match, the results are presented in the Potentially matched section of the reconciliation report.

Tolerances have the following characteristics:

• They can be added to more than one amount column in a single source table.

• They support one-to-many (1:N), many-to-many (N:N), and many-to-one (N:1) matching. Through tolerances, the aggregate sum of all amounts of a related transaction in one table can be matched to the aggregate sum of all the related transactions in the other table.

• They can be applied to monetary keys in both tables that are used in the matching process. However, they can be applied to only one side (that is, one table) of a vector mapped monetary key. Here's an example.

  Table A	Table B
  Date	Date
  Invoice Amount (Tolerance +5)	Ledger Amount
  Billed Amount	Posted Amount (Tolerance +10)

Apply tolerances to amount mapping keys

During reconciliation vector selection, you can specify which monetary keys you want to apply tolerances to.

To apply tolerances to a monetary key, follow these steps.

1. Open the Excel workbook that contains the data you want to use reconciliation in Excel to compare.
2. Follow the regular process of data reconciliation until the step where the vectors are presented. Learn how to reconcile data in Reconcile data with Copilot for Finance.
3. Select Keep to start to edit the mapping keys.
4. Hover over the monetary key where the tolerance is to be applied.
5. Select Add modifier.
6. Select one of the three range options: +/-, +, or -.
7. Specify the tolerance amount.
8. In the Reference column field, select the column for which the base of tolerance range is to be determined
9. Select Save.

1. The monetary key now contains a label indicating the tolerance amount and range option.

2. The base for the tolerance range now contains the "REF" label.

3. Select Reconcile Data to continue with reconciliation.

   Note

   Tolerances must be applied one at a time.

To edit or remove a tolerance, locate the monetary key that you want to edit or remove a tolerance for, then follow these steps.

1. To edit a tolerance, select the tolerance label (portion with the amount displayed). In the Modifier pop-up, edit the amounts and range options. Select Save.
2. To remove a tolerance, select X next to the label indicating the tolerance amount.
3. Select Reconcile or Re-reconcile to proceed.