13-Week Liquidity Forecast (AR/AP-driven)

The Problem

Building a 13-week rolling cash forecast manually is a three-to-five day exercise for most mid-market finance teams. A controller at a company with 50–300 open receivable lines and 80–200 open payable lines must export the AR aging and AP aging from the ERP, clean date formats, infer missing due dates from payment terms, layer in a recurring outflows calendar covering payroll, GST/TDS/PF/PT statutory dues, rent, EMIs, and utilities, then hand-build a weekly waterfall in Excel — a process that is stale before it is finished.

The complexity compounds because raw sub-ledger dates do not reflect real collection timing. AR customers routinely pay 3–10 days after the stated due date; ignoring that lag overstates early-week inflows and understates the probability of a covenant breach. The open AP register carries its own hazard: bills for MSME vendors flagged under Section 15 of the Micro, Small and Medium Enterprises Development (MSMED) Act 2006 must be paid within 45 days of acceptance, and any overdue MSME payable triggers a disallowance under Section 43B(h) of the Income Tax Act 1961, yet most Excel-based forecasts treat MSME and non-MSME vendors identically.

At scale — multiple entities, weekly re-forecast cycles, stress scenarios for customer delays and credit haircuts — the manual model breaks entirely. Controllers spend more time reconciling formula errors across 13 weekly columns than interpreting the output. The result is a forecast that arrives too late to influence the treasury team's borrowing or early-payment decisions for the coming week.

Cadel's 13-Week Liquidity Forecast workflow accepts three file uploads — open AR aging, open AP aging, and a recurring outflows calendar — and returns a fully bucketed, covenant-monitored cash waterfall with stress scenarios in a single automated run, replacing days of spreadsheet work with a deterministic, auditable output.

Why It Matters: Context

The 13-week liquidity forecast is the primary short-term cash visibility tool recommended by the Institute of Chartered Accountants of India (ICAI) in its guidance on treasury management for mid-market entities. In practice, it is also the document that lenders, credit rating agencies, and internal audit committees request first when assessing liquidity adequacy. A cash covenant — typically a minimum balance requirement expressed in absolute rupee terms — is a standard clause in working capital credit facilities; Acme Industries Pvt Ltd carries a Rs 50 lakh minimum cash covenant representative of the mid-market segment. Breaching that covenant, even for a single week, can trigger a notice of default or a renegotiation of credit terms.

Statutory outflows add precision requirements that generic forecasting tools ignore. GST payment is due by the 20th of the following month under Section 39 of the CGST Act 2017; TDS must be deposited by the 7th of the month following deduction under Section 200 of the Income Tax Act 1961; Provident Fund contributions are due by the 15th under the Employees' Provident Funds and Miscellaneous Provisions Act 1952; Professional Tax deadlines vary by state. Each of these has a fixed calendar date that must land in the correct weekly bucket or the forecast misstates the outflow week by up to three weeks. MSME payables carry a separate urgency: a payment outstanding beyond 45 days of acceptance is disallowed as a business expense under Section 43B(h) for the relevant assessment year, creating a direct tax cost if the forecast does not surface the overdue amount immediately.

When the forecast is wrong — because a due date was missing, a customer's pay-lag was ignored, or a statutory due date fell into the wrong week — the consequences are concrete: the treasury team draws more from the working capital line than necessary (incurring interest), or worse, fails to draw in time and breaches the minimum balance, triggering a covenant event. An internal auditor reviewing the forecast under SA 315 (Identifying and Assessing the Risks of Material Misstatement) will flag an unmonitored covenant or an undisclosed MSME overdue as a control deficiency.

What This Workflow Automates

