Guiding principles in Financial Modelling

A good financial modeler has the discipline of adhering to a list of guiding principles to help ensure that the development of the financial model achieves the desired results. By following these simple steps, a financial modeler should be able to build a financial model that is simple, accurate and most importantly consistent, to help build confidence in a financial decision making process.
Financial Modeling Discipline can be acquired in all 3 stages of the financial modeling process:

• Specification Stage
• Design Stage
• Build Stage

Specification Stage

1. Be very clear on the effort involved and the dependencies before committing to deadlines - the financial modeling exercise is usually on the critical path!
2. Get the algebra right — make sure all revenues, cash flow inwards and assets are positive while expenses, cash outflows and liabilities are negative. This will ensure that we rarely use the minus sign in formulae and can use the sum() function.
3. Avoid all calculations that will cause circular references.

Design Stage

1. Ensure that each assumption is input only once in a financial model.
2. Define scenario variables clearly in a separate “Scenario manager” section or worksheet in the financial model.
3. Define the time unit that is to be used consistently throughout the financial model.
4. Group all assumptions and inputs into one sheet and state units clearly in the financial model.
5. Avoid executing complex calculations in the “Output section” of the financial model.
6. Build an Interface sheet if you are working with a financial model with multiple workbooks.

Build Stage

1. Always note all assumptions, sources and calculation methods in the financial model for future reference.
2. Avoid complicated macros in the financial model if possible - macros make it difficult to follow logic, spot errors or amend the financial model, besides bloating the file size.
3. Lay all financial model calculations in chronological order - Avoid having calculations in one row refer to calculations in lower rows.
4. Do not try to do too much in one cell with a large complex calculation formula. Break the calculation into blocks.
5. Lay the financial model calculations out in blocks, to enable copying formulae across columns or down rows saving time in developing and reviewing financial models.
6. All financial model calculation and output sections should be locked to avoid inadvertent data entry therein.
7. Include charts in the output section for easy understanding, analysis and auditing of the financial model.
8. Always keep back-ups preferably on separate disks and leave the ‘autosave’ option on for your financial model workbook.
9. Stick to a consistent version labelling system eg “company xyz_2/2/09_V02_DC”. Save several versions of your financial model each day and retain old versions.
10. Avoid jumping to conclusions / sharing results based on preliminary financial model results.

OTHER POINTS TO NOTE

• Specify the purpose of the modelling. Every template/financial model must be backed by and based on a purpose.
• Test the model and preferably all possible formulae before actually working. Models are always prone to errors.
• Keep a documentation of all inputs, outputs and changes to the model.
• in case of formulated cells use if/error and iterations to avoid cells showing N/A, #REF!, #Value!, #DIV/0! etc.

Some Useful Excel spreadsheet functions are:

Audit functions
Grouping functions
Choose function
Range naming techniques
Calculate enable/disable option
Protecting worksheets
Filters (to display selected queries)
Data Sorting
Data input validation
Data tables for sensitivity analysis
Forms & Format controls for sensitivity analysis