Subscribe to PSTV 'Views and News'

Our monthly 'PSTV Views and News' gives extra tidbits on guest interviews and upcoming guests..

First Name *
Last Name *
Email *
Mar
21 2012

Avoiding financial spreadsheet mistakes: Structure, hard-coding

Syndicated from: Canadian Youth Business Foundation

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC Article series: Avoiding financial spreadsheet mistakes Part 1 of 3 Financial spreadsheets are often compromised due to some rather basic mistakes by spreadsheet developers. No matter how basic or thorough a financial spreadsheet is, it is important to maintain a simple and concise approach to developing it. While putting together spreadsheets, the developer should take a thoughtful approach and avoid cutting corners. This will prevent the following rookie mistakes. Worksheet Structure There are many spreadsheets that are not user-friendly because they lack structure. It is often because there is one primary user who builds, manages and operates the model. They get to know the unique and quirky structure of the spreadsheet which other users find difficult to understand. Begin by adding a separate cover sheet and table of contents at the front of the spreadsheet. Then segment, as shown below, into certain worksheets such as assumptions/inputs, source data or model imports, calculation and outputs. Ensure the structure of the worksheet has a logical information flow, which will make it easier for other users to navigate and operate the spreadsheet. Hard-coding numbers One of the most frustrating and time-consuming activities for spreadsheet users is understanding where hard-coded numbers came from. Hard-coding involves entering a number into a spreadsheet cell, rather than sourcing information from another source (such as a formula that applies data from another cell(s) or previously inputted cell). Ironically, spreadsheet developers perceive hard-coding as the easiest and quickest solution at the time of building the spreadsheet. They are often not aware that the users will spend so much time and effort later on trying to understand what the model means. This is a real audit risk for your company’s financial spreadsheets. It is wiser to ring-fence all external cell referencing to designated model import sheets; this will uphold the auditability of your spreadsheet. Ring-fencing involves isolating a specific informational element of the financial model, such as externally referenced spreadsheets, in order to prevent spreadsheet risk i.e. cell errors or compromised external data links. Ad-hoc data inputting should be confined to assumptions worksheets. Check out the rest of the articles in the series ‘Avoiding financial spreadsheet mistakes’ Avoiding financial spreadsheet mistakes: Structure, hard-coding Avoiding financial spreadsheet mistakes: Referencing files, formatting Avoiding financial spreadsheet mistakes: Data links, formulae

Previous post:

Next post: