Microsoft

How to Build a Monthly Cash Flow Model in Excel

Accountant reviewing a monthly cash flow model at an office desk

A well-structured Excel cash flow model helps an accountant answer practical questions: when will the client have enough cash to pay suppliers, salaries and taxes, and when might funding be needed? The value is not in a decorative accounting spreadsheet template. It is in a repeatable method that another person can inspect, update and reconcile.

This guide explains how to build a monthly cash flow forecast in Excel from source data supplied by a client. It also covers tax timing, formula checks, model protection and safe delivery. The model is a planning tool, not a replacement for the client’s accounting system, bank records or statutory reporting process.

Plan the model before entering formulas

Start by defining the purpose, reporting period and level of detail. A twelve-month forecast is common, but a shorter period may be more useful when cash is tight or the client has reliable information only for the next few months. Use one column for each month and keep the period consistent throughout the workbook.

For an accountant or bookkeeping office, a practical workbook can contain these sheets:

  • Read me: purpose, reporting date, currency, prepared-by information, version and notes about the source data.
  • Inputs: expected receipts, operating payments, payroll, tax payments, financing movements and other known cash events.
  • Assumptions: payment delays, collection rates, tax timing, recurring costs and other planning estimates.
  • Cash flow: the main monthly calculation showing opening cash, inflows, outflows and closing cash.
  • Checks: formula tests, missing-input warnings and reconciliation differences.
  • Summary: a compact view for discussion with the client.

Do not mix client-entered values with formulas in the same area without a clear visual or structural distinction. If a client changes an assumption, you should be able to identify the change quickly during a review.

Set up the monthly reporting period

Place the first day of each month in the column headings and format it as a readable month and year. Store real dates rather than text such as “January” because dates can drive formulas, checks and summary labels.

For example, if the first month is in cell B3, the next month can be generated from it with a month-based formula. The exact function syntax can vary with the Excel language and regional settings, so check the local version used by your office. Avoid manually typing twelve unrelated month labels, which can create gaps or duplicate periods.

Decide whether the model is prepared on a cash basis. A cash flow forecast records when money is expected to enter or leave the bank account, not necessarily when an invoice is issued or an expense is recognised. A sales invoice issued in March may be collected in April, while a tax liability recognised in one period may be paid in another.

Record the opening bank balance for the first month from a reliable source, such as a bank statement or the latest agreed bookkeeping records. If the client has multiple bank accounts, either model them separately or document the reason for combining them. Do not silently combine operating, savings, loan or restricted accounts.

Create the input tables

Build input tables around cash events rather than around every general ledger account. The correct level of detail depends on the client. A small service business may need sales receipts, payroll, rent, suppliers, tax and loan payments. A larger client may require separate customer groups, projects, currencies or bank accounts.

Useful inflow categories include:

  • Customer receipts, separated by recurring and one-off income where this affects collection timing.
  • Deposits, grants or other expected receipts, clearly marked as confirmed or estimated.
  • Loan drawdowns, shareholder funding or other financing inflows.
  • Asset disposals or other non-operating receipts.

Useful outflow categories include:

  • Supplier and subcontractor payments.
  • Payroll, employer costs and contractor payments.
  • Rent, utilities, insurance, software and professional services.
  • VAT or other tax payments, where relevant to the client’s cash planning.
  • Loan repayments, interest, equipment purchases and other capital expenditure.
  • Owner drawings, dividends or other distributions, when they are expected cash movements.

Each input row should have a clear label, an owner or source, a monthly value and a note where the figure is uncertain. If you use an Excel Table for the underlying data, new rows can be included more reliably in formulas and filters. Keep imported data separate from manually entered estimates so that the origin of each number remains visible.

Separate assumptions from calculated values

Assumptions are not facts. A good Excel model makes that distinction obvious. For example, the client may expect 80% of March invoices to be collected in April, but that is a planning assumption unless supported by historical collection data or a confirmed payment schedule.

