How to Perform a Cash Reconciliation: A Real-World Example

This is the fifth chapter of a series of blog posts regarding cash reconciliation for accountants. Below are all topics included in this series:

1. An Accountant’s Guide to Order to Cash
2. Introduction to Cash Reconciliation
3. 4 Reasons Why Accounting Teams Need Cash Reconciliations
4. How to Perform a Cash Reconciliation: A Step-By-Step Guide for Accountants
5. How to Perform a Cash Reconciliation: A Real-World Example
6. 5 Most Common Problems When Performing a Cash Reconciliation

In the last chapter, you learned in detail the four steps on how to perform a cash reconciliation. In this chapter, we will go over each of the steps using a real-world scenario and example reports.

A Real-World Cash Reconciliation Example

Scenario:

Widget Company is an online store and sells one-year subscriptions for access to Widget’s photo editing software. Widget conducts all business in USD currency. The Widget Company accounting team is closing the accounting period 3/1/20XX – 3/31/20XX, and will conduct a cash reconciliation to ensure that cash is complete and accurate for the period.

Step 1: Determine the accounting period that is being reconciled

3/1/20XX – 3/31/20XX

Step 2: Download reports from financial systems

1. Download Billing System sales transactions that occurred from 3/1 to 3/31.

   Click here to view an example Billing System report.

2. Download RevRec System sales transactions that occurred from 3/1 to 3/31.

   Click here to view an example RevRec report.

3. Download Payment Processor Payout Report, gross & net of fees, from 3/1 to 3/31.

   Click here to view an example Payment Processor Payout report.

4. Download 20XX-03 Bank Statement. Filter for only bank transactions received from sales.

   Click here to view an example full Bank Statement.
   Click here to view an example Bank Statement filtered for sales.

Step 3: Calculate cash within each report

Using the example reports above, the total cash are as follows:

When calculating cash from the Billing System, use total cash net of discounts and taxes.

Step 4: Identify reconciling items across each report

Let’s identify reconciling items for the differences noted in step 3 above.

1. Compare cash between Billing System and RevRec System

   No differences

2. Compare cash between Billing System/RevRec System and Payment Processor payouts (gross of fees)

   The total cash difference is $6,328.84. Reconciling items are as follows:

   • -$5,541.61 from payout_id po_dks992lmqw is excluded from Billing System/RevRec System and included in Payout report. We need to exclude these transactions because according to the transactions detail (Payment Processor Payout report, Page 2) they were created in 20XX-02.
   • $11,870.45 from payout_id po_irudne4k234 is included in Billing System/RevRec System and excluded from Payout report. This payout is from the 20XX-04 Payout report. Because this payout was created in the beginning of the month, it is likely that some transactions within this payout are for 20XX-03 and must be included in this reconciliation. Therefore, we need to dig into the transactions detail (Payment Processor Payout report, Page 3) to sum the total transactions created in 20XX-03.

   Note: This example illustrates the complex process of comparing cash between Billing/RevRec System and Payment Processor. On the payout level, accounting typically cannot tie back to Billing/RevRec reports unless they have all the underlying transactions for each payout. But for companies with 100k+ transactions each month, this process can quickly become tedious and unmanageable in Excel.

   Ideally, a RevRec system should have a cash matching feature in place. This will enable accountants to identify which payout transaction is associated with which RevRec transaction.

3. Compare Payment Processor payouts (net of fees) and Bank cash
   The total cash difference is $3,012.21. Reconciling items are as follows:

   • -$8,938.12 Bank deposit on 3/1/20XX is excluded from Payout report and included in Bank statement. We need to exclude this Bank deposit because it is a payout from 20XX-02. In this example, we do not include prior month payouts. However, when performing a cash reconciliation for your business, you must review prior month payouts to determine which amounts to exclude.
   • $11,950.33 from payout_id po_3985nsld3ss is included in Payout report and excluded from Bank statement. This is a deposit-in transit from Payment Processor created on 3/31/20XX and scheduled to arrive on 4/1/20XX. Because we reconcile based date created, we need to include this payout.

Once all reconciling items have been identified, you can get comfort that cash and revenue balances are reported accurately.

In the next chapter, we will go through the 5 most common problems when performing a cash reconciliation. These include timing, currencies, FX rates, and more.

Read next: 5 Most Common Problems When Performing a Cash Reconciliation