Dynamic Cash Flow Statement Integration

The Bridge Between Profit and Cash

A key concept in finance is that profit is not the same as cash. A company can report high net income due to accrual accounting principles but still face a cash shortage. The statement of cash flows bridges this gap. It starts with the accrual-based Net Income and adjusts it to show the actual cash generated or used.

This reconciliation is typically done using the indirect method, which is what we will focus on. It's the most common approach because it directly uses the income statement and balance sheets, which are readily available. The statement is organized into three sections:

1. Cash Flow from Operating Activities (CFO): Cash generated or used by the core business operations.
2. Cash Flow from Investing Activities (CFI): Cash used for or generated from long-term assets (like buying equipment) or other investments.
3. Cash Flow from Financing Activities (CFF): Cash raised from or paid to owners and creditors (like taking on debt or paying dividends).

Conceptual Walkthrough: The Mechanics of the Cash Flow Statement

Before we dive into Python, it's essential to understand the logic in a more visual environment like a spreadsheet. The following video from Kenji Explains provides an exceptionally clear step-by-step construction of a cash flow statement from a balance sheet and income statement.

An Intuitive Shortcut to Understanding Cash Flow

The logic of which signs to flip can sometimes feel confusing. There's an elegant and intuitive way to think about it that reveals the underlying accounting equation at work. The accounting equation states Assets = Liabilities + Equity. This must be true for every period. Therefore, the change from one period to the next must also balance:

$\Delta \text{Assets}=\Delta \text{Liabilities}+\Delta \text{Equity}$

We can split ΔAssets into ΔCash and ΔNon-Cash Assets:

$\Delta \text{Cash}+\Delta \text{Non-Cash Assets}=\Delta \text{Liabilities}+\Delta \text{Equity}$

Rearranging to solve for ΔCash, we get:

$\Delta \text{Cash}=(\Delta \text{Liabilities}+\Delta \text{Equity})-\Delta \text{Non-Cash Assets}$

This powerful identity means that the change in cash is simply the sum of the changes in all other balance sheet accounts, with the sign flipped for assets. The video from "Your CFO Guy" demonstrates this brilliantly.

Implementing the Cash Flow Statement in Python

Now, let's apply this logic to our Python model. We will continue the script from our last lesson, creating a new projected_cfs DataFrame and using the projected income statement and balance sheet to populate it.

The Medium article "How I Rebuilt 10 Years of Finance with Pandas" provides a concise template for rebuilding the cash flow from operations, which we can adapt and extend for our full statement.

Let's integrate this into our model. We will calculate the change in all relevant balance sheet accounts and then use those changes to build the three sections of the cash flow statement.

import pandas as pd
import numpy as np

# --- Code from previous lesson ---
# 1. Historical Data and Revenue Forecast
years = ['2021A', '2022A', '2023A', '2024F', '2025F', '2026F']
projected_is = pd.DataFrame(index=['Sales'], columns=years)
projected_is.loc['Sales'] = [150.0, 170.0, 195.0, 214.5, 236.0, 259.6]

# 2. Income Statement Projections
assumptions = {
    'cogs_margin': 0.60, 'sga_margin': 0.15, 'depr_margin': 0.05, 
    'tax_rate': 0.22, 'capex_margin': 0.07, 'dividend_payout': 0.30,
    'ar_days': 45, 'inv_days': 60, 'ap_days': 30
}
projected_is.loc['COGS'] = projected_is.loc['Sales'] * -assumptions['cogs_margin']
projected_is.loc['Gross Profit'] = projected_is.loc['Sales'] + projected_is.loc['COGS']
projected_is.loc['SG&A'] = projected_is.loc['Sales'] * -assumptions['sga_margin']
projected_is.loc['Depreciation'] = projected_is.loc['Sales'] * -assumptions['depr_margin']
projected_is.loc['EBIT'] = projected_is.loc['Gross Profit'] + projected_is.loc['SG&A'] + projected_is.loc['Depreciation']
projected_is.loc['Interest Expense'] = 0
projected_is.loc['EBT'] = projected_is.loc['EBIT'] + projected_is.loc['Interest Expense']
projected_is.loc['Tax'] = -projected_is.loc['EBT'].apply(lambda x: x * assumptions['tax_rate'] if x > 0 else 0)
projected_is.loc['Net Income'] = projected_is.loc['EBT'] + projected_is.loc['Tax']

# 3. Balance Sheet Projections (Incomplete without Cash)
projected_bs = pd.DataFrame(columns=years)
projected_bs.loc['Accounts Receivable', '2023A'] = 25.0
projected_bs.loc['Inventory', '2023A'] = 20.0
projected_bs.loc['PP&E', '2023A'] = 100.0
projected_bs.loc['Accounts Payable', '2023A'] = 15.0
projected_bs.loc['Retained Earnings', '2023A'] = 80.0
# Add initial cash for the starting point of our cash flow calculation
projected_bs.loc['Cash', '2023A'] = 30.0 

