Before You Start

This guide assumes you have intermediate Excel skills and access to historical financial data (e.g., bank statements, P&L, balance sheet). Ensure you have a clear understanding of your key revenue drivers and expense categories.

Overview

50 min
Estimated Time
Intermediate
Difficulty
Weekly
Maintenance

What You’ll Learn

  • How to structure your Excel workbook for clarity and efficiency
  • Projecting cash inflows from various sources
  • Forecasting cash outflows for operational expenses and capital expenditures
  • Calculating net cash flow and ending cash balances for 13 weeks
  • Identifying potential cash shortages or surpluses

1. Preparation Steps

Gather these essential data points to ensure your forecast is accurate:

Required Data

  • Current Bank Balances (start of forecast period)
  • Accounts Receivable aging report (expected collections)
  • Accounts Payable aging report (expected payments)
  • Payroll schedule and amounts
  • Recurring fixed expenses (rent, utilities, subscriptions)

Key Assumptions Needed

  • Sales projections (volume and average price)
  • Customer payment terms (e.g., Net 30)
  • Vendor payment terms
  • Planned capital expenditures or investments
  • Seasonal variations impacting revenue/expenses

2. Key Components of the Model

A 13-week cash flow model focuses on two main flows: cash coming in and cash going out.

Cash Inflows

These are the sources of cash entering your business.

Common Inflows:
  • Customer Payments (Sales)
  • Loan Disbursements
  • Owner Contributions
  • Interest Income
Exclude (Non-Cash):
  • Accounts Receivable (until collected)
  • Depreciation/Amortization
  • Accrued Revenue

Cash Outflows

These are the uses of cash leaving your business.

Common Outflows:
  • Payroll & Benefits
  • Vendor Payments (Accounts Payable)
  • Rent & Utilities
  • Debt Service Payments
Exclude (Non-Cash):
  • Accounts Payable (until paid)
  • Prepaid Expenses (until paid)
  • Accrued Expenses

3. Step-by-Step: Building Your Forecast

The core of the 13-week forecast involves tabulating your starting cash, adding expected inflows, subtracting expected outflows, and calculating your ending cash balance for each week. This balance then becomes the starting cash for the next week.

This iterative process helps visualize your liquidity over the short term. It’s crucial to map out recurring events and one-off transactions.

Below is a simplified view of the structure you’ll build:

Week 1 | Week 2 | ... | Week 13
-------|--------|-----|--------
Starting Cash:
+ Total Inflows:
  - Sales Receipts
  - Loan Funds
  - Other Income
- Total Outflows:
  - Payroll
  - Vendor Payments
  - Rent
  - Marketing
Ending Cash: (Starting + Inflows - Outflows)

Here is a sample data structure you might track for a single transaction.

{
  "transaction_id": "INV-2025-001",
  "type": "inflow",
  "description": "Customer Payment (Invoice #1001)",
  "amount": 5500.00,
  "expected_date": "2025-11-05",
  "category": "Sales Receipts"
}

4. Data Input and Formula Setup

  1. 1

    Create Base Worksheet

    Set up columns for each of the 13 weeks. Create rows for ‘Starting Cash’, ‘Inflows’, ‘Outflows’, and ‘Ending Cash’.

  2. 2

    Populate Inflows

    Input expected customer payments from your AR report, projected sales, and any other cash receipts based on payment terms and historical averages.

  3. 3

    Populate Outflows

    Add all expected payments for payroll, AP, rent, utilities, and other operating expenses. Be thorough with recurring and one-off expenses.

  4. 4

    Implement Formulas

    For each week, Ending Cash = Starting Cash + Total Inflows - Total Outflows. Ensure the ‘Ending Cash’ of one week flows directly to the ‘Starting Cash’ of the next.

Common Error: Double-Counting

Be careful not to double-count items. For example, if you project revenue, make sure you’re forecasting cash collections from that revenue, not the revenue itself if it’s on credit terms.

5. Testing and Validation

Validation Checklist

  • Compare actual past cash flows against forecast (if applicable)
  • Verify starting cash balance matches current bank statements
  • Check for any missing recurring payments or expected receipts
  • Review extreme fluctuations in weekly balances for logical explanation
  • Share with a colleague for a second pair of eyes

Need Help?

Get Support

Struggling to build or refine your cash flow model? Our financial experts can help customize a solution for your business.

Contact Us