Building Cash Flow Forecasting Models

import pandas as pd
import numpy as np
from prophet import Prophet

df = pd.read_csv('treasury_cash_flows_clean.csv', parse_dates=['date'])
REGS = ['is_payroll_week', 'is_tax_week']          # known calendar events from Module 8
df[REGS] = df[REGS].astype(int)

def fit(data, target, regs):
    d = data[['date', target] + regs].rename(columns={'date': 'ds', target: 'y'})
    d.loc[data['non_recurring'], 'y'] = np.nan      # never train on one-offs
    m = Prophet(weekly_seasonality=False, yearly_seasonality=False,
                daily_seasonality=False, changepoint_prior_scale=0.05)
    for r in regs:
        m.add_regressor(r)
    return m.fit(d)

# 1. Backtest: train on weeks 1-42, test on weeks 43-52
train, test = df[:42], df[42:]
for target, regs in [('net_flow', REGS), ('receipts', [])]:
    m = fit(train, target, regs)
    pred = m.predict(test[['date'] + regs].rename(columns={'date': 'ds'}))['yhat'].values
    actual = test[target].values
    err = pred - actual
    line = (f"{target}: MAE EUR {np.abs(err).mean():,.0f} | "
            f"WAPE {np.abs(err).sum() / np.abs(actual).sum() * 100:.1f}% | "
            f"Bias EUR {err.mean():,.0f} | RMSE EUR {np.sqrt((err ** 2).mean()):,.0f}")
    if (actual > 0).all():                          # MAPE only when actuals never cross zero
        line += f" | MAPE {np.mean(np.abs(err) / actual) * 100:.1f}%"
    print(line)

# 2. Next 13 weeks, on the same weekday as the data
future = pd.DataFrame({'ds': df['date'].max() + pd.to_timedelta(np.arange(1, 14) * 7, unit='D')})
last_payroll = df.loc[df['is_payroll_week'] == 1, 'date'].max()
future['is_payroll_week'] = (((future['ds'] - last_payroll).dt.days // 7) % 4 == 0).astype(int)
month_end = (future['ds'] + pd.Timedelta(days=6)).dt.month != future['ds'].dt.month
future['is_tax_week'] = (month_end & future['ds'].dt.month.isin([3, 6, 9, 12])).astype(int)

net = fit(df, 'net_flow', REGS).predict(future)
rec = fit(df, 'receipts', []).predict(future)

# 3. Scenarios: shock the receipts, not the net flow
out = future[['ds']].copy()
out['base'] = net['yhat'].values
out['lower'] = net['yhat_lower'].values
out['upper'] = net['yhat_upper'].values
out['receipts_fc'] = rec['yhat'].values
out['optimistic'] = out['base'] + 0.10 * out['receipts_fc']
out['pessimistic'] = out['base'] - 0.15 * out['receipts_fc']

OPENING, MINIMUM = 2_450_000, 2_000_000
for s in ['base', 'optimistic', 'pessimistic']:
    out[f'{s}_balance'] = OPENING + out[s].cumsum()
    out[f'{s}_status'] = np.where(out[f'{s}_balance'] < MINIMUM, 'BREACH', 'OK')

out = out.set_index('ds').round(0)
print(out[['base', 'pessimistic', 'pessimistic_balance', 'pessimistic_status']])
out.to_csv('forecast_13_weeks.csv')