Financial Accounting and Controls

THE USE OF SPREADSHEETS

Accounting and Controls home Fiscal Close Information Financial Controls Year-End Accrual Procedures The Use of Spreadsheets SAS 115 

Responsible Office

Office of the Controller

Contact:

Questions about this document may be directed to the General Accounting Office: gao@berkeley.edu.

Campus units may rely on spreadsheets as a key component in their financial (actuals) reporting and operational processes. As a result, the use of spreadsheets is an integral part of the information and decision-making framework for these departments. In developing and using spreadsheets, departments need to balance their ease and flexibility against the importance of reliable information for management's use.

Campus unit management is responsible for the accuracy of spreadsheets used as tools in its financial reporting and operational processes. Campus units should periodically evaluate the adequacy of controls over spreadsheets supporting their significant general ledger account balances and financial management reporting.

This document outlines specific control activities that should be considered by department management in its use of significant spreadsheets as part of its financial reporting or processing.

Procedures

There are five high-level steps to implementing a process to ensure appropriate controls over spreadsheets used in financial processing or reporting. Given the potentially large number of remediation items relating to spreadsheet controls, it is recommended that efforts start with high priority items, defined as financial spreadsheets containing complex calculations which support chart strings with significant balances or significant management reports.

1. Inventory Spreadsheets Used To Support Significant Financial Processes

This step is critical to ensuring the population of spreadsheets in use within the department is defined and subjected to evaluation.
The inventory should include:

a. the name of the spreadsheet,

b. a brief description of the spreadsheet and the financial amounts calculated,

c. the department and person responsible for the development as well as any other departments that utilize the spreadsheet, and

d. the frequency and extent of the changes to the spreadsheet.

2. Evaluate The Use And Complexity Of Spreadsheets

After the inventory, the use and complexity of each spreadsheet should be evaluated.

Spreadsheets can be grouped into the following use categories:

• Operational: Spreadsheets used to facilitate tracking and monitoring of workflow to support operational processes, such as a listing of open purchase orders, unreviewed vouchers and other information that previously would have been retained in manual, paper file folders. These may be used to monitor and control that financial transactions are captured accurately and completely.

• Analytical/Management Information: Spreadsheets used to support analytical review and management decision-making. These may be used to evaluate the reasonableness of financial amounts.

• Financial: Spreadsheets used to directly determine financial statement transaction amounts or balances that are populated into the general ledger and/or subsidiary financial systems.

In addition, the complexity of spreadsheets may be categorized in the following manner:

• Low: Spreadsheets which serve as an electronic logging and information tracking system.

• Moderate: Spreadsheets which perform simple calculations such as using formulas to total certain fields or calculate new values by multiplying two cells. These spreadsheets can be used as methods to translate or reformat information, often for analytical review and analysis or for recording journal entries.

• High: Spreadsheets which support complex calculations, valuations and modeling tools. These spreadsheets are typically characterized by the use of macros and multiple supporting spreadsheets where cells, values and individual spreadsheets are linked. These spreadsheets might be considered "applications" (i.e., software programs) in their own right. They may be used to determine transaction amounts or as the basis for journal entries into the general ledger or financial statement disclosures.

3. Determine the Necessary Level of Controls for the Spreadsheet

The level of controls should be relative to the spreadsheet's use, complexity and the required reliability of the information. The appropriate combination of controls and processes, such as those described below, should be considered to mitigate the risks associated with spreadsheets.

a. Change Control: Maintaining a controlled process for making changes to a spreadsheet, including testing and formal sign-off that the change is functioning as intended.

b. Version Control: Ensuring only current and approved versions of spreadsheets are being used by creating naming conventions and directory structures.

c. Access Control: Limiting access at the file level to spreadsheets on a central server and assigning property rights. Spreadsheets can also be password protected to restrict access.

d. Input Control: Ensuring that reconciliations or reviews occur to make sure that data is inputted completely and accurately.

e. Security and Integrity of Data: Using a process to ensure that data embedded in spreadsheets is current and secure, such as "locking" or protecting cells to prevent inadvertent or intentional changes to standing data.

f. Documentation: Ensuring that the appropriate level of spreadsheet documentation is maintained and kept up-to-date to understand the objective and specific functions of the spreadsheet.

g. Development Lifecycle: For critical and complex spreadsheets, the use of a standard development process, including requirements, design, building, testing and maintenance.

h. Back-ups: Implementing a process to back up spreadsheets on a regular basis.

i. Archiving: Maintaining historical files no longer available for update in a segregated drive and locking them as "read only".

j. Logic Inspection: Reviewing the logic in critical spreadsheets by someone other than the user or creator of the spreadsheet.

k. Segregation of Duties: Defining and implementing roles, authorities, and procedures for issues such as ownership, sign-off, and usage.

l. Analysis: Implementing analysis to find errors in spreadsheets used for calculations.

For more significant amounts and/or spreadsheets with higher complexity, migration to an application system with a more formalized information technology controls environment may be necessary to achieve an adequate level of control.

4. Evaluate Existing "As Is" Controls for Each Spreadsheet

Evaluation of existing controls is done by comparing the existing spreadsheet controls against a checklist of necessary or desired controls (such as those listed above) based on the use and complexity of the spreadsheet. Gaps between existing and necessary or desired controls should be identified for fixing.

In addition, controls should be tested to ensure they are operating effectively.

5. Develop Action Plans for Fixing Control Deficiencies

An action plan should be developed for each control gap identified. Elements of the action plan should include:

• Assigning responsibility for the actions
• Prioritizing efforts
• Establishing due dates

Related Document

Navigating UC Berkeley's Business Environment: A Guide to Administrative Responsibilities