There is a lot discussed about the importance of best practice financial modelling. However financial professionals need to be aware of what constitutes the opposite; a worst practice financial model and how it can be transformed into one that is deemed best practice.

Evidently there are many ways to greatly improve a financial model, but to begin with, this article will focus on basic financial modelling tips to attain best practice.

Spreadsheet structure and financial model cosmetics

A worst practice model will suffer from a poor layout. It will possess inconsistent and non-intuitive worksheet labeling. Further, it will exhibit unclear or no worksheet headings to guide the model users through the workbook. There might be a lack of proper headers (heading or corporate logo) or footers (sheet name, page, date).

Spreadsheet gridlines could also be showing, which greatly detracts the legibility and overall model finish. The model might be devoid of a cover sheet and table of contents. It could be missing a model user guide. The model might suffer from rows or columns hidden inadvertently, which might cause incorrect presentation or summation of key financial outputs.

A better approach is to use the Data Group function, which will always clearly designate if columns or rows have been hidden in a financial model. A worst practice model will display conflicting or illegible font styles or number formats. It is advisable to present negatives in brackets rather than in red, just in case users are printing out in black. Restrict financial numbers to one or two decimal and apply thousand separators consistent way throughout the financial model.

Formula computations and cell referencing

The model may not present time series financial data consistently across columns across worksheets. The worst practice model is likely to reference cells in an ad-hoc manner; rather than from designated model import worksheets by year or company. The worst practice model could simply miscalculate financial outputs or apply the wrong the formula.  

The model might suffer from inconsistent or misspelt naming convention of business units, projects or assets. Adopt defined cell names across the model, which ensure consistency and prevent typos or misspellings. In terms of cell referencing, it is advisable to apply absolute rather than relative cell referencing of external data, because the user otherwise risks referencing the same cell more than once.

Presentation of Outputs

The worst practice model could exhibit columns or rows not wide enough to read figures or text. The spreadsheet might have rows or columns erroneously hidden. The model could exhibit a weak attention to detail, or simply it might reference the wrong cells or worksheet.

There should be definitions, comments or notes accompanying certain worksheets’ outputs, which are not as commonly understood by users such as a financial ratios summary.

When presenting the financial outputs, it is advisable to format the cells of key financial metrics such as EBITDA, EBIT or Net Income, with double or single underlines to better present these bottom line numbers.

Checks and balances

A worst practice model will be deficient or simply devoid of financial model checks and balances. It is imperative to validate external source financial information, but also to cross-check key performance indicators across the entire model. Often there can be a disconnect between the computed financials in the detailed worksheets, through to the financial outputs and dashboards.

An additional verification is to subtotal or total of financial numbers, which can help to validate further source, external and summary financial information. Finally, the addition of a dashboard with high-level financial KPIs or graphs can inadvertently double up as an unlikely verification check.

Final words

On an introductory level there a number of practical remedies for a worst practice financial model. The main areas are improving the spreadsheet structure and financial model aesthetics, which will greatly improve the model’s marketability, readability and usability for financial model users.

Second, the model needs to be robust, consistent and display error-free cell referencing and computation of financial data. Third, the financial outputs need to be presented and explained in a detailed, opaque and thorough manner.

Finally, the model needs adequate checks and balances to verify all source and calculated data is accurate and correct.