Featured image for Cash Flow Projection in Excel: Step-by-Step Example

Cash Flow Projection in Excel: Step-by-Step Example

Published on:

Reading time: 8 min

Topic: Management

Author: Leandro Valencia

#cash flow#projected cash flow#cash flow excel#cash flow forecast#small business finance#management

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

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:

  1. Collect earlier. Advances of 30% to 50% on projects, early-payment discounts, automatic receivables reminders, invoicing the same day you deliver.
  2. 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.
  3. Reduce inventory. Buying more often and in smaller quantities frees cash even if the unit price rises a little.
  4. Finance investments. Equipment that lasts five years should not come out of one month's cash.
  5. 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

  1. Start from the real bank balance

    The opening balance is what is in your accounts and cash today, not what the books say.

  2. Project collections, not sales

    Record each inflow in the month the customer actually pays, based on their real payment terms.

  3. List every payment

    Suppliers, payroll, rent, taxes, loan installments and investments.

  4. Calculate each month's balance

    Opening balance + collections − payments = closing balance, which becomes the next opening balance.

  5. 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

Partnerships

Tools I use every day, on better terms for this community.

Affiliate links. Your price does not change.See all partnerships
Training program

Ready to turn your idea into a real project?

Transforma is the program where you will learn to create, execute and scale your project with clarity and method.

Discover the Transforma Program