Build a 13-Week Cash Flow Forecast Report in Excel with Python

A 13-week cash flow forecast answers the question a profit forecast cannot: will there be enough money in the bank each week of the next quarter? It is built bottom-up from things that already exist — invoices customers owe, bills the business owes, payroll and rent that recur — placed in the week the cash will actually move. Done by hand in Excel it is a weekly chore of copying ledgers and dragging dates. Built in Python it becomes a script that reads the ledger exports, applies the timing rules once, and writes the same report every Monday. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

ledgers.xlsx holds the exports a finance system typically provides: AR, the open customer invoices with due dates; CustomerHistory, each customer's average days paid late over the last year; and AP, the open supplier bills. Recurring commitments that are not in either ledger yet — payroll, rent, a loan repayment — are defined in the script. The forecast starts on Monday 5 October 2026 with 90,000 in the bank.

Step 1 — Set the horizon and the weeks

Everything is bucketed into weeks starting on Monday. Define the start, the horizon and a helper that maps any date to the Monday of its week:

import pandas as pd

START = pd.Timestamp("2026-10-05")        # a Monday
WEEKS = 13
OPENING_CASH = 90_000.00
MIN_CASH = 60_000.00

weeks = pd.date_range(START, periods=WEEKS, freq="W-MON")
end = weeks[-1] + pd.Timedelta(days=6)

def week_of(dates):
    return dates - pd.to_timedelta(dates.dt.weekday, unit="D")

print(weeks[0].date(), "to", end.date())

Step 2 — Expected receipts from receivables

A due date is when the customer should pay; the forecast needs when they will. Shift each invoice by that customer's average days late. Invoices already overdue are assumed to arrive in the first week at the earliest, rather than in the past:

ar = pd.read_excel("ledgers.xlsx", sheet_name="AR", parse_dates=["DueDate"])
history = pd.read_excel("ledgers.xlsx", sheet_name="CustomerHistory")

ar = ar.merge(history, on="Customer", how="left", validate="many_to_one")
ar["AvgDaysLate"] = ar["AvgDaysLate"].fillna(30)   # unknown customers: assume a month late
ar["Expected"] = ar["DueDate"] + pd.to_timedelta(ar["AvgDaysLate"], unit="D")
ar["Expected"] = ar["Expected"].clip(lower=START)
ar["Week"] = week_of(ar["Expected"])

beyond = ar[ar["Expected"] > end]
print(f"{len(ar)} invoices, {ar['Amount'].sum():,.2f} open; "
      f"{len(beyond)} expected after the horizon ({beyond['Amount'].sum():,.2f})")
print(ar.groupby("Customer")["Amount"].sum().round(2).sort_values(ascending=False).to_string())

The fallback for customers with no history is a decision to make visibly and review; 30 days late is deliberately cautious. More careful forecasts use a distribution of payment delays per customer, but the average is where most teams start, and it can be checked each week against what actually arrived. Build an Accounts Receivable Aging Report produces the payment history this step depends on.

Step 3 — Payments to suppliers

Supplier bills are paid on their due date, or on the next payment run. Here payments go out on Thursdays, so each bill moves to the Thursday on or after its due date, which keeps it in the same Monday-to-Sunday week unless it falls due on Friday to Sunday:

ap = pd.read_excel("ledgers.xlsx", sheet_name="AP", parse_dates=["DueDate"])
days_to_thursday = (3 - ap["DueDate"].dt.weekday) % 7
ap["PayDate"] = (ap["DueDate"] + pd.to_timedelta(days_to_thursday, unit="D")).clip(lower=START)
ap["Week"] = week_of(ap["PayDate"])
print(ap[["Bill", "DueDate", "PayDate"]].head(4).to_string(index=False))

Step 4 — Recurring commitments

Payroll, rent and loan repayments are known in advance but not yet billed. Generate them from a small schedule so they are visible and editable in one place:

recurring = pd.concat([
    pd.DataFrame({"Item": "Payroll", "Date": pd.date_range("2026-10-09", end, freq="2W-FRI"), "Amount": 46_500.00}),
    pd.DataFrame({"Item": "Rent", "Date": pd.date_range("2026-10-01", end, freq="MS"), "Amount": 18_500.00}),
    pd.DataFrame({"Item": "Loan repayment", "Date": pd.date_range("2026-10-01", end, freq="MS") + pd.Timedelta(days=14), "Amount": 7_250.00}),
], ignore_index=True)
recurring = recurring[recurring["Date"].between(START, end)]
recurring["Week"] = week_of(recurring["Date"])
print(recurring.groupby("Item")["Amount"].agg(["count", "sum"]).to_string())

October's rent falls on the 1st, before the forecast starts, so it is excluded: it has already left the account and is in the opening balance. The loan line uses the same month starts as rent, shifted by 14 days to the 15th; starting that range on the 15th itself would skip October, because the first month start after 15 October is 1 November.

Step 5 — Receipts from sales not yet invoiced

