11.2 Building Integrated Financial Statement Models

Building integrated financial statement models is crucial for accurate financial forecasting. These models link the income statement, balance sheet, and cash flow statement, allowing for dynamic relationships between financial data. Understanding how changes in one statement affect others is key to creating robust financial projections.

Forecasting techniques for revenue, expenses, and balance sheet items form the foundation of these models. By connecting assumptions and ratios to financial statement line items, analysts can create flexible models that update automatically. This enables scenario analysis and helps in making informed financial decisions.

Integrated Financial Statement Modeling

Linking the Three Primary Financial Statements

Top images from around the web for Linking the Three Primary Financial Statements

• 

  The Statement of Cash Flows | Boundless Accounting View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Finance View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Business View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Accounting View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Finance View original

  Is this image relevant?

1 of 3

Top images from around the web for Linking the Three Primary Financial Statements

• 

  The Statement of Cash Flows | Boundless Accounting View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Finance View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Business View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Accounting View original

  Is this image relevant?

• 

  The Statement of Cash Flows | Boundless Finance View original

  Is this image relevant?

1 of 3

• An integrated financial statement model connects the income statement, balance sheet, and cash flow statement in Excel using cell references and formulas to create dynamic relationships between the statements
• The income statement reports revenues, expenses and profitability (net income)
• The balance sheet reports assets, liabilities and shareholders' equity at a point in time
• The cash flow statement reports cash inflows and outflows from operating, investing and financing activities over a period of time

Key Concepts for Building Integrated Models

• Building an integrated model requires understanding the interrelationships and accounting equations that link the financial statements together
  • Net income flows to retained earnings on the balance sheet
  • Depreciation expense is added back on the cash flow statement
• Assumptions for growth rates, profitability ratios (gross margin), turnover ratios (inventory turns) and other metrics are entered in a separate assumptions section and cell referenced to applicable line items to create a dynamic model
• Circular references can occur in integrated financial statement models when cell references create a continuous calculation loop
  • Must be resolved using techniques like enabling iterative calculations or using a copy/paste macro
• Integrated financial statement models are used for forecasting, sensitivity analysis, scenario modeling (base case, downside), and valuation (discounted cash flow)
  • Allow the impact of changes in assumptions to flow through to all of the statements

Forecasting Income Statement Items

Sales Revenue Forecasting Techniques

• Forecasting sales revenue is often the starting point for building a financial model
• Common techniques include:
  • Using historical growth rates
  • Regression analysis
  • Industry benchmarks
  • Tying to underlying demand drivers (GDP growth, housing starts)

Forecasting Expenses

• Variable expenses like cost of goods sold and certain operating expenses are often forecasted as a percentage of sales based on historical or expected future ratios
  • This creates a dynamic link where they automatically adjust with sales
• Fixed expenses may be forecasted based on historical trends, inflation, known future changes, or as a fixed percentage of sales if sufficient operating leverage exists
• Depreciation is forecasted based on the existing fixed asset schedule and accounting depreciation method (straight-line, accelerated) plus depreciation on future capital expenditures
  • Amortization of intangibles is forecasted similarly
• Interest income and interest expense are tied to the cash balance and debt balance from the balance sheet and projected forward using expected interest rates on excess cash and borrowings
• Income tax expense is calculated by applying the marginal tax rate to taxable income
  • Permanent and temporary book tax differences must be considered

Projecting Balance Sheet Items

Balance Sheet Items Linked to the Income Statement

• Certain balance sheet items are tied directly to the income statement
  • Retained earnings is linked to net income
  • Accumulated depreciation is linked to depreciation expense
• Accounts receivable is driven by credit sales and the accounts receivable collection period in days
  • Increasing sales or slower collections will increase accounts receivable
• Inventory is projected based on inventory turnover ratios or days inventory outstanding and cost of goods sold
  • Higher inventory relative to COGS reduces turnover
• Accounts payable is tied to inventory purchases and the payables payment period
  • Increasing inventory or stretching payables increases the accounts payable balance
• Accrued expenses are typically based on a certain number of days of the underlying expense (wage accruals based on days of compensation expense)

Fixed Assets and Debt Projections

• Fixed assets are rolled forward each period for capital expenditures, depreciation and any disposals or impairments
  • The fixed asset schedule must be maintained to calculate depreciation
• Debt may be tied to an interest coverage ratio or leverage ratio (debt/EBITDA) and is increased or paid down based on funding needs
  • The model must account for mandatory debt repayments and new borrowings

Cash Flow Statement Forecasting

Cash Flow from Operating Activities

• The cash flow statement starts with net income from the income statement and makes adjustments to convert from accrual accounting to a cash basis
• Depreciation and amortization are added back to net income as non-cash expenses
  • Deferred income taxes are also added back if using straight-line depreciation for book and accelerated for taxes
• Changes in operating assets and liabilities are calculated based on the beginning and ending balances from the balance sheet to determine the impact on cash
  • An increase in an asset like accounts receivable is a use of cash
  • An increase in a liability like accounts payable is a source of cash

Cash Flow from Investing and Financing Activities

• Cash flows from investing activities include:
  • Capital expenditures as a use of cash
  • Proceeds from fixed asset sales as a source of cash
  • These are linked to PP&E on the balance sheet
• Cash flows from financing activities include:
  • Debt borrowings as a source of cash
  • Debt repayments as a use of cash
  • These are tied to the change in the debt balance on the balance sheet
• The net change in cash flows is added to the beginning cash balance to arrive at the ending cash balance for each forecasted period, which links to the balance sheet