Even with all the variety and customization of financial models, there are some general industry expectations, formatting and best practices. The best models are easy to read, accurate, well-matched to the application and flexible enough to fully accommodate the complexity of the task at hand. Here are some best practices:
1) Develop an understanding of the problem, the users of the models and the overall goal of the model.
2) Unless you absolutely can’t avoid it, construct the financial model on multiple worksheets. This makes the model easier to understand and prevents user errors.
3) For increased clarity and flexibility, group your assumptions into blocks. Start with your revenue assumptions, then your balance sheet and income statement on separete tabs. Your sections will vary depending on which financial model you are using, but keep them in separate tabs that are easy to differentiate.
4) Follow standard protocols for color coding. Use a blue font on yellow cell backgroung for hard-coded numbers (assumptions).
5) Be consistent with number formats throughout your model. For instance, if you choose to identify negative dollar values with parentheses, you should always use parentheses. In Excel, you can maintain this consistency by right clicking all cells that represent financial values, select “format cells,” choose the number tab and click “accounting.” You could also choose “currency,” but this has more options and therefore more opportunities for accidental inconsistency.
6) To avoid errors and preserve the readability of the model, every value should have a cell to itself and only appear once in the sheet. You should never embed an assumption into a formula. If you do, you are likely to forget it’s there when you adjust your model, and this could significantly affect the accuracy of your output later.
7) Keep your formulas as simple as possible, and break complex calculations into multiple formulas.
8) Check your numbers and your formulas. Your model is only as good as its construction, and your output is only as good as the data you use to generate it.
9) Test your model. Try to construct scenarios to make it fail so you can refine it or at least understand its limits.