Open invoices run out after a few weeks, because next month's sales have not been billed yet. A forecast built only from the ledger therefore always looks bleak at the end. Add an estimate for future billings: here a run rate of 20,000 a week, collected on average 35 days after the week it is billed. Keep it on its own line so nobody mistakes an estimate for an invoice:

RUN_RATE = 20_000.00
COLLECT_AFTER = pd.Timedelta(days=35)
new_sales = pd.DataFrame({"Date": weeks + COLLECT_AFTER, "Amount": RUN_RATE})
new_sales = new_sales[new_sales["Date"] <= end]
new_sales["Week"] = week_of(new_sales["Date"])
print(f"{len(new_sales)} weeks of estimated receipts, first on {new_sales['Date'].min().date()}")

Base the run rate on the last quarter's billings and revisit it monthly; it is the least certain line in the forecast, and the first to adjust when sales change.

Step 6 — Bucket by week and roll the balance forward

Stack the three sources into one table of signed cash movements, pivot to one column per week, and roll the balance forward: each week's closing balance is the next week's opening balance.

flows = pd.concat([
    pd.DataFrame({"Line": "Customer receipts", "Week": ar["Week"], "Amount": ar["Amount"]}),
    pd.DataFrame({"Line": "New sales (estimate)", "Week": new_sales["Week"], "Amount": new_sales["Amount"]}),
    pd.DataFrame({"Line": "Supplier payments", "Week": ap["Week"], "Amount": -ap["Amount"]}),
    pd.DataFrame({"Line": recurring["Item"], "Week": recurring["Week"], "Amount": -recurring["Amount"]}),
])
flows = flows[flows["Week"].isin(weeks)]
lines = ["Customer receipts", "New sales (estimate)", "Supplier payments", "Payroll", "Rent", "Loan repayment"]
grid = (flows.pivot_table(index="Line", columns="Week", values="Amount", aggfunc="sum", fill_value=0)
             .reindex(index=lines, columns=weeks, fill_value=0))

net = grid.sum()
closing = OPENING_CASH + net.cumsum()
opening = closing.shift(1, fill_value=OPENING_CASH)
summary = pd.DataFrame({"Opening": opening, "Net flow": net, "Closing": closing}).round(2)
summary.index = summary.index.strftime("%d %b")
print(summary.to_string())
low = summary[summary["Closing"] < MIN_CASH]
print("weeks below minimum cash:", ", ".join(low.index) or "none")

Four weeks fall below the minimum: the first week, when payroll and a cluster of supplier bills leave before receipts arrive; two more later in October, when the second payroll and then November's rent go out before the larger receipts of mid-November; and the last week of the year, when payroll and rent land together. Those weeks are the reason the report exists: they tell the treasurer when to chase a large receivable, delay a discretionary payment or draw on a credit line — weeks before it becomes urgent.

Step 7 — Write the report with live balance formulas

The reader will want to try changes: move a receipt a week later, add a one-off payment. Write the flows as values and the balances as formulas, so the workbook recalculates when a cell is edited. Weeks go across, lines go down, the way treasury teams read a cash forecast:

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell

wb = xlsxwriter.Workbook("cash_forecast.xlsx")
ws = wb.add_worksheet("13-week forecast")
money = wb.add_format({"num_format": "#,##0;[Red]-#,##0"})
bold_money = wb.add_format({"num_format": "#,##0;[Red]-#,##0", "bold": True, "top": 1})
head = wb.add_format({"bold": True, "bg_color": "#EEF2FF", "align": "center"})
label = wb.add_format({"bold": True})
low_fill = wb.add_format({"bg_color": "#FDE2E2", "font_color": "#9B1C1C"})

ws.write(0, 0, "Cash flow forecast from 5 October 2026", label)
ws.write(1, 0, "Week starting", head)
for c, wk in enumerate(weeks, start=1):
    ws.write(1, c, wk.strftime("%d %b"), head)

ws.write(2, 0, "Opening balance", label)
for r, line in enumerate(lines, start=3):
    ws.write(r, 0, line)
    for c, wk in enumerate(weeks, start=1):
        ws.write_number(r, c, float(grid.loc[line, wk]), money)
net_row, close_row = 3 + len(lines), 4 + len(lines)
ws.write(net_row, 0, "Net cash flow", label)
ws.write(close_row, 0, "Closing balance", label)
ws.write(close_row + 2, 0, "Minimum cash")
ws.write_number(close_row + 2, 1, MIN_CASH, money)

for c in range(1, WEEKS + 1):
    opening_ref = "=" + xl_rowcol_to_cell(close_row, c - 1) if c > 1 else OPENING_CASH
    if c == 1:
        ws.write_number(2, c, OPENING_CASH, money)
    else:
        ws.write_formula(2, c, opening_ref, money)
    first, last = xl_rowcol_to_cell(3, c), xl_rowcol_to_cell(net_row - 1, c)
    ws.write_formula(net_row, c, f"=SUM({first}:{last})", bold_money)
    ws.write_formula(close_row, c, f"={xl_rowcol_to_cell(2, c)}+{xl_rowcol_to_cell(net_row, c)}", bold_money)

