Session 9 of 26 Part III: Forecasting — Chapter 9 of 18

Part III: Forecasting Chapter 9 of 18 Session 9 Python

Integrated Financial Forecast Model

Build a complete three-statement model in Python — linking the income statement, balance sheet, and cash flow statement into a single, self-consistent forecasting engine.

Learning Objectives

Understand the architecture of an integrated three-statement financial model
Build a forecast income statement from revenue and margin assumptions
Build a forecast balance sheet with working capital, PP&E, debt, and equity schedules
Derive the cash flow statement from the income statement and balance sheet changes
Resolve circular references in Python using iterative convergence
Validate the model: does the balance sheet balance? Does cash reconcile?

9.1 The Architecture of an Integrated Model

An integrated financial model is a system where the three financial statements are linked so that a change in one statement automatically flows through to the others. Revenue growth drives the income statement, which drives retained earnings on the balance sheet, which drives the cash flow statement, which feeds back to the cash balance on the balance sheet — closing the loop.

This is fundamentally different from the independent forecasting we did in Chapters 7 and 8. There, we forecast revenue and margins in isolation. Here, we build a model where:

Revenue drives COGS, operating expenses, working capital, and taxes.
EBIT drives interest expense (through the debt schedule) and net income.
Net income drives retained earnings on the balance sheet.
Capex and depreciation drive net PP&E.
Working capital changes drive operating cash flow.
The cash flow statement determines the ending cash balance, which appears on the balance sheet.
Excess cash (or cash shortfall) drives debt issuance or repayment — closing the funding loop.

The Three-Statement Loop

Income Statement → Net Income → Retained Earnings (Balance Sheet) → Cash Flow Statement → Ending Cash (Balance Sheet) → Interest Income/Expense (Income Statement) → Net Income (closes the loop). A well-built model iterates until this loop converges.

💡

Pro Tip: In Excel, the circular reference from interest expense → debt → interest is handled by enabling iterative calculation. In Python, we handle it explicitly with a while-loop that recalculates until the change between iterations is below a tolerance. This gives you precise control over the convergence process — something Excel's black-box iteration does not offer.

9.2 The Income Statement Forecast

The income statement forecast starts with your revenue projection (from Chapter 7) and applies margin assumptions to derive each line item. The key principle: every line item is driven by either revenue (a percentage of sales) or by a schedule (depreciation, interest).

import numpy as np
import pandas as pd

def forecast_income_statement(last_year_data, assumptions, forecast_years=5):
    """
    Build a 5-year income statement forecast.

    Parameters
    ----------
    last_year_data : dict with keys: Revenue, COGS_Pct, SGnA_Pct,
                     Depreciation, Interest_Expense, Tax_Rate
    assumptions : dict with keys: Revenue_Growth (float or list of 5),
                  Gross_Margin_Pct, SGnA_Pct_of_Revenue
    forecast_years : int

    Returns
    -------
    DataFrame with forecast income statement.
    """
    rev_growth = assumptions['Revenue_Growth']
    if isinstance(rev_growth, (int, float)):
        rev_growth = [rev_growth] * forecast_years

    gross_margin = assumptions.get('Gross_Margin_Pct',
                                    (1 - last_year_data.get('COGS_Pct', 0.55)) * 100)
    sga_pct = assumptions.get('SGnA_Pct_of_Revenue',
                               last_year_data.get('SGnA_Pct', 0.15))

    # Starting values
    prev_revenue = last_year_data['Revenue']
    depr_rate = last_year_data.get('Depreciation_Rate', 0.05)
    tax_rate = last_year_data.get('Tax_Rate', 0.25)

    rows = []
    for y in range(forecast_years):
        revenue = prev_revenue * (1 + rev_growth[y])
        cogs = revenue * (1 - gross_margin / 100)
        gross_profit = revenue - cogs
        sga = revenue * sga_pct
        ebitda = gross_profit - sga

        # Depreciation grows with capex — simplified: constant % of revenue
        depreciation = revenue * depr_rate
        ebit = ebitda - depreciation

        # Interest — placeholder; will be refined with debt schedule
        interest = last_year_data.get('Interest_Expense', revenue * 0.01)

        pbt = ebit - interest
        tax = max(pbt, 0) * tax_rate  # No tax credit on losses
        net_income = pbt - tax

        rows.append({
            'Year': y + 1,
            'Revenue': revenue,
            'COGS': cogs,
            'Gross Profit': gross_profit,
            'Gross Margin %': round(gross_margin, 1),
            'SG&A': sga,
            'EBITDA': ebitda,
            'Depreciation': depreciation,
            'EBIT': ebit,
            'Interest Expense': interest,
            'PBT': pbt,
            'Tax': tax,
            'Effective Tax Rate %': round(tax / pbt * 100, 1) if pbt > 0 else 0,
            'Net Income': net_income,
        })

        prev_revenue = revenue

    return pd.DataFrame(rows).set_index('Year')


# --- Example: Forecast for a consumer company ---
last_year = {
    'Revenue': 35500,
    'COGS_Pct': 0.57,
    'SGnA_Pct': 0.18,
    'Depreciation_Rate': 0.03,
    'Interest_Expense': 160,
    'Tax_Rate': 0.25,
}

assumptions = {
    'Revenue_Growth': [0.14, 0.13, 0.12, 0.11, 0.10],
    'Gross_Margin_Pct': 43.0,
    'SGnA_Pct_of_Revenue': 0.175,
}

