Saturday, June 1, 2013

How to review an Excel spreadsheet aka spreadsheet auditing

Anyone who has installed a spreadsheet software like Excel/Open Office can create a spreadsheet. but reviewing one is much more challenging. One needs technical expertise on the subject matter, but also an ability to review/audit the spreadsheet, which depends on diligence/knowledge of Excel audit tools etc. Having searched this topic for a hour today, I give below my insights on the same based on referenced links, and my own experience. Surprisingly, there is little focussed training on this, both in professional firms or in organizations where it is presumed that people will pick it up on the job.
  1. Keep data/constants separately, at one place for ease in updation, and document the assumptions/link to source file. 
  2. Build in checks(like period y-o-y changes), constraints etc to ensure arithmetic accuracy, sanity check on reasonableness etc.
  3. Use relative references and constant file naming conventions/file structure conventions, so that updating links possible using 'Edit Links' option. 
  4. If any manual changes made to formula cells, shade that cell for identification or else identify it separately. Otherwise, excel has an option to identify cell not simillar to its surrounding cells, that may help you identify cells where formulas have been edited different to that copied across range.
  5. Ensure the Excel is ready for printing, and for copying in presentation. This means, in addition to segregating data from algorithms, placing meaningful column and row labels in each portion of the spreadsheet, and not just what the preparer understands.
  6. Develop "Summary'/'Analytical Tab'  to synthesize the results of all key assumptions and analytical results, concurrent with the ongoing analysis, so that only key findings and results will be presented or communicate

No comments: