Spreadsheet Auditing tools, techniques, tricks, tips, and checklists - what do you recommend?
Hi everyone
I need some guidance. I have several large models from different sources that I'd like to audit.
There are a wide variety of tools out there, some are free and some require payment. I'd welcome advice on the tools that work best for you. I'm really interested in what you've used and why you think it's effective. I'd also appreciate any techniques, tricks, tips, and checklists that you use, particularly anything that provides a sequence or roadmap. Nothing beats real life experience.
Answers (8)
Vanita R.
Director at Pikvan Consulting Solutions Private Limited
Best Answers in: Education and Schools (1), Mentoring (1), Organizational Development (1), Professional Organizations (1), Using LinkedIn (1)
Hi Steve,
It depends upon the content and scope of an audit. It is very necessary to know what is to be audited and what is the purpose of auditing. Then only one can decide upon the tools and techniques of auditing.
However following simple steps are useful for each and every type of audit:
1. Decide upon the scope of audit and audit objective.
2. Audit Planning
3.Conducting transaction audit (Sampling, Walkthroughs, Vouching, Observations , Interviews, etc)
4.Conducting balance sheet/ scrutiny audit (Analysis, Inferring, Etc)
5. Reporting
My favourite audit tool is MS Excel. It is handy, free of cost and easily accesible. It suits to almost all the scenarios.
Lawrence H.
Senior Leadership in EHS/Environmental Auditing, Conflict Minerals and Sustainability
Best Answers in: Manufacturing (5), Regulation and Compliance (4), Supply Chain Management (3), Risk Management (1), Corporate Governance (1), Quality Management and Standards (1), Energy and Development (1)
Hi Steve. My suggestion is to not allow the tool - whatever tool you select - to become a crutch for auditors. In other words, no audit tool should be expected replace the knowledge/experience of the auditors themselves. An audit is more complex than simply checking off boxes in a spreadsheet or database. In my realm (environmental, health and safety), I tend not to use a formal tool. I use the regulations themselves and the specific permits and audit directly against those. Doing so eliminates any judgement, assumptions, interpretations or errors that may stem from converting the requirements into a protocol/checklist format. That may not be practical in other areas, though. Good luck
If I had to audit spreadsheets, I would first understand the use of each spreadsheet and select higher risk ones as my sample. Then I would test them for validity of logic/formulas, restricted access and segregation of duties, procedure around change, version control, input validations, backup and archive. If possible, I would like to compare it with a report to validate all the inputs and recalculate it myself to ensure that it has the same results.
Eckhardt K.
Independent IT Consultant
Best Answers in: Business Analytics (4), Auditing (2), Databases (1)
Steve - Hi, I have performed many spreadsheet reviews and audits. I use a variety of tools in my trusty toolkit. I find however the ones I use most are ASAP and PUP. These two combined with the formula auditng tool Excel provides can handle most situations.
Feel free to contact me for further info.
Links:
Chris K.
Partner at Kelly Partners LLP
Best Answers in: Accounting (5), Auditing (5), Risk Management (4), Job Search (1), Corporate Debt (1), Economics (1), Corporate Governance (1)
Hi Steve. Personally I find spreadsheet audits are a lot of fun as they tend to hide loads of short cuts, forced results and errors. I tend to start with logic and create my own map of what the spreadsheet is supposed to do. I have summarised a checklist of errors to look out for on my web page (link below). Additionally I have a flyer on the topic and would be happy to to email to you if you contact me via the info link on the website.
Links:
Tim K.
Principal Systems Architect at SageworksCapital
Best Answers in: Financial Regulation (1), Risk Management (1), Small Business (1)
Sageworks provides automated analytical procedures worksheet to help you calculate expected values and assess risk of material accounts.
http://www.sageworksinc.com
Marty M.
Certified Public Accountant
Best Answers in: Accounting (2), Organizational Development (1)
Marty M. suggests this expert on this topic:
Steve, you should talk to Brian about this. Brian knows his stuff.
Look at the auditing toolbar in Excel 2003. There are a lot of features built into Excel which simplify this process.
What kind of audit / what are your objectives for this audit? Without knowing what you're trying to accomplish, it's hard to recommend a tool/approach, since there are so many different types of objectives you could have.
Look at endusercomputing.com. It appears to be a blog related to this issue, and they have some resources there from various locations which might be of interest.
Best regards, Brian