is_forecast = forecast_income_statement(last_year, assumptions)
print("=== INCOME STATEMENT FORECAST (Rs. Crore) ===")
print(is_forecast.round(1))

The income statement above is the first building block. But it has a flaw: interest expense is a placeholder. In a fully integrated model, interest depends on the debt balance, which depends on the cash flow, which depends on net income, which depends on interest. This circular reference is resolved in Section 9.5.

9.3 The Balance Sheet Forecast

The balance sheet forecast projects assets, liabilities, and equity. Most line items are driven by revenue (working capital), by schedules (PP&E, debt), or by flows from other statements (retained earnings from net income, cash from the cash flow statement).

def forecast_balance_sheet(last_year_bs, is_forecast, assumptions, forecast_years=5):
    """
    Build a 5-year balance sheet forecast.

    Parameters
    ----------
    last_year_bs : dict — {Cash, Receivables, Inventory, Net_PPE, Total_Assets,
                            Payables, Short_Term_Debt, Long_Term_Debt, Equity}
    is_forecast : DataFrame — from forecast_income_statement()
    assumptions : dict — {Capex_Pct_of_Revenue, WC_Receivable_Days,
                          WC_Inventory_Days, WC_Payable_Days,
                          Debt_Repayment, Dividend_Payout_Ratio}

    Returns
    -------
    DataFrame with forecast balance sheet.
    """
    # Working capital drivers
    ar_days = assumptions.get('WC_Receivable_Days', 60)
    inv_days = assumptions.get('WC_Inventory_Days', 80)
    ap_days = assumptions.get('WC_Payable_Days', 55)
    capex_pct = assumptions.get('Capex_Pct_of_Revenue', 0.06)
    div_payout = assumptions.get('Dividend_Payout_Ratio', 0.30)
    s_t_debt = last_year_bs.get('Short_Term_Debt', 0)
    lt_debt = last_year_bs.get('Long_Term_Debt', 1000)

    # Starting balances
    cash = last_year_bs.get('Cash', 2800)
    net_ppe = last_year_bs.get('Net_PPE', 6500)
    equity = last_year_bs.get('Equity', 12500)

    rows = []
    for idx, is_row in is_forecast.iterrows():
        revenue = is_row['Revenue']
        cogs = is_row['COGS']
        net_income = is_row['Net Income']
        depreciation = is_row['Depreciation']

        # Working capital
        receivables = revenue * (ar_days / 365)
        inventory = cogs * (inv_days / 365)
        payables = cogs * (ap_days / 365)

        # Capex and PP&E
        capex = revenue * capex_pct
        net_ppe = net_ppe + capex - depreciation

        # Debt — simplified: constant until we add the full integration
        total_debt = s_t_debt + lt_debt

        # Equity — retained earnings
        dividends = max(net_income, 0) * div_payout
        equity = equity + net_income - dividends

        # Total assets and liabilities
        current_assets = cash + receivables + inventory
        total_assets = current_assets + net_ppe

        current_liabilities = payables + s_t_debt
        total_liabilities = current_liabilities + lt_debt
        total_liabilities_equity = total_liabilities + equity

        rows.append({
            'Year': idx,
            # Assets
            'Cash': cash,
            'Receivables': receivables,
            'Inventory': inventory,
            'Total Current Assets': current_assets,
            'Net PP&E': net_ppe,
            'Total Assets': total_assets,
            # Liabilities
            'Payables': payables,
            'Short-Term Debt': s_t_debt,
            'Total Current Liabilities': current_liabilities,
            'Long-Term Debt': lt_debt,
            'Total Liabilities': total_liabilities,
            # Equity
            'Shareholders Equity': equity,
            'Total Liabilities & Equity': total_liabilities_equity,
            # Check
            'Balance Check': total_assets - total_liabilities_equity,
            # Memo
            'Capex': capex,
            'Dividends': dividends,
        })

    return pd.DataFrame(rows).set_index('Year')


last_year_bs = {
    'Cash': 2800, 'Receivables': 3500, 'Inventory': 4200,
    'Net_PPE': 6500, 'Total_Assets': 22000,
    'Payables': 3600, 'Short_Term_Debt': 500, 'Long_Term_Debt': 1800,
    'Equity': 12500,
}

bs_assumptions = {
    'WC_Receivable_Days': 55,
    'WC_Inventory_Days': 75,
    'WC_Payable_Days': 50,
    'Capex_Pct_of_Revenue': 0.06,
    'Dividend_Payout_Ratio': 0.30,
}

bs_forecast = forecast_balance_sheet(last_year_bs, is_forecast, bs_assumptions)
print("\n=== BALANCE SHEET FORECAST (Rs. Crore) ===")
print(bs_forecast.round(1))

# Verify the balance sheet balances
balance_ok = (bs_forecast['Balance Check'].abs() < 0.01).all()
print(f"\nBalance Sheet Balances: {'YES' if balance_ok else 'NO — check formulas'}")

⚠

Red Flag: If the Balance Check column shows anything other than zero (or near-zero from rounding), your model has a bug. Common causes: forgetting to add net income to retained earnings, double-counting depreciation, or mismatching the cash flow with the balance sheet change. A non-zero balance check means the model is not integrated and cannot be trusted for valuation.

9.4 The Cash Flow Statement Forecast

The cash flow statement is derived from the income statement and balance sheet. You do not forecast it independently — it is the mathematical consequence of your IS and BS forecasts. The cash flow statement serves a dual purpose: it reconciles the change in cash, and it reveals the cash generation capability that drives your DCF.

