Three statement modeling with iterations overview.

The first thing we need to remember when building a three statement model, is that we need to have the iteration setting turned off before starting. This is an important way of preventing accidental circular references in our model. Step one is to input historical data for the income statement and balance sheet. For most companies, this will be from the latest financial statements, but if you're working on a deal, the data could come from management accounts. Step two is to use this historical data to calculate ratios and statistics, such as revenue growth, margins CapEx ratios, and working capital days. These ratios help us to understand the company's recent performance and business drivers so that we can make better predictions of future performance.

Step three is to use our ratio analysis to build forecast assumptions. For example, if we found that revenue growth last year was 10%, and our analysis showed that 4% of this revenue growth came from non-recurring foreign currency moves, while 6% of the revenue growth came from growing market share that we think will continue next year, we could forecast revenue growth for next year of 6%. We now have everything we need to start actually building our forecasts. We start our model build with the income statement, and we build this from top to bottom, starting with revenue all the way down to the tax line. There's one line that we have to miss out when first building the income statement, and that's interest. This is because we can't calculate interest until we know the cash and debt balances, so we leave them blank and come back to them later. The next step is to build our forecast balance sheet, which again, we build from top to bottom, assets, then liabilities, then equity. This time we'll need to miss a couple of line items out. Those items are cash and the revolver, or short term debt. This is because we can't forecast these items until we've built the cash flow statement, so we'll leave them blank and come back to them later. The next step is to build the cash flow statement using the rules of cash. Once we've done that, the next step is to use our cash flow statement to plug our cash and revolver balances in the balance sheet. We'll do this using the max and min functions in Excel. This is a really neat way of ensuring that a positive ending cash balance is included as cash in our balance sheets, and a negative ending cash balance is included as short-term debt in our balance sheet. Our balance sheet is now finished, and because we've used the cash flow statement to calculate our cash and revolver balances, our balance sheet should now perfectly balance. The next step is to use our cash and debt balances to build our interest calculations. We then need to link the interest into the income statement and deal with any circular references that may arise. One thing to note, when we include interest in our income statement, the numbers in our model will change as interest will affect net income and therefore also cash flows in the year and therefore also the cash or revolver balances. However, as long as our model is linked up correctly, the balance sheet will still balance.