RCM Monthly Invoice Bank Reconciliation Report

To reconcile your monthly invoice you will need a copy of your invoice and then download the data using the Managed Billing Payments Reconciliation report. Follow the process below to ensure that the data needed to reconcile the invoice is included in the data.

To run the Managed Billing Payments Reconciliation report and download the data:

Locate the Batch number for your invoice

Set Report Parameters (Filter) 

1. Click Reports in primary navigation bar and click Managed Billing Payments Reconciliation under Advanced Reporting section.
2. Set the Posted Date Range.
3. Enter the Batch number in Invoice Batch ID.
   
   
4. Click Run Report. 
5. Click Customize Grid. 
6. Include ONLY the following columns:
   
   • Payer
   • Payment Date
   • Posted Date
   • Paid Amount
   • Payment Number
     
7. Click the CSV on the upper right tab to download.
   

Build the Summary Report

To create a summary report you will need to modify the CSV file. This is done by removing duplicate entries, arranging the columns and adding formulas for the final summary.

Remove Duplicate Data

1. Open the csv file in Excel.
2. Navigate to Payment Number and select the column.
   
3. Align the data left.
4. Select Data on the menu.
5. Click Remove Duplicates.
   
   
   
6. Verify that only the column letter for the selected column is checked, click OK.
   
7. A confirmation box is displayed indicating duplicate values found and unique values remaining. Click OK.
   

   NOTE: The Data on in the column will shift up on the spreadsheet. This will not be a concern.

Create the Summary Report

You will create the summary report on a new tab, add new columns and create formulas for the needed data.

Create a new Worksheet and copy Payment Number data:

1. Create a new Worksheet by clicking + beside the Managed Billing Payments Reconciliation tab.
2. Copy the Payment Number column to the new worksheet.
   

   NOTE: After copying verify that Payment Number is in Row 1. If not, delete the row.
   

3. Add the following column headings:
   
   • Payer
   • Payment Date
   • Posted Date
   • Paid Amount
4. The spreadsheet you've created should look like this:
   

Add Formulas

The formulas added will reference data on the original worksheet, Managed Billing Payments Reconc.

Cell B2

Enter the formula:
=VLOOKUP(A2,'Managed Billing Payments Reconc'!A:C,3,FALSE)

In this formula:

1. A2 is the first Payment Number on the worksheet you are on.
2. 'Managed Billing Payments Reconc'!A:C is the original worksheet with the data you are looking up in columns A through C.
   
3. 3 is the number of the column that will be displayed: 3=Column C - Payer
   
4. False makes sure that it is only displayed if it is an exact match.

Cell C2

Enter the formula:
=VLOOKUP(A2,'Managed Billing Payments Reconc'!A:D,4,FALSE)

Cell D2

Enter the formula:
=VLOOKUP(A2,'Managed Billing Payments Reconc'!A:E,5,FALSE)

Cell E2

This formula will add the paid amounts from the data table, Managed Billing Payments Reconc.

Enter the formula:
=SUMIF('Managed Billing Payments Reconc'!A:A,A2,'Managed Billing Payments Reconc'!F:F)

Copy Formulas

Copy the formulas down on your new worksheet to the end of the list of Payment Numbers:

Add Sum Amount

You can add a sum of the Paid Amount Column:

1. Right click on the 1 of Row 1 and select insert.
2. Select the cell above Paid Amount.
3. Enter the formula to sum the column:
   =sum(e3:e77)