def forecast_cash_flow(is_forecast, bs_forecast):
    """
    Derive the cash flow statement from the IS and BS forecasts.

    The CF statement has three sections:
      1. Operating Activities (CFO) — cash from core business
      2. Investing Activities (CFI) — capex, acquisitions
      3. Financing Activities (CFF) — debt, equity, dividends

    Parameters
    ----------
    is_forecast, bs_forecast : DataFrames from the IS and BS forecast functions

    Returns
    -------
    DataFrame with the cash flow statement.
    """
    rows = []

    # We need the BS from the prior year for changes.
    # Simulate a "Year 0" BS row for the first year's change calculation.
    bs_shifted = bs_forecast.shift(1)
    bs_shifted.iloc[0] = bs_forecast.iloc[0]  # Placeholder; replace with actual prior year

    for idx in is_forecast.index:
        is_row = is_forecast.loc[idx]
        bs_row = bs_forecast.loc[idx]
        bs_prev = bs_shifted.loc[idx]

        # --- Operating Activities ---
        net_income = is_row['Net Income']
        depreciation = is_row['Depreciation']

        # Working capital changes
        delta_receivables = -(bs_row['Receivables'] - bs_prev['Receivables'])
        delta_inventory = -(bs_row['Inventory'] - bs_prev['Inventory'])
        delta_payables = bs_row['Payables'] - bs_prev['Payables']

        cfo = (net_income + depreciation +
               delta_receivables + delta_inventory + delta_payables)

        # --- Investing Activities ---
        capex = -(bs_row['Capex'])  # Cash outflow
        cfi = capex

        # --- Financing Activities ---
        dividends = -(bs_row['Dividends'])
        # Debt change (simplified)
        delta_st_debt = bs_row['Short-Term Debt'] - bs_prev['Short-Term Debt']
        delta_lt_debt = bs_row['Long-Term Debt'] - bs_prev['Long-Term Debt']
        cff = delta_st_debt + delta_lt_debt + dividends

        # --- Reconciliation ---
        net_change = cfo + cfi + cff
        implied_ending_cash = bs_prev['Cash'] + net_change

        rows.append({
            'Year': idx,
            'Net Income': net_income,
            '+ Depreciation': depreciation,
            '+/- Δ Receivables': delta_receivables,
            '+/- Δ Inventory': delta_inventory,
            '+/- Δ Payables': delta_payables,
            'CFO': cfo,
            'Capex': capex,
            'CFI': cfi,
            'Dividends': dividends,
            '+/- Δ Debt': delta_st_debt + delta_lt_debt,
            'CFF': cff,
            'Net Change in Cash': net_change,
            'Implied Ending Cash': implied_ending_cash,
        })

    cf_forecast = pd.DataFrame(rows).set_index('Year')

    # Verify: Implied Ending Cash should match BS Cash
    cash_match = (cf_forecast['Implied Ending Cash'] - bs_forecast['Cash']).abs()
    print(f"Max Cash Reconciliation Error: Rs. {cash_match.max():.2f} Cr")
    print(f"Cash Reconciles: {'YES' if cash_match.max() < 1.0 else 'NO — investigate'}")

    return cf_forecast


cf_forecast = forecast_cash_flow(is_forecast, bs_forecast)
print("\n=== CASH FLOW STATEMENT FORECAST (Rs. Crore) ===")
print(cf_forecast.round(1))

The cash flow statement is the ultimate test of model integrity. If the implied ending cash does not match the cash on the balance sheet, your model has a logic error. A reconciled cash flow statement is the gold standard of financial modeling.

9.5 Resolving Circular References: The Iterative Convergence Engine

The models in Sections 9.2–9.4 have a deliberate simplification: interest expense is fixed, and debt does not respond to cash flow. In reality, these create a circular loop:

Interest Expense → Net Income → Retained Earnings → Cash Flow → Cash Balance → Debt Issued/Repaid → Interest Expense (loop closes)

In Excel, you enable iterative calculation. In Python, we implement a convergence loop: compute the model with an initial guess for interest, update the debt based on the resulting cash flow, recompute interest from the new debt, repeat until the change in interest between iterations is below a tolerance.

def resolve_circular_reference(model_func, initial_guess, tolerance=0.001, max_iterations=50):
    """
    Generic iterative convergence engine for circular references.

    Parameters
    ----------
    model_func : callable — takes a value and returns (new_value, converged_bool)
    initial_guess : float
    tolerance : float — stop when |new - old| < tolerance
    max_iterations : int — safety limit

    Returns
    -------
    converged_value, iterations
    """
    old_value = initial_guess
    for i in range(max_iterations):
        new_value = model_func(old_value)
        if abs(new_value - old_value) < tolerance:
            return new_value, i + 1
        old_value = new_value

    print(f"WARNING: Did not converge after {max_iterations} iterations. "
          f"Last change: {abs(new_value - old_value):.4f}")
    return new_value, max_iterations


def integrated_model_with_debt_schedule(last_year, assumptions, forecast_years=5):
    """
    Complete integrated model with circular reference resolution.

    The circular reference: Interest → Net Income → CF → Debt → Interest

    This function uses iterative convergence to resolve it.
    """
    tolerance = 0.01
    max_iter = 30

    # Extract assumptions
    rev_growth = assumptions['Revenue_Growth']
    if isinstance(rev_growth, (int, float)):
        rev_growth = [rev_growth] * forecast_years

    gross_margin = assumptions['Gross_Margin_Pct']
    sga_pct = assumptions['SGnA_Pct_of_Revenue']
    tax_rate = assumptions.get('Tax_Rate', 0.25)
    capex_pct = assumptions.get('Capex_Pct_of_Revenue', 0.06)
    div_payout = assumptions.get('Dividend_Payout_Ratio', 0.30)
    interest_rate_on_debt = assumptions.get('Interest_Rate_on_Debt', 0.08)
    min_cash = assumptions.get('Min_Cash_Balance', 500)

    # Starting balances
    cash = last_year['Cash']
    receivables = last_year['Receivables']
    inventory = last_year['Inventory']
    net_ppe = last_year['Net_PPE']
    payables = last_year['Payables']
    debt = last_year['Short_Term_Debt'] + last_year['Long_Term_Debt']
    equity = last_year['Equity']
    prev_revenue = last_year['Revenue']

    # Working capital days
    ar_days = assumptions.get('WC_Receivable_Days', 55)
    inv_days = assumptions.get('WC_Inventory_Days', 75)
    ap_days = assumptions.get('WC_Payable_Days', 50)

    all_years = []

    for y in range(forecast_years):
        # --- Revenue & Operating Costs (no circularity) ---
        revenue = prev_revenue * (1 + rev_growth[y])
        cogs = revenue * (1 - gross_margin / 100)
        gross_profit = revenue - cogs
        sga = revenue * sga_pct
        ebitda = gross_profit - sga
        depreciation = revenue * last_year.get('Depreciation_Rate', 0.03)

        # This is where Excel would have a circular reference.
        # We resolve it by iterating over interest and debt.
        def interest_debt_loop(guess_interest):
            """One pass through the circular loop given an interest guess."""
            ebit = ebitda - depreciation
            pbt = ebit - guess_interest
            tax = max(pbt, 0) * tax_rate
            net_income = pbt - tax

            # Cash flow
            wc_delta = (
                -(revenue * ar_days / 365 - receivables) +
                -(cogs * inv_days / 365 - inventory) +
                (cogs * ap_days / 365 - payables)
            )
            # Actually, for the iteration we use: CFO = NI + Depr - WC_Investment
            wc_investment = (
                (revenue * ar_days / 365 - receivables) +
                (cogs * inv_days / 365 - inventory) -
                (cogs * ap_days / 365 - payables)
            )
            cfo = net_income + depreciation - wc_investment

            capex = revenue * capex_pct
            cfi = -capex

            # Financing: determine debt issuance/repayment
            dividends = max(net_income, 0) * div_payout
            # Cash before financing
            cash_before_financing = cash + cfo + cfi - dividends

            # Debt sweep: repay debt if cash exceeds minimum, borrow if short
            if cash_before_financing > min_cash:
                debt_repayment = min(debt, cash_before_financing - min_cash)
                new_debt = debt - debt_repayment
                cff = -debt_repayment - dividends
            else:
                debt_issuance = min_cash - cash_before_financing
                new_debt = debt + debt_issuance
                cff = debt_issuance - dividends

            new_cash = cash + cfo + cfi + cff
            new_interest = new_debt * interest_rate_on_debt

            return new_interest, new_debt, net_income, cfo, cfi, cff, new_cash

        # Resolve the circular reference
        initial_interest_guess = debt * interest_rate_on_debt
        converged_interest = initial_interest_guess
        prev_interest = converged_interest

        for iteration in range(max_iter):
            converged_interest, new_debt, ni, cfo, cfi, cff, new_cash = \
                interest_debt_loop(prev_interest)

            if abs(converged_interest - prev_interest) < tolerance:
                break
            prev_interest = converged_interest

        # Final pass with converged interest
        final_interest, final_debt, ni, cfo, cfi, cff, final_cash = \
            interest_debt_loop(converged_interest)

        ebit = ebitda - depreciation
        pbt = ebit - final_interest
        tax = max(pbt, 0) * tax_rate

        # Store year results
        all_years.append({
            'Year': y + 1,
            # IS
            'Revenue': revenue,
            'COGS': cogs,
            'Gross Profit': gross_profit,
            'SG&A': sga,
            'EBITDA': ebitda,
            'Depreciation': depreciation,
            'EBIT': ebit,
            'Interest Expense': final_interest,
            'PBT': pbt,
            'Tax': tax,
            'Net Income': ni,
            # BS
            'Cash': final_cash,
            'Receivables': revenue * ar_days / 365,
            'Inventory': cogs * inv_days / 365,
            'Net PP&E': net_ppe + capex - depreciation,
            'Payables': cogs * ap_days / 365,
            'Total Debt': final_debt,
            'Shareholders Equity': equity + ni - max(ni, 0) * div_payout,
            # CF memo
            'CFO': cfo,
            'CFI': cfi,
            'CFF': cff,
            'Capex': capex,
            'Dividends': max(ni, 0) * div_payout,
            'Iterations': iteration + 1,
        })

        # Update starting balances for next year
        prev_revenue = revenue
        cash = final_cash
        receivables = all_years[-1]['Receivables']
        inventory = all_years[-1]['Inventory']
        payables = all_years[-1]['Payables']
        net_ppe = all_years[-1]['Net PP&E']
        debt = final_debt
        equity = all_years[-1]['Shareholders Equity']

    result = pd.DataFrame(all_years).set_index('Year')

    # Verify model integrity
    total_assets = (result['Cash'] + result['Receivables'] +
                    result['Inventory'] + result['Net PP&E'])
    total_le = (result['Payables'] + result['Total Debt'] +
                result['Shareholders Equity'])
    result['Balance_Check'] = total_assets - total_le

    bal_ok = result['Balance_Check'].abs().max()
    print(f"Max Balance Check: Rs. {bal_ok:.2f} Cr")
    print(f"Model Balanced: {'YES ✓' if bal_ok < 1.0 else 'NO ✗'}")

    fcff = result['EBIT'] * (1 - tax_rate) + result['Depreciation'] - result['Capex']
    # WC change
    wc_items = result['Receivables'] + result['Inventory'] - result['Payables']
    wc_prev = wc_items.shift(1)
    wc_prev.iloc[0] = last_year['Receivables'] + last_year['Inventory'] - last_year['Payables']
    fcff = fcff - (wc_items - wc_prev)
    result['FCFF'] = fcff

    return result


