Financial Statement Modeling & Spreadsheet Engineering

“Training in spreadsheet modeling improves both the efficiency and effectiveness with which analysts use spreadsheets”

Steve Powell, Dartmouth College

Understand the Decision

 What is the problem? What questions must be answered?

 What is the time horizon of the problem? What happens then?

 What kind of output information is needed?

 Who will use the information? What level of detail is necessary?

Modules

 Data Inputs  Assumptions  Decision Variables  Internal Data and Data Structures  Outputs  Pro-forma statements  Other structural relationships  Documentation

Plan Your Layout

 On paper, map out the different sections of your model and their planned locations within the workbook  Consider how  readable the model layout will be for the user  easy it will be to modify the assumptions  easy it will be to extend the model to include more time periods or new line items.

Layout Strategies

 Modules on different worksheets  Stair-step format in same worksheet  For each strategy,  Consider how difficult it will be to insert or delete a row or column  Consider how much linking needs to be done between different modules  Consider linking a data input to the computation area or output for later reference

Other Layout Considerations

 Gaps between logical areas improve readability  Isolate blocks of similar formulas for safe and convenient copying  Use styles, fonts and colors with consistent meanings  Assumptions –vs- decision inputs  Key outputs

Data Inputs

 Internal data that describes the current state of the system  Firm’s financial statements  Resources and capacities  External data that describes the firm’s operating environment  Economic conditions such as interest rate, costs, stock prices  Assumptions about future relationships

Data Input Guidelines



Isolate constants into their own cells

 Do not use constants in formulas unless number has a mathematical foundation (e.g. p , 1 in percentage calculations)  Don’t allow an input value to be entered in multiple places in the model  Organize decision variables and model parameter/assumptions logically into separate physical areas or color schemes  Use

Data > Data Validation

Modeling Sales Forecasts

• Simple Method: Assume average or different growth rate for each period • • • Develop detail forecast by product, business unit, customer or geographic market and aggregate forecasts Forecast the unit price and volume Forecast size of market and estimate market share • Forecast costs and apply a margin assumption to derive revenues

Financial Planning Model Assumptions

• • • Items that are functional relationships of sales – Percent of sales • • Operating expenses Working capital and fixed assets Items that are functional relationships of other financial statement items – Interest, depreciation, dividends, marketable securities, cash flows Items that involve policy decisions (e.g. long term debt, equity, capital expenditures)

Generating the Income Statement

 Expenses in period

t

 Related to asset and liability balances at the end of period

t – 1

Or 

Interest Expense t

based on

Debt t-1



Depreciation t

based on

Gross Fixed Assets t-1

 Related to

Sales

in period

t



COGS t = COGS/Sales * Sales t

11

Balance Sheet Sector

 Track financial state of firm through time  Results in period (

t – 1

) become inputs for period (

t

)  EXAMPLE:

Debt t = Debt t-1 + Debt Issued t – Debt Repaid t

which feeds into income statement

Interest Expense t = Debt t-1 * Interest Rate

12

The “Plug”

 The balance sheet item which will “close” or balance the balance sheet model so that Total Assets will always equal Total Liabilities & Equity  Models the assumption of how the firm finances itself  Examples are cash & marketable securities, debt, debt & equity

To Reduce Formula Errors

 Use relative and absolute cell addressing where appropriate  Set up common structures in different parts of the model (e.g. same row labels, order and vertical positioning)  Use formulae for a cell which are consistent in their relationship to adjacent cells  Minimize linking formulas across worksheets  Split complex formulas into multiple intermediary formulas

Document the Spreadsheet

 Use range names to make individual formulas more readable and module layouts clearer  Use comments to document assumptions and modifications in a formula’s logic as well as sources of data  Use textboxes to describe portions of the model’s logic and assumptions as well as instructions for use  Create a worksheet to describe the purpose of the model and document changes

Testing the Model

  Compare a second calculation method to verify accuracy Ask intuitive questions:   Does it balance under different conditions?

Does it make sense?

 Extreme growth      Set growth rate to -50% and 50% Does it still balance?

Do debt or equity go negative?

What happens to excess cash flow?

What happens when Net Income is negative?

16

Circular References

• • • • Created when expenses in period

t

refer to assets and liabilities in period

t

Iterative process is required to produce a consistent and correct final set of financial statements To solve, use

File> Options> Formulas> Enable Iterative Calculation

Not all circularities are convergent. Make sure to verify accuracy!

Verify Formula Reliability

 Use Excel’s Auditing Toolbar 

Formulas>Formula Auditing

 Use [Ctrl] ` to toggle between formulas and calculations  Use Conditional Formatting to flag undesirable results  Highlight cell to be watched and add rule 

Home>Conditional Formatting