How To Reconcile Accounts

University of Miami Controller’s Office January 2015

Objectives

Provide an explanation of what the formal account reconciliation process entails and how it differs from a review Identify terms and concepts that are essential to account reconciliation Present tools such as DMAS online screens and the monthly RDS Report 90 that are available for users who reconcile accounts Instruct account reconcilers how to perform this essential internal control activity which may help identify potential misappropriations and/or fraud Provide guidance on how to document that the reconciliation was performed and what records should be kept to support the reconciliation 2

What is it, How it Differs from a Review and Why is it So Important

Reconciliation Process

3

Reconciliation vs. Review

Reconciliation

The process of comparing information in two systems or locations, analyzing differences and making the necessary corrections to ensure the information is accurate, complete and consistent in both systems or locations

Review

The process of examining financial information for accuracy and reasonableness Formal account reconciliations are to be performed on a monthly basis and in a timely manner Reviewers make sure that accounts have been reconciled appropriately and in a timely manner The review process is in addition to the reconciliation process Account reconciliations are typically identified as a key internal control activity to help identify potential misappropriations and/or fraud A review is broader and less detailed than a reconciliation 4

Reconciliation is Important to an Effective Internal Control Environment It ensures the following: The accuracy and validity of the entries and balances That transactions are accurately recorded That unauthorized charges or changes did not occur That discrepancies are resolved in a timely fashion • • • • Faculty and staff should refer to Senior Vice President Joe Natoli’s memo addressing the integrity and reputation of our University The April 5, 2012 memo is found on the Internal Controls Corner web page at: http://www.miami.edu/finance/inde x.php/internal_controls/ It mentions that fraud is an avoidable waste of University resources When dealing with financial controls it is better to be safe than sorry thus it’s OK to point out inappropriate transactions.

5

Segregation of Duties and Compensating Controls

Individual in charge of reviewing financial transactions in Report 90’s for a department/area should not be approving or recording expenses reflected in the account.

Individual performing the cash collecting function should not be responsible for recording deposits or for reviewing Report 90’s.

Compensating controls, such as performing a reasonability check or a review of inventory variances by an independent party (one with no access to cash, inventory, etc. and no approval authority), could help mitigate a lack of segregation of duties as depicted above.

Reconcilers, especially for small departments, should not be initiating and approving transactions or reconciling and reviewing.

6

Account Numbers and Their Attributes Sub Object Codes Transaction Types and Codes Batch Reference ID Transaction Fields Important Formulas

Terms and Concepts Needed To Understand the Account Reconciliation Process

7

Subsidiary Ledger (SL) Accounts and Their Attributes

Account attributes or characteristics include the following: • The purpose of the account • The associated GL (General Ledger) Account number starts with zero • The Responsible Person generally has primary responsibility for control of the activity within the account • Only Sponsored Accounts have an end date • The account status may be open, frozen or deleted • The 5 digit Department # and 2 digit Sub Dept. # that the account belongs under • Account fund group • Accounts have year end codes as follows • P (project year) – balances that accumulate from the start date to the end • F (fiscal year) - accounts that do not have an end date and the balance may or may not carry forward to the new fiscal year 8

Sub-Object Codes

Four digit codes attached to the account number Categories used to record transactions in the Subsidiary Ledger (revenue/expenses) Listing and explanations for expenses and revenue found in General Accounting section of Controller’s web site 9

How Sub-Object Codes are Numbered

0001 to 0999 - Revenue Sub Object Codes, 0011 is a Balance Forward code found in designated and restricted accounts Starting with digit 1 - Used by Payroll Office for payroll related expenditures Starting with digit 2 - Used by Payroll and Benefit Offices for fringe benefit related expenditures Starting with 3, 4 , 5 - Supplies, services Starting with 6 - Capital , Non UM Title Purchases , Land, Buildings, Leasehold Acquisitions & Improvements Starting with 7 – Transfers, Annuities and Actuarial Liabilities, Debt Service for Plant Fund Starting with 8 - Indirect Costs and Cost Sharing Starting with 9 – Allocations Codes 10