# --- Run the full integrated model ---
full_assumptions = {
    'Revenue_Growth': [0.14, 0.13, 0.12, 0.11, 0.10],
    'Gross_Margin_Pct': 43.0,
    'SGnA_Pct_of_Revenue': 0.175,
    'Tax_Rate': 0.25,
    'Capex_Pct_of_Revenue': 0.055,
    'Dividend_Payout_Ratio': 0.30,
    'Interest_Rate_on_Debt': 0.08,
    'Min_Cash_Balance': 500,
    'WC_Receivable_Days': 55,
    'WC_Inventory_Days': 75,
    'WC_Payable_Days': 50,
}

last_year_full = {
    'Revenue': 35500,
    'Cash': 2800,
    'Receivables': 3500,
    'Inventory': 4200,
    'Net_PPE': 6500,
    'Payables': 3600,
    'Short_Term_Debt': 500,
    'Long_Term_Debt': 1800,
    'Equity': 12500,
    'Depreciation_Rate': 0.03,
}

integrated_result = integrated_model_with_debt_schedule(
    last_year_full, full_assumptions)

print("\n=== COMPLETE INTEGRATED MODEL OUTPUT ===")
print(integrated_result.round(1))
print(f"\nAverage iterations to converge: {integrated_result['Iterations'].mean():.1f}")

9.6 The IntegratedModel Class: A Reusable Python Framework

Now we package everything into a single, reusable class. This is the forecasting engine you will use for your capstone project. Instantiate it with historical data and assumptions, call .run(), and get back the complete integrated forecast with FCFF ready for DCF valuation.

class IntegratedFinancialModel:
    """
    Complete three-statement integrated financial forecasting model.

    Usage:
        model = IntegratedFinancialModel(historical_data, assumptions)
        forecast = model.run(forecast_years=5)
        print(forecast.summary())
    """

    def __init__(self, historical, assumptions):
        """
        Parameters
        ----------
        historical : dict with keys —
            Revenue, COGS_Pct, SGnA_Pct, Depreciation_Rate,
            Tax_Rate, Cash, Receivables, Inventory, Net_PPE,
            Payables, Short_Term_Debt, Long_Term_Debt, Equity

        assumptions : dict with keys —
            Revenue_Growth (list or float), Gross_Margin_Pct,
            SGnA_Pct_of_Revenue, Capex_Pct_of_Revenue,
            Dividend_Payout_Ratio, Interest_Rate_on_Debt,
            Min_Cash_Balance, WC_Receivable_Days,
            WC_Inventory_Days, WC_Payable_Days, Tax_Rate
        """
        self.hist = historical
        self.assumptions = assumptions
        self.result = None

    def run(self, forecast_years=5, tolerance=0.01):
        """Execute the integrated forecast."""
        self.result = integrated_model_with_debt_schedule(
            self.hist, self.assumptions, forecast_years
        )
        return self.result

    def summary(self):
        """Return a concise summary DataFrame."""
        if self.result is None:
            raise ValueError("Model not run. Call .run() first.")

        df = self.result
        return pd.DataFrame({
            'Revenue': df['Revenue'].round(0),
            'Growth %': (df['Revenue'].pct_change() * 100).round(1),
            'EBITDA': df['EBITDA'].round(0),
            'EBITDA Margin %': (df['EBITDA'] / df['Revenue'] * 100).round(1),
            'EBIT': df['EBIT'].round(0),
            'Net Income': df['Net Income'].round(0),
            'FCFF': df['FCFF'].round(0),
            'FCF Margin %': (df['FCFF'] / df['Revenue'] * 100).round(1),
            'Total Debt': df['Total Debt'].round(0),
            'Cash': df['Cash'].round(0),
            'Iterations': df['Iterations'],
        })

    def plot_summary(self):
        """Plot key outputs: revenue, margins, FCFF, and debt."""
        if self.result is None:
            raise ValueError("Model not run. Call .run() first.")

        df = self.result
        fig, axes = plt.subplots(2, 2, figsize=(16, 11))

        # Revenue + EBITDA
        ax = axes[0, 0]
        x = df.index
        ax.bar(x - 0.15, df['Revenue'], 0.3, color='#6c8cff', label='Revenue')
        ax.bar(x + 0.15, df['EBITDA'], 0.3, color='#00c9a7', label='EBITDA')
        ax.set_title('Revenue & EBITDA Forecast', fontweight='bold')
        ax.legend()

        # Margins
        ax = axes[0, 1]
        ebitda_margin = df['EBITDA'] / df['Revenue'] * 100
        ebit_margin = df['EBIT'] / df['Revenue'] * 100
        ax.plot(x, ebitda_margin, 'o-', color='#00c9a7', linewidth=2, label='EBITDA Margin')
        ax.plot(x, ebit_margin, 's-', color='#6c8cff', linewidth=2, label='EBIT Margin')
        ax.set_title('Margin Forecast', fontweight='bold')
        ax.set_ylabel('%')
        ax.legend()

        # FCFF and components
        ax = axes[1, 0]
        ax.bar(x, df['FCFF'], color='#00c9a7', edgecolor='white', label='FCFF')
        ax.set_title('Free Cash Flow to Firm Forecast', fontweight='bold')
        ax.set_ylabel('Rs. Crore')
        ax.axhline(y=0, color='#e0556a', linestyle='--', alpha=0.5)

        # Debt and Cash
        ax = axes[1, 1]
        ax.plot(x, df['Total Debt'], 's-', color='#e0556a', linewidth=2, label='Total Debt')
        ax.plot(x, df['Cash'], 'o-', color='#00c9a7', linewidth=2, label='Cash')
        ax.fill_between(x, df['Cash'] - df['Total Debt'], alpha=0.15,
                         color='#6c8cff', label='Net Debt / (Cash)')
        ax.axhline(y=0, color='gray', linestyle='-', alpha=0.5)
        ax.set_title('Capital Structure Evolution', fontweight='bold')
        ax.set_ylabel('Rs. Crore')
        ax.legend()

        plt.suptitle('Integrated Financial Model — 5-Year Forecast',
                     fontsize=15, fontweight='bold', y=1.01)
        plt.tight_layout()
        plt.show()


