Automated 13-Week Cash Flow Forecast with Power Query: Stop Updating Spreadsheets Every Week

Introduction

Every Monday morning, someone in your finance team opens a spreadsheet and manually types in last week's bank balance. Then they update collections from the invoicing system. Then they copy-paste supplier payment dates from the AP system. By the time the forecast is current, it's Tuesday afternoon and half the week is gone.

A rolling cash flow forecast is one of the highest-value financial tools an SME can maintain. But its value drops to near-zero when it's updated manually-because the data is already stale by the time it's entered.

The fix isn't a new tool. It's Power Query.

Power Query (built into Excel 2016+) can connect directly to your accounting system's data export, your bank feed CSV, and your AP tracker. Set it up once, and your forecast refreshes in the time it takes to click "Refresh All."

This guide walks through the real setup I use with clients who operate on Xero, MYOB, and QuickBooks. The approach works with any system that can produce a CSV, XLSX, or API feed.

What a 13-Week Forecast Needs

Before we automate, we need to know what we're building. A 13-week rolling forecast needs five data sources:

Opening cash balance - usually from the bank feed or the last reconciled balance
Cash inflows - expected customer payments, mapped by expected receipt date, not invoice date
Cash outflows - supplier payments, payroll, BAS, rent, loan repayments
Timing adjustments - which customers pay in 7 days vs 30 days, which suppliers allow 60-day terms
Buffer/overdraft line - the minimum balance your business needs to operate without stress

The key insight: the forecast is only useful if it's current. If you're relying on someone to update it by hand, it won't be current after week two.

Step 1: Set Up Your Data Feeds in Power Query

Bank Balance Feed

Most Australian banks and accounting platforms can export a daily balance feed. The simplest approach is a CSV export scheduled through the platform.

In Power Query:

Go to Data > Get Data > From File > From Text/CSV
Select your bank balance export
Power Query will auto-detect columns. Set the date column to Date type, the balance column to Decimal.

Name this query BankBalances.

Accounts Receivable Feed

Export your outstanding invoices from Xero, MYOB or QuickBooks with expected payment dates. If your system doesn't have an "expected payment date" field, create a mapping table that applies your standard payment terms (e.g., all invoices to customers in "Construction" vertical are paid in 45 days on average).

Name this query AR_Aged.

Accounts Payable Feed

Same approach-export upcoming supplier payments with due dates. Include recurring payments: rent, leasing, insurance, loan repayments.

Name this query AP_Due.

From a client I worked with: A mid-sized construction firm was manually updating a 50-row cash flow forecast each week. The first time we ran Power Query against their Xero export, it flagged that their "cash balance" was actually $180K lower than they thought-because the manual spreadsheet had been pulling the wrong bank account for 6 months. The automated forecast paid for itself in that single correction.

Step 2: Build the Forecast Model

In a new Excel worksheet, create a table with 13 weekly columns starting from the current Monday. Here's the structure:

Row	Item	Week 1	Week 2	Week 3
1	Opening Balance	=PQ data	formula	formula
2	Cash In - Customer A
3	Cash In - Customer B
4	Cash In - Customer C
5	Total Inflows	=SUM(2:4)
6	Cash Out - Supplier X
7	Cash Out - Supplier Y
8	Payroll
9	BAS/GST
10	Rent & Overhead
11	Total Outflows	=SUM(6:10)
12	Net Cash Flow	=5-11
13	Closing Balance	=1+12	=next opening

The magic happens when you connect each row back to your Power Query tables using formulas.

For the AR rows, use a SUMIFS that checks: does the expected payment date for this customer fall within this week's date range?

=SUMIFS(AR_Aged[Amount], AR_Aged[CustomerName], "Customer A", AR_Aged[ExpectedDate], ">="&WeekStart, AR_Aged[ExpectedDate], "<="&WeekEnd)

For recurring fixed items (payroll, rent), just hardcode them in a parameter table that Power Query can also read from.

Step 3: Build the Power Query Merger

Here's the critical Power Query move: instead of keeping three separate queries, merge them into a single ForecastData query that Excel can pivot against.

In Power Query Editor:

Go to Home > Merge Queries
Select BankBalances as the primary table
Merge AR_Aged on the Date column (Left Outer)
Merge AP_Due on the Date column (Left Outer)
Expand both merged columns to bring in Amount fields
Add a custom column that labels each row: "Balance", "Receipt", "Payment"
Pivot the Amount column by Label
Load to worksheet

Now your forecast sheet has a single data table that updates every time you refresh. No weekly data entry.

Step 4: Add Scenario Capabilities

A forecast you can't adjust isn't a forecast-it's a wish. Add three quick scenario toggles:

Scenario 1: Delayed Payments. What if your 3 biggest customers all pay 14 days late? Create a parameter table with a "Delay Days" column. The forecast reads from this and shifts expected receipt dates.

Scenario 2: New Recurring Cost. Adding a staff member? Enter the start date and monthly cost in the parameters. The forecast automatically inserts the cash outflows from that date forward.

Scenario 3: Best/Worst Case. Apply a percentage multiplier to inflows and outflows. A 10% revenue drop scenario takes 30 seconds to run.

Power Query can read these parameters from a named range in Excel. Change the number, refresh, and the entire forecast recalculates.

Step 5: Automate the Refresh

The final step: make it so your team doesn't need to think about it.

Save your data exports (bank CSV, AR, AP) in a consistent folder location
Power Query reads from file paths defined in a parameter table
Set up a scheduled task (or Power Automate flow) to re-download the exports daily
When someone opens the workbook, or on a timed schedule, Excel refreshes the queries

For clients using Excel 365, I set up a simple VBA trigger:

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
End Sub

Now every time someone opens the file, they see the current forecast. No manual data entry required.

Why This Beats Most Cash Flow Apps

There's no shortage of cash flow forecasting tools on the market (Float, Fathom, Futrli, Spotlight). They're good products. But they miss a critical use case that Power Query handles naturally:

You already have 2 years of history in Excel that you want to keep.

Every SME I've worked with has an existing spreadsheet-some version of a cash flow tracker-that's been running for months or years. The data in it is more nuanced than any canned report from an app. It includes the one-off payments the CFO knows about but aren't in the system. It includes the informal adjustments ("Bob said he'll pay early this month").

Power Query lets you keep the existing spreadsheet as the interface while replacing the manual data entry with automation. You don't lose your history. You don't retrain your team on a new tool. You just stop typing numbers in.

Practical Tips from Real Implementations

Start with AP first. Accounts payable automation gives the fastest win. Most SMEs already know what's coming due-it's the inflows that are uncertain. Automate the easy half first, then tackle AR.

Use a date lookup table. Power Query works better with a standalone date dimension table. Create one with every date for the next 26 weeks, plus week numbers, month-end flags, and BAS quarter markers.

Build buffer into the columns. I always include an "Unexpected" row set at 5% of outflows. It's not a substitute for scenario analysis, but it prevents the forecast from looking perfectly funded when every real SME I know has surprise expenses.

Set a refresh cadence and stick to it. The forecast is only valuable if it's current. Daily data pull, weekly review of the output. The automation handles the pulling-your review cycle handles the decisions.

FAQs

Do I need Excel 365 for Power Query? Power Query is available in Excel 2016 and later on Windows. Excel 365 has the most features, but the core functionality (CSV import, merge queries, unpivot) works in 2016 and 2019.

Can I connect directly to my accounting software? Yes, if your platform supports ODBC or has a direct Power Query connector. Xero and QuickBooks have community connectors. Most SMEs find the CSV export approach simpler and equally reliable.

How long does the initial setup take? Two to three hours for a first-time build. Half that if you already have a working forecast in Excel and just need to wire up the Power Query feeds.

What if my data sources change? Power Query transformations are saved as steps. If your bank changes its CSV format, you edit one step in the query rather than rewriting formulas. That alone is worth the setup time.