Because financial modeling requires a great deal of spreadsheet work, most often in Microsoft Excel, I wanted to take the time to highlight some important features of many financial models that can be found on Wall Street and in Corporate America. A few of these items, common to most financial models that you will come across, revolve around proper color-coding (for ease-of-use) and dealing with circularity problems (for proper functionality). While there are many other topics of discussion regarding financial modeling, such as scenario/sensitivity and IRR returns analysis (for evaluating and interpreting the value of a firm or security), we will save those for future articles to come.

Where do I begin?

As a former investment banker, I cannot over-emphasize how important it is to properly format your work, whether it’s a PowerPoint presentation to a board of directors. an offering memorandum sent to a potential investor, or even a financial model that is being prepared for a client. One of the more important and key formatting standards is the concept of color-coding your model. Why is color-coding so important?

Let’s give an example: Imagine that you are the key analyst on a very important deal and are in charge of maintaining that deal’s financial model. However, because you are an investment banker, you are also involved in several other deals that demand your attention, and one of the Managing Directors has decided to send you on a trip to Europe to work with a client. Another analyst /associate/VP will have to take over your original modeling duties and must be able to easily understand, and navigate, that model in your absence.

Without a set of color-coding standards, your successor would have a very difficult time following the financial model, unaware of where an input should be changed or a formula modified. Quite frankly, auditing someone else’s work in a financial model without these color-coded guidelines can be very frustrating, and worse, time consuming! This is where applying proper color-coding techniques can help you and your deal team save time (and your job!)

Above is an example of the use of color-coding in a financial model. We have historical revenues for the years 2004-2006 manually inputted into the model, and this is reflected in the use of blue text in the cells and yellow shading in the background. This color combination makes it very easy for a financial model user to identify what has been manually typed into the model and locate what other cells may need to be changed in order to adjust projections and assumptions, such as cells F4 through H4 predicting revenue growth rates. This blue text with a yellow background is a standard practice across Wall Street and should be incorporated into any financial model. Corresponding with this is the practice of using black text font and a clear background to identify formulas in a financial model. Cells D4 to E4 and F3 to H3 are examples of this practice, where historical growth rates are being calculated as well as future revenue amounts. Below are some general guidelines when it comes to cell color-coding and how to apply this formatting.

My model works! No it doesn’t!

The whole point of building any financial model is to create a dynamic set of projections about the future state of a business or economy and interpret the results. How do we make a model dynamic? As an investment banker or equity research analyst. the goal is to analyze a company’s revenues, income, cash flows and balance sheet accounts over time (weeks, months, or years). In a financial model, each of these items is “linked” in such a way that changing assumptions about one criteria can have an effect on all the others. Let us examine this basic relationship more closely:

A company’s abbreviated financial statements are presented below:

Here we have the four major components of a financial model: the income statement, balance sheet, cash flow statement, and a debt schedule to keep track of debt repayments or borrowings if cash is needed. To understand the linkage between the financial statements, we will start with net income.

All items on the income statement, starting with revenues all the way down to taxes, affect net income at the end of the day. Net income is our starting point for the cash flow statement and this will be critical in understanding the circularity that will be created in a financial model. Because net income is not exactly cash, some adjustments are made, such as the add-back for depreciation expense (non-cash) that was found in the income statement, as well as the change in inventories year-over-year on the balance sheet ($1000-$400=$600). This $600 represents the inventory items sold and charged as “Cost of Goods Sold” on the income statement.

Next on the cash flow statement we find that the company spent $500 on capital expenditures during the year, decreasing cash flows but increasing PP&E on the balance sheet due to the increase in equipment purchased. Keep in mind that PP&E only increased by $450 during the year because of a $50 depreciation expense, lowering the value of PP&E. Now that we have tabulated both “Cash from Operations” of $685.6 and Cash from Investing of ($500), we can see that we have $185.6 to use for paying down debt (assume the original $100 on the balance sheet is the minimum required balance and is not available to pay-down any debt). If we use this excess cash to repay debt, our ending debt balance, as shown in the debt schedule above, is $814.4. This debt amount can also be seen on the balance sheet as the ending balance for “Year 2.” We then capture this change in debt under the “Cash from Financing” section of the cash flow statement, and realize a net change in cash of zero for the year (we spent it all to repay debt!).

If the problem with linking up your financial statements this way does not seem obvious, let’s turn our attention again to the income statement. Recall that I mentioned that every line item on the income statement affects net income at the end of the day. If you look, you’ll notice that this includes interest expense, which is a function of your interest rate (10%) times your debt balance. Here is where we introduce the circularity created in model, and why Excel is not always happy with your choice to create such a dynamic model.

When you link interest expense into your income statement, a circularity is introduced into the model.

1. Net income is reduced (interest expense reduces net income)
2. Cash available to repay debt gets reduced (lower net income yields lower cash flows)
3. Thus debt levels increase (lower cash flows means less cash for debt pay-down)
4. Interest expense increases (higher debt yields higher interest expense)
5. Net income is reduced…and on and on. This process of iteration occurs over and over again, until steady-state levels are reached.
6. This is the circular reference in a financial statement model and it must be dealt with

Because of this circularity in the financial model, Excel may become unstable and may show “REF!”, “Div/0!” or “#Value” errors. Regardless of which one shows up, this isn’t good! In order to deal with the circularity produced in the model, we have a couple of solutions. The first is to make sure that you have “Iterations” checked on in your model, per the pictures below. This can be done by:

Excel 2003: Tools > Options > Calculation Tab > Set Iterations to 100 (check box)

Excel 2007: Office Button > Excel Options > Formulas Tab > Set Iterations to 100 (check box)

The next solution is to do one of the following:

Option 1: Manually break the circularity

1. Copy the interest expense reference from the income statement off to the right – beyond the last projection column.
2. Replace the income statement interest expense projections with zeros. This effectively “breaks” the circularity – the errors should now disappear.
3. Copy and paste the interest expense formulas (that you pasted to the right of your model) back into the income statement.

Option 2: Insert a circularity breaker toggle (preferred option)