How to Build a Financial Model in Excel

A cash flow model is a powerful tool that can help you project future performance of a business, evaluate the impact of a transaction (such as an LBO, refinancing or acquisition), and perform returns and ratio analysis. This article seeks to provide a general framework for constructing a cash flow model with the aforementioned uses in mind.

This is not meant to be a perfect guide, but just one perspective on modeling.

Things You'll Need

• Microsoft Excel 2003
• Knowledge of Basic Accounting
• A pumping soundtrack
• Time

Instructions

1. Get in the right mindset.

   "Garbage in, garbage out."

   Whether you're pricing options with Black-Scholes or building a Discounted Cash Flow model, you should sanity check your assumptions. This applies to cash flow modeling, too, as the model will faithfully generate outputs without regard as to whether or not the inputs make sense (it's just a bunch of formulas, after all).

   NEVER HARDCODE. The model should be dynamic and adjust automatically according to your inputs. There should never be a formula, for example, that multiplies a certain number of units with a hardcoded price. What happens if you need to change the price? You would have to go to the right cell and change it every single time (assuming you also changed it correctly and didn't make an accidental keystroke somewhere).

2. Decide on the assumptions. I usually build these out in an assumptions tab. It is useful to build out multiple divisions and consolidate them before linking into the income statement.

   Operating Assumptions
   - Revenue growth and buildout
   - COGS (as a % of revenue)
   - SG&A (as a % of revenue)
   - EBITDA margin
   - D&A
   - Capex

   Working Capital Requirements
   - AR Days
   - AP Days
   - Inventory Days
   - etc.

3. Build out the Income Statement with everything except for interest expense which will be added in later.

4. Build out the Pro Forma Balance sheet to adjust for any transactions that will be represented in the model. Balance Sheet should be built out with everything except for cash balance.

5. Build out the the debt tranches and interest expense calculations.

   Revolver? Term Loans? Mezzanine Debt? PIK/Toggle (just kidding..forget about this one)? Go crazy! Make sure that you have the right reference base rates in place and proper calculation of interest. Remember to put in the revolver and cash sweep!

   Run the interest expense back to the Income Statement.

6. Build out the cash flow statement. Operating, Investing, Financing sections...you know the drill. Link the end cash back into the Balance Sheet.

7. The summary ratios. This is what you have been working so hard for - the outputs! You can build out the liquidity and solvency ratios!

   Examples:
   - Senior Debt/EBITDA
   - Total Debt/EBITDA
   - Interest Coverage
   - Fixed Charge Coverage
   - etc.

8. Check and validate the model. Go line-by-line. It can be tedious, but better than finding a nasty surprise waiting for you later!

   I have left out a lot of bells and whistles. You can build in the returns for an LBO at various sensitivities, returns on mezzanine debt, a complex tax calculation...the sky's the limit really. It's about what you want to put in and how much time you have available.

   With all that said - good luck and happy modeling!