|
Do
|
Don't
|
|
Keep inputs separate from everything else
|
Don't use numerical values in formulae (eg =sales/12). This is referred to as 'hard coding'
|
|
Use a single formula for an entire row - anticipate all the situations a formula will have to deal with
|
Don't change formulae part way across a row to cope with something that occurs in that time period
|
|
Use a balance sheet – and make sure it stays in balance. Use check sums and cross-checks
|
Don't cheat – your model should work by its own internal integrity, not by 'fixing' it with values to make the figures balance
|
|
Keep layouts and formats consistent - Input income as positive, costs as negative, the same year in the same column on each worksheet. Use consistent colouring for input
cells.
|
Don't allow circular references – they can cause instability and when you have one, more can be introduced without you realising it.
|
|
Keep it simple – take small calculation steps between rows, use subtotals, flags and clear labelling. Make sure the calculations flow logically, and separate them into
'modules'
|
Don't build huge formulae that take up several lines in the Excel formula bar – break them up into more manageable and easily understood steps
|
|
Do your calculations at the lowest level you will need (eg if you sometimes need monthly figures, make all your figure monthly – you can easily summarise into quarterly or
annual figures later)
|
Don't try to create formulae that deal with changing from quarterly or monthly figures in the first few columns to annual figures in later columns.
|
|
Use colour, labels and borders to make your model as clear to read as possible
|
|
