Conventions and Layout of Models.  Financial models can be large and complex.  They are also highly customized to each situation.  To the extent that they can be standardized, it helps users understand, audit, and operate the models.  Following are a few best practices for model construction and layout.

  1. Follow firm format.  In general, try to follow the layout and appearance of financial statements that your firm already produces.  Obtain your public filings or audit reports to see how your company lays out its financial statements.  The more familiar the construction, the easier it is for executives and others to understand the outputs from the model.
  2. Include an Executive Summary.  Even the best laid out models can be quite large.  The punch-line from the model is often concise.  Crate a separate sheet that contains one or two pages of top level consolidated information.  This is the information to drive decisions.  No one wants to squint through a stack of 11”x17” sheets to understand what is going on.
  3. Format for output.  See the site’s section of Excel tips.  Create page numbers, headers, and footers.  Make logical page breaks on all sheets.
  4. Use version control.  Come up with a version nomenclature and use it.  A simple method is the date and hour.  Then, as the model is used or referred to later, you will know which one was used.  Track that version number in presentations and memos outside of the model.  It might seem superfluous at the time, but models are constantly being tweaked.  Three years later when someone wants to know what the board of directors was looking at, it will be the only way to know.
  5. Keep a change log.  Designate a tab in the spreadsheet to log modifications and changes to the inputs and sheet design.  The level of detail is a firm decision, but some comment should be made with each version.  This is very helpful when someone wants to know why the value dropped 15% from last week.
  6. Use multiple sheets/tabs.  Yes, a modern spreadsheet has a lot of space, and yes, there are wonders than can be performed with print formatting, but very large sheets of any kind are very difficult to work with.  A good practice is to place all three statements (income, balance, cash flow) on one tab to aid in construction and use, but to place other detail on separate tabs (Some prefer to separate the three statements, it is sometimes a question of complexity and style).  It is much easier to flip to the depreciation tab to look at the 30 rows of detail than to scan through a “spaghetti” sheet.
  7. Consolidate inputs and mark them.  Making a single sheet that consolidates all inputs seems impractical at times, but it makes it much easier to operate and audit a model.  It also avoids the problem of lost or hidden inputs.  Dual screen systems and simple window splitting / additions in Excel increase the usefulness of this tactic.  Mark your inputs with a particular color so that they stand out in the model.  Yellow cell fill works well because of good contrast.  Many mark inputs with blue text, but this can be difficult to see on some monitors and does not differentiate well on black and white printouts (yellow generally appears as a gray highlight).  Others mark cells that have off sheet references with certain colors.  This is fine, but can lead to a busy appearance.  Also, tracing tools, text searches, and Alt ~ are very effective at determining this through other means.
  8. Sign Conventions.  As with general formatting, try to use what your company generally uses.  That said, the cash flow statement almost always has consistent signs – all negatives are cash out flows and all positives are cash inflows.  The assets and liabilities on the balance sheet are each shown as positives (the liabilities are not negative because they are liabilities).  On the income statement there is a general convention to show both revenue and expense as positive numbers, except in the case of other income.  Whatever your decisions, ensure that they are consistent throughout the model. 
  9. Try to have consistency between sheets.  Ideally, each sheet should have the same year in the same column.  i.e., 2011 is in column C in all sheets.  This makes creating, formatting, editing, and debugging much easier. 
  10. Do not leave spaces between columns.  This can interfere with editing, searching, and filling in a model.  Excel does have means to fill across spaces, but all users do not know how to use them.  If you would like more space, increase the column width. 
  11. Do not use columns in place of indenting.  Do not add additional, narrow columns to create indents for row names.  Use one column and the indention functions (or less effectively, spaces) to indent text.  The additional columns create clutter and interfere with model editing and operation.
  12. Do not hard code.  It may seem tedious, but have an input cell for all inputs.  No one can use, or edit the model where revenue is multiplied by 1.2 in every cell and the Euro / Dollar exchange rate moves.
  13. Try to have consistent formulas on each row.  When in year five, a product line is added and the expense formula changes there will be trouble.  Always consider making formulas the same across the row even if there is no function to the formula for many time periods (this also forces you to have clean and consistent input sheets).  If you must change the formula in a row, it is helpful to fill the changed cell with a color.  Many models have been mysteriously thrown to error when you or another user edits the beginning of a row and fills across.  The color will alert everyone that a change has taken place (and is much more visible and consistent than the green excel “tick”).
  14. Include a financial history.  Models that start with projections are fun for decision makers.  Is this better? Worse? The plateau of a hocky-stick?  No one knows if some actual history is not included.  All aspects of the company’s financial performance are informed by including several years of actual results.  Also, make the results “live”.  Don’t just type in their numbers.  If you are forced to make the three statements work together from actual inputs, you will be amazed at the insights you gain into the business operations of the company.  If accounts don’t roll forward, there is a reason (sold assets, acquisitions, stock sales, re-statements).
  15. Simplify where you can.  As in the pension example, if there are items that do not affect the model or are not material, do not make the model overly complex (which can add errors and time) in an attempt to perfectly reflect that which no one cares about.
  16. Read your output and trap errors.  It is common for the balance sheet to unbalance.  Write a formula that raises a flag when this happens.  Look for common pitfalls by adding ratios and margins even if they don’t make it to the final output.  It is easy to see when your SG&A formulas do not work when SG&A runs from 10% of revenue to 20% in a few years.  Likewise for profits, assets, and working capital.  There should be a business reason for all fluctuations in results (product mix, price changes, expansions, downsizing, etc.).  The model should not, through only its operation, affect the future economics of the enterprise.