Budget, Actual and Encumbrance Transactions and Their Corresponding Transaction Codes

Budget – 020 to 029 Three digits that identify type of transaction Actual – Cash Receipts - 030 to 039 Actual – Cash Disbursements 040 to 049 Encumbrance or Commitment– 050 to 059 Actual – Journal Entry – 060 to 069 • • Accounting System Transaction Codes – The University’s Accounting systems (FRS and DMAS) use transaction codes. Transaction codes identify to the system what type of entry or entries the system will be recording. Transaction codes are three digits in length.

For a complete listing and explanation visit the General Accounting section of the Controller’s Office website 11

Batch Reference ID Field

Batch reference initials identify the individual or sub system that originated a transaction Controller’s Office web-site General Accounting section contains a current listing of all authorized ID’s and their contact information Batch reference numbers are used on budget, actual and encumbrance transactions 12

Transactions in Your Accounts Contain the Following Fields

Transaction code Debit/Credit account and sub-object Reference 1 and 2 (7 characters) Date Description (20 characters) Amount Indicator •D (debit) or C (credit) or left blank to default •P/F/N – partial, final or no liquidation of commitment Batch Reference Number – includes 3 digit ID 13

Important Formula to Know Budget Balance Available (BBA)

• This formula represents the available balance on your accounts • Budget minus Actual minus Commitment Totals = BBA 14

Other Important Formulas

Open Commitment Current Amount or OCCA • This formula represents the open commitment remaining balance • Original amount less liquidating expenditure + or – adjustments = OCCA • This formula is found in DMAS and FRS screens and in Report FR90 in RDS Purchase Order Remaining Balance or PORB • Original amount of PO less liquidated amounts plus or minus adjustments • PO Remaining Balance as of today is shown on DMAS Purchasing Menu - Document Inquiry after the PO # 15

DMAS and FRS Screens Other Subsystems

Using Tools to Reconcile Accounts

16

Access to DMAS and FRS Systems

The university operates the Departmental Management Accounting System (DMAS) that you need to access to in order to properly reconcile your accounts DMAS is the web version of the Financial Records System (FRS) The integrity of the data used in the reconciliation is vital and therefore, the only data that should be used in the reconciliation process is the data in DMAS (the official University general ledger) rather than using data from shadow systems such as Dolfin 17

DMAS Online System - Access to Financial Information DMAS Log In: https://dmas.ir.miami.edu/DMAS/signon.asp

• Menu Screen available after you log in The DMAS Main Menu contains useful screens for account reconciliation Key screens used to display financial information include: • Accounts • Departments • Purchasing • Sponsored Awards 18

DMAS Account Menu Screens to be Used for Account Reconciliations

Account Summary • Listing of all sub-objects for the fiscal year • Budget, Actual, Encumbrance and Balance columns Open Commitments • Original, Liquidated, Adjustments and Current columns Account Transactions by Month • Detail for a particular month Account Detail • Transaction history detail for the fiscal year 19

Examples of Other Systems or Applications

DMAS users also have access to the FRS application with the same security Sponsored Programs Menu (SPM) allows users to manage sponsored accounts, awards and proposals UMeNet (Ariba) contains various tabs such as Purchasing and eBERF KRONOS is a timekeeping system for hourly and student employees 20

FR90 and Other Reports

Using Report Distribution System Tools to Reconcile Accounts

21

Using Report Distribution System to Reconcile Accounts DMAS provides real time inquiry while most RDS reports are generated after month end close • Visit the Report Distribution System web-site for additional information on this application RDS or EOS Thin Client Applications • Contain monthly reports such as: • FR90 SL Accounts • Labor Distribution • Salary Encumbrance Report by Account • Telecommunications – detail and summary reports 22

Month End Report FR90

Three sections include: • Account Statement In Whole Dollars (Summary) - BBA • Open Commitments Status • Report of Transactions Includes only SL Accounts • Report FR91 includes GL accounts Salary Detail • Available on Report of Transactions only if user has been given system access to salary information 23

