End User Computer Controls

1. Presented December 6, 2012 to the Association of International Bank Auditors End User Computer Controls  Marc Engel, CPA, CISA, CFERisk Management Advisory Services LLCMarc.Engel@rmadvisory.com Marc.engelcpa@gmail.com973-953-8569 Risk Management Advisory Services

2. Key Discussion Points RM Advisory Services Topics: (1)_Overview of Excel risks as part of the risk assessment       (2)_Risks of fraud and errors; best practices to prevent and detect them       (3)_Applying Change controls to Excel 

3. Excel Risks as Part of the Risk Analysis RM Advisory Services Background Companies that are subject to FDICIA or SOX should now be compliant for their primary computer systems and applications. It’s possible that some companies subject to FDICIA or SOX are insufficiently covering the risks inherent in user-directed apps. Many may need to tighten controls over applications such as Excel or Access. These are often used in accounting and finance departments to generate calculations or support for journal entries or business decisions.

4. The Problem: Inherently Weak Controls RM Advisory Services • Can anyone give some personal observations of incorrect information caused by Excel use? • Some of my observations: • a formula for a financial statement number using a random number generator; no documentation • budget equaled actual exactly because the preparer copied the budget numbers • New accountant changed an allocation; Regulator gave MoU.

5. Risks involving the use of Excel RM Advisory Services Consider these examples: • An Excel spreadsheet to control fixed assets. • Some Risks: • Formulas are not locked, eg. because each new purchase adds a line to the list of fixed assets. • Approvals consist of a signature on the hard copy. • Excel may be used to prepare financial statements and for variance analyses; • Some Risks of inaccurate information: • Lack of control over input cells, output cells, formula results, and • different versions of the spreadsheet • Consolidation worksheets – information downloaded to standardized workbook then consolidated at corporate offices

6. Need for Controls RM Advisory Services Could such errors appear in the financial statements and the MD&A? Even if totally innocent, whose responsibility? Consequently, lack of proper controls over such applications could result in a finding of a significant deficiency or even a material weakness. If not corrected prior to year end, this might have to be reported as an exception in the annual report.

7. Solution Overview RM Advisory Services • COSO compliant, effective controls are easily implemented. Five basic areas to consider are: • Risk Assessment, • Limited Access, • Design and Documentation, • Change Controls, and • Monitoring.

8. Risk Assessment RM Advisory Services Formalized risk assessment is a required element of internal control under COSO. A company could generate a risk threshold for spreadsheets, based on a percentage of its total assets or gross revenue. Any spreadsheet generating aggregate entries over that percentage would be deemed critical. So if the gross revenue is $500m and the threshold is .1% of that, any spreadsheet generating entries of $500k in aggregate over the year would be deemed “critical” and subject to additional controls.

9. Risk Assessment RM Advisory Services • Key steps: • Inventory all spreadsheets used to generate journal entries and supporting work papers for published financial information, and • measure them in aggregate by type of entry. In the above fixed asset example, all fixed asset entries would be aggregated to include the spreadsheet in the critical spreadsheet group, rather than excluding it based on many small individual entries it would generate.

10. Spreadsheet Inventory RM Advisory Services Spreadsheet inventory should have: List of all spreadsheets used for production of financial statements and numbers that support JEs. Include location, owner, main user, frequency of use. (Keep current by requiring all new spreadsheets to be registered.) Security inventory with all passwords for all sheets; Kept by IT Security.

11. Control Attributes RM Advisory Services Each spreadsheet’s purpose, frequency of being run, and formulas should be documented and explained on a separate tab in the workbook. Passwordsshould be backed up separately so if the password keeper leaves or forgets, the company still can unlock the spreadsheet. All superseded versions should be removed from the production folders.

12. Design and Documentation RM Advisory Services Good spreadsheet design makes a spreadsheet reliable, without constant testing or risk of error. Keypoints are: • Range control, Formula control, and Password protection. • Range control entails • setting up input areas, so that formulas do not need to be revised whenever data is added. This is done by • putting formulas on a separate sheet in the workbook; • putting them at the top of the page and adding data underneath. • Controling input via Excel’s excellent Forms functionality

13. Design and Documentation RM Advisory Services Formula controls: • Formulas are locked and password protected so the user cannot change them. Only specific input areas are unlocked for the user. • Formulas should be color coded to be easily recognizable. Color coding conventions (standards) should be included in the company’s procedures for designing spreadsheets. • Excel 2007 provides formats for different cell types, such as calculated cell, input, output, and others, on the Home ribbon. • Best practice designs • Use one spreadsheet for a particular purpose so a new version each month (or quarter) is not needed.

14. Limited Access RM Advisory Services • The company should set up a secure directory or folder. The network administrator limits access to • specific profiles of staff needing access to perform their duties. • Other staff members are excluded.

15. Limited Access RM Advisory Services Quick connections to external data In Office Excel 2007, you no longer need to know the server or database names of corporate data sources. Instead, you can use Quicklaunch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and makes it easier to reuse a connection or to substitute a connection with another one. (Excel online documentation). Using these features makes enforcing a secure download process straightforward; (documenting it as well).

16. Monitoring RM Advisory Services • Enforcing segregation of duties • i.e. a single individual should not have rights to both prepare and enter an entire transaction. • There must be an audit trail to document the review of entries. A checklist should be used and approved, to prove that all needed spreadsheets were updated timely. • Off premises vacation rules to prevent override of controls > Spreadsheets are updated by other staff.

17. Monitoring RM Advisory Services • Management can monitor spreadsheet activity since: • All spreadsheets to prepare FS or supporting info are inventoried, aggregated as to FS impact, risk rated, and all critical spreadsheets are secured. • Mid management knows which are the key spreadsheets and can enforce controls over spreadsheets. • Spreadsheets are password protected and the passwords are kept by IT security.

18. Change Controls RM Advisory Services Possibly contentious but consider: How would you feel about the IT staff revising your software just on the programmer’s approval? Same concept applies to your finance dept staff changing critical spreadsheets with no approval.

19. Change Controls RM Advisory Services Change controls block unauthorized changes, verify accuracy of changes to the spreadsheet, and enforce version control. Naming conventions lower the risk of using a superseded version. Granted that most spreadsheets will most likely be initially designed by the owner / user. Nevertheless, after a spreadsheet is designed properly, it should be password protected so the designer/ user cannot make changes. Changes should be performed in a test folder set up for this purpose and kept out of the production folders. A second person tests the spreadsheet with a pre-approved test set. The final version is forwarded to the approver who password protects it and posts it to the secure folder.

20. Questions? • Marc Engel, CPA, CISA, CFERM Advisory ServicesMarc.Engel@rmadvisory.com • Marc.engelcpa@gmail.com973-953-8569 Any questions can be addressed to: