---
title: "Cash Flow Analytics: Financial Interpretation and Decisions"
---
```{python}
#| label: python-setup-39-cash-flow-financial
#| include: false
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta
```
::: {.callout-note icon="false"}
## 📋 Learning Objectives
- Compute and interpret the Cash Conversion Cycle (CCC) and its components (DSO, DIO, DPO)
- Calculate key cash flow ratios that assess financial health and solvency
- Build and forecast 13-week rolling cash flow statements for treasury decisions
- Stress-test cash flows under macroeconomic scenarios (FX, commodity prices, revenue)
- Integrate cash flow analysis with working capital optimisation
- Develop a complete CFO dashboard for a Nigerian manufacturing firm
:::
## Working Capital and the Cash Conversion Cycle
In 2022, a profitable food manufacturing company in Onitsha nearly went bankrupt. On paper, the company was doing well: revenue was growing at 18% per year, and the profit-and-loss statement showed healthy margins. But the managing director was sitting in her office with a very different problem: she could not make payroll. The company's bank account was almost empty despite millions of naira in "profit."
What happened? Her company had sold a large order of processed foods to three major supermarket chains. The supermarkets, as large buyers often do in Nigeria, insisted on 90-day payment terms — they would pay 90 days after delivery. Meanwhile, the company had already paid its suppliers for the raw materials (tomatoes, palm oil, packaging) upfront, or within 30 days. And the finished goods sat in the supermarkets' warehouses for another 30 days before they cleared their shelves and triggered payment.
Result: the company shipped goods in January, paid its suppliers in January, the goods sat on shelves until March, and did not receive payment until April — four months after the cash went out. Four months of operations, workers' salaries, electricity bills, and transport costs all had to be funded from somewhere. The company had to borrow at 24% annual interest to bridge the gap. The "profit" on paper was being consumed by the financing cost of waiting to be paid.
This gap — the time between spending money and receiving it back — is called the **Cash Conversion Cycle (CCC)**, and it is one of the most important concepts in financial management. Understanding it allows a business to manage its cash position proactively rather than scrambling for liquidity at the last moment.
Working capital is the lifeblood of operations. A company can be profitable on paper (accrual accounting) yet insolvent if cash is trapped in inventory or uncollected receivables. The Cash Conversion Cycle (CCC) quantifies precisely how long cash is tied up in the operations cycle — from paying suppliers to collecting from customers.
### Definition and Components
The Cash Conversion Cycle is:
$$\text{CCC} = \text{DSO} + \text{DIO} - \text{DPO}$$
where:
- **Days Sales Outstanding (DSO)**: Average days to collect cash from customers. DSO = (Accounts Receivable / Revenue) × 365.
- **Days Inventory Outstanding (DIO)**: Average days inventory sits before sale. DIO = (Inventory / COGS) × 365.
- **Days Payables Outstanding (DPO)**: Average days to pay suppliers. DPO = (Accounts Payable / COGS) × 365.
**Interpretation**: A Nigerian manufacturing firm with DSO = 45, DIO = 60, DPO = 30 has CCC = 45 + 60 - 30 = 75 days. This means cash is tied up for 75 days: the firm must finance 75 days of operations before cash returns.
### Economic Impact of CCC
A shorter CCC improves cash generation and reduces the need for external financing:
**Example: Food Processing Manufacturer**
| Metric | Current | Optimised |
|:---|---:|---:|
| DSO | 60 | 45 |
| DIO | 90 | 70 |
| DPO | 30 | 40 |
| CCC | 120 | 75 |
| Annual Revenue | ₦1B | ₦1B |
| Daily Revenue | ₦2.74M | ₦2.74M |
| Cash Tied Up | ₦328.8M | ₦205.5M |
| **Benefit** | — | **₦123.3M freed** |
Reducing CCC by 45 days frees ₦123.3M of cash—equivalent to a ₦123.3M interest-free loan. At 12% cost of capital, this is worth ₦14.8M annually.
### Nigerian Context: Payment Cycles
Nigeria's business environment has distinctive payment patterns:
**Long B2B Payment Cycles**: Government and large corporates often pay 60-90 days after invoice. This inflates DSO beyond developed-market norms.
**Inventory Pressures**: Import-dependent firms (FMCG, manufacturing) hold large inventories due to port delays, logistics unreliability, and demand volatility. DIO can exceed 100 days.
**Supply-Side Power Dynamics**: Small suppliers with weak bargaining power often receive 30-day terms, while large buyers extract 60-90 days. DPO is often constrained below optimal.
::: {.callout-note icon="false"}
## 📘 Theory
The Cash Conversion Cycle is a key metric of working capital efficiency. Shorter CCC means faster cash generation, lower financing needs, and more resilience to shocks. For many businesses, improving CCC is as valuable as increasing revenue.
**Key insight**: A 10-day CCC reduction for a ₦1B revenue firm frees approximately ₦27.4M of cash—a one-time benefit equivalent to 2.74% of revenue.
:::
::: {.callout-tip icon="false"}
## 🔑 Key Formula
$$\text{CCC} = \text{DSO} + \text{DIO} - \text{DPO} = \frac{AR}{Revenue} \times 365 + \frac{Inventory}{COGS} \times 365 - \frac{AP}{COGS} \times 365$$
**Cash tied up** (approximately):
$$\text{Cash} = \text{CCC} \times \frac{\text{Daily Revenue}}{365}$$
**One-day CCC improvement = freed cash**:
$$\Delta \text{Cash} = \frac{\text{Annual Revenue}}{365} \text{ (for 1-day CCC reduction)}$$
:::
::: {.callout-caution icon="false"}
## 📝 Section 39.1 Review Questions
1. A telecom company has DSO = 5 days (post-paid subscribers pay upfront), DIO = 0 (no inventory), DPO = 30 days. What is the CCC, and what does it mean?
2. A Nigerian food distributor reduced DSO from 60 to 45 days by offering 2% discount for 10-day payment. Annual revenue is ₦500M. Calculate the cash freed and break-even discount rate.
3. If a supplier offers "2/10 Net 30" terms (2% discount if paid in 10 days, net amount due in 30), should you take it, assuming 15% cost of capital?
:::
## Cash Flow Ratios and Health Metrics
Beyond the CCC, several ratios assess cash generation and financial health.
### Key Cash Flow Ratios
**1. Operating Cash Flow (OCF) Ratio**
$$\text{OCF Ratio} = \frac{\text{Operating Cash Flow}}{\text{Current Liabilities}}$$
Measures how much operating cash generation can cover short-term obligations. Healthy firms have OCF ratio > 1.0.
**2. Cash Conversion Ratio**
$$\text{Cash Conversion} = \frac{\text{Operating Cash Flow}}{\text{Net Income}}$$
Measures the quality of earnings. A ratio < 1 means accrual profits are not being realised in cash; a red flag.
**3. Free Cash Flow (FCF)**
$$\text{FCF} = \text{Operating Cash Flow} - \text{Capital Expenditure}$$
The cash available for debt repayment, dividends, and growth after maintaining/expanding the asset base.
**4. Cash Interest Coverage**
$$\text{Cash Interest Coverage} = \frac{\text{Operating Cash Flow}}{\text{Interest Expense}}$$
Directly measures the firm's ability to service debt from operations. Must exceed 2.0x for safety.
**5. Cash Runway**
For early-stage or high-burn companies:
$$\text{Runway} = \frac{\text{Cash Balance}}{\text{Monthly Burn Rate}}$$
Months of operations sustainable with current cash, assuming no additional funding.
### Code: Cash Flow Dashboard for Nigerian Manufacturer
::: {.panel-tabset}
## R
```{r}
#| label: cash-metrics-r
#| eval: true
library(tidyverse)
# Synthetic financial data: Nigerian food manufacturer, 3 years (2022-2024)
# Quarterly data
financials <- tribble(
~period, ~revenue, ~cogs, ~ar, ~inventory, ~ap, ~ocf, ~capex, ~net_income, ~interest,
"Q1 2022", 50e6, 30e6, 12e6, 8e6, 9e6, 8e6, 2e6, 4e6, 1.5e6,
"Q2 2022", 52e6, 31e6, 13e6, 8.5e6, 9.5e6, 9e6, 2.5e6, 4.2e6, 1.5e6,
"Q3 2022", 51e6, 30.5e6, 12.5e6, 8.2e6, 9.2e6, 8.5e6, 2e6, 4e6, 1.5e6,
"Q4 2022", 55e6, 33e6, 14e6, 9e6, 10e6, 9.5e6, 3e6, 4.8e6, 1.6e6,
"Q1 2023", 58e6, 35e6, 15e6, 10e6, 11e6, 10e6, 2.5e6, 5.2e6, 1.8e6,
"Q2 2023", 60e6, 36e6, 15.5e6, 10.5e6, 11.5e6, 10.5e6, 2.5e6, 5.4e6, 1.8e6,
"Q3 2023", 62e6, 37e6, 16e6, 11e6, 12e6, 11e6, 3e6, 5.6e6, 1.8e6,
"Q4 2023", 65e6, 39e6, 17e6, 11.5e6, 12.5e6, 11.5e6, 3.5e6, 5.8e6, 2e6,
"Q1 2024", 68e6, 41e6, 18e6, 12e6, 13e6, 12e6, 3e6, 6e6, 2.2e6,
"Q2 2024", 70e6, 42e6, 18.5e6, 12.5e6, 13.5e6, 12.5e6, 3.5e6, 6.2e6, 2.2e6,
"Q3 2024", 72e6, 43e6, 19e6, 13e6, 14e6, 13e6, 3e6, 6.4e6, 2.3e6,
"Q4 2024", 75e6, 45e6, 20e6, 13.5e6, 14.5e6, 13.5e6, 4e6, 6.8e6, 2.4e6
)
# Calculate metrics
financials <- financials |>
mutate(
# Working capital components
dso = (ar / revenue) * 365,
dio = (inventory / cogs) * 365,
dpo = (ap / cogs) * 365,
ccc = dso + dio - dpo,
# Cash flow ratios
ocf_ratio = ocf / (revenue * 0.2), # Assume current liabilities = 20% revenue
cash_conversion = ocf / net_income,
fcf = ocf - capex,
cash_interest_coverage = ocf / interest,
# Return metrics
ocf_margin = ocf / revenue,
fcf_margin = fcf / revenue
)
# Display key metrics over time
key_metrics <- financials |>
select(period, dso, dio, dpo, ccc, cash_conversion, fcf, cash_interest_coverage) |>
arrange(match(period, financials$period))
print("Working Capital and Cash Metrics (Quarterly):")
print(key_metrics)
# Trends
trend_summary <- financials |>
summarise(
avg_dso = mean(dso),
avg_dio = mean(dio),
avg_dpo = mean(dpo),
avg_ccc = mean(ccc),
avg_cash_conversion = mean(cash_conversion),
avg_cash_interest_cov = mean(cash_interest_coverage),
avg_fcf = mean(fcf)
)
print("\nAverage Metrics (12 quarters):")
print(trend_summary)
# Health assessment
latest <- financials |> slice_tail(n = 1)
cat(sprintf("Latest Quarter (Q4 2024) Assessment:\n"))
cat(sprintf("CCC: %.0f days (lower is better)\n", latest$ccc))
cat(sprintf("Cash Conversion: %.2f (closer to 1.0 is better)\n", latest$cash_conversion))
cat(sprintf("Cash Interest Coverage: %.1fx (must exceed 2.0x)\n", latest$cash_interest_coverage))
cat(sprintf("Free Cash Flow: ₦%.1fM (positive is healthy)\n", latest$fcf / 1e6))
```
## Python
```{python}
#| label: py-cash-metrics
import pandas as pd
financials = pd.DataFrame({
'period': ["Q1 2022", "Q2 2022", "Q3 2022", "Q4 2022",
"Q1 2023", "Q2 2023", "Q3 2023", "Q4 2023",
"Q1 2024", "Q2 2024", "Q3 2024", "Q4 2024"],
'revenue': [50, 52, 51, 55, 58, 60, 62, 65, 68, 70, 72, 75],
'cogs': [30, 31, 30.5, 33, 35, 36, 37, 39, 41, 42, 43, 45],
'ar': [12, 13, 12.5, 14, 15, 15.5, 16, 17, 18, 18.5, 19, 20],
'inventory': [8, 8.5, 8.2, 9, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5],
'ap': [9, 9.5, 9.2, 10, 11, 11.5, 12, 12.5, 13, 13.5, 14, 14.5],
'ocf': [8, 9, 8.5, 9.5, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5],
'capex': [2, 2.5, 2, 3, 2.5, 2.5, 3, 3.5, 3, 3.5, 3, 4],
'net_income': [4, 4.2, 4, 4.8, 5.2, 5.4, 5.6, 5.8, 6, 6.2, 6.4, 6.8],
'interest': [1.5, 1.5, 1.5, 1.6, 1.8, 1.8, 1.8, 2, 2.2, 2.2, 2.3, 2.4]
})
# Multiply by 1M for actual values (keeping in millions for readability)
# Calculate metrics
financials['dso'] = (financials['ar'] / financials['revenue']) * 365
financials['dio'] = (financials['inventory'] / financials['cogs']) * 365
financials['dpo'] = (financials['ap'] / financials['cogs']) * 365
financials['ccc'] = financials['dso'] + financials['dio'] - financials['dpo']
financials['cash_conversion'] = financials['ocf'] / financials['net_income']
financials['fcf'] = financials['ocf'] - financials['capex']
financials['cash_interest_coverage'] = financials['ocf'] / financials['interest']
financials['ocf_margin'] = financials['ocf'] / financials['revenue']
print("Working Capital and Cash Metrics (Quarterly):")
print(financials[['period', 'dso', 'dio', 'dpo', 'ccc', 'cash_conversion', 'fcf', 'cash_interest_coverage']].round(2))
print("\nAverage Metrics:")
summary = pd.DataFrame({
'Metric': ['Avg DSO', 'Avg DIO', 'Avg DPO', 'Avg CCC', 'Avg Cash Conversion', 'Avg FCF (₦M)', 'Avg Cash Interest Cov'],
'Value': [
f"{financials['dso'].mean():.1f} days",
f"{financials['dio'].mean():.1f} days",
f"{financials['dpo'].mean():.1f} days",
f"{financials['ccc'].mean():.1f} days",
f"{financials['cash_conversion'].mean():.2f}x",
f"₦{financials['fcf'].mean():.1f}M",
f"{financials['cash_interest_coverage'].mean():.1f}x"
]
})
print(summary)
print("\nLatest Quarter (Q4 2024) Health Assessment:")
latest = financials.iloc[-1]
print(f"CCC: {latest['ccc']:.0f} days")
print(f"Cash Conversion: {latest['cash_conversion']:.2f}")
print(f"Cash Interest Coverage: {latest['cash_interest_coverage']:.1f}x (target: >2.0x)")
print(f"Free Cash Flow: ₦{latest['fcf']:.1f}M")
```
:::
::: {.callout-caution icon="false"}
## 📝 Section 39.2 Review Questions
1. A firm has OCF = ₦100M, Net Income = ₦80M, Current Liabilities = ₦50M, Interest Expense = ₦20M. Calculate OCF ratio and Cash Interest Coverage. Are both healthy?
2. If Cash Conversion ratio is 0.7 (OCF < Net Income), what might explain this? Is it always a red flag?
3. For a high-growth startup with ₦10M cash and ₦2M monthly burn, what is the runway? Should the company raise capital?
:::
## Connecting Cash Flow Forecasts to Treasury
Treasury functions manage day-to-day liquidity, FX hedging, and working capital. A 13-week rolling forecast bridges accounting accruals and cash action.
### 13-Week Rolling Forecast Structure
The 13-week forecast projects weekly cash inflows (customer collections, debt draws) and outflows (payroll, suppliers, debt service, tax). The bottom line is projected cash balance.
**Typical line items:**
*Inflows*:
- Customer collections (estimated from DSO and weekly sales)
- Debt draws, equity raises
- Sale of assets
*Outflows*:
- Supplier payments (estimated from DPO and weekly COGS)
- Payroll
- Tax payments
- Debt service (principal + interest)
- CapEx, dividends
### Working Capital Sensitivity and FX Hedging
For Nigerian firms:
- **Currency exposure**: USD-denominated suppliers (CIF imports) create FX payables. A 10% naira depreciation increases payables in local currency by 10%. Treasury must decide: hedge (reduce upside), or absorb (increases risk).
- **Seasonality**: Businesses with seasonal revenue (FMCG peaks in December) must forecast working capital lows (mid-quarter) and ensure adequate liquidity.
### Code: 13-Week Rolling Cash Forecast
::: {.panel-tabset}
## R
```{r}
#| label: cash-forecast-13w-r
#| eval: true
library(tidyverse)
library(lubridate)
set.seed(8243)
# 13-week forecast starting from January 1, 2024
forecast_weeks <- 13
start_date <- as.Date("2024-01-01")
forecast_dates <- seq(start_date, by = "week", length.out = forecast_weeks)
# Base daily revenue: ₦1M/day, with 20% weekly variation
# Weekly collections (cash from customers) depend on DSO = 30 days
# Roughly 30% of current week sales collected immediately (upfront), 70% from past 30 days
forecast_data <- tibble(
week_end = forecast_dates,
week_num = 1:forecast_weeks,
# Revenue forecast: base ₦7M/week with random variation
weekly_revenue = 7e6 * (1 + rnorm(forecast_weeks, 0, 0.1)),
# Collection (DSO = 30 days): collect current week 0.3 + average of past 4 weeks * 0.7
weekly_cogs = 0.6 * weekly_revenue,
# Collections from customers (staggered by DSO = 30 days)
# Simplified: collect previous week + collections from 30-days-ago bucket
collections = 0 # Placeholder; will calculate
)
# Calculate collections (naive: lag 4 weeks = 28 days)
forecast_data <- forecast_data |>
mutate(
prev_week_revenue = lag(weekly_revenue, default = 7e6),
old_revenue = lag(weekly_revenue, 4, default = 7e6),
# DSO = 30 days; collect 30% current + 70% from 30-day bucket
collections = 0.3 * weekly_revenue + 0.7 * old_revenue
)
# Supplier payments (DPO = 30 days)
forecast_data <- forecast_data |>
mutate(
# Pay current week COGS + balance from prior periods
supplier_payment = 0.3 * weekly_cogs + 0.7 * lag(weekly_cogs, 4, default = 4.2e6)
)
# Other outflows
forecast_data <- forecast_data |>
mutate(
payroll = 1e6, # Weekly
utilities = 0.3e6,
debt_service = 0.2e6, # Weekly principal + interest
capex = ifelse(week_num %in% c(2, 8), 1.5e6, 0), # Lumpy
# Net cash flow
total_inflows = collections,
total_outflows = supplier_payment + payroll + utilities + debt_service + capex,
net_cash_flow = total_inflows - total_outflows
)
# Running cash balance (starting balance = ₦15M)
starting_cash <- 15e6
forecast_data <- forecast_data |>
mutate(
cash_balance = starting_cash + cumsum(net_cash_flow)
)
# Display forecast
forecast_display <- forecast_data |>
select(week_num, week_end, collections, supplier_payment, payroll, debt_service, capex,
net_cash_flow, cash_balance) |>
mutate(
collections = collections / 1e6,
supplier_payment = supplier_payment / 1e6,
payroll = payroll / 1e6,
debt_service = debt_service / 1e6,
capex = capex / 1e6,
net_cash_flow = net_cash_flow / 1e6,
cash_balance = cash_balance / 1e6
)
print("13-Week Cash Flow Forecast (₦ millions):")
print(forecast_display)
# Summary
summary_forecast <- tibble(
Metric = c("Avg Weekly Collections", "Avg Weekly Payments", "Avg Net Cash Flow",
"Min Cash Balance", "Max Cash Balance", "Final Cash Balance"),
Value = c(
paste0("₦", round(mean(forecast_data$collections) / 1e6, 1), "M"),
paste0("₦", round(mean(forecast_data$total_outflows) / 1e6, 1), "M"),
paste0("₦", round(mean(forecast_data$net_cash_flow) / 1e6, 1), "M"),
paste0("₦", round(min(forecast_data$cash_balance) / 1e6, 1), "M"),
paste0("₦", round(max(forecast_data$cash_balance) / 1e6, 1), "M"),
paste0("₦", round(max(forecast_data$cash_balance) / 1e6, 1), "M")
)
)
print("\nForecast Summary:")
print(summary_forecast)
# Liquidity alert
min_required_balance <- 5e6
if (min(forecast_data$cash_balance) < min_required_balance) {
cat(sprintf("WARNING: Forecast shows cash falling below ₦5M minimum. Week %d dips to ₦%.1fM\n",
which.min(forecast_data$cash_balance),
min(forecast_data$cash_balance) / 1e6))
}
```
## Python
```{python}
#| label: py-cash-forecast-13w
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
np.random.seed(8243)
# 13-week forecast
start_date = pd.Timestamp("2024-01-01")
forecast_dates = pd.date_range(start_date, periods=13, freq='W')
forecast_data = pd.DataFrame({
'week_end': forecast_dates,
'week_num': range(1, 14)
})
# Revenue and COGS
forecast_data['weekly_revenue'] = 7e6 * (1 + np.random.normal(0, 0.1, 13))
forecast_data['weekly_cogs'] = 0.6 * forecast_data['weekly_revenue']
# Collections (DSO = 30 days ~ 4 weeks)
# Collect 30% of current week + 70% from 4 weeks ago
forecast_data['collections'] = (0.3 * forecast_data['weekly_revenue'] +
0.7 * forecast_data['weekly_revenue'].shift(4).fillna(7e6))
# Supplier payments (DPO = 30 days)
forecast_data['supplier_payment'] = (0.3 * forecast_data['weekly_cogs'] +
0.7 * forecast_data['weekly_cogs'].shift(4).fillna(4.2e6))
# Fixed outflows
forecast_data['payroll'] = 1e6
forecast_data['utilities'] = 0.3e6
forecast_data['debt_service'] = 0.2e6
forecast_data['capex'] = forecast_data['week_num'].apply(lambda x: 1.5e6 if x in [2, 8] else 0)
# Net cash flow
forecast_data['total_inflows'] = forecast_data['collections']
forecast_data['total_outflows'] = (forecast_data['supplier_payment'] +
forecast_data['payroll'] +
forecast_data['utilities'] +
forecast_data['debt_service'] +
forecast_data['capex'])
forecast_data['net_cash_flow'] = forecast_data['total_inflows'] - forecast_data['total_outflows']
# Running balance (starting ₦15M)
starting_cash = 15e6
forecast_data['cash_balance'] = starting_cash + forecast_data['net_cash_flow'].cumsum()
# Display in millions
display = forecast_data[['week_num', 'week_end', 'collections', 'supplier_payment',
'payroll', 'debt_service', 'capex', 'net_cash_flow', 'cash_balance']].copy()
for col in ['collections', 'supplier_payment', 'payroll', 'debt_service', 'capex', 'net_cash_flow', 'cash_balance']:
display[col] = display[col] / 1e6
print("13-Week Cash Flow Forecast (₦ millions):")
print(display.round(1))
print("\nForecast Summary:")
print(f"Avg Weekly Collections: ₦{forecast_data['collections'].mean()/1e6:.1f}M")
print(f"Avg Weekly Payments: ₦{forecast_data['total_outflows'].mean()/1e6:.1f}M")
print(f"Avg Net Cash Flow: ₦{forecast_data['net_cash_flow'].mean()/1e6:.1f}M")
print(f"Min Cash Balance: ₦{forecast_data['cash_balance'].min()/1e6:.1f}M (Week {forecast_data['cash_balance'].idxmin() + 1})")
print(f"Max Cash Balance: ₦{forecast_data['cash_balance'].max()/1e6:.1f}M")
# Liquidity check
min_required = 5e6
if forecast_data['cash_balance'].min() < min_required:
low_week = forecast_data['cash_balance'].idxmin() + 1
low_amount = forecast_data['cash_balance'].min() / 1e6
print(f"\n*** WARNING: Cash falls below ₦5M minimum in Week {low_week} (₦{low_amount:.1f}M) ***")
```
:::
::: {.callout-caution icon="false"}
## 📝 Section 39.3 Review Questions
1. In the 13-week forecast, why do collections lag COGS payments by roughly 4 weeks if both DSO and DPO are 30 days?
2. If a CapEx spike occurs in week 2, how would you adjust the forecast to avoid a liquidity crisis?
3. A firm has ₦10M minimum required cash. The forecast shows a dip to ₦4M in week 6. What treasury actions would you recommend?
:::
## Stress Testing Cash Flows Under Macroeconomic Scenarios
Cash flow stress testing quantifies resilience to adverse scenarios: FX devaluation, commodity price shocks, revenue declines, or supply chain disruptions.
### Scenario Design
For a Nigerian manufacturer with USD-denominated imports and NGN-denominated revenues:
**Scenario A: Base Case**
- No change; revenue, costs, FX = historical average
**Scenario B: FX Shock (Naira Devalues 30%)**
- COGS increases 30% (imports more expensive in NGN)
- Revenue flat (hedged or slow to pass through)
- Operating margin compressed
**Scenario C: Revenue Shock (−15%)**
- Demand drops (recession, competition)
- Fixed costs remain (payroll, rent)
- Profitability declines; may turn negative
**Scenario D: Combined Stress (FX +30% + Revenue −15%)**
- Worst case; low-probability but high-impact
- Tests firm's capital adequacy and financing cushion
### Code: Multi-Scenario Stress Test
::: {.panel-tabset}
## R
```{r}
#| label: stress-test-r
#| eval: true
library(tidyverse)
# Base case quarterly financials
base_case <- tibble(
revenue = c(50, 52, 51, 55, 58, 60, 62, 65) * 1e6,
cogs = c(30, 31, 30.5, 33, 35, 36, 37, 39) * 1e6,
opex = c(10, 10, 10, 11, 11, 11, 11, 12) * 1e6,
capex = c(2, 2.5, 2, 3, 2.5, 2.5, 3, 3.5) * 1e6,
debt_service = c(1.5, 1.5, 1.5, 1.6, 1.8, 1.8, 1.8, 2) * 1e6
) |>
mutate(
ebit = revenue - cogs - opex,
fcf = ebit - capex - debt_service,
period = 1:n()
)
# Create stress scenarios
scenarios <- expand_grid(
scenario_name = c("Base Case", "FX Shock (+30% COGS)", "Revenue Shock (-15%)", "Combined Stress"),
fx_impact = c(0, 0.30, 0, 0.30),
revenue_impact = c(0, 0, -0.15, -0.15)
)
# Apply scenarios
stress_results <- NULL
for (i in 1:nrow(scenarios)) {
scenario <- base_case |>
mutate(
scenario_name = scenarios$scenario_name[i],
revenue_stressed = revenue * (1 + scenarios$revenue_impact[i]),
cogs_stressed = cogs * (1 + scenarios$fx_impact[i]),
opex_stressed = opex, # Fixed
capex_stressed = capex,
debt_service_stressed = debt_service,
ebit_stressed = revenue_stressed - cogs_stressed - opex_stressed,
fcf_stressed = ebit_stressed - capex_stressed - debt_service_stressed
)
stress_results <- bind_rows(stress_results, scenario)
}
# Summary by scenario
scenario_summary <- stress_results |>
group_by(scenario_name) |>
summarise(
avg_revenue = mean(revenue_stressed) / 1e6,
avg_ebit = mean(ebit_stressed) / 1e6,
avg_ebit_margin = mean(ebit_stressed / revenue_stressed),
total_fcf = sum(fcf_stressed) / 1e6,
min_fcf_quarter = min(fcf_stressed) / 1e6,
.groups = "drop"
) |>
arrange(desc(total_fcf))
print("Stress Test Summary:")
print(scenario_summary)
# Waterfall: impact from base case to combined stress
base_fcf <- sum(base_case$fcf) / 1e6
base_scenario_fcf <- stress_results |>
filter(scenario_name == "Base Case") |>
pull(fcf_stressed) |>
sum() / 1e6
fx_shock_fcf <- stress_results |>
filter(scenario_name == "FX Shock (+30% COGS)") |>
pull(fcf_stressed) |>
sum() / 1e6
revenue_shock_fcf <- stress_results |>
filter(scenario_name == "Revenue Shock (-15%)") |>
pull(fcf_stressed) |>
sum() / 1e6
combined_fcf <- stress_results |>
filter(scenario_name == "Combined Stress") |>
pull(fcf_stressed) |>
sum() / 1e6
cat("Waterfall: Base Case FCF to Combined Stress FCF\n")
cat(sprintf("Base Case: ₦%.1fM\n", base_fcf))
cat(sprintf("FX Shock Impact: ₦%.1fM (change to ₦%.1fM)\n", fx_shock_fcf - base_fcf, fx_shock_fcf))
cat(sprintf("Revenue Shock Impact: ₦%.1fM (change to ₦%.1fM)\n", revenue_shock_fcf - base_fcf, revenue_shock_fcf))
cat(sprintf("Combined Stress: ₦%.1fM\n", combined_fcf))
cat(sprintf("Total Impact: ₦%.1fM (%.1f%% decline)\n", combined_fcf - base_fcf, 100 * (combined_fcf - base_fcf) / base_fcf))
```
## Python
```{python}
#| label: py-stress-test
import pandas as pd
import numpy as np
# Base case
base_case = pd.DataFrame({
'revenue': [50, 52, 51, 55, 58, 60, 62, 65],
'cogs': [30, 31, 30.5, 33, 35, 36, 37, 39],
'opex': [10, 10, 10, 11, 11, 11, 11, 12],
'capex': [2, 2.5, 2, 3, 2.5, 2.5, 3, 3.5],
'debt_service': [1.5, 1.5, 1.5, 1.6, 1.8, 1.8, 1.8, 2]
})
base_case['ebit'] = base_case['revenue'] - base_case['cogs'] - base_case['opex']
base_case['fcf'] = base_case['ebit'] - base_case['capex'] - base_case['debt_service']
# Define scenarios
scenarios = pd.DataFrame({
'scenario_name': ['Base Case', 'FX Shock (+30% COGS)', 'Revenue Shock (-15%)', 'Combined Stress'],
'fx_impact': [0, 0.30, 0, 0.30],
'revenue_impact': [0, 0, -0.15, -0.15]
})
# Apply scenarios
stress_results = []
for _, scenario_row in scenarios.iterrows():
base_copy = base_case.copy()
base_copy['scenario_name'] = scenario_row['scenario_name']
base_copy['revenue_stressed'] = base_copy['revenue'] * (1 + scenario_row['revenue_impact'])
base_copy['cogs_stressed'] = base_copy['cogs'] * (1 + scenario_row['fx_impact'])
base_copy['ebit_stressed'] = base_copy['revenue_stressed'] - base_copy['cogs_stressed'] - base_copy['opex']
base_copy['fcf_stressed'] = base_copy['ebit_stressed'] - base_copy['capex'] - base_copy['debt_service']
stress_results.append(base_copy)
stress_df = pd.concat(stress_results, ignore_index=True)
# Summary
scenario_summary = stress_df.groupby('scenario_name').agg({
'revenue_stressed': 'mean',
'ebit_stressed': 'mean',
'fcf_stressed': 'sum'
}).reset_index()
scenario_summary.columns = ['scenario_name', 'avg_revenue', 'avg_ebit', 'total_fcf']
scenario_summary = scenario_summary.sort_values('total_fcf', ascending=False)
print("Stress Test Summary:")
print(scenario_summary.round(1))
# Waterfall
base_fcf = stress_df[stress_df['scenario_name'] == 'Base Case']['fcf_stressed'].sum()
fx_fcf = stress_df[stress_df['scenario_name'] == 'FX Shock (+30% COGS)']['fcf_stressed'].sum()
rev_fcf = stress_df[stress_df['scenario_name'] == 'Revenue Shock (-15%)']['fcf_stressed'].sum()
combined_fcf = stress_df[stress_df['scenario_name'] == 'Combined Stress']['fcf_stressed'].sum()
print("\nWaterfall: FCF Impact")
print(f"Base Case: ₦{base_fcf:.1f}M")
print(f"FX Shock: ₦{fx_fcf:.1f}M (change: ₦{fx_fcf - base_fcf:.1f}M)")
print(f"Revenue Shock: ₦{rev_fcf:.1f}M (change: ₦{rev_fcf - base_fcf:.1f}M)")
print(f"Combined: ₦{combined_fcf:.1f}M (change: ₦{combined_fcf - base_fcf:.1f}M, {100*(combined_fcf-base_fcf)/base_fcf:.1f}%)")
```
:::
::: {.callout-caution icon="false"}
## 📝 Section 39.4 Review Questions
1. In the stress test, a 30% FX shock reduces FCF by ₦X, but a 15% revenue shock reduces it by ₦Y. Which has bigger impact? Why?
2. If the combined stress (FX + revenue) reduces 8-quarter FCF to negative, what implications does this have for the firm's solvency?
3. Design a fifth scenario to test resilience: "Credit Crunch" (no access to new debt, must pay down ₦10M in 8 quarters).
:::
## Case Study: Manufacturing CFO Dashboard
### Context
A Nigerian food processor (₦1B annual revenue) faces naira devaluation (USD/NGN rising), import price inflation, and slowing demand. The CFO wants a real-time dashboard to track cash health and stress-test survival under adverse scenarios.
### Dashboard Components
The dashboard includes:
1. **Working Capital Trend**: DSO, DIO, DPO, CCC over 12 months
2. **13-Week Rolling Forecast**: Weekly cash balance projection with minimum cushion line (₦10M)
3. **Stress Test Summary**: Scenario FCF comparison
4. **Key Ratios Dashboard**: OCF margin, Cash Conversion, Interest Coverage, FCF margin
5. **Alerts & Actions**: Flag if CCC > threshold, if cash < minimum, if ratios deteriorate
### Synthetic Implementation
Building on previous sections, the dashboard integrates:
- 12 months of quarterly historical data (financials, working capital)
- 13-week forward forecast
- Three stress scenarios
- KPI alerts and traffic light status
**(Implementation spans Sections 39.2, 39.3, 39.4)**
### Key Outputs and Interpretation
**Dashboard Snapshot (Q4 2024)**
| Component | Current | Status | Trend |
|:---|---:|:---|:---|
| CCC | 68 days | Yellow | Deteriorating (was 60 days in Q1) |
| Cash Balance | ₦12.5M | Green | Above ₦10M minimum |
| OCF Margin | 18% | Green | Stable |
| Cash Interest Coverage | 5.6x | Green | Above 2.0x threshold |
| 13-Week Min Forecast | ₦8.2M | Yellow | Below ₦10M in Week 6 |
| FX Stress Test FCF | ₦22M | Orange | 20% below base case |
**Interpretation**: The firm is currently healthy but faces headwinds. CCC deterioration (DSO up from 45 to 51 days due to slower customer payments) is consuming working capital. The 13-week forecast shows a liquidity dip in mid-quarter (seasonal); management should consider a short-term facility to bridge. FX stress is material; hedging a portion of USD payables is prudent.
### Recommendations
1. **Immediate** (2 weeks):
- Activate ₦5M short-term credit facility as a precaution; secure before Week 6 trough.
- Launch collection improvement: offer 2% discount for 10-day payment (reduce DSO by 5 days = ₦13.7M freed).
2. **Medium-term** (4 weeks):
- Review supplier terms; negotiate DPO extension from 30 to 35 days (₦4.6M benefit).
- Implement inventory tightening: reduce DIO from 86 to 80 days (₦3.3M benefit).
- Net: ₦20M working capital freed by end of Q1 2025.
3. **Strategic**:
- Hedge 50% of USD payables (₦200M annual exposure) with 6-month forwards; cap downside.
- Increase prices 5% to offset inflation; elasticity suggests 1-2% volume loss (net positive margin).
::: {.callout-caution icon="false"}
## 📝 Case Study Review Questions
1. The CCC deteriorated from 60 to 68 days. Was this due to DSO, DIO, or DPO? What is the cash impact for a ₦1B annual revenue firm?
2. If a short-term facility costs 18% per annum, is it worth drawing ₦5M for 8 weeks to bridge the liquidity gap?
3. In the FX stress scenario, should the firm hedge 25%, 50%, or 100% of USD payables? What are the trade-offs?
:::
## Exercises
::: {.exercises}
#### Chapter 39 Exercises
1. **CCC Improvement**: A Nigerian importer has DSO = 60, DIO = 120, DPO = 45, annual revenue ₦500M. Calculate CCC and cash tied up. If you reduce DSO to 45, what is the new cash position?
2. **Cash Conversion Quality**: A firm reports Net Income = ₦50M, OCF = ₦40M over the year. What is the cash conversion ratio? Is this healthy? What might explain a low ratio?
3. **Interest Coverage Crisis**: A firm has OCF = ₦100M, Interest = ₦30M (annual). Interest rates rise, and interest climbs to ₦50M. Calculate coverage before and after. Is the firm in distress?
4. **DSO Negotiation**: A food distributor's largest customer (₦100M annual, 20% of revenue) typically pays in 90 days. Negotiate a terms improvement: offer 2% discount for 30-day payment. Calculate the IRR of this incentive vs 15% cost of capital.
5. **Seasonal Working Capital**: A fashion retailer has peak demand Oct-Dec (60% of annual sales). Model quarterly cash flows for 4 quarters (Q1-Q4) with DSO = 30, DIO = 60, DPO = 30. At what quarter does cash dip lowest?
6. **FX Hedging Decision**: A firm imports ₦500M annually (USD-denominated). The naira/USD spot is 1,500. Use a forward rate of 1,650 (3-month forward, 10% depreciation priced in). Decide: hedge 0%, 50%, or 100%? Justify.
7. **CapEx and FCF**: A manufacturer has OCF = ₦200M. Planned CapEx is ₦150M. Calculate FCF. If debt service is ₦50M, is there a dividend capacity? Should dividends be paid?
8. **13-Week Forecast Confidence**: What factors create the largest forecast uncertainty in a 13-week rolling forecast? How would you validate the forecast weekly?
9. **Stress Test Design**: Design a "supply shock" scenario where COGS increases 25% (bottleneck on raw materials). Model the impact on OCF and stress interest coverage.
10. **Working Capital Financing**: A startup has negative CCC (collects before paying suppliers). Is this advantageous? Can the firm avoid external financing solely through CCC improvement?
:::
## Further Reading
- Damodaran, A. (2006). *Damodaran on Valuation: Security Analysis for Investment and Corporate Finance* (2nd ed.). Wiley.
- Ittelson, T. R. (2009). *Financial Statements: A Step-by-Step Guide to Understanding and Creating Financial Reports*. Career Press.
- Palepu, K. G., Healy, P. M., & Peek, E. (2016). *Business Analysis and Valuation: Using Financial Statements* (5th ed.). Cengage Learning.
- Bragg, S. M. (2020). *Business Ratios and Formulas* (4th ed.). Accounting Tools.
---
## Chapter 39 Appendix: Working Capital and Cash Flow Derivations
### A1. Cash Conversion Cycle from First Principles
Working capital consists of:
$$WC = AR + Inventory - AP$$
where AR = Accounts Receivable, Inventory = stock, AP = Accounts Payable.
Expressed in days:
$$WC_{\text{days}} = DSO + DIO - DPO$$
This is the Cash Conversion Cycle. The economic interpretation: after payment to suppliers, it takes CCC days before cash is recovered from customers.
### A2. Impact of CCC on Cash Requirements
Daily cash requirement (for operations) is:
$$\text{Daily Cash Requirement} = \frac{\text{COGS}}{365}$$
To sustain CCC days of operations without external funding:
$$\text{Cash Required} = \text{Daily Cash Requirement} \times CCC = \frac{\text{COGS}}{365} \times CCC$$
A 10-day CCC reduction frees:
$$\Delta \text{Cash} = \frac{\text{COGS}}{365} \times 10$$
For a firm with ₦1B COGS, each CCC day is worth ₦2.74M.
### A3. Free Cash Flow and Terminal Value
Free Cash Flow is:
$$FCF_t = EBIT_t (1 - \tau) + D\&A_t - CAPEX_t - \Delta NWC_t$$
where:
- $\tau$ = tax rate
- $D\&A$ = depreciation & amortisation
- $\Delta NWC$ = change in net working capital
The terminal value of a perpetuity (Gordon growth) is:
$$TV = \frac{FCF_{\infty} (1 + g)}{WACC - g}$$
where $g$ is long-term growth rate and $WACC$ is weighted average cost of capital.
### A4. OCF and Accrual Conversion
Net Income under accrual accounting is:
$$NI = \text{Revenue} - \text{COGS} - D\&A - \text{Interest}$$
Operating Cash Flow adjusts for working capital changes:
$$OCF = NI + D\&A - \Delta AR - \Delta Inventory + \Delta AP$$
The ratio $OCF / NI$ measures earnings quality. A ratio < 0.8 suggests aggressive accounting (accruals not backed by cash).
### A5. Interest Coverage and Solvency
Cash Interest Coverage is:
$$\text{CIC} = \frac{OCF}{\text{Interest}}$$
A firm can service debt indefinitely if CIC > 2.0x. For a firm in distress (CIC < 1.0x), debt service becomes unsustainable and default risk spikes.
---