Loading...
HomeMy WebLinkAbout2021-9 Munis Purchasing topics-Part IV Analyses report (Final 11-22-21)County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 County accounting system (MUNIS) purchasing topics Part IV – Analyses To request this information in an alternate format, please call (541) 330-4674 or send email to David.Givans@Deschutes.org Deschutes County, Oregon David Givans, CPA, CIA Deschutes County Internal Auditor 1300 NW Wall St Bend, OR 97703 541-330-4674 David.Givans@deschutes.org Audit committee members: Daryl Parrish, Chair - Public member Jodi Burch – Public Member Tom Linhares - Public member Scott Reich - Public member Summer Sears – Public member Stan Turel - Public member Patti Adair, County Commissioner Charles Fadeley, Justice of the Peace Lee Randall, Facilities Director Take survey by clicking HERE County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 TABLE OF CONTENTS: HIGHLIGHTS 1. INTRODUCTION 1.1. Background on the Audit …………..…………….………………………………………. 1 1.2. Objectives and Scope ……………….……..…………….…………….……..………… 1-2 1.3. Methodology ………………………………….………….…………………………..……… 2-3 2. FINDINGS and OBSERVATIONS 2.1. Analyses of purchasing transactions ……………………………..………….… 4-15 3. MANAGEMENT RESPONSE 3.1. Finance …………………………………………..……………..……….…………..... 16-18 County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 HIGHLIGHTS Why this audit was performed: The County transitioned to a new integrated accounting and human resource system. What was recommended: Recommendations include: • establishing unique invoice number guidance; • developing a search for duplicate invoices; • providing guidance on use of requisitions and receiving of goods; • training to approvers; • identifying routine payments that could be setup with a purchase order or contract; and • limiting usage of its accountable plan payments to employees. Part IV - Analyses The focus of this audit work is on purchasing topics for the County accounting system (Tyler Munis). The new accounting system (Munis) provides extensive access to the information it collects and handles. It provides information from the inquiry screens built into the system (which also allow export to excel for further analyses); numerous levels of reports; and computer access too many of the underlying tables. The analyses work entails various transactional analyses identified during planning. What was found During the audit, a number of analyses were performed that highlighted some areas for improvement including: 1. Duplicate payments of invoices are a potential risk due to the setup of vendors as well as entry of invoices. The analyses identified only a relatively small number of duplicate invoices. 2. A selective review of purchase orders around three way match indicated some unusual practices. 3. Approvers using bulk approvals may result in ineffective oversight. The analysis developed (to identify situations where more than one item was approved at the same time (same date/time down to the second)) showed this was occurring at a rate of 37% for invoices. 4. Some invoices offer opportunities to use purchase orders and contracts in Munis. 5. Use of accountable plan should be limited to employee payments. A number of other analyses were performed but did not yield any recommendations. Deschutes County Internal Audit County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 1 1. Introduction 1.1 BACKGROUND ON THE AUDIT Audit Authority: The Deschutes County Audit Committee authorized the review of purchasing topics for the County Accounting system (Tyler Munis) with the FY 21 internal audit workplan. Purchasing topics will include additional analyses of the purchasing transactions with the new accounting system. The overall topic was divided up due to the complexity of the topic and to release findings in a timelier manner. The first report issued in this series was on security and approval workflows (report #19/20-9); the second report on vendor master file (report #20/21-6); and the third report on procurement cards (report #20/21-8). This fourth and final audit report is on purchasing transaction analyses not previously performed in the prior reports. This will complete the series of reports on the new County accounting system (Munis). It could be beneficial to review the background on the Munis accounting system included in the first report. 1.2 OBJECTIVES and SCOPE “Audit objectives” define the goals of the audit. Objectives included: (addressed in prior reports) 1) (Report #19/20-9) Assess and evaluate the security roles and approval workflows established for processing purchases through Munis. 2) Assess and evaluate risks to the vendor master file. The vendor master file manages who and how payments are made to vendors. a) (Report #20/21-6) Changes and associated support b) (Report #19/02-9) Segregation of duties and access 3) (Report 20/21-8) Assess and evaluate the use of County issued procurement cards. 4) Analyze purchases in a number of areas, including a) Effectiveness of approvals b) Duplicate payments search County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 2 The audit work occurred during the COVID-19 pandemic. c) Use of discounts (Reviewed under vendor master work) d) Whether transactions have been split to avoid proper approvals or purchasing requirements e) Benford’s analysis – identifies unusual disbursement amounts. 5) Be aware of any issues with compliance with federal and state regulations and requirements, as may be applicable. Scope and timing: The overall audit commenced in March 2020. The work was interrupted by a pressing audit request from the Board of County Commissioners and Budget committee. Work re-commenced in September 2020. Work on analyses occurred from March 20021 through September 2021 with data used varying through March 2021. The scope of the audit did not include all aspects of internal controls employed. The accounting system is only partly represented in Munis as there are numerous other internal control systems in place at the County including administrative, budgetary, and legal processes. 1.3 METHODOLOGY “Audit procedures are created to address the audit objectives” Audit procedures relevant to the reported topics in this report include: • Interviewing staff related to accounting system questions. • Reviewing associated accounting system documentation, • Analyze background information within Munis, • Analyses for duplicate payments (including fuzzy matching – where similar matches are also considered) • Benford’s law analysis of invoice transactions for fourteen months for calendar year 2020 and first two months of 2021. • Analyzed invoices greater than $5 thousand o Use of requisition, purchase order, and contracts County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 3 o Splitting of transaction to avoid proper approvals. • Analyzed and reviewed for rounded invoices. • Analyzed and reviewed for above average payments per vendor. • Munis allows users to bulk approve items. Analyses of approvals from workflows by process and flagging approvals that occur by approver in the same date and time down to the second. This implies that approvers used the bulk approval feature to approve the selected items all at once (without spending additional time in review). It was noted that approvers that did spend additional time per item had unique date/time stamps for each item. • Analyzed purchase orders for potential issues in quantities received versus ordered and amounts paid verse ordered. Judgmentally reviewed selected purchase orders to see how three way match was working. Observations may not be able to be extended to the overall population. We conducted this performance audit in accordance with generally accepted government auditing standards. Those standards require that we plan and perform the audit to obtain sufficient, appropriate evidence to provide a reasonable basis for our findings and conclusions based on our audit objectives. We believe that the evidence obtained provides a reasonable basis for our findings and conclusions based on our audit objectives. (2018 Revision of Government Auditing Standards, issued by the Comptroller General of the United States.) The County Internal Auditor was created by the Deschutes County Code as an independent office conducting performance audits to provide information and recommendations for improvement. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 4 2. Findings and Observations The audit included limited procedures to understand the systems of internal control. No significant deficiencies were found in this audit. A significant deficiency is defined as an internal control deficiency that could adversely affect the entity’s ability to initiate, record, process, and report financial data consistent with the assertions of management in the financial statements. The findings noted were primarily compliance and efficiency matters. Audit findings result from incidents of non-compliance with stated procedures and/or departures from prudent operation. The findings are, by nature, subjective. The audit disclosed certain policies, procedures and practices that could be improved. The audit was neither designed nor intended to be a detailed study of every relevant system, procedure or transaction. Accordingly, the opportunities for improvement presented in the report may not be all-inclusive of areas where improvement may be needed and does not replace efforts needed to design an effective system of internal control. 2.1 ANALYSES OF PURCHASING TRANSACTIONS The new accounting system (Munis) provides extensive access to the information it collects and handles. It provides information from the inquiry screens built into the system (which also allow export to excel for further analyses); numerous levels of reports; and computer access to many of the underlying tables. Much of the analyses performed with this audit resulted from direct computerized inquiry of the underlying tables. Note for management: Should management want to routinely perform any of the analyses in this report (or prior reports), many of the analyses can be scripted in SQL, PowerBI, or Excel. Internal Audit can also be requested to perform the analyses again as part of follow-up services. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 5 An exact match of vendor, invoice date, invoice number and invoice amount yielded the best matches. Duplicate payments of invoices are a potential risk due to setup of vendors as well as entry of invoices. The analyses identified only a relatively small number of duplicate invoices. There were nineteen (19) confirmed duplicates which represented some .04% of invoices analyzed (even a smaller percentage if looking at invoice amounts). In order to assess if duplicates are occurring and paid, a number of analyses were performed. Many of these analyses result in “false positives” that are not duplicate payments and must be reviewed. Judgment was used to select from the identified matches to see if there were true duplicates. The most successful analyses (in descending order) were • analyzing all invoices for an exact match on vendor, invoice date, and invoice amount and allowing different invoice numbers; • analyzing all invoices for an exact match on vendor, invoice date, invoice number, and invoice amount (this exact match yielded the best matches); and • analyzing all invoices for an exact match on invoice date, invoice number, and invoice amount and allowing different vendors. Causes for the duplicated payments appear to include: • As noted in the second report on vendor master file (report #20/21-6), some vendors (with 19% of paid invoices) are allowed to have duplicate invoice numbers. This is a setting, but this doesn’t always result in duplicate payments. This setting is used typically with utilities. • In some cases, it appeared staff made a small change to the invoice number which allowed the invoice to pass duplicate testing, • There are no current financial accounting procedures applied to identify potential duplicates aside from those built into the accounting system. Overall, the impact of duplicating payment on these invoices amounted to just under nine thousand County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 6 dollars. • Some of the duplicate invoices were discovered and addressed by vendors and credits automatically applied to future invoices; • Other duplicates were discovered by staff and remediated; and • A small percentage of the duplicates were identified through this work and addressed by department staff. Recommendations Include: As previously recommended in report #20/21-6 - it is recommended all vendors be setup to search for duplicate invoices. It is recommended, for those billed invoices that only include an account number (no invoice number), for staff (based on guidance from Finance) to include additional descriptors to make a unique invoice number (such as adding a month and year to the account number). It is recommended that Finance develop procedures to review for duplicate invoice payments and, at least annually, perform a duplicate invoice search/review. Inconsistencies in usage of requisition/purchase order process and performance of 3 way match may hamper effectiveness. A selective review of purchase orders around three way match indicated some unusual practices. Those practices included: • receiving more units than actually received. In this instance, the purchase order payments were approved even though the price per unit was above that expected. This occurred since the extra receiving did not flag the invoices that were at a greater amount per unit than they should be. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 7 • setting up of quantity to be received does not align with how items should be received (i.e. three computers of one type were entered as a quantity of one). This leads to confusion if the entire order is not received at the same time. • setting up services as units to be received against (by quantity not amount). Receiving these services on quantity does not make a lot of sense and a requisition by amount or even a contract is an option that could be better. (An example of this issue is a copier lease setup for 48 equal payments and then paying those by setting up a receiving report for an item and invoice for a payment.) • entering receiving reports after recognizing that payments are being held up for the vendor. This might imply that receiving entries were done after the fact, instead of as received. Departments have been encouraged to use requisitions so the entire years’ worth of expenditures only have to go through approval workflow once rather than having each invoice get approved. This brings with it greater efficiency; higher level approvals as aggregate amounts are being approved; and visibility in budgeting as amounts are typically encumbered. In addition, using purchase orders provides for three way match that provides a control to ensure items paid for on purchase orders are actually being received. Permissions are setup (in most cases) so that the three steps in the process, the requisition (converted to a purchase order), the receiving entry, and the invoice, have to be completed by at least two different people providing greater segregation of duties. As indicated in earlier report (#19/20-9), usage of requisitions has been increasing. Using requisitions was anticipated to be a big boost in efficiency as well as improved internal control (i.e. three-way match on requisitions). In addition, entering purchases through requisitions, purchase orders, or contracts results in encumbrances in the accounting system that help identify costs incurred against available budget. This is a particularly important tool for staying within budget. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 8 CHART 1 – Diagram of three way match in requisition process for Munis accounting system (generalized) In order to provide the best purchasing environment, the County needs to provide additional direction on how the requisition and purchase order environment should be operated. Clarifying use of blanket purchase order, requisitions on amount or quantity, and even use of contracts in Munis to attain the desired outcomes of efficiency and control. The greatest impacts from processing of requisitions is how after initial approval is given on the requisition it is carried over to all of the underlying invoices. So it would be good to make sure we are operating the system in way that assures that the goods and services are authorized, purchased, and received as intended. There are not many current resources to educate users on to how to enter various purchases through the purchasing options available in Munis. It is recommended for Finance to provide additional guidance to those using the Munis accounting At least two separate people are involved in the three way match. This assures better segregation of duties. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 9 system as to the use of requisitions (by service or goods) (by quantity or amount), receiving of goods and services, and how the system is intended to operate. It is recommended Finance provide greater guidance on the importance and impacts of receiving activities. Graph 1 – Overall percent usage of bulk approvals by application type Approvers using bulk approvals may result in ineffective oversight. An analysis was developed to identify situations where more than one item was approved at the same time (same date/time down to the second for each approver). These are considered to be bulk approvals. Approvals occur in the established workflows for each application type for each step as established. See a more in depth discussion of workflows in report #19/20-9. Source: Workflow history data (Munis) for twelve months ending August 2021 Invoices are more predominant in the system and had the most approval activity. They were also A higher percentage indicates a greater usage of bulk approvals by approvers. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 10 Some approvers find the bulk approval process efficient. the most impacted by the bulk approval process with thirty-seven percent (37%) of the approvals made done in a bulk approval way. During implementation, the availability of using bulk approvals was discussed but it was indicated that (in practice) it should be discouraged. Some approvers find the bulk approval process efficient and note that they still have the ability to review the invoices during the process. There definitely were some differences evident among the approvers in how bulk approvals were being used and some had limited or no bulk approvals. In the absence of proper approvals the workflow as designed may not be receiving the due diligence required. There has not been very good visibility to the performance of approvers in the system. Many of the workflows were established as we were implementing the Munis Accounting System. It might be that some of the workflows could be streamlined more. It is also possible that efforts to improve use of purchase orders and contracts in the system will decrease the regular invoice approvals needed. It is recommended for Finance to consider additional training to approvers on batching approvals. This would include setting expectations at various levels in the approval process as well as communicating them in written procedures. It is recommended for Finance to assess, periodically, the usage of bulk approving and the impacts on the purchasing workflow. It is recommended for Finance to consider revisiting workflows periodically to see if they can be further streamlined. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 11 Some invoices offer opportunities to use purchase orders and contracts in Munis. A review of invoices greater than five thousand dollars ($5k) provided some additional insight on types of transactions that might be considered for additional usage of the accounting software’s more efficient handling of purchase orders or contracts. • Some invoices being paid are not being attributed to outstanding contracts or purchase orders, which requires them to go through a separate approval process when they may be under a contract or purchase order. • Some tax turnovers and payroll related payments could be considered for purchase orders. • Some invoice payments related to a contract are not being entered as part of a larger contract and may not be receiving the oversight and authorization at level required. • Some memorandums of understanding and line item budgetary expenditures could be setup as purchase orders or contracts. Many departments have not fully embraced the usage of purchase orders and contracts in the Munis accounting system. In the absence of using the system as intended, the departments are missing out on efficiencies and may not be receiving the approvals necessary for the extent of purchases being made. It is recommended, with the incidence of some invoices not being entered against a purchase order or contract, that Finance consider whether it would be beneficial to provide departments a tool to reference their purchase orders. It is recommended for Finance to work with departments to identify and consider setting up County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 12 routine payments with a purchase order or contract. Other analyses performed did not yield any findings. A number of analyses were performed that did not yield any specific findings. These types of analyses can identify issues of fraud or problems within the data. Benford’s Law analysis Benford's Law (which was first mentioned in 1881 by the astronomer Simon Newcomb) states that if we randomly select a number from a table of physical constants or statistical data, the probability that the first digit will be a "1" is about 0.301, rather than 0.1 as we might expect if all digits were equally likely. In layman terms in a larger random population of amounts there is an anticipated distribution of the leading digit. Use of this analysis has been identified as a way to identify unusual statistical anomalies in amounts for further investigation. A test using Benford’s Law was performed for the fourteen months of invoice transaction data ending February 2021 to identify if the leading digits of the transactions follow the expected frequency distribution. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 13 GRAPH II – Benford’s Law analysis on first digit of invoice transaction amounts Overall, there was fairly close adherence to the expected distribution. Most of the minor fluctuations from expected were explained by recurrent and expected payment values within the data. Therefore, the disbursements are not entirely random. No unusual activity was identified when reviewed at a high level. Additional Benford analysis on the first two digits, did not provide any additional insight. Analyses of rounded invoices People who commit fraud often create invoices with rounded amounts, which are invoices without pennies. The analyses looks at the prevalence of rounded amount invoices for each vendor. Observation: The review indicated nearly 40% of active vendors had entirely rounded (no cent) invoices. A review of the larger amounts by vendor didn’t identify any unusual transactions. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 14 Above average payments per vendor review Analysis used an algorithm 1 to identify invoices that are way above average for a particular vendor. It identifies those that are three times (3x) or greater for review as they indicate the payment is more than three standard deviations above the mean. Suppose a vendor normally has invoices ranging from $1,000 to $3,000; suddenly an invoice shows up for $25,000. You may want to investigate this abnormality and can do so using this alert pattern. A review of those with scores above three (3) identified an accountable plan reimbursement situation noted below for a recommendation. No other unusual situations noted. Review of invoices greater than five thousand for split invoices Splitting invoices can allow approvals below a limit that might get additional notice or review. In reviewing invoices greater than five thousand, there were no invoices observed that appear to be split to avoid proper approval. Use of accountable plan should be limited to employee payments. In reviewing some of the transactions, there were some payments identified specifically as accountable plan distributions that didn’t appear to be related to employee transactions. Those transactions related to Community Development Department grant payments. These related to certain septic tank grant payments for approved properties. 1 z-score for vendor invoices = (invoice amount – average amount of invoices per vendor) / standard deviation of invoices per vendor County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 15 Accountable plan payments are generally for allowances or reimbursements paid to employees for job-related expenses and are excluded from wages and are not subject to withholding. Payments made in this manner are not subjected to wage or 1099 reporting. Finance and the Community Development Departments were using this particular approach (since moving to Munis) for refunding someone for something that had proof of payment for it. However, the usage extended to non-employee/non-volunteers. Using the accountable plan for non-employee/non-volunteer reimbursements could potentially disqualify the County’s accountable plan. This could lead to treatment of the underlying payments as income to recipients. Through the current approach, the County may not be providing sufficient tax information reporting. Individual recipients of grants (such as those in question) might be exposed to reportable income for those grant payments. {Note: Underlying tax treatment is the responsibility of the parties involved. This audit is not a substitute for professional tax advice.} It is recommended the County limit usage of its accountable plan payments to employees/volunteers and that other departmental payments should not to be paid through the County’s accountable plan. Finance and the department should be aware these grant payments may require addressing information reporting requirements. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 16 3. Management responses Finance Department Greg Munn, Chief Financial Officer and Treasurer DATE: November 15, 2021 TO: David Givans, County Internal Auditor FROM: Greg Munn, Treasurer and Chief Financial Officer CC: Tom Anderson, County Administrator SUBJECT: Response to County Accounting System (MUNIS) Purchasing Audit, Part IV – Purchasing Transaction Analysis Report #2021-9 _____________________________________________________________________________________________________________________________ 1. As previously recommended in report #20/21-6 - it is recommended all vendors be setup to search for duplicate invoices. It is recommended, for those billed invoices that only include an account number (no invoice number), for staff (based on guidance from Finance) to include additional descriptors to make a unique invoice number (such as adding a month and year to the account number). It is recommended that Finance develop procedures to review for duplicate invoice payments and, at least annually, perform a duplicate invoice search/review. Agreed. The implementation of the Munis ERP was contractually “completed” in 2017 but was left without adequate ongoing support and coaching to assist County staff to fully utilize the system, analyze and make improvements to workflow and daily practices, examples of which are mentioned in this audit. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 17 Finance Department (continued) Additional staffing support has been approved but has been a challenge to recruit. To date, the process has been underway for eight months but has not produced a successful candidate. Once permanent support has been secured, the position will work closely with county staff, HR, Technology, Internal Audit and Finance to address these recommendations and those requiring similar attention from the three prior purchasing audits. 2. It is recommended for Finance to provide additional guidance to those using the Munis accounting system as to the use of requisitions (by service or goods) (by quantity or amount), receiving of goods and services, and how the system is intended to operate. It is recommended Finance provide greater guidance on the importance and impacts of receiving activities. Agreed. This will be part of the process identified in the response to item #1 above. 3. It is recommended for Finance to consider additional training to approvers on batching approvals. This would include setting expectations at various levels in the approval process as well as communicating them in written procedures. It is recommended for Finance to assess, periodically, the usage of bulk approving and the impacts on the purchasing workflow. It is recommended for Finance to consider revisiting workflows periodically to see if they can be further streamlined. Agreed. This will be part of the process identified in the response to item #1 above. 4. It is recommended, with the incidence of some invoices not being entered against a purchase order or contract, that Finance consider whether it would be beneficial to provide departments a tool to reference their purchase orders. County accounting system (MUNIS) purchasing topics: Part IV – Analyses report #2021-9 November 2021 Page 18 Finance Department (continued) It is recommended for Finance to work with departments to identify and consider setting up routine payments with a purchase order or contract. Agreed. This will be part of the process identified in the response to item #1 above. 5. It is recommended the County limit usage of its accountable plan payments to employees/volunteers and that other departmental payments should not be paid through the County’s accountable plan. Agreed. This will be part of the process identified in the response to item #1 above. {End of Report} Please take a survey on this report by clicking on the attached link: https://www.surveymonkey.com/r/Munis_Purchasing_Analyses_2021-9 If you would like to receive future reports and information from Internal Audit or know someone else who might like to receive our updates, sign up at http://bit.ly/DCInternalAudit.