13-week cash flow forecast in Python with pandas
To build a 13-week cash flow forecast in Python, start from the bank balance, take the invoices still open, expect each customer to pay as late as it did last year and each supplier on the next payment run, add the known items from the treasury calendar, and add it all up week by week. The 54 lines of pandas below do this on a DATA DARE cash flow practice set — a manufacturer's bank account over almost three years — and the results agree with the answers in the set's Question Book.
The sets live now
Mizan Manufacturing: The 13-Week Cash Forecast — Cash Flow Planning
Build a 13-week direct cash forecast from open invoices, customers' own payment delays, the payment run and the treasury calendar - then test it against what th...
Get the data
Mizan Group: How Good Is the Treasury's Forecast? — Cash Flow Planning
Grade a treasury's weekly 13-week cash forecasts against three bank accounts: bias, accuracy by horizon, the minimum-cash covenant and what the latest forecast...
Get the dataThe steps
- Load the bank lines, the invoices and the treasury calendar — three sheets of one Excel workbook — and take the bank balance at the close of the day before the forecast.
- Find the invoices still open at that close: posted by then and not yet settled.
- Expect each customer invoice on its due date plus that customer's average delay last year, and each supplier invoice in the first Monday payment run on or after its due date.
- Add the treasury calendar — payroll, tax, interest and dividends — for the 13 weeks.
- Put everything into weeks, add up from the opening balance, and compare the week-13 balance with what the bank really showed.
The code
Download the L1 cash flow set (DD-CFP-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl).
import numpy as np
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
workbook = "DD-CFP-001_CashFlowPlanning.xlsx"
start = pd.Timestamp("2026-01-01") # the forecast is made on 1 January
close = pd.Timestamp("2025-12-31") # ... from the books at the close of 31 December
end = pd.Timestamp("2026-04-01") # the last day of week 13
bank = pd.read_excel(workbook, sheet_name="FactBankTransactions")
invoices = pd.read_excel(workbook, sheet_name="FactInvoices")
calendar = pd.read_excel(workbook, sheet_name="FactCashCalendar")
def week(dates):
# Week 1 = 1-7 January, week 2 = 8-14 January ... anything earlier counts in week 1.
return ((dates - start).dt.days // 7 + 1).clip(lower=1)
# 1. Open invoices at the close of 31 December: posted by then, not yet settled.
is_open = (invoices["PostingDate"] <= close) & ~(invoices["SettledDate"] <= close)
cust = invoices[is_open & (invoices["InvoiceType"] == "Customer invoice")].copy()
supp = invoices[is_open & (invoices["InvoiceType"] == "Supplier invoice")].copy()
# 2. Receipts: each customer pays as late as it did last year (average days after due date).
paid = invoices[(invoices["InvoiceType"] == "Customer invoice")
& (invoices["SettledDate"].dt.year == close.year)]
late = (paid["SettledDate"] - paid["DueDate"]).dt.days.groupby(paid["CounterpartyCode"]).mean()
delay = np.sign(late) * np.floor(late.abs() + 0.5) # whole days, halves away from zero
days = cust["CounterpartyCode"].map(delay).fillna(0) # no history: on the due date
cust["Week"] = week(cust["DueDate"] + pd.to_timedelta(days, unit="D"))
# 3. Payments: suppliers are paid in the Monday payment run on or after the due date.
monday = supp["DueDate"] + pd.to_timedelta((7 - supp["DueDate"].dt.weekday) % 7, unit="D")
supp["Week"] = week(monday)
# 4. Known items from the treasury calendar: payroll, tax, interest, dividends.
cal = calendar[calendar["PlannedDate"].between(start, end)].copy()
cal["Week"] = week(cal["PlannedDate"])
# 5. The 13-week table, from the bank balance at the close of 31 December.
weeks = pd.DataFrame({
"Receipts": cust.groupby("Week")["Amount"].sum(),
"Payments": -supp.groupby("Week")["Amount"].sum(), # invoices are stored positive
"Calendar": cal.groupby("Week")["PlannedAmount"].sum(),
}).reindex(range(1, 14)).fillna(0) # anything after week 13 is outside the forecast
weeks["Net"] = weeks.sum(axis=1)
opening = bank.loc[bank["TransactionDate"] <= close, "Amount"].sum()
weeks["Closing"] = opening + weeks["Net"].cumsum()
print(f"Opening balance 31 Dec 2025: {opening:,.2f}\n")
print(weeks.to_string(), "\n")
actual = bank.loc[bank["TransactionDate"] <= end, "Amount"].sum()
print(f"Forecast at 1 Apr 2026: {weeks['Closing'].iloc[-1]:,.2f} actual: {actual:,.2f}")
print(f"Forecast error: {weeks['Closing'].iloc[-1] - actual:,.2f}")
print(f"Lowest forecast balance: end of week {weeks['Closing'].idxmin()}")
What it prints
The forecast made on 1 January 2026, exactly as the script prints it:
Opening balance 31 Dec 2025: 24,730,084.28
Receipts Payments Calendar Net Closing
Week
1 1,771,499.51 -2,106,196.32 0.00 -334,696.81 24,395,387.47
2 1,392,279.11 -530,802.39 0.00 861,476.72 25,256,864.19
3 1,391,416.41 -849,630.90 0.00 541,785.51 25,798,649.70
4 1,429,272.00 -1,232,795.83 -1,091,000.00 -894,523.83 24,904,125.87
5 1,301,561.38 -508,507.75 -99,000.00 694,053.63 25,598,179.50
6 1,038,679.18 -577,133.96 0.00 461,545.22 26,059,724.72
7 1,384,835.13 -443,873.34 0.00 940,961.79 27,000,686.51
8 743,377.18 -218,704.95 0.00 524,672.23 27,525,358.74
9 517,478.10 -174,739.06 -1,190,000.00 -847,260.96 26,678,097.78
10 276,723.92 0.00 0.00 276,723.92 26,954,821.70
11 109,508.87 0.00 -159,000.00 -49,491.13 26,905,330.57
12 105,737.10 0.00 0.00 105,737.10 27,011,067.67
13 70,427.08 0.00 -1,190,000.00 -1,119,572.92 25,891,494.75
Forecast at 1 Apr 2026: 25,891,494.75 actual: 25,683,203.46
Forecast error: 208,291.29
Lowest forecast balance: end of week 1
How to read the result
- The forecast starts from 24,730,084.28 in the bank and expects 11,532,794.97 from customers, 6,642,384.50 to suppliers and 3,729,000.00 net out for payroll, tax, interest and dividends.
- It ends week 13 at 25,891,494.75. The bank really closed 1 April 2026 at 25,683,203.46, so the forecast was 208,291.29 too high — within 1%.
- The lowest point is the end of week 1, when every overdue supplier invoice falls into the first payment run. That is the week to watch.
- The opening balance, the forecast receipts, payments and calendar items, the week-13 forecast, the actual balance and the lowest week are all questions in the set, so each figure can be checked in its Question Book.
Mistakes this data will catch
- Expecting customers to pay on the due date. They do not; each customer's delay last year is the simplest honest forecast, and terms alone overstate the receipts.
- Leaving overdue invoices out. Anything already past due is expected in week 1, not dropped.
- Forgetting the signs. Supplier invoices are stored as positive amounts; payments leave the bank, so turn them negative.
- Rounding the delay with numpy's round. It rounds 2.5 to 2; the rule here rounds half a day away from zero.
The next levels
The L2 set (DD-CFP-002) comes as CSV, JSON, Excel and Parquet files for a group: several entities, cash categories, credit facilities, the treasury calendar and earlier forecasts to test against the bank. The same weekly table carries over, entity by entity.
Python or Power BI
Python suits the weekly forecast itself: the same rules run every Monday on the latest open items. Power BI suits the treasury view, where actual and forecast sit side by side week by week.
The course
- Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
- Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
- Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
- Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
- Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...