What is the best template for a financial model

Professional financial models - "Best Practice Modeling" principles

In contrast to many other areas in finance, there are no regulations for financial modeling and there are no general guidelines and guidelines. For the often complex, individual financial models developed with Excel, this means an increased model risk. If you consider the major investment and credit decisions that often depend on the model results, it is surprising that the majority of model developers still work without clearly defined standards in terms of structure, format, methodology and quality. This post shows you possible solutions.

If a company lacks a uniform methodology, standards and templates when creating complex financial models, there is an increased model risk. This can mean that:

  1. Model errors lead to wrong investment decisions.
  2. The development, updating and, if necessary, auditing of models is associated with high costs.
  3. Models are often linked to one person (usually the "developer") and there are high levels of dependency.
  4. A lot of time is wasted due to inconsistencies, errors and problems in use, which leads to frustration for employees but also for the recipients of the models (e.g. business partners).

Enough reasons to establish uniform, practice-oriented "Best Practice Modeling" principles in companies and in the individual development of Excel-based cash flow and financial models.

The "Best Practice Modeling" principles

The three fundamental principles in the context of financial modeling are:

  1. Consistency, transparency and comprehensibility
  2. Maximum flexibility
  3. Meaningful presentation

That may sound banal at first, but this article should be brought to life. The screenshots shown here come from the final Excel file of the sample project created in the video tutorials. To illustrate the “Best Practice Modeling” principles, the file contains numerous notes and explanations in the form of colored “speech bubbles” on the individual sheets. The complete final Excel model can be requested here free of charge for testing.

Consistency, transparency and comprehensibility

Aim: A financial model should be clear and logical and easy to understand. Updates, changes and use are effective and efficient with low model risk.

Recommendations:

  • Clear "user interface" => separation of assumptions / inputs, calculations and result sheets (differentiate in color)
  • As short and concise formulas as possible:

- No long, nested IF-THEN links, but use flags
- One formula per line (simplicity, copier, traceability, audit)
- No double calculations, just a link to the previous calculation
- No links to links ("daisy chains"), always link to the input cell

  • Link assumptions on calculation sheets from input sheet (off-sheet reference) => traceability, no change of sheet necessary, simple check (F2) possible
  • Plausibility check of input and output data: control calculations and cells; Error reduction through "data verification"
  • Use of balance sheet accounts for overview and control purposes (especially for loans, taxes, working capital and inventory

  • No circular references
  • Minimize the use of macros (document well when used)

flexibility

Aim: In a flexible model, extensive changes or scenarios can be implemented quickly and reliably. Adjustments, additions and new scenarios do not require extensive structural changes.

Recommendations:

  • Calculate sensitivity analyzes and scenarios with your own, separate scenario manager => Use special adjustment cells in order to be able to maintain the initial scenario (base case)
  • Use dynamic, easily adaptable time scale => Simple adjustments, e.g. for shifts or delays, possible

  • Take empty reserve lines into account when developing the model => Minimal additional effort, but later quick expansion / supplementation possible => Otherwise extensive structural changes on several sheets are necessary (high effort and susceptibility to errors in retrospect)
  • No constants directly in the formula, e.g. in sales calculations (use names instead for constants)

presentation

Aim: A meaningful and uniform presentation style within financial models facilitates the use and navigation as well as the interpretation and communication of results. Professional presentation increases trust in the model and supports intuitive use of the model.

Recommendations:

  • Use of special, individual format templates (advantages: time savings in model development, uniform, consistent appearance, adaptable to the desired corporate design (also afterwards)
  • Input cells can be identified quickly as they are uniformly formatted
  • Clearly define the unit used for each line or each input value (in particular EUR vs. EUR thousand, per month vs. per period, kWh vs. MWh, etc.).
  • Use comprehensible and unambiguous cell labels.
  • Meaningful naming / labeling of the individual subsections on each sheet. Group related lines in worksheets for a better overview.
  • Use conditional formatting (e.g. flags, switches, error messages)

  • Clearly name all worksheets, in a meaningful order and with color coding
  • Use integrity checks (control sums and counters) (e.g. use of funds = source of funds; assets = liabilities; cash never <0 etc.)
  • Consistent use of line totals
  • Use of the "Split window" or "Freeze window" function so that the time axis and line labels are always visible even when navigating through the sheets
  • Supporting presentation of the essential results through meaningful graphics