I have built my fair share of models (mostly discounted cash flow valuation models of big companies) and a small bug can make a few billion dollar difference in valuation.
My main strategy against bugs:
- Simple formulas: plus, minus, times, divided by.
- If possible only 2 numbers per formula, intermediate results appear as another line
- Round up to a unit that leaves you with one digit behind the dot (millions, thousands, etc.)
- Everything points down, a result always depends on values higher up in the spreadsheet, never the other way around
- And finally: adhere to the rule that if it looks wrong, it is probably wrong. Averages should be within the minimum and the maximum value, gross margins are usually around 50%, etc. etc.