WHY INTERNAL CONTROLS?
Internal controls ensure that an organization meets its objectives in operational effectiveness and efficiency, reports reliable financial statements, and complies with laws, regulations, and policies. In the United States, the Sarbanes-Oxley (SOX) Act of 2002 requires every public company to report their internal control structure to the U.S. Securities & Exchange Commission and validate the controls within 90 days before issuing the report. SOX audits are focused on four key areas: IT security, access controls, data backup, and change management. Therefore, a company should be aware of where its sensitive data is; who has access to the data; whether the data is backed up and stored properly; and that the data is maintained securely when data, employees, and software changes occur. Even though modern audit projects will vary depending on the company and location, there are several general questions each company should consider, such as:- Does the company have policies that outline how to create, modify, and maintain accounting systems, including software that handles financial data?
- What safeguards does the company have to prevent data tampering?
- Is access to sensitive data being monitored and recorded?
THE VALUE OF DOCUMENTATION
Documentation, the act of recording evidence of an event, a transaction, or an agreement, can take several forms. Some events are recorded on formal, structured documents called source documents such as invoices, purchase orders, journals, and ledgers, whereas other events are recorded on lengthy contracts. We can also use various techniques such as flowcharts, dataflow diagrams, and business flow diagrams to document processes. Recognizing the benefits of documentation, SOX emphasized and required assessments of controls to be documented in the U.S. With the requirement to assess internal controls over financial reporting, more accountants became aware of the need to document the organization’s policies, procedures, processes, and information systems. Documentation allows organizations to retain knowledge within the organization. Because employees who design and implement processes and systems may leave, the knowledge of these employees can be retained by making sure the design and implementation alternatives and decisions are thoroughly documented and updated as the system and business environment changes. Documentation also provides evidence of various events that happen in an organization that can be used to prevent, detect, and correct fraud and errors. Even though there has been an emphasis on documenting application-level internal controls, Excel spreadsheets tend to be overlooked.DOCUMENTING SPREADSHEETS
The lack of emphasis on controls over Excel stems from the challenge managers face with the application being an end-user tool. Unlike other information systems, Excel use can’t be managed centrally. But there are several actions managers can take to have some control over how Excel is being used in an organization. Here are some of the options. Create a list of Excel files. Similar to the IT asset inventory maintained by IT, department managers should maintain a list of major Excel files used by the department. Managers should also encourage every employee to create a list of spreadsheets that they use on a weekly basis. The list of Excel files should document the following items:- The name of the Excel file
- The creator’s name and job title
- The date the file was created
- The purpose of the Excel file
- The names/job titles of employees who share the Excel file
Documenting these details will help managers determine whether there are any inconsistencies and duplication of work among similar spreadsheets. Further, in case of employee turnover, the manager will be able to retain the knowledge and take an inventory of the tasks and responsibilities efficiently. The information documented in spreadsheets can then be used to train a new employee on the new job responsibilities.
Encourage maintaining Excel workbooks instead of individual worksheets as files. Instead of creating individual worksheet files for every task, encourage employees to think about categorizing and maintaining a workbook with multiple sheets for activities with a similar purpose. This will help reduce the number of workbooks maintained and reduce data errors and redundancies. Even though a file may be created for a specific purpose every week or month, the number of different types of files created can be minimized, resulting in more control over Excel files.
Encourage employees to submit the spreadsheet for independent review. After creating a workbook, encourage employees to obtain an independent review to ensure that the workbook is free from error. An independent review can be conducted by the internal audit department or enterprise risk management department, if available. In small to midsize companies, an independent review can be conducted by a supervisor.
Document each Excel file/workbook. Encourage employees to go a step beyond listing files to documenting every major Excel workbook. They can document the workbook by creating an initial worksheet and maintaining the following details (see Figure 1 for a sample documentation worksheet that can be maintained for each workbook).
- The name of the Excel file/workbook
- The creator’s name and job title
- The date the Excel workbook was created
- Subsequent updates: who updated what and when
- The purpose of the workbook
- The names/job titles of others who share the spreadsheet, if applicable
- Information about the worksheets, such as the following:
- List of worksheets maintained in the workbook
- A brief description of each worksheet
- Data entered into each worksheet and the source of the data
- Required actions or specific instructions for the worksheet
CONTROLS WITHIN EXCEL
Controls should be implemented by design. When we build an information system, we incorporate various controls and functionalities to prevent, detect, and correct any errors in data capture, process, storage, and output. We can take a similar approach when creating Excel spreadsheets and consider incorporating controls into the design of the spreadsheet. To design controls in an Excel workbook, we need to start thinking about the possibility of using certain Excel functions as a control activity. By having an internal controls mindset when designing workbooks, we can use certain built-in functions to reduce errors in data entry and calculations to increase the integrity of the data in spreadsheets. Employees can be encouraged to develop an internal controls mindset by considering the following:
How can we restrict unauthorized access to the data on an Excel spreadsheet?
Password-protecting spreadsheets can help reduce damage from loss of data in case a device such as a laptop is stolen from the organization. Passwords can be implemented at various levels on an Excel workbook. A password at the file level will prevent unauthorized access by stopping unauthorized users from opening a file. Password protection at the worksheet level will prevent other users from manipulating data on a particular worksheet. Excel allows users to lock specific cells. At the time of designing the worksheet, the creator of the worksheet should consider whether certain cells, such as those containing formulas, should be locked to prevent any manipulation and unintentional errors. By locking the cells before protecting a worksheet, you can allow other users to edit unlocked cells within the worksheet and reference the locked cells in other worksheets without being able to make changes to the locked cells. To lock specific cells in a worksheet, select the whole worksheet. On the Home tab, click on the arrow for the Font group to open the Format Cells dialog. Select the Protection tab, uncheck the locked option, and select OK. Then select the cells you want to lock, reopen the Format Cells dialog, and check the locked option. Then password protect the worksheet. You can minimize intentional and unintentional data manipulation by locking and protecting your worksheet apart from password protecting or encrypting the whole workbook. Excel offers security at the file, workbook, worksheet, and cell level. In case a workbook is shared by other users, consider whether everyone needs to see all the details in a worksheet. If the users need a summary version, use Excel functions such as grouping data to provide summary views to the respective users. Excel users can prevent errors and subsequent data manipulation by others by incorporating Excel functions into the design of a workbook.
How can we create a better audit trail in Excel?
Often, users perform analysis without maintaining a history of the activities. For example, a user may import a file into Excel and clean the data without retaining the source data. In case the user makes an error in cleaning the data, the error will get carried forward to the output. To help minimize such errors and improve data integrity by providing an audit trail, consider the following: Separate source data from the workings. Create a separate sheet to maintain the source data for the worksheet. Often employees import or copy and paste data into a worksheet and start cleaning, organizing, and working on the data in the same worksheet. But tracing an error will then be more difficult, as it may not be clear whether this error is a result of the work they’ve performed or was in fact produced when cleaning the source data. Maintaining the source data in a separate file provides an audit trail for the subsequent workings. Additionally, separating the source data from the working data will enable automation of the subsequent cleaning, organizing, and calculation using macros, formulas, and other Excel functions. By spending a little extra time when designing and creating the initial Excel workbook, the user can save time and effort for the use of the spreadsheet in the future. Consider referencing data. Excel offers various functions to reference data. Rather than copying and pasting data into the subsequent worksheets, reference the data using an equals sign and the original source location. When you reference the data, any changes in the source data will be automatically reflected in the respective worksheets where the data is referenced. This will help maintain the integrity of the data and reduce errors of omission when the workbook is used in the future. Additionally, name important cells. Right-click on a specific cell or cell range and select “Define Name” to name a particular cell or range of cells. Cell names can be used for a worksheet or workbook. Using a unique cell name will help you reference the cell anywhere in the workbook. There are several advantages to using cell references. When new rows and columns are inserted, referencing another cell by its name will retrieve the original data. Further, cell names can be used in formulas, which will help users to understand and review the accuracy of formulas. Separate data entry/input data from formulas. When creating formulas, maintain the data input separately. Don’t hard-code numbers into formulas in Excel. Sometimes it may be easier to use the formula wizards in Excel and type in the numbers and conditions into the wizard rather than reference data cells. The problem with hard-coding is that the hard-coded data makes the formula static. If the input data changes, users will be unaware of the change and will continue to depend on the output. Input data for the formula is maintained separately and can be referenced using the cell reference or the cell name in the formula to make sure any changes to the input data are going to be reflected in the subsequent calculations. A benefit of separating input data is that it increases the transparency of the calculations. Another benefit is it enables the user to maintain the integrity of the data. To increase integrity and avoid fraud from data manipulation or errors, users can lock the cells and password protect them to limit access to users. The additional layer of protection is made possible by separating input data from formulas.
How can we prevent errors in calculations?
Consider the validity of the conditions used in formulas. One major advantage of Excel is its ability to perform various analytics. Excel’s built-in formula wizards enable users to easily use advanced formulas for various calculations. The use of formulas is relatively intuitive and straightforward. But taking a controls approach to building a worksheet in Excel can make the conditions entered into formulas more robust and can increase the security and integrity of the worksheet. When creating formulas, it’s useful to ask the following questions:
- Can this condition be applied to the whole column without having to select specific cells?
- Will the condition change in the future?
- Should the condition be hard-coded, or can it be maintained separately to allow changes in the future?
- Will the calculation always have positive numbers?
- If the input is a negative number, does the calculation hold?
How can we periodically evaluate the accuracy of the formulas?
After designing the worksheet, it’s important to evaluate the integrity of the workbook. Formula auditing in Excel graphically displays the relationships between formulas and cells. Formula auditing functions such as trace precedents, dependents, and error checking can be used to evaluate which cells are contributing to the calculation in the active cell or which formulas are referring to the active cell. The graphical display using arrows to or from a cell makes visualization easy and helps identify whether there are any errors in the worksheet. The Evaluate Formula option under formula auditing can help break down complex formulas. Using the evaluate option, a user can go through complex calculations one step at a time. The evaluate option will underline the expression that will be evaluated next. It’s a good practice to do an error check once you have designed a worksheet. Under error checking, you’ll be able to make sure that there are no circular references and trace errors, and you can get help resolving the errors.
Another basic control is to periodically review the worksheet. Take a critical look at the worksheet to see whether there are ways to improve the analysis and the presentation of the information. If possible, ask a supervisor for an independent review of the worksheet.
THE CONTINUED USE OF EXCEL
The use of and dependence on Excel in organizations continue to increase. Even though complex information systems implemented in organizations can query the necessary data and create various reports for decision making, business users continue to depend on Excel to conduct various analytics and get timely data for decision making. There are several reasons for this dependence. First, when business users want information that isn’t in a standard report format, the request has to go through proper channels to the IT department. The process of requesting a report from IT may be time-consuming. Further, IT can’t build queries for every business need. Therefore, business users may find it easier to do a data dump from an existing system and combine internal data with external data to answer various strategic and operational questions. Another reason for the heavy dependence on Excel is the availability of advanced functionalities and tools that are easy to use. Built-in wizards, a help function, a large user community, and various analytical tools make Excel attractive to business users. But this dependence on Excel by business users has unintended consequences on the organization. The first consequence is the challenge to retain knowledge within the organization when there’s employee turnover. Most often, Excel spreadsheets created by users go undetected and forgotten. New employees may create a new set of spreadsheets because of a lack of knowledge of the existing spreadsheets. Even if a new employee is aware, they may not be able to understand the existing spreadsheets because of the lack of documentation about the purpose, what’s being done, who uses it, and how to use it. The lack of continuity caused by creating a new set of spreadsheets may negatively impact the organization. Therefore, one way to address continuity and retention of knowledge is to create a list of Excel workbooks used in each department and document what’s being done and how each Excel workbook is being used within the department/organization. The second consequence is the increased security vulnerability of the data because of Excel spreadsheets. Unprotected Excel files can contain large amounts of sensitive data. In case a mobile device is lost or stolen, if workbooks aren’t encrypted or password protected, cybercriminals may be able to get unauthorized access to the sensitive data easily. The third consequence is intentional and/or unintentional manipulation of data resulting in misleading information. When we discuss systems integrity, we generally use the phrase “garbage in, garbage out” to emphasize the importance of using preventive controls to ensure that the data being captured is accurate and complete. If the captured data is free from error, the probability of obtaining an accurate output that’s free of errors and omissions is higher. Consequently, we need to consider whether we take this same perspective when we use Excel spreadsheets as well. The fourth consequence is the impact spreadsheets have on audits. If sensitive financial information is maintained in Excel, audits should consider access controls, IT security, data backup, and change management issues concerning end-user computing tools such as Excel as well. Table 1 summarizes control objectives and Excel controls and how they’re related to the SOX areas of focus in particular. Creating a documentation sheet will enable the organization to identify where sensitive data is stored and who maintains it. This will allow the organization to take corrective actions promptly in case of a data breach. Documentation will help retrieve the correct version in case of data loss or disruption. Therefore, documenting your spreadsheet can increase data integrity and compliance and reduce the cost of performing SOX audits.
If employees continue to use Excel to maintain and analyze data, and produce information used in decision making and financial reporting, management should consider implementing controls over Excel to ensure the integrity of outputs produced. Dependence on Excel has unintended consequences. But using an internal controls approach to Excel spreadsheets can help minimize errors while increasing the usefulness of the spreadsheets.
July 2022