Review of FR90 - Columns/Sections

Start with Revised Budget (Listed by Sub Code) Actual Current Month • Ties back to Report of Transactions • Payroll sub codes – refer to Labor Distribution • Includes other expenditures such as telecomm and physical plant Open Commitments • Salary and other encumbrances tie back to Open Commitment Status Section Balance Available • Apply BBA formula to get surplus or deficit amount 24

From Analyzing Online Screens and Monthly Reports To Preparing Findings and Following Up

Account Reconciliation Process

25

Account Reconciliation Involves the Following

Prepare findings & follow up on and resolve/correct pending transactions in a timely manner Analyze monthly reports including Report 90, Labor Distribution & Salary Encumbrance found in RDS Examine detailed transactions posted to the account on a monthly basis Review of overall financial standing of accounts BBA formula Track account information in DMAS & download to Excel as starting point for reconciliation 26

Go to RDS and select the appropriate reports (printing is recommended) for the account and month being reconciled

A Step by Step Look at the Account Reconciliation Process

Utilize the Open Commitments report as it is essential in reconciling encumbrances especially for the review of open Purchase Orders Perform the Budget monitoring Go to DMAS and download the Account Transactions by Month screen for the account and month being reconciled to an Excel spreadsheet Ensure that all actual transactions are accurate, complete, reasonable and allowable Prepare findings and follow up on and resolve and correct pending transactions in a timely manner 27

Analysis of Current Month Actual

Access DMAS and download the Account Transactions by Month to Excel Eliminate Encumbrance transactions (05x transaction codes) Separate Budget transactions (02x transaction codes) Group and total Actual transactions Verify that Excel Actual total ties to Actual Current Month total on Report 90 Calculate adjustments, if any, for Budget & Actual 28

Analysis of Current Month Actual (continued)

This spreadsheet can be the starting point of the reconciliation Adjustments calculation gives you adjusted BBA at the end of the reconciliation process Helps to report findings, follow up for questions, resolutions and adjustments Option to sort transactions by Ref. 1, Batch Ref ID, Description….

29

Are All Transactions Accurate, Appropriate and Incurred in the Conduct of Valid UM Business

Verify that all revenue entries are correct and all expenditures are properly allocated and pertain to the purpose of the account.

Verify back up, amount and approvals. Use all available tools discussed earlier to determine that all transactions are accurate, complete and allowable. Correct any improper charges by preparing correcting journal entries. On sponsored accounts, ensure that all charges are allowable under the sponsor's regulations. Sponsored accounts are subject to rules and regulations that vary depending on the awarding agency. Ascertain that billing and reporting is done on a timely basis and payments are received accordingly. Corrections after 90 days need justification. An Excel spreadsheet may be kept as reference for tracking corrections . These steps are not meant to replace other departmental reconciliation requirements. 30

Prepare Findings and Follow Up

Use the Excel spreadsheet and notations in the Report 90 summary page. Report your findings to the account responsible person and immediate designees as applicable. Include information about journal entry corrections already prepared or to be prepared in the future. 31

How to Document That the Reconciliation Was Performed

Once the account has been reconciled allow for a place on the Report 90 printout for reconciler and reviewer signatures and dates The approver then reviews and signs and dates to attest their review and agreement of the reconciled account documentation Sign and date that you have reconciled the account and then provide the reconciliation and applicable documentation to the reviewer 32

Keeping Records of the Reconciliation

Supporting documentation for transactions recorded and used for reconciliation purposes should be retained Supporting documentation should be kept in accordance with record retention guidelines for the University Visit the UM Records Retention Schedule for guidelines at: http://www.miami.edu/finance/index.php/spend_management_and_records _retention/um_records_retention_schedule / 33

Summary

Documenting that the reconciliation was done and work papers are maintained per UM policy What are the processes involved in performing a proper account reconciliation The difference between reconciliation and review was discussed The account reconciler should be knowledgeable in key terminology and concepts found in online systems and reports before the account reconciliation takes place Account reconcilers should have access to UM systems as needed to facilitate the reconciliation 34