Place assumptions in a dedicated area with fields such as:

  • Expected collection delay by customer group.
  • Expected supplier payment delay.
  • Monthly growth or seasonal changes.
  • Payroll changes and planned hiring dates.
  • Tax payment months and estimated amounts.
  • Interest rates or repayment dates taken from the relevant agreement.

Use named cells or clearly labelled references where this improves readability. For instance, a receipt calculation can refer to a collection-rate assumption rather than embedding a percentage inside a long formula. This allows the reviewer to understand and test the logic.

Do not hide uncertainty inside formulas. If a receipt is speculative, add a status such as “confirmed”, “probable” or “estimated”, or create separate base, cautious and optimistic scenarios. The scenario labels should be explained in the workbook. A forecast does not become more accurate because it contains more decimal places.

Calculate opening and closing cash

The central calculation is simple, but it must be consistent. A basic monthly cash flow structure contains:

Line Finalidad Example formula for month B
Opening cash Cash available at the start of the month Input from bank records
Total inflows Receipts and other cash entering the account =SUM(B6:B10)
Total outflows Payments and other cash leaving the account =SUM(B13:B22)
Net cash movement Inflows less outflows =B11-B23
Closing cash Cash expected at the end of the month =B5+B24

For the next month, link opening cash to the previous month’s closing cash. For example, if January closing cash is in B25, February opening cash should be =B25, not a second manually typed balance. This link is one of the most important controls in the model.

If the client has a minimum cash requirement, add a minimum cash assumption and a shortfall line:

Cash shortfall = closing cash – minimum required cash

A negative result indicates that the forecast falls below the selected buffer. It does not automatically prove that financing is required; the result should be discussed with the client and compared with confirmed facilities, payment options and operational plans.

Use a separate line for financing movements. A loan drawdown can improve closing cash while creating a future repayment obligation. Presenting financing separately prevents the client from confusing operating performance with funding activity.

Model tax and irregular payments carefully

Tax is often a timing issue in a monthly cash flow forecast. Do not simply spread an annual tax estimate evenly across twelve months unless that is the documented planning assumption. Enter expected payment dates and amounts where they are known. Where the amount is uncertain, record the calculation basis and include a review date.

Also identify payments that are easy to overlook: annual insurance, equipment purchases, professional fees, bonuses, loan instalments and owner distributions. A monthly model can look healthy until one large annual payment is placed in the correct month.

Use notes beside unusual items. A note such as “based on signed order” is more useful than a generic comment that provides no evidence. For client work, keep source documents in the firm’s approved document system rather than embedding sensitive files unnecessarily in the workbook.

Build checks before formatting the report

Checks should be part of the model, not an afterthought. Add a visible check that compares each month’s closing cash with the following month’s opening cash. The difference should be zero.

Other useful checks include:

  • Opening cash is present for the first forecast month.
  • Every forecast month has a valid date and no duplicate period.
  • Required input cells are not blank.
  • Tax, payroll and loan lines have notes when they are estimated.
  • Formula cells have not been replaced by hard-coded numbers.
  • Total inflows and outflows agree with their detailed rows.
  • Closing cash is compared with the minimum cash requirement.
  • The forecast is reconciled to the latest available bank or bookkeeping balance.

Test the workbook with an obvious temporary change. Increase one receipt and confirm that net movement and closing cash change by the same amount. Then change one payment and verify that closing cash decreases correctly. Inspect formulas across the full period rather than checking only the first month.

Conditional formatting can highlight negative cash, missing inputs or failed checks, but colour should not be the only explanation. Include a text status such as “Review required” so the meaning remains clear when the file is printed or converted to PDF.

Protect and review the finished model

Once the formulas are tested, protect calculation areas and leave only intended input cells editable. Protection is a safeguard against accidental changes, not a substitute for access control or a complete security strategy. Keep a controlled master copy and record the model version, preparation date and significant changes.

