Audit 397440

Department of the Treasury Local Assistance and Tribal Consistency Fund (ALN 21.032) Activities Allowed and Unallowed, Allowable Costs / Cost Principles, and Reporting Criteria or specific requirement: In accordance with 2 CFR Part 200.303, non-Federal entities must establish and maintain effective internal control over the federal award that provides reasonable assurance that the entity is managing the federal award in compliance with federal statutes, regulations, and the terms and conditions of the federal award. Furthermore, effective internal control should include regular reconciliations between subsidiary tracking systems (spreadsheets) and the primary accounting system (General Ledger) to ensure data integrity. Condition: While the County utilizes General Ledger software for its primary accounting functions, grant-level financial tracking and reporting are often performed using manual spreadsheets. Although the data entered into these spreadsheets is intended to reflect the same transactions recorded in the General Ledger, formal reconciliations are not regularly performed to ensure the spreadsheet data matches the General Ledger records. Cause: Management relied on the assumption that because the spreadsheet data originates from General Ledger transactions, the output would inherently remain consistent. Management has not implemented a formal policy or procedure requiring a periodic, documented reconciliation between these two data sets. Effect of potential effect: The lack of reconciliation between spreadsheets and the General Ledger increases the risk of misstatements within the Schedule of Expenditures of Federal Awards or noncompliance of individual grants and programs. Furthermore, the use of spreadsheets alone lack the automated controls found in the General Ledger software, such as: 1) Data Protection: Risk of accidental deletion or modification of formulas/data. 2) Data Validation: No automated prevention of duplicate entries or formatting errors. 3) Dual Entry/Audit Trail: No systematic record of who changed data or why. Questioned costs: No reportable questioned costs. Context: A total of 60 transactions were selected for testing from programs that relied on spreadsheets for tracking and reporting. Of the 60 transactions selected, a sole deviation was identified. The difference between what was reported on the tracking spreadsheet and the General Ledger was trivial in amount and clearly immaterial to the program and Schedule of Expenditures of Federal Awards as a whole. However, the reliance on spreadsheets, and a lack of reconciliation back to the General Ledger for grant tracking and reporting, was found to be systemic to the County’s system of internal control over grant reporting. Repeat finding: No Recommendation: We recommend that the County implement a formal reconciliation process between the grant tracking spreadsheets and the General Ledger. This reconciliation should be performed periodically, such as monthly, and should include: 1) Documented Comparison: A side-by-side verification of total expenditures and revenues per grant on amounts reported within the general ledger and amounts included on subsidiary tracking spreadsheets. This verification should include specific general ledger account numbers used for tracking revenues and expenditures. 2) Supervisory Review: Reconciliations should be reviewed and signed off by a person independent of the spreadsheet preparation. 3) System Integration: The County should explore available grant management features and modules within their existing General Ledger software to eliminate the reliance on manual "shadow" systems or spreadsheets. Views of responsible officials: Management acknowledged the finding and recommendation, and plans to implement a formal reconciliation process between grant tracking spreadsheets and the General Ledger. This reconciliation will be performed at minimum quarterly (when most grants are submitted).