Cell-based modelling is a root cause of Excel Hell. James Power offers a few tips on how to avoid some common sources of trouble.
Some of the unavoidable issues you are likely to encounter when using Excel for financial modelling include:
- Simple errors in formula construction, returning error values such as: #VALUE!; #REF!; #NAME?; #N/A; etc.
- Errors in formulas dependent on other feeder cells that only become apparent later on, usually in different tabs to the tab you are currently working on, but missed because you cannot see them or are not alerted to them.
- Changing the spreadsheet structure which frequently creates errors containing the notation #REF! which ripples through financial statement rollups, thus making them unreadable.
At the most basic level, even very simple checksums can help you maintain the integrity of every spreadsheet you construct. However, most people fail to plan this aspect, usually due to time-pressures, but also on account of lack of know-how or even pure laziness! But why not build every new spreadsheet from a template already containing a basic Checksum structure?
Here’s how to do it