# --- Usage ---
model = IntegratedFinancialModel(last_year_full, full_assumptions)
forecast = model.run(forecast_years=5)
print(model.summary())
model.plot_summary()

9.7 Model Validation: Ensuring Your Model Is Trustworthy

An integrated model must pass these tests before it can be used for valuation:

#	Test	How to Check	Pass Condition
1	Balance Sheet Balances	Total Assets == Total Liabilities + Equity	Difference < Rs. 0.01 Cr
2	Cash Flow Reconciliation	Beginning Cash + CFO + CFI + CFF == Ending Cash	Difference < Rs. 0.01 Cr
3	Circular Reference Converges	Monitor iterations; ensure they stay < max_iter	Converges in < 20 iterations
4	No Negative Cash (without debt draw)	Cash balance stays > min_cash or debt is drawn	Cash > 0 in all years
5	Reasonable Ratios	Check: Net Income < EBITDA, Capex > Depreciation (growing company), D/E within industry norms	No absurd values (e.g., 500% margin)
6	FCFF Consistency	FCFF calculated from IS/BS vs FCFF from CFO − Capex	Difference < Rs. 0.01 Cr

def validate_model(result, last_year):
    """Run all validation checks on the integrated model output."""
    print("=" * 50)
    print("  MODEL VALIDATION REPORT")
    print("=" * 50)

    checks = []

    # 1. Balance Sheet
    bs_ok = result['Balance_Check'].abs().max() < 0.1
    checks.append(('Balance Sheet Balances', bs_ok,
                   f"Max error: Rs. {result['Balance_Check'].abs().max():.2f} Cr"))

    # 2. Cash Reconciliation
    cash_change = result['CFO'] + result['CFI'] + result['CFF']
    implied_cash = last_year['Cash']
    cash_errors = []
    for idx in result.index:
        implied_cash = implied_cash + cash_change.loc[idx]
        actual_cash = result.loc[idx, 'Cash']
        cash_errors.append(abs(implied_cash - actual_cash))

    cash_ok = max(cash_errors) < 0.1
    checks.append(('Cash Reconciliation', cash_ok,
                   f"Max error: Rs. {max(cash_errors):.2f} Cr"))

    # 3. Convergence
    avg_iter = result['Iterations'].mean()
    conv_ok = avg_iter < 20
    checks.append(('Convergence', conv_ok,
                   f"Avg iterations: {avg_iter:.1f}"))

    # 4. Minimum Cash
    min_cash_ok = (result['Cash'] > 0).all()
    checks.append(('Positive Cash Balance', min_cash_ok,
                   f"Min cash: Rs. {result['Cash'].min():.0f} Cr"))

    # 5. Ratio sanity
    ebitda_margin = result['EBITDA'] / result['Revenue'] * 100
    margin_ok = ebitda_margin.between(5, 80).all()
    checks.append(('Reasonable Margins', margin_ok,
                   f"EBITDA margin range: {ebitda_margin.min():.0f}% – {ebitda_margin.max():.0f}%"))

    # 6. FCFF consistency
    fcff_method1 = result['FCFF']
    fcff_method2 = result['CFO'] + result['CFI']
    fcff_diff = (fcff_method1 - fcff_method2).abs().max()
    fcff_ok = fcff_diff < 0.1
    checks.append(('FCFF Consistency', fcff_ok,
                   f"Max difference: Rs. {fcff_diff:.2f} Cr"))

    for name, passed, detail in checks:
        status = '✓ PASS' if passed else '✗ FAIL'
        print(f"  {status} | {name}: {detail}")

    all_ok = all(c[1] for c in checks)
    print(f"\n  OVERALL: {'ALL CHECKS PASSED — Model is trustworthy' if all_ok else 'FAILURES DETECTED — Fix before using for valuation'}")

    return all_ok


validate_model(integrated_result, last_year_full)

9.8 From Forecast to FCFF: The Bridge to DCF Valuation

The ultimate purpose of the integrated model is to produce Free Cash Flow to Firm (FCFF) — the cash available to all capital providers after the company has made the investments needed to sustain and grow the business. FCFF is the numerator in every DCF valuation.

8.8.1 The FCFF Formula