Review the workbook in two ways. First, perform a technical review of formulas, links, checks and period dates. Second, perform a business review: do the forecasted receipts match the client’s sales pipeline, do payment dates reflect reality, and are tax and payroll obligations timed correctly?

For recurring work, duplicate a clean master structure for each client rather than carrying last month’s values forward without review. Remove obsolete assumptions and confirm the opening balance every period. This makes the Excel model for accountants easier to audit and reduces the risk of stale information.

Share the model safely with a client

Cash flow files can contain personal, payroll, customer and financial information. Use your office’s approved storage and sharing process, limit access to the intended recipients and avoid sending confidential workbooks to an unverified email address. Consider whether the client needs an editable workbook or only a PDF report for discussion.

A PDF is useful when the client needs a fixed presentation, but it is not a replacement for the working model. Before exporting, check that the print area includes the relevant months, headings are readable and formulas are not accidentally displayed as errors. If the client must approve assumptions or a forecast, use an approved e-signature service and retain the signed version according to your office procedures. Do not treat an email reply as a formal approval unless your process allows it.

For GDPR-conscious handling, collect only the information needed for the engagement, keep access restricted and follow your firm’s retention and deletion procedures. Avoid placing passwords or sensitive client details in worksheet comments. A cash flow model also does not replace secure email, a properly managed server for accounting software, endpoint protection or backups. Those controls belong in the wider practice technology environment.

Microsoft 365 Business Standard can provide a consistent business environment for creating spreadsheets and documents, communicating with clients and sharing information between team members. Organisations can use the familiar Microsoft Office applications and cloud-connected services as part of their workflow, while still applying their own access, retention and client-data policies. Check the current product details and your organisation’s requirements before standardising a process.

Which model approach fits each client?

Client situation Recommended approach Motivo
Small business with stable monthly activity Category-based twelve-month model Simple to maintain and easy to explain.
Seasonal business Monthly model with historical comparison and scenarios Highlights low-cash periods and seasonal assumptions.
Client with uncertain collections Customer or receipt-group inputs with cautious and base cases Makes collection risk visible rather than hiding it in an average.
Business with several bank accounts or financing facilities Separate account and financing sections with a consolidated summary Preserves the distinction between operating cash and funding.
Client needing statutory or ledger reporting Use the cash flow model alongside the accounting system The forecast supports planning but does not replace accounting records.

Preguntas frecuentes

What is an Excel cash flow model?

It is a structured workbook that estimates monthly cash inflows, outflows, opening balances and closing balances. It focuses on cash timing, so it should not be confused with a profit and loss forecast or a balance sheet.

How often should a monthly cash flow forecast be updated?

Update it according to the client’s activity and risk. A monthly review is a practical minimum for many businesses, while clients facing rapid changes may need more frequent updates. Confirm the opening cash balance each time.

Should tax be included in the model?

Yes, when tax payments affect the client’s cash position. Enter expected payment months and amounts based on available information, label estimates clearly and review uncertain figures with the responsible adviser.

Can this replace an accounting spreadsheet template?

A repeatable model is more useful than an opaque template, but it still does not replace bookkeeping software, bank evidence or statutory records. Adapt the categories and controls to the client rather than reusing unexplained formulas.

How can accountants share the forecast securely?

Use approved storage, access controls and secure communication, and send a PDF when the client only needs a fixed report. Use an approved e-signature workflow for formal approvals and follow your firm’s data-retention procedures.

Genuine Microsoft licences, including Microsoft 365 Business Standard, are available from KEYFORREST with instant electronic delivery. See the current product page for the latest licence information.

author-avatar

Acerca de Marios Chaidousis

Marios Chaidousis es especialista en software y licencias, con experiencia en Autodesk (AutoCAD, Revit, Inventor), Microsoft (Office, Microsoft 365, Windows, Windows Server, SQL Server), Adobe y software de seguridad. Ayuda a ingenieros, profesionales y empresas a elegir la licencia adecuada y a sacar el máximo partido a su software.LinkedIn · Facebook · Instagram