Excel – A Love/Hate Relationship by Guest Blogger Anita Cadena, CPAPosted: April 17, 2017
In my accounting experience, Excel is the most used tool in the Microsoft Office suite. It can do so many things to make our lives easier with its various functions. In addition, its data analysis capabilities can be extremely valuable. But how do you really know what it’s calculating and displaying is accurate? That could depend on how many people utilize the file and who created it.
If you think about software you use to help with calculations (for example – depreciation), there is some comfort that the program will calculate accurately because we generally cannot edit the “source code.” However, when we use Excel for something similar, we can easily and accidentally edit the “source code” (i.e. formulas). I know – we use it whenever possible because it is familiar and convenient. However, if it is calculating something significant, you might want to add some control over who can access and edit the file (even provide some protection against yourself!).
Here are some ideas – the right solution and application will vary based on circumstances:
- Include check figures or some sort of “reasonableness test.”
- Protect the worksheet (you can lock certain cells – i.e. formulas and/or all “non input” fields).
- Protect the network folder (you may need help from your IT team).
Also, if someone other than the user(s) created the worksheet, you may want to keep documentation, including a log of changes. An additional tab could be created with the following information:
- Purpose of the spreadsheet
- User(s) of the spreadsheet
- Who created the worksheet and when
- Detailed list of any changes and date they were made, including who made them and why
Certain changes can also be tracked via Excel (in a shared workbook). Some details on this can be found here.
Finally, some things to look out for include hidden rows/columns, broken or incorrect links to data, “plug formulas,” and numeric vs. text values. These items could affect formulas.
To learn more, simply search online for “spreadsheet controls.” You’ll find a ton of information at your fingertips.
Anita Cadena is the Internal Auditor for Navy Army Community Credit Union in Corpus Christi, Texas. She has been an internal auditor for public and private companies for the last eight years. In addition, she has over 10 years of experience in public accounting where she provided tax and audit services to a variety of industries.