How to create a cash flow forecast in Excel for a loan

Build a cash flow forecast in Excel by setting out each period, recording expected cash coming in and going out, then calculating the closing balance. That closing balance becomes the next period’s opening balance, making it easier to see when cash may become tight. For a Start Up Loan application, the forecast should cover 12 months, and a cash flow forecast example can help you structure the figures clearly.

It’s easy to focus on whether a business expects to make a profit, but loan planning also depends on when money is due to reach the bank and when bills and repayments must be paid. A cash flow forecast example helps bring those timings into view, so you can spot potential shortfalls and consider how borrowing fits alongside other costs.

This guide walks through an Excel layout and its calculations, then explains how to adapt a startup forecast for a business plan or loan application. You’ll also learn how to keep it useful as estimates change and actual figures become available.

What are the steps to create a cash flow forecast using Excel?

Set up periods as columns and cash movements as rows, then calculate the closing balance for each period. Match the forecast’s time span and level of detail to your business’s cash cycle.

1. Choose a forecasting period and method

   Choose weekly or monthly columns, then extend the forecast to cover your cash cycle. A rolling 12-month forecast, updated monthly, is reasonable for small and medium organisations not in immediate financial difficulty. Short-term forecasts need daily or weekly updates; medium- and long-term forecasts need monthly reviews. The ACCA advises considering cash needs day to day, month to month and year to year.

   A cash flow forecast estimates future cash movements. A profit forecast estimates income and costs to show expected profit, while a cash flow statement reports cash movements from a past period.

2. Allow for seasonality and quiet periods

   Enter sales receipts in the period you expect the money to reach your bank account, not simply when you make a sale. Check seasonal patterns against past figures, customer payment terms and expected quiet periods. Even a business with stronger sales in one season may have lower cash balances in quieter months, especially when regular payments continue.

   In Excel, calculate net cash flow by subtracting outflows from inflows. Add the result to the opening balance to calculate the closing balance, then carry that figure forward as the next period’s opening balance.

3. Set up mixed-period and rolling forecasts

   Use daily detail when payment timing is tight, then switch to weekly or monthly columns for the longer view. For example, a mixed-period forecast might show two weeks of daily projections, followed by four weeks of weekly projections. This keeps near-term figures precise without making every later period unnecessarily detailed.

   Choose a rolling forecast if each update should extend the outlook; a fixed-term forecast counts down to a set endpoint. Update estimates when circumstances change or actual figures become available.

4. Separate cash flow from profit forecasts

   Keep cash and profit in separate Excel schedules because sales and expenses don’t always match the timing of bank receipts and payments. Record actual cash items, including transfers, cheques, and cash payments or receipts, in the period they occur. This makes the cash balance useful for assessing whether you can meet expected payments.

   Review the closing balances and assumptions with each update. If an expected receipt arrives later, move it to the period when it is due, recalculate the balance and identify any emerging shortfall.

Cash flow forecast template

Set up an Excel template that separates expected receipts from payments and shows a balance for each period. Keep the layout consistent so updated figures recalculate the forecast.

1. Use the cash flow forecast template

   Start with columns for each forecast period and rows for cash sources and uses. Include sales receipts, owner investment, grants, supplier payments, wages, tax and loan repayments where relevant. A typical expenditure section can contain 10 to 20 line items, grouped into clear categories. Keep each amount on its own row, rather than hiding different payments in a single total.

   Add rows for opening balance, total inflows, total outflows, net cash movement and closing balance. Use Excel formulas to total each group and calculate the balance. A spreadsheet set up for forecasting can recalculate results when you change an income or spending figure.

2. Use the weekly forecast template

   Choose a weekly layout when payment dates matter more than monthly totals. Label each column with its week, then enter receipts and payments in the week they’re expected to reach or leave the bank. Separate invoices due from invoices merely issued, and put supplier bills in their expected payment weeks.

   Carry each week’s closing balance into the next week’s opening balance. This shows how a delayed customer payment could affect the cash available for wages or supplier bills. Keep the weekly view focused on timing rather than adding detail that won’t change a decision.

3. Separate cash movements from budgeting categories. For example, record a VAT payment in the period it is payable rather than spreading it evenly across weeks. Label expected, confirmed and paid amounts clearly, so you can see which entries rely on estimates.
4. Review the template after each update. Compare forecast amounts with bank receipts and payments, then replace estimates with actual figures. If a receipt moves to a later week, update its timing and let Excel recalculate the balances. This keeps the template useful for tracking cash pressure and refining future assumptions.

Cash flow forecasts for business plans and loans

For a business plan or loan application, show when cash is expected to arrive and when payments fall due. Use realistic assumptions, then update the forecast as actual figures replace estimates.

1. List business outgoings

   Group expected payments into clear rows, such as supplier bills, wages, rent, VAT and Corporation Tax. A forecast expenditure section can contain 10 to 20 line items, depending on the business. Keep regular costs separate from one-off spending, so a planned equipment purchase doesn’t distort the expected balance in other periods.

   Enter each payment in the period it is due to leave the bank. If a supplier allows 30 days to pay, forecast the outflow when payment is expected, not when the goods arrive. Use cautious assumptions: plan for spending at the earliest opportunity and income arriving at the latest.

