10 Great Simplification Tips for your Financial Model

Years back when I started building financial models for clients (typical promoters having very good business sense on fingertips, based on rules of thumb, but never resorted to data crunching or excel formulae), I used to get a very repetitive response to my models TOO COMPLICATED TO UNDERSTAND. It took me a while to actually understand the problems underlying such a comment. It wasn’t that there were errors in the model, it was just that a third person looking at it wasn’t able to make any head or tail. So, I made an attempt to decipher what might be going wrong.

Right from the time we right click on your desktop to open a new excel sheet, we are actually trying to create a model. What is a model? What is a financial model? A business model? An operating model of the company? What are they actually? A calculation is a model depicting a real life situation, quantifying inputs / outputs of an operation or a process. We call it a calculation as long as quantum of variables and scenarios are limited. As you continue to add variables and scenarios, it takes the shape of model.

After interacting with such clients more often, I realized that what or how I think, is irrelevant. How a client thinks and processes information was of prime importance. It was important to understand how the reader will process and represent the calculations on a piece of paper if he / she was asked to depict the situation. And this understanding acted as a catalyst towards my ability to keep things simple while building a financial model.

But at the end of the day, a model is nothing but a series of calculations (simple and / or complex) structured in a particular fashion so that it’s comprehensible. And that’s the reason why modeling per se, is neither intellectual nor an interesting activity. The intellect and interest lies in making a simple, scalable and robust model. Easier said than done, what do these terms mean in the first place?

Definition is quite simple and easy to understand: A model is said to be simple if a decently educated person with average intellect and not necessarily from the same field the model pertains to, comprehends the model with ease and comfort to such an extent that he / she rolls it out nearly in the same fashion when asked to do so.  In simpler terms, it should limit the number of variables; avoid irrelevant data, structured properly, sequentially step by step and coherently. A common error, for example, that kills simplicity or comprehensibility of a model for a common man is not differentiating assumptions from computations by use of different font colour. If the reviewer asks you to change the working capital in your model, you should know which cell you have to go and change so that the desired change is effected.

Over a period of time, I developed few rules for myself while modeling to keep it simple. I am sharing those tips below with you for comments and adoption:

1. Roll it out exactly the way and in the same sequence the thoughts and / or calculations process in your mind
2. List all assumptions at one place so that if someone trusts your modeling skill set but not your assumptions, he / she needs to review just one sheet.
3. Differentiate the assumptions / hard coded figures from calculated figures by using different font colour. This enables a reader to effect a change in the model easily and quickly.
4. Minimize usage of compounding formula while modeling [for example { = if (AND (A>B, max (C,D))…….}]. If compounding has to be used try restricting it to order of two (maximum one function inside other).
5. Don’t hide unwanted rows or columns. Either eliminate them or group them so that a “+” sign appears on the top or at the side.
6. Keep the column width such that all the relevant columns are visible on the screen. While scrolling up or down is fine, one should not be required to scroll it laterally to view the model. This may require you to change the units from Rs. Lakhs to Rs. Crores, US $ mn to bn and restrict the number of digits beyond decimal.
7. Don’t report financials in decimals. Try to round it off by reducing digits after decimal unless the changes are happening only in the digits after decimal; restrict it to that place of decimal where change is evident.
8. Percentages, fractions etc should be differently presented in italics and expressed up to one place of decimals.
9. Structure and arrange your sheets exactly in the same order as you have made them. Revenue build up sheet should be placed before P&L, for example.
10. Format your model only after completion. Maintain consistency in design and format of the model:
    • Separate headings, sub headings etc by different font size and row colors. Maintain consistency in formatting scheme across the model.
    • Report a particular year in the same column across sheets. For example, if FY17 has been reported in Column G on P&L sheet, it should be reported in the same column across all the sheets (assumption, balance sheet, build up, cash flow, valuation etc).

Do you have some more tips to keep your financial model simple? Please feel free to share them here.