projected_bs.loc['Accounts Receivable'] = (assumptions['ar_days']/365) * projected_is.loc['Sales']
projected_bs.loc['Inventory'] = (assumptions['inv_days']/365) * -projected_is.loc['COGS']
projected_bs.loc['Accounts Payable'] = (assumptions['ap_days']/365) * -projected_is.loc['COGS']

capex = projected_is.loc['Sales'] * assumptions['capex_margin']
depreciation_val = -projected_is.loc['Depreciation']
dividends = projected_is.loc['Net Income'] * assumptions['dividend_payout']

for year_idx in range(3, len(years)):
    prev_year = years[year_idx - 1]
    curr_year = years[year_idx]
    
    beg_ppe = projected_bs.loc['PP&E', prev_year]
    projected_bs.loc['PP&E', curr_year] = beg_ppe + capex[curr_year] - depreciation_val[curr_year]
    
    beg_re = projected_bs.loc['Retained Earnings', prev_year]
    projected_bs.loc['Retained Earnings', curr_year] = beg_re + projected_is.loc['Net Income', curr_year] - dividends[curr_year]
# --- End of previous lesson's code ---


# 4. Build the Cash Flow Statement
projected_cfs = pd.DataFrame(columns=years[3:]) # Forecast years only

# Calculate changes in balance sheet accounts. `diff(axis=1)` calculates change from previous column.
bs_changes = projected_bs.diff(axis=1)

# Cash Flow from Operations (CFO)
cfo = pd.Series(index=projected_cfs.columns)
cfo = projected_is.loc['Net Income']
cfo += depreciation_val # Add back non-cash depreciation
# Adjust for changes in working capital (flip sign for assets)
cfo -= bs_changes['Accounts Receivable']
cfo -= bs_changes['Inventory']
cfo += bs_changes['Accounts Payable']
projected_cfs.loc['CFO'] = cfo

# Cash Flow from Investing (CFI)
# In our simple model, this is just capital expenditures. Capex is a cash outflow, so it's negative.
projected_cfs.loc['CFI'] = -capex

# Cash Flow from Financing (CFF)
# In our simple model, this is just dividends. Dividends are a cash outflow.
# A full model would include changes in debt and equity.
projected_cfs.loc['CFF'] = -dividends

# Calculate Net Change in Cash and Ending Cash Balance
projected_cfs.loc['Net Change in Cash'] = projected_cfs.loc[['CFO', 'CFI', 'CFF']].sum()

# 5. Link Cash Flow back to the Balance Sheet
for year_idx in range(3, len(years)):
    prev_year = years[year_idx - 1]
    curr_year = years[year_idx]
    
    beginning_cash = projected_bs.loc['Cash', prev_year]
    net_change_in_cash = projected_cfs.loc['Net Change in Cash', curr_year]
    projected_bs.loc['Cash', curr_year] = beginning_cash + net_change_in_cash

print("\n--- Projected Cash Flow Statement ---")
print(projected_cfs.round(1))

print("\n--- Completed Projected Balance Sheet ---")
# Let's add totals to see if it balances
projected_bs.loc['Total Assets'] = projected_bs.loc[['Cash', 'Accounts Receivable', 'Inventory', 'PP&E']].sum()
# For simplicity, we assume no other liabilities or equity besides AP and RE
projected_bs.loc['Total Liabilities & Equity'] = projected_bs.loc[['Accounts Payable', 'Retained Earnings']].sum()
projected_bs.loc['Balance Check'] = projected_bs.loc['Total Assets'] - projected_bs.loc['Total Liabilities & Equity']
print(projected_bs.round(1))

The crucial steps here are #4 and #5. We first construct the cash flow statement by starting with Net Income and adjusting for non-cash items and changes in balance sheet accounts. Then, we calculate the Ending Cash for each period and use that value to populate the Cash line on our balance sheet. The Balance Check line, which should now be zero (or very close to it), confirms that our model is linked correctly.

Conclusion

Congratulations! You have now built a fully linked, three-statement financial model in Python. You've seen how the cash flow statement serves as the critical reconciler between the income statement's profit and the balance sheet's actual cash position. This dynamic linkage is the foundation of all serious financial modeling.

Key Takeaways:

• The CFS Reconciles Profit and Cash: It starts with Net Income and makes adjustments to arrive at the net change in cash.
• The Indirect Method is Key: We build the CFO by starting with Net Income, adding back non-cash expenses (Depreciation), and adjusting for changes in working capital accounts.
• Sign Conventions Matter: An increase in an asset is a use of cash (negative flow), while an increase in a liability or equity is a source of cash (positive flow).
• The Final Link: The Ending Cash calculated by the CFS becomes the Cash value on the balance sheet for that period, completing the model's circular logic.

You now have a working, albeit simplified, model. In our next lesson, we will formalize the Balance Check and implement a more robust set of validation checks to ensure our model is always error-free, a practice essential for professional-grade financial analysis.