2. Add business expenses

   Include operating expenses and irregular costs that affect the cash available for loan repayments. Add tax payments in their expected payment periods; for example, VAT may be payable every quarter. Show Corporation Tax separately from day-to-day costs, and include payroll when wages are expected to leave the account.

   Check that each expense appears only once. Don’t treat an accounting charge as a cash payment unless money will actually leave the bank during the forecast period. This keeps projected cash movements separate from profit calculations.

3. Enter income when you expect it to reach the bank, not when you issue an invoice. Include relevant non-sales receipts, such as grants, tax refunds or owner investment. Banks use cash flow forecasts to assess when funds will arrive and how repayments fit around other business costs.
4. For a Start Up Loan application, prepare a forecast covering 12 months and include monthly loan repayments. If the application succeeds, the first repayment is due soon after the loan is drawn down. Update assumptions as estimates become more exact, and compare each period’s forecast with actual receipts and payments.

Startup cash flow forecast example

Build the forecast from realistic sales, payment dates and startup costs. Sophie and Jack’s interior design partnership illustrates why timing matters as much as projected sales.

1. Record the opening funds. A startup’s opening balance is zero before investment funds are added. Enter the investment when it is expected in the bank, then use that balance to fund early payments. This makes the difference between available cash and future sales clear from the outset.
2. Forecast sales by project and receipt date. The partnership forecasts two projects monthly during its first six months. Customers are expected to pay 25% as a deposit. Record each deposit when expected, then enter the remaining receipt according to its anticipated payment timing, rather than treating the full sale as immediate cash.
3. Enter costs when money is going out. Project materials are forecast at £1,500 per project, with suppliers allowing 30 days to pay.
4. Add one-off purchases and review the results. Forecast cash inflows average around £10,000 monthly, totalling £62,500 across the first six months, including £10,000 investment. The forecast shows a £1,500 net cash outflow in April. Calculate each closing balance and use Excel or accounting software to test how changed payment dates affect cash available.

For help turning startup assumptions into a practical forecast, Contact Us.

Coombs Chartered Accountants: Cash Flow Forecasting

Coombs Chartered Accountants provides Cash Flow Forecasting for businesses across Manchester, Stockport, Wilmslow and Alderley Edge. This support can help when you’re planning growth, preparing a loan application or managing uneven receipts and payments. A forecast brings expected income and outgoings into view, helping you assess how borrowing may affect cash available for day-to-day needs.

Consider this support when you need a clearer view of future cash movements or want to refine assumptions as your business plan develops. Where relevant, Statutory Accounts Preparation, Tax Compliance and Tax Planning can also put forecast figures in context alongside wider financial responsibilities.

Contact Coombs Chartered Accountants to discuss your cash flow forecasting needs.

Make your forecast part of your next decision

Treat the spreadsheet as a decision-making tool, not a document completed only for an application. Before submitting it, decide which changes would prompt you to review spending, revisit sales assumptions or discuss repayment timing. This turns the figures into practical prompts for managing the months ahead, rather than a static set of estimates.

A cash flow forecast example should reflect the decisions your business needs to make. Consider how different trading conditions could affect your plans, and keep the assumptions behind each scenario visible. When actual results begin to differ from expectations, use the gap to refine the next forecast and adjust decisions early. Make ownership of updates clear so new figures lead to timely action.

If you’d value support shaping a forecast around your business plans and loan requirements, Contact Us. A considered approach can help you move forward with greater confidence.

Frequently Asked Questions

What is the difference between a rolling and a fixed-term cash flow forecast?

A rolling forecast extends beyond its current end date each time you update it, while a fixed-term forecast retains a set endpoint. A rolling view suits ongoing planning because it keeps future periods visible as time passes. A fixed-term version can help track a defined project or loan period. Choose the format that matches the decision you need to monitor.

What components should be included in an accurate cash flow forecasting model?

Include opening cash, expected receipts, expected payments, net cash movement and closing cash for each period. Add separate rows for different sources and uses, such as customer receipts, grants, supplier invoices, wages and tax payments. Show the dates or periods when amounts should clear the bank. Label uncertain estimates clearly, then compare them with actual transactions to improve later forecasts.

What is a cash flow forecast and what does it include?

A cash flow forecast estimates money expected to enter and leave a business over a future period. It includes anticipated receipts and payments, organised by when they should reach or leave the bank. For example, a business might record a customer invoice receipt in a later period than the sale if payment is expected after delivery. The forecast shows the resulting cash position.

What is a simple cash flow forecast example for a small business?

A simple example could follow a small design business through a project cycle. It records a customer deposit when it is expected, materials when the supplier payment falls due, and the remaining customer payment when it should arrive. The business then compares cash coming in with payments going out. This makes timing gaps visible, even when the project’s sales value exceeds its costs.

How do you create a monthly cash flow forecast step by step?

Start with the opening bank balance for the first month. Add receipts expected during that month, then enter payments in the months they are due. Subtract total payments from total receipts to find the month’s net cash movement, and apply it to the opening balance. Carry the closing figure into the next month, then revise estimates as dates or amounts change.

How should VAT, tax and payroll be included in a UK cash flow forecast?

Record VAT, tax and payroll as cash outflows in the periods when payments are expected to leave the bank. Record VAT in the period when payment is expected, rather than spreading it evenly across months. Forecast payroll against the business’s actual pay schedule, and separate Corporation Tax from routine operating costs to make each obligation easier to track.