Lesson Objective: To blueprint the physical layout of the integrated financial model, ensuring that the Income Statement (IS), Balance Sheet (BS), and Cash Flow Statement (CFS) are linked in a logical, chronological “arc” that prevents balance sheet breaks.

In-Depth Notes:

1. The Mechanical Hierarchy:
The global standard dictates a specific physical ordering of sheets in the workbook:

• Sheet 0: Control Panel/Assumptions (All inputs, scenario toggles, and exchange rates).

• Sheet 1: Historical Data (Raw) (Pasted directly from audited financials, untouched).

• Sheet 2: Historical Data (Restated/Organized) (Adjusted for modeling taxonomy, e.g., reclassifying operating leases).

• Sheet 3: Supporting Schedules (Debt schedule, PP&E schedule, Working Capital schedule).

• Sheet 4: The Core Financial Statements (Projected IS, BS, and CFS).

• Sheet 5: Valuation/Outputs (Dashboards, ratios, charts).

2. The P&L Sequencing (Gross to Net):
The Income Statement must be constructed using “Step” logic, compliant with both IFRS and US GAAP “function of expense” classification:

• Revenue: Gross Revenue minus Returns/Allowances.

• Cost of Goods Sold (COGS): Directly attributable production costs (must include depreciation from the PP&E schedule).

• Gross Profit / EBITDA Proxy: Global standards now emphasize EBITDA (Earnings Before Interest, Taxes, Depreciation, & Amortization) as a proxy for operating cash flow.

• Operating Expenses (SG&A): Must be split into Cash SG&A and Non-Cash SG&A (Stock-Based Compensation).

• Operating Income (EBIT): The critical “checkpoint” that links to the CFS.

• Non-Operating Items: Interest Income/Expense (linked from the debt schedule) and Other Income (linked from assumptions).

• Tax: Calculated on Pre-Tax Income, but must incorporate a “tax loss carryforward” schedule to handle NOLs (Net Operating Losses), which is highly relevant in European modeling due to slower post-recession recovery periods.

3. The Balance Sheet Mechanics (A = L + E):
This statement must be built using “roll-forward” schedules. The model must automatically check the balance at every forecast period using a “Balance Check” that flags an error if Assets – Liabilities does not equal Equity.

• Assets: Cash (the plug), AR (linked to Days Sales Outstanding), Inventory (linked to Days Inventory Outstanding), PP&E (linked to the Capital Expenditure schedule).

• Liabilities: AP (linked to Days Payable Outstanding), Accrued Expenses, and Debt (Short-term and Long-term linked to the debt schedule).

• Equity: Share Capital, Retained Earnings (which is the cumulative sum of Net Income minus Dividends). Dividends are a major differentiator here; US models often project dividends as a percentage of net income, whereas European models (influenced by regulatory capital requirements for banks) often project dividends as a fixed payout ratio of earnings per share.

4. The Cash Flow Statement (The Reconciliation Engine):
The CFS is the engine that forces the balance sheet to balance. It must be constructed using the Indirect Method (mandatory under US GAAP and widely used in Europe).

• Operating Cash Flow: Starts with Net Income, adds back non-cash charges (D&A, Stock Comp), and subtracts/ adds changes in Working Capital. Crucially, global standards require separating “Changes in Operating Assets/Liabilities” to show the cash impact of inventory or AR growth.

• Investing Cash Flow: Primarily CAPEX (capital expenditures) and proceeds from asset sales.

• Financing Cash Flow: Dividends, share buybacks, and net borrowings (drawdowns/repayments).

• Ending Cash Check: The ending cash balance derived from the CFS must equal the cash balance on the Balance Sheet. If it does not, the model is “broken” and the output is useless. This is known as the “Cash Sweep” process.