Cash Flow Forecasting

Cash Flow Forecasting

Overview

Cash flow forecasting predicts when money actually enters and leaves your bank account — not when revenue is recognized. For ecommerce businesses, cash and revenue timing diverge significantly: payment processors hold funds for days or weeks, inventory must be purchased and paid for weeks before it sells, and marketplace disbursements are bi-weekly or delayed by account reviews.

This skill guides you through building a 13-week rolling cash flow forecast using your platform's data, covering the key cash timing differences by sales channel, and setting up a simple model in a spreadsheet or BI tool that you update weekly.

When to Use This Skill

• When managing cash position and needing to know your runway
• When planning inventory buys and needing to confirm you have the cash to fund them
• When preparing for a fundraise and needing to show investors a 12–18 month cash model
• When stress-testing the business against a revenue shortfall scenario
• When selling on Amazon and managing bi-weekly disbursement timing
• When planning for seasonal cash flow gaps (Q1 trough after Q4 peak)

Core Instructions

Step 1: Pull your historical revenue and expense data from your platform

Before building a forecast, gather 12 months of actuals. Here is where to find the data by platform:

---

Shopify

1. Revenue data: Go to Analytics → Reports → Sales over time — export to CSV; this gives you daily/weekly/monthly gross sales, discounts, returns, and net sales 2. Order-level data: Go to Analytics → Reports → Orders over time — shows order count and AOV trends useful for projecting future orders 3. Finance summary: Go to Analytics → Finances summary — shows gross sales, discounts, returns, shipping charged, and net sales for any date range 4. Payouts: Go to Finances → Payouts — shows exactly when Shopify Payments transferred funds to your bank and the amount; this is your actual cash inflow history, not just revenue recognition 5. Export orders: Go to Orders → Export — download all orders with financial data; use this to build a detailed spreadsheet model

Shopify Payments cash timing:

• Standard payout: 2–3 business days after the transaction
• After confirming your payout schedule in Finances → Payout schedule, build this lag into your inflow model

---

WooCommerce

1. Revenue data: Go to WooCommerce → Analytics → Revenue — shows gross revenue, refunds, coupons, net revenue, and taxes by day/week/month; export to CSV 2. Orders export: Go to WooCommerce → Orders → Export — download order history with payment method, dates, and amounts 3. Payment gateway timing: Check your gateway dashboard (Stripe, PayPal, Square) for payout history and typical lag:

• Stripe: typically 2 business days
• PayPal: 1–3 business days (can vary for new accounts)

4. Expenses: WooCommerce does not track expenses natively; pull these from your accounting system (QuickBooks, Xero) or bank statements

---

BigCommerce

1. Go to Analytics → Store Overview — shows revenue, orders, and conversion trends; export to CSV 2. Go to Analytics → Purchase Funnel and Analytics → Marketing for additional revenue breakdowns 3. Use BigCommerce's data export API or connect via Stitch or Fivetran to pull order data into a spreadsheet or warehouse for multi-month analysis

---

Step 2: Map cash timing by channel

The most important step is understanding the lag between when revenue is earned and when cash arrives. Build a channel-by-channel timing table:

Build this timing into your cash inflow schedule: For each week's projected revenue, create a separate row showing when that cash actually arrives. Revenue earned this week from Amazon arrives in the week two weeks from now.

Step 3: Build a 13-week rolling cash flow model

Use a spreadsheet (Google Sheets or Excel) with this structure. Update it every Monday morning.

Model structure (one column per week, 13 weeks forward):

                          WK1    WK2    WK3    WK4    ...  WK13
OPENING CASH BALANCE

OPERATING INFLOWS
  + Shopify Payments (2-day lag from prior week sales)
  + Amazon disbursement (bi-weekly; map exact dates)
  + PayPal settlements
  + B2B/wholesale payments (net-30 invoices due this week)

OPERATING OUTFLOWS
  - Inventory purchases (PO payments due this week)
  - Inbound freight & duties
  - 3PL / fulfillment fees (typically billed weekly)
  - Outbound shipping not passed to customer
  - Marketing & ad spend (credit card charge date, not spend date)
  - Payroll (exact pay dates)
  - Platform/software subscriptions
  - Rent & facilities
  - Customer refunds (process in same week issued)
  - Sales tax remittances (quarterly — schedule known dates)

NET WEEKLY CASH FLOW
CLOSING CASH BALANCE

Getting outflow data:

• Inventory payments: Pull open purchase orders from your supplier portal or inventory system; note the due date for each PO
• Marketing spend: Check your credit card statement for when ad platform charges clear (usually monthly billing cycle); Meta and Google bill in arrears or when threshold is reached
• Payroll: Use exact pay dates from your payroll system (Gusto, ADP, Rippling)

Step 4: Build base / bear / bull scenarios

Run three scenarios:

Critical rule: In the bear case, reduce inflows by the revenue shortfall percentage but do NOT reduce fixed outflows (payroll, rent, software). Only variable costs (COGS, marketing, variable fulfillment) should scale with revenue. This is the most common forecasting mistake — in a revenue downturn, fixed costs remain, which dramatically worsens cash position.

Runway calculation: Find the first week in the bear case where the Closing Cash Balance reaches your minimum viable cash threshold (typically 4–6 weeks of fixed operating costs). The number of weeks until that point is your runway under the bear case.

Step 5: Connect to live data for automatic updates

---

Shopify

Use Shopify's built-in Finances export (scheduled weekly) or connect via:

• Shopify + Google Sheets: Install the Sheets for Shopify app to sync orders and payouts automatically into your cash flow spreadsheet
• Shopify + Xero/QuickBooks: Use the Xero or QuickBooks Shopify integration to sync payout data into your accounting system, then build your forecast in your accounting tool

WooCommerce

• Connect WooCommerce → QuickBooks (via the official WooCommerce QuickBooks plugin) or WooCommerce → Xero (via the WooCommerce Xero extension)
• Use Metorik to export weekly revenue data into a CSV that feeds your spreadsheet model

Dedicated cash flow tools (all platforms)

These tools connect to your bank, payment processors, and ecommerce platforms to build automated cash flow forecasts:

• Float (floatapp.com): Connects to Xero/QuickBooks; great for 13-week cash forecasting
• Pulse (pulseapp.com): Simple cash flow tool with manual and bank-feed inputs
• Dryrun: More advanced; supports scenario modeling and connects to accounting systems
• Runway (runway.com): More comprehensive financial planning tool; connects to QuickBooks/Xero

Best Practices

• Update the 13-week forecast every Monday morning — use prior-week actuals to replace the oldest week's projection and roll the forecast forward one week
• Reconcile the forecast to your actual bank balance weekly — if there is a gap between forecast and actual closing balance, find it before it compounds
• Model Amazon payment timing explicitly — Amazon bi-weekly disbursements are the single largest source of cash timing surprises for marketplace sellers; map the exact disbursement dates for the next 13 weeks
• Build a minimum viable cash threshold — define the minimum cash balance needed to operate; alert yourself when the forecast shows cash approaching this floor
• Track the ad spend cash outflow date, not the spend date — Meta and Google bill monthly or when a threshold is reached; the credit card payment date is when cash leaves, not when impressions run
• Schedule tax remittances as known outflows — sales tax remittances, quarterly estimated income taxes, and VAT payments are predictable; put them on the calendar in the model

Common Pitfalls

Related Skills

• @ecommerce-budgeting-forecasting
• @financial-reporting-dashboard
• @profit-margin-analysis
• @unit-economics-tracking
• @marketplace-fee-reconciliation

13-Week Cash Flow Model for an Ecommerce Operator

Problem/Feature Description

Birchwood Supply Co. is an ecommerce business selling home organization products. Their operations team has been running the business on gut feel and a spreadsheet, but after nearly running out of cash during a slow January (they had ordered heavily for Q4 and the holiday revenue came in later than expected), the CEO has decided to build a proper short-term cash flow model.

They need a Python script that produces a 13-week forward-looking cash flow, giving them clear visibility into their net operating cash position each week. The business buys inventory from overseas suppliers who require payment 30 days after warehouse receipt, and the lead time from order placement to receipt is 45 days. Current inventory on hand is worth approximately $180,000 and they use a 40% COGS rate. The team targets maintaining 8 weeks of forward stock coverage.

The script also needs to handle the fact that some large outflows are not monthly — they have a $28,000 annual software contract renewal in week 6 of the forecast, a $15,000 trade show expense in week 9, and they remit quarterly sales tax at the end of the quarter (approximately $22,000 due in week 11). These are easily forgotten but material.

Finally, the ops team knows exactly what is happening in the first four weeks: they have a confirmed $95,000 purchase order payment due in week 2, scheduled payroll of $18,000 per week, and two confirmed customer payments totalling $67,000 arriving in week 3. They want the model to use these concrete commitments for the near term rather than relying on averages or models.

Output Specification

Produce a Python script cash_model.py that:

