CalMHSA developed a new process called Invoice Reconciliation which will allow counties to reconcile invoices created from the Invoice Generator for accuracy.
The Invoice Reconciliation compares and isolates discrepancies between the original invoices created via the Import Generator to newly created invoices also from the Invoice Generator. Multiple invoices can be uploaded at once to speed up the comparison.
This comparison matches the Service Id between the invoices and looking for instances where the Line Amount has changed between the original and new invoice. The Invoice Reconciliation tool will generate a report listing out the discrepancies thus allowing a true up to be performed if necessary.
To cut down on the need for Invoice Reconciliation it is recommended that services are claimed prior to generating invoices for contractors.
Note: The Invoice Reconciliation tool does not track invoices previously reconciled invoices. It is up to the discretion of the counties to ensure contractors are not paid more than once through this process.
Requirements
- The invoices must have been generated from the Invoice Generators provided by CalMHSA.
- Previously generated invoices must still be accessible and:
- The format of the spreadsheet must not have been modified.
- Saved in either the .xls or .xlsx or .xlsm format.
Quick Start Guide
- Ensure the original invoices are accessible.
- Create new invoices via the Invoice Generator using the same parameters used for the original invoices.
- Browse and upload the original and newly generated invoices into the Invoice Reconciliation tool.
- Click the ‘Reconcile Selected’ button.
- A reconciled excel file will be generated, select a location to save the file.
- Open the reconciled file to review the results.
Installation
- Navigate to the following location on CalMHSA’s website: Contract Provider Data Entry Only – 2023 CalMHSA
- Under the Contract Provider Invoicing Process click on the hyperlink labeled Invoice Reconciliation Tool
- Download the zipped executable file from CalMHSA’s website:
- Open the folder containing the tool and click on setup.exe to install the tool:
- A dialog box should pop up. Click Install to install the application:
Functional Overview
Go to Programs on the computer and search for InvoiceReconciliation to launch the tool.
- Click the “Browse…” button to bring up the File Explorer, navigate to the area where the invoice is stored, and select it for reconciliation. Invoices can be dragged and dropped from File Explorer into the Invoice Selection area beneath the “Browse…” buttons.
- The “Select Paid Invoices” area should be populated with previously paid invoices.
- The “Select Updated Invoices” area should be populated with updated or newly generated invoices meant to be compared to previously paid invoices.
- The “Clear Selected” buttons allows previously selected invoices to be removed.
- The button “Reconcile Selected” will be initially disabled. Once both the “Paid Invoices” and “Updated Invoices” documents (.xls or .xlsx or .xlsm format) are selected, the button becomes available. Clicking this button will trigger the reconciliation process and output the reconciled Excel file.
- The bottom left area displays the current status and additional prompts.
- The “Exit” button closes the application.
- The title of the application includes the installed application version number. Please reference this number if additional support is needed.
Saving the Reconciled Invoice
- Click the ‘Reconcile Selected’ button and a Save Dialog window will appear. A suggested filename will be populated but can be edited as needed.
- Click ‘Save’ after navigating to the location within the network or computer where the reconciled invoice should be stored.
Viewing the Reconciled Invoice
When comparing previously paid invoices to newly generated invoices, the Invoice Reconciliation tool takes the Service ID attached to the procedure and looks for instances where the Line Amount has changed between the invoices being compared.
When a discrepancy is encountered, the reconciled Invoice will display the record from both invoices.
- The Batch Id is pulled from Column A in the Input Data tab of the invoice and can be used to determine which invoice contains the record.
- The ServiceId and ChargeId should match between the two records being compared.
- The Line Amount column will show the amount originally paid to the contractor as a credit or negative amount
- The Line Amount from the newly generated invoice will reflect directly underneath the entry from the I and will have a status of ‘Please Review’ in the Status Column.
- If a service exists in the original invoice but there is not a matching service in the new invoice a message of ‘Matching service not found on updated invoice’ will appear in the Status Column.
If a service exists in the new invoice but there is not a matching service in the original invoice a message of ‘Matching service not found on original invoice’ will appear in the Status Column
- All column headers can be filtered making it easier to isolate a subset of records for review
- An Excel expression of Subtotal can be inserted at the end of the spreadsheet to quickly determine how much a contractor was under or overpaid.
-
- To insert this expression navigate to the last row and type in’=SUBTOTAL(9,S2:SXXX). Replace the ‘XXX’ with the last row number and then press enter after adding the closing parenthesis:
