Skip to content
Gallery
Excel Training 2024 G1
Sign Up for Course
Welcome
Course Venue
Dates and Time
Program
Participants Overview
Participants Cards
Financial Modelling Theory
Financial Modelling Standards
Golden Function Rules
Cell Referencing Exercise
Download Course Material
What is Coda
Watch Coda Videos
Share
Explore

icon picker
Financial Modelling Standards

BEST PRACTICE STANDARDS IN FINANCIAL MODELING

BEST PRACTICE STANDARDS

WHY BEST PRACTICE STANDARDS
As spreadsheet modelling activities and underlying organisations grow and become more complex, the need for universal spreadsheet modelling standards has also grown.
To minimise the risk of errors occurring, improve reliability, usability, robustness and accuracy.

SELECTED STANDARDS
> Workbook structure: Separate data input, calculations, and output and clearly identify each to make them easier to recognise and utilise.
> Templates: Where there are many similar sheets, design a template that can be applied to all sheets. This makes it far easier to maintain than having to edit multiple sheets individually
> Units of currency: Column and row headings should include units and currencies where relevant. Columns and rows should only contain data in one unit and/or currency.
> Dynamic naming: Make use of dynamic ranges for chart data and named ranges to ensure these always expand as your data grows.
> Field Name in a Database: Don’t use the same field name in a database table more than once as this causes readability issues when using pivot tables or filters.
> Blank lines: Minimise the use of blank lines in data/calculation blocks as these cause problems with formatting and manipulation.
> Cover sheet: Every workbook that contains more than one sheet should contain a separate cover sheet as the first sheet in the workbook.
> Section cover sheet: A section cover sheet should be used at the start of each section in a workbook to indicate the commencement of each new section.
> Workbook Navigation: There should be hyperlinks from the table of contents to every sheet in the workbook;
> Hyperlink Consistency - All hyperlinks within a workbook should use a consistent, dedicated style or format so that - they are visually identifiable as being hyperlinks.
> Formats and Styles Key - Every workbook should contain a key or legend that explains the purpose of each format and style that has been applied to the cells in the workbook.
> Assumptions Location - All assumptions contained in a workbook should be located on dedicated and visually identifiable assumptions sheet. Assumptions should never be located on output sheets.
> Assumption Repetition : Any single assumption should never be recorded more than once in a workbook.
> Consistent Formulae - When more than one adjacent cell contains a similar type of output, the structure and components of the formulae within the cells should always be consistent, so that the cell can be copied across / down the relevant range without needing to make changes to the formula text.
> Assumptions in Mixed Cell Content- Assumptions should not be embedded in cells containing mixed cell content – i.e. cells containing content with a combination of constants and formulas.
> Circular Reference: A workbook or group of linked workbooks should never contain a circular reference.
> Limiting Worksheet Depth: It is recommended that the number of rows utilised on any worksheet be limited, where practical, to what can be seen on the screen without vertical scrolling.
> Freezing Panes: It is recommended that frozen panes be used on every worksheet in a workbook (excluding cover sheets) to ensure that the sheet title, any hyperlinks, error flags or date and time titles are always in view.
> Preventing Invalid Assumption Entries: It is recommended that controls, data validation and sheet protection be used to limit the scope for model users to enter invalid assumptions into assumption sheets.
> Assumption Entry Interfaces: It is recommended that every assumption in a workbook that has a finite number of entry possibilities should use an assumption entry interface that limits the model user to only those finite entry possibilities.
> Cell naming conventions: Named ranges should be named consistently throughout the file as SourcesheetTypeName eg workingsTblMonth (or just wTblMonth) or setupStrFilename.
> Data Input Areas: Data input areas should be easily recognisable eg different background colour. Data inputs should not be mixed with calculations and inputs should not be hard-coded into calculations.
> Sheet Tab Colours: Use sheet tab colours to assist with visually identifying sheets with a similar function.
> Lookup: Use data tables and lookups to pull data out and place it where it needs to go.
> Splitting long formulas: Split long formulas into smaller chunks to aid readability. Document longer formulas in English (eg via cell note) to assist yourself and other users.
> User-defined functions: Consider using user-defined functions where long, complex formulas are required. This simplifies readability and maintainability of formulas and allows better documentation, portability, and re-use.
> Book Order: Spread sheets will generally be read from left to right and top to bottom. As such, formulas should refer to cells to the left and above.
> Naming ranges: Use range names to aid formula readability.
> Error checks: Calculate results using different cells and/or methods to detect potential errors. Use Conditional Formatting to provide a visual indication of any errors that exist.
> Lookup: Use COLUMN() or ROW() to automatically calculate the offset required for VLOOKUP, HLOOKUP, and other reference functions. This ensures the offset will be correct even if new columns or rows are inserted.
> Unusual situations: Ensure formulas can handle unusual situations such as zero, negative values or text values in cells (particularly where formulas use division) eg via data validation, error messages, or by handling the error in a formula.
> Subtotal: Calculate subtotals using SUBTOTAL rather than SUM to allow easier maintenance.
> Copying formulas: Avoid copying a formula that includes a named range from one spreadsheet to the other as this creates a hidden link between the workbooks.
> Multiple Function Formulae: It is recommended that formulae within a workbook that contain more than one function be separated within the formula such that each new function is displayed on a separate line of the formula bar.
> Sheet and Cell Protection -It is recommended that every cell in a workbook that is not an assumption cell be protected (locked) prior to distribution of the workbook to model users. For this cell protection to operate effectively, every sheet in the workbook must be protected.
> Data Series: Limit the number of data series on a chart to 5 or less, otherwise it becomes too cluttered and cannot be easily understood.
> Chart Axis: Ensure chart axes scale and titles are correct. Consider linking them to a cell.
> Named Ranges: Use named ranges in macros rather than absolute cell references so that the file can be amended (eg new row/column inserted or deleted) without requiring amendment to macro code.
> Documentation: VBA code should always be well-documented with proper indentation according to the style manual.

Financial Modelling and FAST Standard

The FAST Standard is a set of rules providing guidance on the structure and design of efficient spreadsheets

Flexible

To be effective, the structure and style of models require flexibility for both immediate usage and the long term. They should allow multiple users to run scenarios and sensitivities and to make modifications over an extended period as new information becomes available. This level of flexibility is achieved through maintaining the simplicity of the model, rather than attempting to incorporate complex devices with an option for every eventuality.

Appropriate

Models must reflect key business assumptions directly and faithfully without being cluttered in unnecessary detail. The modeller must not lose sight of what a model is: a good representation of reality, rather than reality itself. Spurious precision is distracting, verging on dangerous, particularly when it is unbalanced. For example, highly specific tax assumptions may lead to an expectation that all elements of the model are equally certain, creating a false impression if the revenue forecast is essentially guesswork.

Structured

Rigorous consistency in layout and organisation is essential in retaining the model’s logical integrity over time, particularly as a model’s author may change. A consistent approach to structuring workbooks, worksheets, and formulas saves time when building, learning, or maintaining the model.

Transparent

Effective models are founded upon simple, clear formulas that can be understood by other modellers and non-modellers alike. Confidence in a financial model’s integrity can only be assured through the clarity of a logical structure and layout. Many of the recommendations that enhance transparency also increase the flexibility of the model: if they can be reviewed easily this facilitates any future adaptation that may be required.


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.