1. Generates a 13-week (91-day) cash flow forecast starting from an opening balance of $450,000. 2. Models inventory purchase order timing and payment dates based on the supplier terms and stock coverage target described above, using a sales forecast of $85,000/week base case. 3. Includes the one-time outflows (software renewal, trade show, sales tax remittance) at the specific weeks described above. 4. Uses the confirmed transaction amounts for weeks 1–4 (where specified above) rather than model-derived estimates. 5. Produces a week-by-week table showing: week number, total inflows, total outflows, net cash flow, and running cash balance.

The script should run with standard Python libraries. Output to console or a CSV file.

Cash Inflow Projection for a Multi-Channel DTC Brand

Problem/Feature Description

Meridian Home Goods is a direct-to-consumer brand selling premium kitchenware across four channels: their own Shopify storefront (processed through Shopify Payments), Amazon FBA, eBay, and a growing B2B wholesale line (Net-30 terms) with independent kitchen retailers. The finance team currently tracks sales in their ERP but has no visibility into when that revenue will actually land in their bank accounts. They need a Python script that takes a week-by-week sales forecast by channel and produces a precise cash inflow schedule — showing the expected cash receipt date and amount for each sale, not just the sale date.

The team has also flagged that their eBay storefront requires a reserve that is held back, and their B2B accounts occasionally result in uncollectible invoices. They want the model to reflect these realities. Additionally, their cookware and tableware products have a meaningful customer return rate (approximately 18%) and they want returns modeled as a cash cost in the schedule as well.

Finally, the business runs monthly paid social campaigns billed in arrears by the ad platforms. The finance team has been burned before when a large ad bill hit the bank account weeks after the spend occurred; they want the script to account for this correctly in the outflow schedule.

Output Specification

Produce a single Python script cash_projection.py that:

1. Accepts or hardcodes a sample weekly revenue forecast dataset (at least 8 weeks, covering the channels listed below) as input data defined within the script itself. 2. Computes a projected cash inflow schedule — a table/DataFrame with columns for the sale date, expected cash receipt date, channel, gross revenue, and expected cash received. 3. Models the return cash outflows for the cookware/tableware category at an 18% return rate, adding these as negative cash entries to the schedule. 4. Models the ad spend cash outflow schedule (use a sample monthly ad budget of $12,000 billed on the last day of the month with payment clearing 5 days later). 5. Outputs a combined cash schedule (inflows and outflows) sorted by cash date, and a weekly summary showing total net cash by week.

The script should run with standard Python data science libraries (pandas, numpy, etc.) and produce console output or a CSV file showing the results.

Input Files (optional)

No external input files are required. All sample data should be generated or hardcoded within the script.

Cash Runway Forecast for Seasonal Ecommerce Business

Problem/Feature Description

Solstice Outdoor Gear is a seasonal ecommerce company selling camping and hiking equipment. Their revenue peaks sharply in May–August (summer) and has a smaller secondary peak in November–December (holiday gifts). The founding team is preparing for a Series A pitch and their lead investor wants to see a rigorous 52-week cash flow projection showing how long the business can operate under different revenue outcomes before needing to raise.

The CFO has three years of weekly revenue data and wants a Python script that: (1) fits a forecasting model to the historical data to project the next 52 weeks of revenue, capturing both the upward trend and the seasonal pattern; (2) converts that revenue forecast into a cash runway model by combining it with the company's known weekly fixed costs ($42,000/week: payroll $25k, rent $8k, software/ops $9k) and variable costs (COGS at 38% of revenue, marketing at 12% of revenue); (3) shows the runway under multiple revenue scenarios so the board can see the downside risk clearly.

The company has $1,200,000 in the bank today. The script should show the investor exactly when cash hits zero under each scenario — or confirm there is no cash-out event within the 52-week window.

Output Specification

Produce a Python script runway_forecast.py that:

1. Generates or hardcodes 3 years of synthetic historical weekly revenue data that exhibits seasonal patterns consistent with an outdoor gear business (summer peak, smaller Q4 peak, weekly granularity = 156 data points). 2. Fits a forecasting model to the historical data and produces a 52-week forward forecast with confidence bands. 3. Computes net weekly cash flow for each scenario by combining the revenue forecast with weekly fixed costs ($42,000) and variable costs (COGS 38%, marketing 12%). 4. Calculates the cash runway for each scenario starting from an opening cash balance of $1,200,000. 5. Outputs a results summary (to console or CSV) showing: scenario name, weeks until cash reaches zero (or ">52 weeks"), minimum cash balance, and the week of minimum balance.

Delete any intermediate large files if generated. The script should run end-to-end with standard Python data science libraries.