1.0 Basic Standards

  • Models are to be designed so others can use and edit them.  Models are to be:
    • readily understandable (what the input is, what the output is, what the model does)
    • easy to edit (simple formulas, basic commands)
    • easy to read, both as a spreadsheet and as a printed document, color coded appropriately

 

  • Models are "works in progress".
    • Rarely does a model reach the "done" stage.  Models are to be designed and built so that they are readily updatable.  Models can be built in modules; modules can be considered "done".

 

  • Input is to be blue.
    •  This makes it much easier for others to understand the model.

       

    • i.e. 4,000 is an input, and 936,000 is an output.  The user is allowed to change the 4,000, but is not allowed to change the 936,000.

2.0 "IS BS CF Model" Standards

A "IS BS CF Model" is a financial model that produces Income Statements, Balance Sheets, and Cash Flow Statements. There are four phases to a "IS BS CF Model".

By way of example of the "Apples, Bananas, and Cherries Company"

  • A "IS BS CF Model"  must contain at least the following worksheets:

Standard Phase

    • About (author, date, simple description of model, contact information, etc.)
    • Setup (one time entries - e.g. name of company, fiscal year end, currency, etc.)
    • ABC Input (what the user is expected to change)
    • ABC IS (ABC Income Statement)
    • ABC BS (ABC Balance Sheet)
    • ABC CF (ABC Cash Flow Statement)
    • ABC Cash (a detailed cash receipts and cash disbursements schedule)

Validation Phase

    • ABC Budget IS (contains hard keyed approved budget income statements)
    • ABC Actual IS (contains hard keyed actual income statements, historical)
    • ABC Actual BS (contains hard keyed actual balance sheets, historical)
    • ABC Cost Accounting (detailed product costing, margin analysis, break-even point)
    • ABC Variance (actual vs budget variances, flexible budget variances)

Advanced Phase

    • ABC Valuation (a valuation of ABC including a regression analysis)

Expert Phase

    • ABC Optimization (a program (Solver) to optimize shareholder value and or profit)

 

  • The model has a defined starting point.
    • A starting point for a "IS BS CF Model" is a balance sheet. Use a balance sheet that is a year or so old as the starting point.  Why?  If the starting point (balance sheet) is plugged into the model and the Input sheet is adjusted so that it recreates the Income Statement over the past year and recreates certain Balance Sheet accounts now, if cash as predicted in the model is equal or close to cash on the Balance Sheet now, then the model has been partly validated.

 

  • For all sheets, the columns match.
    • e.g. If column "CL" refers to May 2005 on the balance sheet, column "CL" on the income statement should refer to May 2005.  This makes it much easier to edit and troubleshoot the financial model.

 

  • The display is set at 1024 X 768. 
    • A full year of data is to be viewable at any one time.  A single click in the scroll area  advances the model exactly one year.  Column widths of 14 and font sizes of 12 work well.  For a 13 period fiscal year model, a 55% zoom works well.  For a 12 month fiscal year, a 60% zoom works well.
    • see the screen shots area for examples.

 

3.0 About these Design Standards

The standards presented here are intended as rules of thumb or good practices when designing and building financial models.

If you have any standards you feel we should include, please e-mail us using the E-mail link.