ws.conditional_format(close_row, 1, close_row, WEEKS, {
    "type": "cell", "criteria": "<", "value": f"$B${close_row + 3}", "format": low_fill})
ws.set_column(0, 0, 20)
ws.set_column(1, WEEKS, 10)
ws.freeze_panes(2, 1)

chart = wb.add_chart({"type": "line"})
chart.add_series({"name": "Closing balance",
                  "categories": ["13-week forecast", 1, 1, 1, WEEKS],
                  "values": ["13-week forecast", close_row, 1, close_row, WEEKS]})
chart.set_title({"name": "Closing cash by week"})
chart.set_legend({"none": True})
ws.insert_chart(close_row + 4, 1, chart, {"x_scale": 1.6})

detail = wb.add_worksheet("Receipts detail")
cols = ["Invoice", "Customer", "DueDate", "AvgDaysLate", "Expected", "Amount"]
detail.write_row(0, 0, cols, head)
datefmt = wb.add_format({"num_format": "yyyy-mm-dd"})
for r, row in enumerate(ar.sort_values("Expected")[cols].itertuples(index=False), start=1):
    detail.write(r, 0, row.Invoice)
    detail.write(r, 1, row.Customer)
    detail.write_datetime(r, 2, row.DueDate.to_pydatetime(), datefmt)
    detail.write_number(r, 3, row.AvgDaysLate)
    detail.write_datetime(r, 4, row.Expected.to_pydatetime(), datefmt)
    detail.write_number(r, 5, row.Amount, money)
detail.set_column(0, 5, 14)
wb.close()
print("wrote cash_forecast.xlsx")

The receipts detail sheet is what makes the forecast arguable: when someone asks why a week looks thin, the answer is a list of invoices and the dates they are expected. Supplier payments deserve the same sheet in a production version.

Step 8 — Check the formulas agree with pandas

Recalculate the file with LibreOffice and compare the closing balances Excel would show with the ones pandas computed. xlsxwriter stores 0 as the cached result of every formula and LibreOffice trusts cached results in .xlsx files by default, so save a copy through openpyxl first, which drops the cached values and forces a recalculation:

from openpyxl import load_workbook

load_workbook("cash_forecast.xlsx").save("cash_forecast_nocache.xlsx")

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated cash_forecast_nocache.xlsx > /dev/null 2>&1
ls recalculated

ws = load_workbook("recalculated/cash_forecast_nocache.xlsx", data_only=True)["13-week forecast"]
excel_closing = [ws.cell(row=close_row + 1, column=c).value for c in range(2, WEEKS + 2)]
diff = max(abs(a - b) for a, b in zip(excel_closing, summary["Closing"]))
print(f"largest difference between workbook and pandas: {diff:.6f}")

Recalculate Excel Formulas Without Excel in Python explains the LibreOffice step and the alternatives.

Keeping the forecast honest

A forecast earns trust by being compared with what happened. Each Monday, before rolling forward, keep last week's forecast and set it beside the actual bank movements: receipts forecast against received, payments forecast against paid. The differences show which assumption is wrong. A customer whose average days late is stale, a supplier paid early, a payroll that grew. Save each week's output with its date in the name so the comparison is always possible; Build a Bank Reconciliation Report provides the actual side.

Common pitfalls

• Forecasting receipts on due dates. Customers pay late, and some always do; use their history, or the forecast is optimistic every week.
• Past-dated flows. Overdue invoices and bills land before the start date; clip them into week one or they vanish from the forecast.
• Double counting. A payroll that is already in AP as a bill must not also be generated as recurring.
• Items beyond the horizon. Report how much falls after week 13; it is still cash to come.
• Hard-coded balances. Write closing balances as formulas so edits flow through the remaining weeks.

Conclusion

Build the forecast from four sources: receivables shifted by each customer's days late, payables moved to the next payment run, recurring commitments generated from a schedule, and a clearly labelled estimate for sales not yet invoiced. Bucket everything into Monday-starting weeks with pivot_table, roll the balance forward with cumsum, and flag weeks below minimum cash. Write flows as values and balances as formulas, recalculate once to confirm the workbook matches pandas, and compare each week's forecast with the actuals.

Frequently asked questions

Why 13 weeks? Thirteen weeks is one quarter: long enough to see a shortfall coming and arrange financing, short enough that the receipts and payments are mostly known invoices rather than guesses.

How do I estimate when customers will pay? Use each customer's average days late from their payment history, and review it against actual receipts every week. Customers without history get a cautious default.

Should the forecast use formulas or values? Write the individual flows as values from Python and the opening, net and closing balances as formulas. The reader can then move a receipt or add a payment and see the effect on every later week.

How is this different from a budget? A budget is about profit and is usually monthly; a cash forecast is about the bank balance, uses actual invoice timing, and is usually weekly.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance reports.
• Build an Accounts Receivable Aging Report — where payment history comes from.
• Build a Budget vs Actual Variance Report — the profit view beside the cash view.
• Build a Bank Reconciliation Report — checking forecasts against the bank.
• Recalculate Excel Formulas Without Excel in Python — getting results into formula cells.