Building Rolling Cash Flow Forecasts

Python
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Base 13-week daily framework
start_date = datetime.now().date()
forecast_days = 91  # 13 weeks
dates = pd.date_range(start_date, periods=forecast_days, freq='D')
df = pd.DataFrame({'date': dates, 'day_of_week': dates.dayofweek})

# Initialize cash flow components
df['ar_collections'] = 0
df['ap_payments'] = 0
df['payroll'] = 0
df['fixed_costs'] = 0
df['capex'] = 0
df['debt_service'] = 0

Progress:

• Set up 13-week daily framework with business day logic
• Build AR aging and collection patterns
• Schedule AP payments by terms
• Map payroll and fixed cost timing
• Add CapEx commitments and debt service
• Apply seasonal adjustments from historical data
• Calculate daily cash position and credit line usage
• Set early warning triggers
• Create stress test scenarios

1. AR Collections Model

Python
# AR aging buckets with collection rates
ar_aging = {
    'current': {'balance': 500000, 'collect_rate': 0.95, 'days': 15},
    '1_30': {'balance': 200000, 'collect_rate': 0.85, 'days': 45},
    '31_60': {'balance': 100000, 'collect_rate': 0.65, 'days': 75},
    '61_90': {'balance': 50000, 'collect_rate': 0.40, 'days': 90}
}

def distribute_collections(balance, rate, avg_days, start_idx):
    daily_rate = rate / avg_days
    collections = np.zeros(91)
    remaining = balance
    for i in range(start_idx, min(start_idx + avg_days, 91)):
        daily_collection = remaining * daily_rate
        collections[i] = daily_collection
        remaining -= daily_collection
    return collections

2. AP Payment Scheduling

Python
# AP by payment terms
ap_schedule = {
    'net_30': {'balance': 300000, 'due_date': start_date + timedelta(days=30)},
    'net_15': {'balance': 150000, 'due_date': start_date + timedelta(days=15)},
    '2_10_net_30': {'balance': 100000, 'due_date': start_date + timedelta(days=10)}
}

# Map to business days only (no weekend payments)
df.loc[df['day_of_week'] >= 5, 'business_day'] = False
df.loc[df['day_of_week'] < 5, 'business_day'] = True

3. Payroll and Fixed Costs

Python
# Bi-weekly payroll: $200K every 14 days
payroll_amount = 200000
payroll_days = list(range(4, 91, 14))  # Fridays every 2 weeks
df.loc[df.index.isin(payroll_days), 'payroll'] = -payroll_amount

# Monthly fixed costs: $50K on 1st of month
monthly_fixed = 50000
month_start_days = [0, 31, 62]  # Approximate monthly starts
df.loc[df.index.isin(month_start_days), 'fixed_costs'] = -monthly_fixed

4. Daily Cash Position Calculation

Python
# Calculate net daily cash flow
df['daily_net_cf'] = (df['ar_collections'] + 
                      df['ap_payments'] + 
                      df['payroll'] + 
                      df['fixed_costs'] + 
                      df['capex'] + 
                      df['debt_service'])

# Rolling cash position
opening_cash = 100000
df['cash_position'] = opening_cash + df['daily_net_cf'].cumsum()

# Credit line usage (assuming $500K line)
credit_line_limit = 500000
df['credit_usage'] = np.maximum(0, -df['cash_position'])
df['available_credit'] = credit_line_limit - df['credit_usage']

Example 1: Weekly Summary Dashboard Input: Daily forecast data Output:

Week 1: Starting Cash: $100K, Ending: $85K, Min: $75K
Week 2: Starting Cash: $85K, Ending: $120K, Min: $65K
Peak Credit Usage: $35K (Day 8)
Days Below $50K: 3 days

Example 2: Stress Test Scenario Input: 20% revenue decline Output:

Python
# Apply 20% haircut to collections
df_stress = df.copy()
df_stress['ar_collections'] *= 0.8
df_stress['cash_position_stress'] = opening_cash + df_stress['daily_net_cf'].cumsum()

# Identify breach dates
breach_days = df_stress[df_stress['cash_position_stress'] < 0]['date'].tolist()
print(f"Cash turns negative on: {breach_days[0] if breach_days else 'No breach'}")

• Use business day logic: No collections/payments on weekends
• Apply seasonal factors: Multiply base amounts by historical seasonal indices
• Set minimum cash targets: Usually 3-5 days of operating expenses
• Include covenant monitoring: Track debt-to-cash ratios daily
• Update weekly: Roll forward one week, add new week at end
• Validate with actuals: Compare forecast vs actual daily for accuracy tuning

• Don't ignore timing nuances: Payroll hits Thursday, clears Friday
• Don't use straight-line collections: Weight towards business days
• Don't forget about holidays: Factor in payment delays around holidays
• Don't over-complicate: Start with major cash flows (80/20 rule)
• Don't set static triggers: Use percentage-based early warnings (e.g., 15% of credit line)
• Don't ignore concentration risk: Large customer payment delays can break the model

Early Warning Triggers:

• Cash below 5 days operating expenses
• Credit usage above 60% of line
• 3+ consecutive days of negative cash flow
• Projected covenant breach within 30 days