Implementing change or improving an existing financial model takes time. In the short term, there are little measures that can be taken to improve the model. In essence the low-hanging fruit of model improvements.

A User Guide

It is hardly the most glamorous and interesting part of developing or improving a financial model, a user guide is a must feature of any financial model. Ensure the guide is straight to the point, not verbose and doesn’t overcomplicate explanations of how the model functions. It can be greatly complemented with detailed screen shots, which will help to better articulate and demonstrate aspects of the model. Incorporate hyperlinks in the user guide, as it will enable users of the guide to drill into specific worksheets of the model.

Error and Alert Checks

This is often the most effective way to digest the detail and mechanics of any financial model. There are many ways to verify numbers. It could be from cross checking the model’s overall Gross Profit, compared to the Gross Profit figure reported and stated in the company’s financial systems, which have been imported into the financial model.

Another example would be to merely verify the Direct Cash Flow ties up to the Indirect Cash Flow number. This will enhance your confidence in the model’s credibility, because these two approaches calculate cash flow differently; indirect presents a reconciliation from reported net income to cash provided by operations, whereas direct presents cash flows from activities via an outline of cash outflows and inflows.

An example of an Alert check would check for items such as Investment amounts always being positive or computed Fixed Assets (Net) values never being negative.

Cell Validations

There are certain input cells that must always be a specific category, such as the forecasting of Accounts Receivable based on Forecast Debtor Days or a Tax Rate Percentage; these cells must always be positive and users must be prevented from inputting negative values as per the following.

A drop-down box is another form of instigating cell validation. There may be cells such as the specification of the financial year-end date that must not accept numerical data inputs, but instead require one of the twelve months of the year.

Defined Names

To prevent silly typos or inconsistent naming conventions of a business unit, project or an asset, one can institute a defined naming convention in the Assumptions worksheets of the model. Don’t forget the facilitation of spare names into the financial model as well, which will enable easier and more seamless additions of entities in the future into the financial model. Thus, instead of having to repetitiously manually type Business Unit ABC into the income statement, cash flow statement etc., you will be able to simply type in BU_1 for example.

Cosmetic Consistency

This partly relates to Defined Names. A quick solution to model improvement is instigating a consistent font across your headings. Excel 2007 and beyond offers default font sizes depending on Heading 1, Heading 2, Heading 3 or Heading 4, or you can choose to adopt your own fonts. Evidently the main title on your Balance Sheet should have the biggest font size, then your Current Assets, Cash at Bank or Short-term Investments, and then individual business units etc.

 

The financial model should also introduce different font and cell colours for input cells, calculation cells and output cells. Once again it is up to the model developer, as to which fonts or colours to use.

The review and improvement of an existing financial model can be a time-consuming and challenging process. In the short-term, model reviewers can still add immense value to a company’s legacy model. One can implement a user guide, error and alert checks, cell validations, defined names and cosmetic consistency in a legacy model within a short time frame.

Your greatly improved financial model

Whenever you undertake a peer review of your company’s financial model or that of a client’s, the review process must be proper and comprehensive. However, that doesn’t prevent you from achieving some fast improvements to a legacy model. You can rapidly implement cosmetic consistency with defined name and cell validation usage with applicable cells. A thorough financial model user guide will further improve the model. Finally, the detailed introduction of error and alert checks throughout the entire model will help you to understand the model’s mechanics more rapidly, and provide heightened confidence the computed financial outputs are correct.