Guidelines for Creating an Effective Model

Earlier, we have learned various elements & their importance in financial modeling. But it is essential to have proper guidelines to create an effective model. The following points must be taken into consideration while creating a financial model:

1. Documentation

Ensure the model has adequate documentation so that it can be easily modified later. The documentation would help in understanding the design and structure of the model and would be particularly useful when the model is required to be operated by a person other than the person who has developed it. Every financial model would have multiple worksheets and calculations. To help users understand the model, one can put a schematic diagram on the front sheet of the model for describing the various sheets and indicating how they interact with each other. One can also use hyperlinks to the relevant modules, thereby making the first sheet as a navigational tool, helping users to find their way around.

2. Input Tabs - Assumptions

All of the model’s hard-coded assumptions such as revenue growth, WACC, operating margin, interest rates, etc. should be kept in a clearly defined section of a model — typically on a dedicated tab called ‘inputs.’

The user has only one place they need to go to change any assumptions. This creates a consistent distinction between areas in the model that the user works in vs. areas the computer works in.

The flow thus would be 

Assumptions → Calculations → Output

3. Hardcodes = landmines

Please take an oath that you will never put a hardcode and a formula in the same cell. All hardcodes or “inputs” should pull out of formulas and consolidated in an inputs / assumptions section. Never repeat an input and you can assign a specific colour to it so you can find them easily.

The danger here is that you’ll likely forget there is an assumption inside a formula. Inputs must be clearly separated from calculations

This hides the information that the PAT Ratio is 65%, but more critically, it means extra work if you need to change the PAT Ratio later, since you will have to go to the individual cells (assuming that you remember where all the hard-coded 60% entries are).

So, never embed inputs in formulas.

Instead, break out into separate line items

4. Going Circular (References)

Circularity refers to a cell referring to itself (directly or indirectly). Usually, this is an unintentional mistake. In the simple example below, the user has accidentally included the sum total (D5) in the sum formula. Notice how Excel becomes confused:

Avoid it if you can. If you have to do it, fine, but check the “enable iterations” box and build in a “circuit breaker” or be prepared for a lot of #REF!

But sometimes a circularity is necessary. Let’s say, if a model calculates a company’s interest expense based on a cell that calculates the company’s revolving debt balance, but that revolving debt balance is itself determined by (among other things) the company’s expenses (including interest expense), then we have a circularity:

The logic of such a calculation is sound: A company’s borrowing needs should take into account the interest expense.

Intentional circularity in financial models need a special setting must be selected within ‘Excel Options’ to prevent Excel from misbehaving when a circularity exists:

5. Granularity - Bottom Up

The devil lies in the details. And for the financial model to have its appeal, it should always follow a bottom up approach to building items such as revenue and cost. Unless you demonstrate that the model captures the complexities of the underlying business, it will simply remain a complex sheet of MS Excel with little relevance to the end user. 

Consider the case of a pizza store. Monthly sales could be the result of the following inputs: foot traffic per day, days open per week, conversion rate per customer, average order quantity, and price per pizza. To a reasonable extent, the more granular your model, the more accurate and defensible it’s likely to be.

6. Financial model flexibility

A model’s flexibility stems from how often it will be used, by how many users, and for how many different uses. A model designed for a specific transaction or for a particular company requires far less flexibility than one designed for heavy reuse (often called a template).

Together, granularity and flexibility largely determine the structural requirements of a model. Structural requirements for models with low granularity and a limited user base are quite low. Remember, there is a trade-off to building a highly structured model: time. If you don’t need to build in bells and whistles, don’t. As you add granularity and flexibility, structure and error proofing become critical.

7. Present ability

Regardless of granularity and flexibility, a financial model is a tool designed to aid decision making. Therefore, all models must have clearly presented outputs and conclusions. Since virtually all financial models will aid in decision-making within a variety of assumptions and forecasts, an effective model will allow users to easily modify and sensitize a variety of scenarios and present information in a variety of ways.

8. Link directly to source cell

Always link directly to a source cell as it is more difficult to audit “daisy chained” data 

9. Build in error checks

The most common error check in a financial model is the balance check — a formula testing that assets = liabilities + equity should be employed.

So have checks like ensuring sources of funds = uses of funds 

Similarly, you could ensure that total forecast depreciation expense does not exceed Plant, Property & Equipment (since depreciation is the wear and tear of (PP&E.) or that debt repayments does not exceed outstanding principal.

Use as many control checks (for example, total assets = total liabilities, sum of product mix =100 %, opening stock + production/purchases - sales - closing stock = 0, etc.)  as possible to ensure that there is no conceivable error in the model. Also, they should be built in such a way that they automatically flash whenever an error occurs in the model. It would also be a good idea to familiarise oneself with the advanced features of Excel and frequently using them, for example, the data validation feature avoids the accidental selection of incorrect value for an input cell.

10. Ruler Testing Your Model

The following points must be taken into consideration for testing your model: -

a) A balanced model doesn’t mean a correct model
b) Go line by line through your model
c) Are your assumptions reasonable and defensible?
d) Is everything properly formatted?
e) Have you footnoted appropriately?
f) Hand check each and every calculation