FCFF = EBIT × (1 − Tax Rate) + Depreciation − Capex − Δ Working Capital

Let us verify that each component flows from the integrated model correctly:

Component	Source in Integrated Model	Notes
EBIT × (1 − T)	Income Statement → EBIT, Tax Rate	Also called NOPAT. The tax rate should be the marginal rate (25% for Indian companies under new regime).
+ Depreciation	Income Statement → Depreciation	Non-cash charge — added back. Must be consistent with the capex schedule.
− Capex	Cash Flow Statement → Capex	Includes both maintenance and growth capex. Should exceed depreciation for a growing company.
− Δ Working Capital	Balance Sheet → Change in (Receivables + Inventory − Payables)	An increase in working capital consumes cash. Fast-growing companies often have large WC outflows.

def extract_fcff(result):
    """Extract FCFF and its components from the integrated model output."""
    fcff_detail = pd.DataFrame({
        'EBIT': result['EBIT'],
        'Tax on EBIT': result['EBIT'] * 0.25,
        'NOPAT': result['EBIT'] * (1 - 0.25),
        'Depreciation': result['Depreciation'],
        'Capex': -result['Capex'],
        'FCFF': result['FCFF'],
    })
    return fcff_detail

print("\n=== FCFF COMPONENTS ===")
fcff_detail = extract_fcff(integrated_result)
print(fcff_detail.round(1))

# FCFF as % of Revenue
fcff_margin = integrated_result['FCFF'] / integrated_result['Revenue'] * 100
print(f"\nFCFF Margin: {fcff_margin.round(1).tolist()}%")
print(f"→ This is the cash conversion rate of each rupee of revenue.")
print(f"→ Higher = more cash generated per rupee of sales = higher valuation.")

🌎

Real World: A common mistake is to forecast revenue growth and margins carefully but then assume working capital and capex grow at the same rate as revenue without checking. Working capital can consume 20–40% of EBITDA in fast-growing companies. If your integrated model shows FCFF significantly below net income, working capital is likely the culprit — and that is normal for growing businesses. The integrated model reveals this automatically; a standalone income statement forecast hides it.

Hands-On Project: Build Your Capstone Integrated Model

Build the complete integrated three-statement model for your capstone company using the IntegratedFinancialModel class. Extract FCFF for all 5 forecast years. This model is the direct input to your DCF valuation in Chapter 14.

Steps

Gather historical data for your capstone company: extract the latest year's Revenue, COGS%, SG&A%, Cash, Receivables, Inventory, Net PP&E, Payables, Debt, and Equity from yfinance or the annual report.
Define your assumptions using the output from Chapters 7 and 8: revenue growth path, margin assumptions, capex intensity, working capital days, dividend policy, and interest rate.
Instantiate and run the model: model = IntegratedFinancialModel(hist, assumptions); forecast = model.run(5)
Validate the model using all 6 checks from Section 9.7. If any check fails, debug and fix.
Extract FCFF for all 5 years. Verify the FCFF margin makes sense (typically 5–20% of revenue for mature companies; can be negative for high-growth).
Run scenario sensitivity: Change one assumption at a time (±20%) and observe the impact on Year 5 FCFF. Create a mini-tornado chart of FCFF sensitivity.
Generate a summary table showing: Year 1–5 Revenue, Revenue Growth %, EBITDA Margin %, EBIT, Net Income, Capex, Change in WC, and FCFF.

View Solution / Walkthrough

Complete Capstone Model Pipeline

# ================================================================
# CAPSTONE INTEGRATED MODEL — Complete Pipeline
# ================================================================

import yfinance as yf
import time

