County of Coos, New HampshireLocal Government

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).

Criteria or specific requirement: Cost principles contained within 2 CFR Part 200 require that charges to federal awards for salaries and wages must be based on records that accurately reflect the work performed. Condition: During our audit we identified multiple instances of duplicate payroll charges against the grant program. In each instance a single transaction was processed and paid to the employee, however amounts reported as grant activity were duplicated. Additionally, we identified several instances of payroll transactions reported as grant activity in which the amount reported was less than the actual payroll charges allowable based on amounts paid to the employee as a result of errors in compiling the grant reporting. Cause: A grant tracking spreadsheet is used to track expenditures being funded by the grant. This spreadsheet is populated manually using payroll system generated information provided by payroll coordinators. A process did not exist to reconcile the amounts included within the grant tracking spreadsheet and the actual payroll expenditures charged within the general ledger. As a result, certain payroll transactions were charged to the grant at amounts less than the actual allowable expenditures incurred and intended to be charged, and multiple instances of duplicate transactions were included within the grant tracking spreadsheet. Effect of potential effect: The condition described has resulted in the County undercharging as well as overcharging the grant based on actual allowable expenditures. This could result in the County being reimbursed an amount other than what it is actually entitled to. In the event of overcharging a grant for unallowable costs this could also result in the County having to return funds back to the funding source. Questioned costs: Known questioned costs of $41,046 were identified through examination of expenditures charged to the program and identifying those expenditures that were duplicate charges. Context: While the total number of payroll charges attributed to the program amounts to several thousand, VCC noted a total of 109 duplicate charges. Additionally, of the 40 payroll transactions sampled for testing, VCC noted 4 transactions that were charged at a lesser amount to the grant in error. We consider the cause to be systemic as it involves the reconciliation process between the grant reporting and the general ledger. A spreadsheet alone does not provide the level of dual entry control that the general ledger offers, and so without a reconciliation process between the two, there is substantial risk of errors on the spreadsheet being undetected. Repeat finding: No Recommendation: We recommend that use of the general ledger be incorporated into the grant reconciliation process. This could be accomplished through the use of dedicated general ledger accounts used to accumulate and track grant expenditures or through a process of reconciling grant tracking spreadsheets back to existing general ledger information. Views of responsible officials: Management plans to perform a more robust review and reconciliation of future reported amounts. Management noted that in this instance the County had the option to either claim grant funds as a replacement for lost revenue or identify specific costs, so while it was agreed that the findings were valid because the County chose to identify specific costs and the amounts reported contained errors, they noted that the level of grant funding received would not have been impacted in this specific instance due to the available levels of lost revenue funds that were not utilized.

Finding #2020-001 Department of the Treasury Coronavirus Relief Fund (ALN: #21.019) Pass Through Payments from the State of New Hampshire Governor?s Office Project Number: FAIN#SLT0082 Criteria or specific requirement: 2 CFR Part 200.302 Financial Management requires that records be maintained to identify expenditures pertaining to federal awards and be supported by source documentation. 2 CFR Part 200.430(i)(1)(vii) specifically requires support of the distribution of wages among activities whenever the wages are not solely associated with a single program and activity. Condition: During our testing of grant related payroll transactions, it was noted that in two of the samples selected supporting documentation was not maintained to support payroll amounts allocated to the program. Questioned costs: $1,450. Context: A statistically valid sample of 40 transactions was selected from a population of over 200 transactions. Of these samples, two transactions were identified which were not supported by adequate documentation of charges consistent with the requirements of the federal cost principles. Effect: Failure to retain supporting source documentation for all charges to federal programs, including detailed calculations supporting cost allocations, increases the risk that unallowable or excess costs may be charged to federal programs. Cause: Incomplete documentation. Initial funding was received as an advance from grantor. Payroll activity was evaluated retrospectively over periods worked to identify potentially eligible activities. This process was conducted by the Human Resource Coordinator and the total eligible wages identified was communicated to the Finance Director for accounting purposes. No additional supporting documentation was retained by the Human Resource Coordinator to support how the calculations were made. This was largely the result of lack of familiarity in administering federal programs which included reimbursement for payroll expenditures and for which the award was granted subsequent to the covered payroll periods. Recommendation: We recommend that all charges made to a federal award be supported by appropriate source documentation. Furthermore, when charges, including wage and wage related costs, are allocated between programs or between a program and non-program cost centers, we recommend that the supporting documentation include the assumptions and methodologies used in calculating the allocation. Lastly, we recommend that unique expenditure accounts be utilized for each program to aid in the reconciliation of program activity and to identify those costs which may be allocated between programs. Views of Responsible Officials: The documentation of nursing home staff who covered employee entrance screening was calculated as an average instead of the actual payroll cost by the HR Coordinator. As of March 2020, the whole process of documenting employees screening the employee entrance was a new procedure without any financial guidance of how to document. We learned as we progressed through this pandemic. The average wage cost for the nursing staff who covered employee entrance screening was used from March 2020 until September 2020. In October 2020, the actual payroll wage was used for each person who covered the employee entrance screening.