Sound spreadsheets

  1. Instigate a file naming and version control system
  2. Do not mix data entry with formulae all in the same block. Instead create a discreet data entry tab or block fro all variable items
  3. Where you can add rows or columns and they should come to the same final total, use a cross check or IF statement that flags if the total of the rows does not sum to the same as the total of the columns
  4. Be careful with rounding. It can get embarrassing if your summary page does not add up because you have say three numbers lots of 3.33 which show, when rounded as 3+3+3 but add up to 10
  5. Use named field or ranges: it makes formulae easier to understand and follow
  6. If anyone ever gives you a cashflow and projected P&L but has not created a balance sheet, throw their printout in the bin and tell them to come back when the balance sheet balances. If you want some fun, insist they tell you what errors that balance sheet then highlighted
  7. For your key input, say, sales, add a reference to an “achievement percentage” in the formulae that shows sales (so is derived as “sales” in your data entry area times ‘achievement percentage’. The you can readily do some what-if analysis by changing just one cell, that achievement percentage.
  8. Experiment with and master the what-if and data table functions to compare, automatically, your key output metrics based on a range of ‘achievement percentages’
  9. Show negative numbers in brackets not with leading minus signs. You may need to set up a custom number format of this: #,##0 ;(#,##0)  note the space before the semicolon
  10. Experiment with, and master, the conditional formatting features and use these in your Key Outputs block to highlight good, acceptable and problematic outcomes

Next: types of accountant