Forecast Cash Flow and Optimize Working Capital

This skill covers cash-flow forecasting, financial modeling, and liquidity management - building accurate, dynamic forecasting models that help a business predict future cash positions, identify funding needs, and optimize working-capital management. Time-horizon structure: daily forecasts on a rolling 13-week window for operational decisions, weekly forecasts over 52 weeks for strategic planning, monthly forecasts over 12-24 months aligned to budget, and scenario modeling across best/base/worst cases. Cash-flow components: operating activity (receipts, payments, payroll, taxes), investing activity (capital expenditures, asset sales), financing activity (debt service, equity transactions, dividends), and FX activity (currency-risk hedging and translation).

A Python forecasting framework is demonstrated via a class computing a collections forecast:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from sklearn.linear_model import LinearRegression

class CashFlowForecaster:
    def __init__(self, historical_data):
        self.data = historical_data
        self.forecast_periods = 13  # weeks
        
    def calculate_collections_forecast(self, sales_forecast, collection_pattern):
        """
        Forecast cash collections based on sales and collection patterns
        collection_pattern: dict with keys 'current', '30_days', '60_days', '90_days'
        """
        collections = pd.DataFrame()
        
        for period in range(self.forecast_periods):
            period_collections = 0
            
            # Current period collections
            if period < len(sales_forecast):
                period_collections += sales_forecast.iloc[period] * collection_pattern['current']
            
            # 30-day collections
            if period >= 4 and period-4 < len(sales_forecast):
                period_collections += sales_forecast.iloc[period-4] * collection_pattern['30_days']
            
            # 60-day collections  
            if period >= 8 and period-8 < len(sales_forecast):
                period_collections += sales_forecast.iloc[period-8] * collection_pattern['60_days']
                
            collections = pd.concat([collections, pd.DataFrame([period_collections])], ignore_index=True)
            
        return collections

from sales and collection-pattern inputs (current/30-day/60-day buckets), plus an operating-cash-flow forecast that shifts revenue and expense forecasts by collection/payment lag periods before netting them. Advanced scenario modeling covers a Monte Carlo simulation class (randomized revenue and expense shocks over configurable iterations, tracking minimum cash, final cash, and periods-negative) and named stress-test scenarios - recession, supply shock, customer loss, each with a revenue-decline and expense-increase assumption - computing minimum cash, cash-deficit periods, and maximum funding need per scenario.

Working-capital optimization covers a collection-terms optimizer comparing Net-15/30/45 payment terms (each with a sales-volume multiplier and average-collection-days assumption) to compute annual sales, average receivables, carrying cost, and net benefit per term, weighed against a cost-of-capital rate. Excel integration and automation is shown via openpyxl-based code that clears and repopulates a cash-flow-forecast worksheet range with new forecast data and writes variance-analysis formulas comparing actual to forecast columns.

KPIs cover cash-flow metrics (cash-conversion cycle = DSO + DIO - DPO, operating cash-flow ratio = operating CF / current liabilities, free cash flow = operating CF - capex, cash-flow coverage ratio = operating CF / total debt service, forecast accuracy via MAPE) and liquidity-management metrics (a minimum cash buffer of 30-90 days of operating expenses, credit-line utilization vs. forecasted needs, cash-flow volatility as the standard deviation of weekly flows, and seasonal-adjustment factors from historical patterns). Best practices cover model validation (monthly backtesting against actuals, maintaining rolling 12-month accuracy metrics, documenting deviations over 10%, updating assumptions as the business changes), risk management (maintaining credit lines at 1.5x the maximum forecasted deficit, monitoring covenant compliance across all scenarios, setting management-action trigger points, regular stress-testing of key assumptions), and reporting (weekly 13-week rolling forecasts for operations, monthly board reporting with variance analysis, quarterly scenario updates, annual model validation).

Use it when building or reviewing a cash-flow forecasting model - collections/payment timing forecasts, Monte Carlo or stress-test scenario analysis, working-capital/payment-terms optimization, or an Excel-integrated forecast workbook. It is not a general accounting or bookkeeping tool - it forecasts and stress-tests future cash positions from existing financial data, not the underlying transaction recording.

Given historical sales and collections data, revenue and expense forecasts, and a cost of capital, it produces a cash-collections forecast, an operating cash-flow forecast, Monte Carlo and stress-test scenario results, payment-terms optimization analysis, and an updated Excel forecast workbook.

Code samples use pandas, numpy, scikit-learn's LinearRegression, and openpyxl for Excel workbook automation.

FP&A and treasury teams building and maintaining cash-flow forecasts and liquidity plans.