Being in the field of finance, we often have to perform calculations, and develop reusable models in excel. This article summarizes and compiles the best practices that I have come across in my experience with excel modeling. Following these tips can make you better at excel modeling, and help you build more robust and error-proof financial models.
General
1. Keep learning your tool: Excel is an ocean and I haven’t come across anyone who knows everything about it. The more you learn this tool, the more you will be able to use it for various purposes.
2. Who’s your audience: You need to have a clear idea about who your audience is. Are you developing the model for yourself, an expert excel user, or a novice? This will help you decide what level of details you need to put in.
3. Life of the model: Are you going to use the model for just a few days, or is it something that you can use for a long time? This is very important because you don’t have to spend innumerable hours on perfecting something that you want for just one day. Depending on how long it will be useful, you can make it more sophisticated.
Excel Specific
4. Layout: You model should have a clean layout, with inputs on one side, and output cleanly organized separately.
5. Color scheme: Follow a color scheme consistently. One scheme is: keep all calculated values (and titles) in black, user inputs/assumptions in blue, and references/sourced assumptions in green.
6. Assumptions and data validation: All assumptions about the inputs should be clearly stated in the form of comments. Use the data validation feature to avoid accidental wrong input by the user.
7. Error checks and documentation: Error checks should be built into the model at each stage. If you have used VBA, try to comment the code as much as possible so that it’s easy for anyone to understand and modify it.
8. Intermediate Calculations: make it easier for the user to access intra-calculations. Taking a simple example, you may have a cell calculating (X+Y)/Z. It may help if =in a side cell you show what (X+Y) actually is. The cells with intra-calculations may be highlighted in another color such as grey. One general tip here is that you should never include a pre-calculated number in a formula, unless it is a standard value, such as pi.
9. Formatting: Once the model is complete, you need to format the spreadsheet. Some things you can do are: remove the gridlines, outline the important cells, and use consistent font sizes. You should also pre-format the pages so that they print well. Finally, unlock the input cells, and protect the spreadsheet to avoid accidental changes.
10. Workbook independence: As much as possible, try to reduce the dependency of your workbook on other workbooks. The model workbook should be complete in itself, so that it can be easily ported.
Excel models can become very complex, and if you have not followed the best practices, the end user can face many problems, to the extent of being unusable. As long as you have followed these practices, you can be assured that your excel model easy to use and update for everyone.
Leave a Reply