Tag: cash flow projection example

  • How to Create a 12 Month Cash Flow Projection Step by Step

    How to Create a 12 Month Cash Flow Projection Step by Step

    Quick Summary

    A 12 month cash flow projection is a month-by-month forecast of the cash coming into and going out of your business over the next year. Start by gathering 12 to 24 months of historical records and your actual opening bank balance, then estimate inflows and outflows by their timing. Each month’s closing balance becomes the next month’s opening balance.

    • Pull 12 to 24 months of bank statements, sales reports and payroll records first.
    • Use your actual bank balance on day one as the opening cash figure.
    • Track when customer money lands, not when you billed it.
    • Closing cash equals opening cash plus net cash flow each month.
    • Update the forecast monthly and compare actuals against it.

    Table of Contents

    What a 12 Month Cash Flow Projection Shows You

    Last Updated: November 5, 2026

    A cash flow projection is a month-by-month forecast of the cash coming into your business and the cash going out. A 12 month cash flow projection covers the next year, so you can see when money will be tight and when it will be plentiful. Most small business owners only look at their bank balance today. A projection lets you see next March before it arrives.

    For contractors and restaurant owners, this matters a lot. Construction payments often arrive 30 to 60 days after work is done. Restaurant sales spike around holidays and drop in slow months. A projection shows these gaps before they cause a missed payroll.

    The goal is simple: no surprises. Here is what a good projection gives you:

    • A month-by-month view of cash in and cash out
    • An early warning when your balance may drop too low
    • A clear picture of when you can afford to hire, buy equipment, or take on new work
    • A basis for talking with your bank or lender

    Gathering Your Historical Financial Data and Opening Cash Balance

    Your projection is only as good as the numbers behind it. Start by pulling your records from the last 12 to 24 months. Past results are your best guide to future patterns.

    Collect these items first:

    • Bank and credit card statements
    • Sales reports or invoice history
    • Payroll records
    • Accounts receivable and accounts payable reports
    • Loan payment schedules
    • Recurring bills, such as rent, insurance, and software subscriptions

    Next, record your opening cash balance. This is the amount in your bank accounts on the first day of the first month. Use the actual bank balance, not what your accounting software shows, because uncleared checks and pending deposits can throw things off.

    Watch out: Forgetting annual or quarterly bills is the most common mistake. Insurance premiums, estimated taxes, and software renewals often land in one month, which can drain cash fast.

    Building a 12 Month Cash Flow Projection Template in Excel (Free Template Setup)

    You do not need expensive software. A spreadsheet works well, and a clean layout with working formulas is the difference between a projection you trust and one you abandon by month three. Set up a simple 12 month cash flow projection template in Excel, Google Sheets, or a similar tool.

    Use this basic layout, with months across the top in columns B through M (January through December) and labels in column A:

    • Row 1: Month names across the top (January through December)
    • Row 2: Opening cash balance
    • Rows 3 to 6: Cash inflows (customer receipts, deposits, other income)
    • Rows 7 to 15: Cash outflows (payroll, rent, materials, loan payments, taxes)
    • Row 16: Total cash outflows
    • Row 17: Net cash flow (inflows minus outflows)
    • Row 18: Closing cash balance (opening balance plus net cash flow)

    Each month’s closing balance becomes the next month’s opening balance. This chain is what makes a 12 month projection useful. In spreadsheet terms, if January is column B and February is column C, then the February opening balance cell (C2) equals the January closing balance cell (B18). Copy that single link across all 12 months so the chain never breaks.

    Here are the formulas to paste in, assuming January is column B:

    • Total inflows (B6): =SUM(B3:B5)
    • Total outflows (B16): =SUM(B7:B15)
    • Net cash flow (B17): =B6-B16
    • Closing cash (B18): =B2+B17
    • Next month opening (C2): =B18

    Three quality checks catch most errors before they spread:

    • Tie the opening balance to the bank. The first month’s opening balance should equal your actual bank balance on day one, not the accounting software’s cash account, because uncleared checks and pending deposits create a gap.
    • Scan for double counting. If you record a customer payment in inflows, do not also record the same invoice as revenue elsewhere in the sheet. A projection tracks cash movement, not accrual revenue.
    • Confirm the chain closes. The final month’s closing balance should equal opening balance plus the sum of all 12 net cash flow rows. If it does not, a formula link is broken.

    Watch out: Do not confuse profit with cash. A profitable month can still show a negative closing balance if customers have not paid yet, and a spreadsheet that mixes accrual revenue with cash outflows will hide that shortfall.

    Keep the template to one tab per scenario (base case, slow sales, strong sales) so you can compare them side by side without overwriting assumptions.

    Estimating Cash Inflows and Their Timing

    Many owners guess their income. Instead, base your inflow estimates on real data. Look at your past sales and customer payment patterns.

    Small business owner reviewing customer payment records for a cash flow projection at a tidy desk
    Small business owner reviewing customer payment records for a cash flow projection at a tidy desk

    Timing matters as much as the amount. A $20,000 invoice means nothing if the customer pays in 90 days. Track when money actually lands in your account, not when you billed it.

    Consider these factors:

    • Your typical payment terms (net 15, net 30, or net 60)
    • How long customers really take to pay
    • Seasonal sales patterns
    • Deposits or progress payments on large jobs

    Here is a practical rule: if a customer usually pays 45 days after the invoice, put that money in month three, not month one.

    Get Started Today →

    Projecting Cash Outflows, Seasonality, and Working Capital Timing

    Outflows are often easier to predict than inflows, but timing still trips people up. List every expense and when it is due.

    Split your expenses into two groups:

    • Fixed costs: rent, insurance, loan payments, software subscriptions
    • Variable costs: materials, subcontractors, food and supplies, fuel

    For seasonal businesses, adjust variable costs to match expected sales. A restaurant buys more food before summer. A contractor may pay for materials weeks before the project’s first invoice.

    Working capital timing is the gap between paying for goods and getting paid. If you pay suppliers in 30 days but customers pay you in 60, you need cash to cover that 30-day gap. Your projection should show this gap clearly.

    Worked Cash Flow Projection Example With Formulas

    Here is a simplified 12-month example for a small restaurant with a $5,000 opening balance. The assumptions are stated up front so you can see how each figure flows into the next, something most guides skip.

    Assumptions:

    • Sales average $20,000 a month but swing with seasonality: $18,000 in January and February, $22,000 in March through May, $26,000 in June through August, $24,000 in September and October, and $28,000 in November and December.
    • 60 percent of sales are collected in the month of sale and 40 percent the following month, reflecting card settlement and a small share of house accounts.
    • Fixed costs are $12,000 a month (rent, insurance, loan payments, software).
    • Variable costs run 35 percent of that month’s sales (food, supplies, hourly labor).
    • A quarterly estimated tax payment of $4,000 lands in April, June, September, and January.
    Month Opening Cash Inflows Outflows Net Cash Flow Closing Cash
    January $5,000 $18,000 $19,500 -$1,500 $3,500
    February $3,500 $18,000 $18,300 -$300 $3,200
    March $3,200 $20,400 $19,700 $700 $3,900
    April $3,900 $22,800 $23,700 -$900 $3,000
    May $3,000 $22,800 $19,700 $3,100 $6,100
    June $6,100 $24,400 $27,100 -$2,700 $3,400
    July $3,400 $26,000 $21,100 $4,900 $8,300
    August $8,300 $26,000 $21,100 $4,900 $13,200
    September $13,200 $25,200 $25,400 -$200 $13,000
    October $13,000 $24,800 $20,400 $4,400 $17,400
    November $17,400 $26,400 $21,800 $4,600 $22,000
    December $22,000 $29,600 $25,800 $3,800 $25,800

    The formulas are straightforward and match the template above:

    • Net Cash Flow = Total Inflows minus Total Outflows
    • Closing Cash = Opening Cash plus Net Cash Flow
    • Next Opening Cash = This Month’s Closing Cash

    Notice how the timing assumptions drive the result. January’s inflows equal January sales because there is no December collection carried in; from February on, inflows combine 60 percent of the current month’s sales with 40 percent of the prior month’s.

    Watch out: Never let a projected closing balance go below zero without a plan. In this example, a 10 percent sales shortfall in January and February would push the balance near zero, so the owner should line up a credit facility before the slow season rather than after.

    Repeat this structure for all 12 months, and keep the assumption block at the top of the sheet so anyone reviewing the forecast can see what changed when you update it.

    Keeping Your 12-Month Rolling Cash Flow Forecast Accurate

    A projection is not a one-time task. Update it every month. Compare your actual results to what you projected, then look at the differences. If sales came in 15 percent lower than expected, adjust the months ahead.

    This approach is called a 12-month rolling cash flow forecast. Each month, you drop the past month and add a new month at the end. Your forecast always looks a full year ahead.

    Monthly bookkeeping makes this much easier. Clean, current books give you accurate actuals to compare against your forecast. Without them, the variance analysis becomes guesswork.

    Booking Help With Your Cash Flow Projections at AMG Accounting

    Building a 12 month cash flow projection takes time, and many owners find it hard to keep up. If you want help setting one up, or you want a clear picture of where your cash is headed, the AMG Accounting team can walk through it with you. Pricing depends on your business size and the services you need, so contact us directly for a quote.

    For a deeper look at how outside help fits into your finances, read our guide to benefits of outsourced accounting services. If you are wondering how monthly records affect your forecast, see how long monthly bookkeeping takes. Owners who want to understand their spending pace can start with how to calculate your business burn rate. You can also learn more about managing seasonal cash flow fluctuations. If you are weighing software options, our comparison of Pilot accounting alternatives for contractors covers the trade-offs.

    Frequently Asked Questions

    What is a 12 month cash flow forecast?

    A 12-month cash flow forecast is a month-by-month estimate of the cash coming into your business and the cash going out over the next year. It starts from your current cash balance, adds expected cash inflows such as customer payments, subtracts expected cash outflows such as payroll, rent, and supplier bills, and shows your projected ending balance each month. Most owners use it to spot months where cash could run short and to plan financing or spending before a problem arises. Update it regularly as actual results come in.

    Can I create a cash flow projection in Excel?

    Yes. Excel works well for a 12 month cash flow projection because you can set up one row for each inflow and outflow category and one column for each month, then use simple formulas to add totals and carry the ending balance forward as the next month’s opening balance. A free template is a good starting point, but you should customize the categories to match how your business actually earns and spends money. Keep your assumptions in a separate tab so you can change them without rebuilding the sheet.

    How do I account for seasonal income in a cash flow forecast?

    Base seasonal estimates on your historical sales for each month, using at least one or two prior years if you have them. Adjust the inflow line for slow and busy periods, and also shift the timing of payments, since customers may pay later during a slow season. Keep fixed costs such as rent and insurance steady, and review variable costs such as materials or labor against the expected volume. Compare your projection to actual results each month to refine the seasonal pattern over time.

    How often should I update a 12-month cash flow forecast?

    Most small business owners update a rolling 12-month cash flow forecast monthly. Each month, replace the projected figures for the month that just ended with actual results, review the variance between projected and actual amounts, and extend the forecast by one more month so it always covers the next twelve months. Update sooner if you win or lose a large contract, change pricing, or take on new debt.