1. The workflow ingests the Open AR Aging spreadsheet and extracts each row's customer_name, invoice_no, due_date, amount_outstanding, and historical_pay_lag_days, then adjusts each receipt date forward by the customer's average lag to produce a lag-corrected expected collection date.
2. Each AR inflow is assigned to one of 13 weekly buckets based on the lag-corrected date; if the lag-adjusted date falls beyond week 13, the invoice is placed in a beyond-horizon queue and excluded from the current waterfall.
3. The workflow ingests the Open AP Aging spreadsheet, reads due_date, amount_payable, payment_terms, and msme_flag for every bill; where due_date is null, it derives an estimated due date by adding the payment_terms days (e.g., Net 30) to bill_date and marks the entry as due date inferred in the exception queue.
4. Any AP row where msme_flag equals Yes and the due date is more than 45 days in the past is flagged with an MSME Overdue >45 Days badge and moved to the current week's immediate-payment priority list, consistent with obligations under Section 15 of the MSMED Act 2006 and the Section 43B(h) disallowance risk.
5. The workflow ingests the Recurring Outflows Calendar, reads each row's category, due_date, amount, frequency, and critical flag, and projects recurring items (payroll, GST, TDS, PF, PT, rent, EMI, utilities) forward across all 13 weeks, placing each occurrence in the correct weekly bucket by its statutory or contractual due date.
6. A week-by-week opening-balance → inflows → outflows → closing-balance waterfall is computed for all 13 weeks; any week whose closing balance falls below the Rs 50 lakh minimum cash covenant triggers a covenant-breach exception badge with the exact shortfall amount and the specific outflow line items that contributed to the breach.
7. Two stress scenarios are calculated automatically: the Top-3 Customer Delay scenario re-buckets the three largest AR balances by one additional pay-lag period and recomputes all 13 closing balances, and the 10% AR Haircut scenario reduces every AR inflow bucket proportionally by 10% to model credit-loss exposure, with both revised waterfalls presented alongside the base case so covenant headroom can be compared.

All of this executes in under 60 seconds with a deterministic, cell-level-auditable output grid that every controller can trace back to source invoice and bill numbers.

Edge Cases We Simulate

The workflow ships with a battery of synthetic test scenarios that exercise every failure mode we have seen in real-world data. Each scenario produces a deterministic outcome that an auditor or controller can verify in seconds.

Sample Documents

Download or inspect the seeded sample files used to demonstrate this workflow:

Sample Results

Running the workflow on the demo file set — ar_aging_clean.xlsx, ap_aging_clean.xlsx, and recurring_outflows_clean.xlsx — the extraction pass resolved 100% of AR rows and 100% of AP rows to weekly buckets, with one AP bill (BFL/2026/0871, Rs 7,20,000, Bharat Forge Ltd) processed successfully under Net 30 terms. The recurring outflows calendar extracted a monthly payroll entry of Rs 42,00,000 for head-office salaries and a critical quarterly advance tax entry of Rs 65,00,000 (Q1 FY27 due 7 June 2026), both marked critical = Yes, and both placed in their correct statutory-due-date weeks. Across the recurring_outflows_covenant_breach_week6.xlsx scenario file, the Rs 65,00,000 advance tax outflow, combined with clustered AP payments, produced a closing-balance shortfall below the Rs 50 lakh covenant threshold in Week 6, which the workflow surfaced as a breach exception with the precise rupee gap.

The most operationally significant exception class the workflow caught was the customer concentration risk pattern visible in ar_aging_customer_concentration_risk.xlsx: a single customer's outstanding balance exceeded 30% of total open AR, meaning their payment timing alone could shift the Week 4–Week 6 closing balances by more than the covenant buffer. The workflow raised a concentration-risk exception noting the affected week, the customer's share of total AR, and the closing-balance impact if that customer deferred payment by one additional week — a finding that would not surface in a standard aging summary report.

Why Automation Wins Here

For a mid-market finance team running the 13-week forecast manually, the end-to-end cycle — ERP extract, date cleaning, lag adjustment, statutory calendar overlay, stress scenario modelling — typically consumes three to five working days per month. Cadel's workflow reduces that to under 60 seconds per run, with the added precision of deterministic lag correction, MSME overdue detection, and two built-in stress scenarios that a manual Excel model rarely includes consistently. The exception queue surfaces covenant breaches, inferred due dates, MSME overdue items, and concentration risks as discrete, actionable entries rather than burying them in 13 columns of formula-dependent cells.

The output of each run is an Excel export of the full 13-week opening-balance → inflows → outflows → closing-balance grid, with exception badges and stress-scenario comparisons included as named tabs. That file drops directly into the controller's audit workpaper file as the supporting schedule for the liquidity covenant compliance assertion, with a timestamped run record that satisfies the documentation standard expected under ICAI SA 230 (Audit Documentation) and the internal control review requirements under SA 315.

Frequently Asked Questions