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 kind of output information is needed?

 Who will use the information?

 What level of detail is necessary?

Structural Inputs of the Model

 Define the equations and relationships that make up the model  What are the decision variables and what are the linkages between the decisions and the goal of the model?

 What are the constraints?

 Definitions based on accounting rules  Economic, industry, resource and technological limits  What happens at the end of the planning horizon?

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

Financial Analysis

 Assess the strengths and weaknesses of the firm’s current condition  Ratio analysis  Break-even analysis  Operating and financial leverage analysis  Generate pro forma financial statements to identify strategies to improve the condition and assess future risks

Growth and the Need for Financing

 Growth frequently associated with cash  Faster growth  more profits  more cash  This is incorrect!

 The problem  Growth and profits do not equal cash flow 

Growth is not an end in itself

 Needs to be managed  Firms often fail because they do not plan for growth 6

Cash Flow is Needed to Support Sales

 Cash is the life blood of organizations    Funds receivables and inventory Buys productive assets Pays investors fair returns

BUT

 Cash needed to support growing sales can exceed cash flow  Firms fail when

Needs

of expansion

Overwhelm Resources

for expansion 7

Flow of a Sales-Driven Model

8

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 sales

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  Balance sheet equations go a long way towards ensuring this  Results in one period (

t – 1

) become inputs to next period (

t

)   EXAMPLE 

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

Feed into income statement 

Interest Expense t = Debt t-1 * Interest Rate

12

Investment Module

Calculates

Required New Investment

  

Compile changes in assets

   Left side of balance sheet Current Assets   Including Cash Balance Not including discretionary items (

e.g.

securities) marketable Long-term assets  Net Fixed Assets  Goodwill

Add cost of maintaining production

 Replacement of depreciated assets

Output information to Financing Module 13

Three Sources for Financing New Investment



Spontaneous

 Increase in liabilities that increase naturally with sales 

Internal equity

 Current year's retained earnings 

External capital markets

 New debt or equity 14

Sustainable Growth in the Rapid Growth Phase

 The "it takes money to make money" phase   Increased sales require more assets of all types Internal sources don’t generate enough cash  Retained earnings  Increase in spontaneous liabilities  Need to raise capital externally 

New question: How fast can firm grow without altering its current capital structure?

15

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  See

Plugs.xlsx

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 products.

Modules

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

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

Range Names

 Use range names to create an overview of a worksheet in the model layout.

 Highlight the cell or range you want to name, select

Insert>Name>Define

(2003) or

Formulas>Define Name

(2007)  Use

View>Zoom

where the Zoom factor is set to less than 40% to see overview  To apply name to all relevant formulas in spreadsheet,

Insert>Name>Apply

(2003) or

Formulas>Define Name>Apply Name

(2007)

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

Data Input Guidelines



Isolate constants into their own cells

 Do not use constants in formulas unless it is a number like 1 or 100 being used in percentage calculations  Don’t have multiple instances of a number that is used as a decision variable or assumption  Organize decision variables and model parameter/assumptions logically into separate physical areas

To Reduce Formula Errors

 Use relative and absolute cell addressing where appropriate (Use [F4] to modify $ in formulas!)  Set up common structures in different parts of the model (e.g. same row labels for different worksheets)  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

Circular References

• • • • Created when expenses in period

t

refer to assets and liabilities in period

t

, resulting in modified values for the plug which then impact the income statement and the Addition to Retained Earnings Iterative process is required to produce a consistent and correct final set of financial statements To solve, use

Tool Options Calculation Iteration

(2003) or

MS Office Button>Excel Options> Formulas> Iteration

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

Verify Formula Reliability

 Use Excel’s Auditing Toolbar 

Tools>Auditing>

(2003) 

Formulas>Formula Auditing

(2007)  Use [Ctrl]+` to toggle between formulas and calculations  Use Conditional Formatting to flag suspicious results 

Format>Conditional Formatting

(2003) 

Home>Conditional Formatting

(2007)

Calculating a Plug with the Investment and Financing Modules

  Investment module  Required New Investment Financing module    Required External Financing Spontaneous Financing Internal Equity Financing

Required External Financing



Required New Investment



Spontaneous Financing



Internal Equity Finan cing

• See

Logic for Debt as Secondary Plug

Excel file

26

Testing the Model

 Questions:   Does it balance under extreme 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?

 High Interest  What happens when Net Income is negative?

27

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