def build_capstone_model(ticker_symbol, forecast_years=5):
    """
    End-to-end: fetch data, build assumptions, run integrated model,
    validate, and extract FCFF.
    """
    print(f"Building capstone model for {ticker_symbol}...\n")

    # --- Step 1: Fetch Historical Data ---
    ticker = yf.Ticker(ticker_symbol)
    info = ticker.info
    is_df = ticker.financials.sort_index(axis=1)
    bs_df = ticker.balance_sheet.sort_index(axis=1)
    cf_df = ticker.cashflow.sort_index(axis=1)

    def safe_get(df, candidates):
        for c in ([candidates] if isinstance(candidates, str) else candidates):
            if c in df.index:
                return df.loc[c]
        return None

    # Latest year
    rev_series = safe_get(is_df, ['Total Revenue', 'Revenue'])
    cogs_series = safe_get(is_df, ['Cost Of Goods Sold', 'Cost of Revenue'])
    sga_series = safe_get(is_df, ['Selling General And Administration'])
    d_a_series = safe_get(is_df, ['Depreciation And Amortization'])
    ebit_series = safe_get(is_df, ['Operating Income', 'EBIT'])
    ni_series = safe_get(is_df, ['Net Income'])

    latest_yr = rev_series.index[0]  # Most recent year

    rev = rev_series[latest_yr] / 1e7
    cogs = cogs_series[latest_yr] / 1e7 if cogs_series is not None else rev * 0.55
    sga = sga_series[latest_yr] / 1e7 if sga_series is not None else rev * 0.18
    depr = d_a_series[latest_yr] / 1e7 if d_a_series is not None else rev * 0.03

    cash = safe_get(bs_df, ['Cash And Cash Equivalents'])[latest_yr] / 1e7
    ar = safe_get(bs_df, ['Accounts Receivable', 'Net Receivables'])
    ar = ar[latest_yr] / 1e7 if ar is not None else rev * 0.15
    inv = safe_get(bs_df, ['Inventory'])[latest_yr] / 1e7
    nppe = safe_get(bs_df, ['Net PPE'])[latest_yr] / 1e7
    ap = safe_get(bs_df, ['Accounts Payable'])[latest_yr] / 1e7
    st_debt = safe_get(bs_df, ['Short Term Debt'])
    st_debt = st_debt[latest_yr] / 1e7 if st_debt is not None else 500
    lt_debt = safe_get(bs_df, ['Long Term Debt'])
    lt_debt = lt_debt[latest_yr] / 1e7 if lt_debt is not None else 1500
    equity = safe_get(bs_df, ['Total Equity Gross Minority Interest',
                               'Stockholders Equity'])[latest_yr] / 1e7

    # Compute historical ratios
    cogs_pct = cogs / rev
    sga_pct = sga / rev
    depr_rate = depr / rev
    gross_margin = (1 - cogs_pct) * 100

    hist = {
        'Revenue': rev, 'COGS_Pct': cogs_pct, 'SGnA_Pct': sga_pct,
        'Depreciation_Rate': depr_rate, 'Tax_Rate': 0.25,
        'Cash': cash, 'Receivables': ar, 'Inventory': inv, 'Net_PPE': nppe,
        'Payables': ap, 'Short_Term_Debt': st_debt, 'Long_Term_Debt': lt_debt,
        'Equity': equity
    }

    print(f"Company: {info.get('longName', ticker_symbol)}")
    print(f"Latest Revenue: Rs. {rev:.0f} Cr")
    print(f"Gross Margin: {gross_margin:.1f}%")
    print(f"Cash: Rs. {cash:.0f} Cr | Debt: Rs. {st_debt + lt_debt:.0f} Cr")

    # --- Step 2: Define Assumptions ---
    # Use 3-year or 5-year historical averages + your forecast judgment
    assumptions = {
        'Revenue_Growth': [0.14, 0.13, 0.12, 0.11, 0.10],
        'Gross_Margin_Pct': max(gross_margin, 30),
        'SGnA_Pct_of_Revenue': sga_pct,
        'Tax_Rate': 0.25,
        'Capex_Pct_of_Revenue': 0.06,
        'Dividend_Payout_Ratio': 0.30,
        'Interest_Rate_on_Debt': 0.08,
        'Min_Cash_Balance': rev * 0.015,
        'WC_Receivable_Days': max((ar / rev) * 365, 30),
        'WC_Inventory_Days': max((inv / (cogs)) * 365, 30),
        'WC_Payable_Days': max((ap / cogs) * 365, 20),
    }

    # --- Step 3: Run Model ---
    model = IntegratedFinancialModel(hist, assumptions)
    forecast = model.run(forecast_years=forecast_years)

    # --- Step 4: Validate ---
    passed = validate_model(forecast, hist)

    # --- Step 5: Extract FCFF ---
    if passed:
        print("\n=== FCFF FOR DCF VALUATION ===")
        for idx, row in forecast.iterrows():
            print(f"  Year {idx}: FCFF = Rs. {row['FCFF']:.0f} Cr "
                  f"({row['FCFF']/row['Revenue']*100:.1f}% of Revenue)")

    # --- Step 6: FCFF Sensitivity ---
    print("\n=== FCFF SENSITIVITY (Year 5 FCFF, Rs. Cr) ===")
    base_y5_fcff = forecast.loc[forecast_years, 'FCFF']

    sensitivities = {}
    for param, delta in [('Revenue_Growth', 0.01), ('Gross_Margin_Pct', 2.0),
                          ('Capex_Pct_of_Revenue', 0.01), ('WC_Receivable_Days', 10)]:
        alt_assumptions = assumptions.copy()
        if param == 'Revenue_Growth':
            alt_assumptions[param] = [g + delta for g in assumptions[param]]
        else:
            alt_assumptions[param] = assumptions[param] + delta

        alt_model = IntegratedFinancialModel(hist, alt_assumptions)
        alt_forecast = alt_model.run(forecast_years=forecast_years)
        alt_y5_fcff = alt_forecast.loc[forecast_years, 'FCFF']
        sensitivities[param] = alt_y5_fcff - base_y5_fcff

    for param, impact in sorted(sensitivities.items(), key=lambda x: abs(x[1]), reverse=True):
        direction = '↑' if impact > 0 else '↓'
        print(f"  {param}: {direction} Rs. {abs(impact):.0f} Cr")

    return {
        'historical': hist,
        'assumptions': assumptions,
        'model': model,
        'forecast': forecast,
        'validation_passed': passed,
        'fcff': forecast[['FCFF']],
    }


# Run for your capstone company
result = build_capstone_model("ASIANPAINT.NS")

Key Takeaways

An integrated model links all three statements. Revenue drives the IS, net income drives equity on the BS, changes in the BS drive the CF statement, and the CF statement updates the cash on the BS — closing the loop.

Circular references are resolved through iterative convergence. Python's explicit iteration loop gives you more control than Excel's black-box iterative calculation.

The cash flow statement is derived, not forecast. If your BS balances and your IS is complete, the CF statement is determined. A reconciled CF statement is the gold standard of modeling integrity.

Validate, validate, validate. Six checks: BS balance, cash reconciliation, convergence, positive cash, reasonable ratios, FCFF consistency. Never use a model for valuation that fails any of these.

The IntegratedFinancialModel class is your capstone engine. It produces FCFF for all forecast years — the direct input to your DCF valuation in Chapter 14. Master this model; it is the core of the course.