
Cash Flow Projection in Excel: Step-by-Step Example
Published on:
Reading time: 8 min
Topic: Management
Author: Leandro Valencia
How to build a projected cash flow in Excel for your small business: structure, formulas, a four-month example and warnings before you run out of money.
Table of Contents
- What cash flow is (and what it is not)
- The three types of cash flow
- The sheet structure
- Example: a distributor that sells on credit
- The formulas in Excel or Google Sheets
- 13 weeks or 12 months
- How to improve cash flow
- Free cash flow and discounted cash flow
- Common mistakes
- Conclusion
Many small businesses that close do not close for lack of sales but for lack of cash: they sell, invoice and even show a profit, but one month they cannot meet payroll. A projected cash flow is the tool that warns you about that month weeks ahead.
In this guide you will build one in Excel or Google Sheets: which rows it needs, which formulas to use, a four-month example with numbers and the warning signs worth watching. You do not need to be an accountant.
The difference between profit and cash, and how cash fits into the complete financial plan, is covered in the basic financial plan for small businesses. Here we focus only on projecting cash.
What cash flow is (and what it is not)
Cash flow records money when it actually enters or leaves your accounts. Not when you invoice and not when you sign the contract.
| Event | Income statement (profit) | Cash flow |
|---|---|---|
| You sell 10 million on 60-day terms | Revenue of 10 million this month | 0 this month; 10 million in two months |
| You buy equipment for 4 million | Only the monthly depreciation | Outflow of 4 million the day you pay |
| You receive a 20 million loan | Not income | Inflow of 20 million |
| You pay the loan installment | Only the interest is an expense | The full installment goes out (principal + interest) |
That is why the two are tracked separately. The break-even point tells you whether the business is profitable; cash flow tells you whether you will have the money to stay open while you prove it.
The three types of cash flow
- Operating flow: collections from customers minus operating payments (suppliers, payroll, rent, taxes). It is the heart of the business.
- Investing flow: buying or selling equipment, vehicles, premises or software development.
- Financing flow: loans received, installments paid, partner contributions and dividends.
For a small business, one section per type within the same sheet is enough.
The sheet structure
The most practical approach for a small business is the direct method: you list every inflow and every outflow of money. The rows:
OPENING BALANCE
+ Cash sales collections
+ Credit sales collections (from prior months)
+ Other income (loans, contributions)
= TOTAL INFLOWS
− Supplier payments
− Payroll and social security
− Rent and utilities
− Taxes
− Loan installments
− Investments (equipment, software)
= TOTAL OUTFLOWS
CLOSING BALANCE = Opening balance + Total inflows − Total outflows
The columns are months (or weeks). The closing balance of each column becomes the opening balance of the next.
Example: a distributor that sells on credit
A B2B distributor invoices more and more, but collects 40% in cash and 60% at 30 days. It pays suppliers in cash. Illustrative figures in millions of Colombian pesos; in December it invoiced 18.
| Item | January | February | March | April |
|---|---|---|---|---|
| Invoiced sales (reference) | 20.0 | 22.0 | 25.0 | 25.0 |
| Opening balance | 8.0 | 7.8 | 8.6 | 6.3 |
| Cash collection (40% of the month) | 8.0 | 8.8 | 10.0 | 10.0 |
| 30-day collection (60% of prior month) | 10.8 | 12.0 | 13.2 | 15.0 |
| Total inflows | 18.8 | 20.8 | 23.2 | 25.0 |
| Suppliers (50% of sales) | 10.0 | 11.0 | 12.5 | 12.5 |
| Payroll | 6.0 | 6.0 | 6.0 | 6.0 |
| Rent and utilities | 2.0 | 2.0 | 2.0 | 2.0 |
| Taxes | — | — | — | 3.0 |
| Loan installment | 1.0 | 1.0 | 1.0 | 1.0 |
| Equipment purchase | — | — | 4.0 | — |
| Total outflows | 19.0 | 20.0 | 25.5 | 24.5 |
| Closing balance | 7.8 | 8.6 | 6.3 | 6.8 |
What the table shows:
- Sales grow 25% between January and April, yet the balance falls from 8.0 to 6.8 million. Growing by selling on credit and paying in cash consumes cash.
- March is the dangerous month: the equipment purchase coincides with the peak of supplier payments.
- Fixed costs (payroll, rent and installment) add up to 9 million a month. If the company wants at least one month of fixed costs in the bank, it is below the cushion all four months.
With this table in January, the manager has options before March arrives: finance the equipment instead of paying cash, ask new customers for an advance or negotiate 30 days with a supplier.
The formulas in Excel or Google Sheets
If the months are in columns B to E and invoiced sales are in row 2 (with December in column A):
| Row | Item | Formula in column C (February) |
|---|---|---|
| 3 | Opening balance | =B15 (prior month's closing balance) |
| 4 | Cash collection | =C2*40% |
| 5 | 30-day collection | =B2*60% |
| 6 | Total inflows | =SUM(C4:C5) |
| 7–13 | Outflows | values or formulas (for example =C2*50% for suppliers) |
| 14 | Total outflows | =SUM(C7:C13) |
| 15 | Closing balance | =C3+C6-C14 |
Add a row with the desired minimum balance and conditional formatting that turns the closing balance red when it falls below it. It is the most useful alert in the whole sheet.
To separate projected from actual, duplicate each month into two columns, Projected and Actual, and add a third with the difference. Large, repeated differences tell you which assumption is wrong (almost always the collection terms).
13 weeks or 12 months
- 13 weeks, weekly detail: for operating. It shows in which week payroll, VAT and a large supplier payment coincide. You update it every Monday with what happened the week before and add a new week at the end.
- 12 months, monthly detail: for planning, applying for a loan or talking to an investor. It is the one that goes inside the business plan.
If you will only maintain one, start with the 13-week version: it is the one that prevents surprises.
How to improve cash flow
The levers, from cheapest to most expensive:
- Collect earlier. Advances of 30% to 50% on projects, early-payment discounts, automatic receivables reminders, invoicing the same day you deliver.
- Pay later without damaging the relationship. Negotiate 30 days with key suppliers or pay with a business credit card when it does not accrue interest.
- Reduce inventory. Buying more often and in smaller quantities frees cash even if the unit price rises a little.
- Finance investments. Equipment that lasts five years should not come out of one month's cash.
- Finance receivables. Factoring or invoice discounting turns accounts receivable into cash for a fee. Use it as a bridge, not a habit.
If you bill customers abroad, the time a transfer takes and the currency exchange fee are also part of the flow. Include them at the collection date, not the invoice date.
Free cash flow and discounted cash flow
Two terms you will see in corporate finance:
- Free cash flow: operating flow minus investments in assets. It is the money left to pay debt, distribute to partners or reinvest.
- Discounted cash flow: used to value a company or project. It brings future flows to present value with a discount rate that reflects risk. If you are raising capital, the investor will likely calculate it.
For the day-to-day operation of a small business, a projected cash flow built with the direct method is enough.
Common mistakes
- Projecting sales instead of collections. The most frequent and the most expensive mistake.
- Being optimistic about terms. If the contract says 30 days and the customer pays at 45, project 45.
- Forgetting non-monthly payments. Bonuses, bimonthly or annual taxes, insurance renewals, annual software licenses.
- Leaving out your own pay. What you take out of the business is also a cash outflow.
- Doing it once and filing it away. A cash flow that is not updated with actuals stops being useful within a month.
Conclusion
A projected cash flow is not a report for the accountant: it is an alarm you set yourself. Build the sheet with your real balances today, project collections using real terms, set a minimum balance and review it every week. The hard month will show up in the table before it reaches your account.
Step-by-step guide
Start from the real bank balance
The opening balance is what is in your accounts and cash today, not what the books say.
Project collections, not sales
Record each inflow in the month the customer actually pays, based on their real payment terms.
List every payment
Suppliers, payroll, rent, taxes, loan installments and investments.
Calculate each month's balance
Opening balance + collections − payments = closing balance, which becomes the next opening balance.
Set a minimum balance and compare
Flag the months below your cushion and update actuals against the projection.
Frequently asked questions
What is a projected cash flow?
It is an estimate of how much money will come in and go out of your business over the coming months or weeks, and of the balance left at the end of each period. It helps you spot months without liquidity and act before the problem arrives.
What is the difference between cash flow and profit?
Profit records sales and expenses when they are invoiced or accrued. Cash flow records money when it actually comes in or goes out. A business can be profitable and still run out of cash if customers pay late or if it invests in inventory or equipment.
How many months should I project cash flow for?
For operations, 13 weeks (one quarter) with weekly detail. For planning and presenting to a bank or investor, 12 months with monthly detail. Ideally you keep both and update them with actuals.
What is free cash flow?
It is operating cash flow minus investments in assets (equipment, machinery, capitalized software). It shows how much cash the business generates after maintaining and growing its capacity, and it is the basis for valuing a company.
Related Posts
Keep exploring similar content that may interest you

Basic financial plan for small businesses: projections and break-even
How to build a small-business financial plan with three sheets —assumptions, 12-month P&L and cash—, calculate break-even, and separate profit from cash.

Digital transformation: taking a process to software
Digital transformation is not buying software: it is understanding, defining, improving and automating your processes so you can scale. A step-by-step method.

Cheap Software Licenses on Eneba for Your Whole Team
Buy VPN, Office and software licenses on Eneba without getting the region or key type wrong. A guide to equip your team without paying list price.
Partnerships
Tools I use every day, on better terms for this community.