How to create and use a cash flow forecast template

Small business owners must be always prepared for the unknown, especially when it comes to unexpected cash flow disruptions. Is your business prepared for a big customer running late with their payments? What if they go out of business and never pay their bill, or decide to part ways with your services?

Using a cash flow forecast is one of the simplest and most effective ways to quantify the long-term impact of these unexpected events, providing important insight as business owners determine how to weather the storm.

What is a cash flow forecast?

Cash flow tracks incoming and outgoing cash and is typically recorded on the aptly named cash flow statement. A cash flow forecast is a financial tool used to predict a company’s future financial liquidity by estimating its incoming and outgoing cash over a given period. The forecast is usually built in a spreadsheet program or specialist accounting software.

Forecasting cash flow helps businesses track their expected cash surpluses and deficits. This makes it easier to time expenses, manage payment terms, and decide on external financing options to avoid a cash crunch.

Note that cash flow is distinct from sales revenue and expenses; it reflects the instant change in a business bank account balance. In contrast, under accrual accounting, revenue and expenses are recognised when they are incurred, regardless of when the cash changes hands. This means that a business with high sales could struggle to pay its bills if customers are slow to pay.

Why use a forecast template?

There’s no need to make a cash flow forecast from scratch every time. A cash flow forecast template provides a structure for quick and easy data entry and analysis. It saves time for the business’s financial team (or the owner, if they’re running the books themselves) by creating a uniform standard of cash flow records. This standardised format helps accountants easily compare financial periods so that they can quickly identify trends with greater accuracy.

How to create a cash flow forecast template

A cash flow forecast example template should cater to your business’s specific expenses and revenue sources so that it can be easily adjusted from one financial period to the next. A simple template can be built into any spreadsheet program. Consider using this basic format:

Firstly, lay out your columns. These vertical sections represent each month or week that the forecast will cover. Cash flow forecasts are commonly generated quarterly, but they can be as far-reaching or focused as needed simply by adding or deleting columns.

Next, lay out your rows. Down the leftmost column of the template, each horizontal section should list each expected incoming and outgoing cash category for the forecasted period, beginning with the company’s opening cash balance for that period.

Going down row by row, inflows might contain earned cash such as sales, loans, grants, and other relevant categories. Outflows might list expenses such as materials, payroll, utilities, and rent. Many forecasts also include “miscellaneous” rows to include non-recurring or atypical cash inflows and outflows, such as advertising costs.

Add rows for totals for each section, as well as a row to calculate the total change for the period. End with a row to represent the closing balance for the period.

FORECASTING CASH FLOW MAKES IT EASIER TO TIME EXPENSES, MANAGE PAYMENT TERMS, AND DECIDE ON EXTERNAL FINANCING OPTIONS TO AVOID A CASH CRUNCH.

How to use a cash flow forecast template

Using a cash flow forecast template is straightforward, as business owners just input the relevant cash values into their appropriate places. But before filling it out, bookkeepers should consider preparing the following records:

• The opening cash balance for the forecasted period.
• Estimated cash inflows, such as sales revenue, asset sales, and accounts receivable that will be collected.
• Estimated cash outflows such as cost of sales, loan repayments, employees’ wages, and other accounts payable that will become due.

Keep in mind that if customers and vendors use credit terms, total sales revenue and expenses may not immediately translate into cash and therefore should not be included in the cash flow forecast. Cash flow is instead based on when payments are made and when cash changes hands.

Once all financial records are collected, accountants can fill out the template using a five-step process, for example:

1. Start by listing the opening cash balance at the top of the first forecasted period: for this example, the business has an opening balance of $30,000 for January. This is the actual value, not an estimate.
2. Then, next to each relevant category, input any recurring and fixed figures: these are items accountants can safely estimate, like monthly rent and salaries. The company adds its estimates for a fixed $15,000 grant disbursement, as well as outflows for payroll, rent, and advertising expenses.
3. Next, input the estimated variable cash that will be received and spent over the period: this might include projected sales, utilities, and any additional transactions that are likely to arise. Depending on the industry, these costs may vary significantly between periods or stay relatively constant. The company adds the cash inflows it expects to receive as well as what it expects to pay for materials and utilities for the period.
4. Add up the total change in cash flow for the period and calculate the new closing balance at the bottom: The company adds up its total forecasted incoming cash, outgoing cash, and other cash transactions. The total change for the period represents how much cash is expected to flow into and out of the company for January. The closing balance is the total forecasted change added to the actual opening balance and becomes the opening balance for the next period.
5. Repeat these steps: each month (or week, or other period) until the full forecast is completed.

Bonus tips for cash flow forecasts

Cash flow forecast templates simplify calculations, but they are not without challenges. For instance, it can be hard to accurately predict what will happen in the future, especially during periods of economic uncertainty or volatile demand.

Cash flow forecasts also require a lot of data, which can be hard to wrangle. Here are three additional tips to consider when creating and using your forecast.

• Look to the past: to create an accurate projection, first analyse historic sale and expense trends to identify patterns and/or seasonality. Then, consider how current and future market conditions or events might impact these figures.
• Plug into technology: manually tracking bill payment and revenue data can be a time-consuming and error-prone task. Many businesses use accounting software to track cash flow and ensure accurate and timely data. Even simple spreadsheet software can automatically add up transactions to give accurate ongoing cash balances to start and end every period. Some businesses use demand forecasting tools to create a more accurate estimate of future sales and revenue.
• Set a realistic time frame: cash flow forecasts that extend over too long of a period are less accurate and more time-consuming. If a business is in a cash crunch or needs to make a quick funding decision, aim to run a forecast over a short period to minimise the amount of data needed to collect and input. A quick, focused forecast can provide immediate insights to inform timely action before a funding crisis escalates or an investment opportunity is missed.

Sample cash flow forecast templates

To make the most of their templates, businesses should deliberately create them with their unique revenue streams and expenses in mind. But to ensure that nothing gets overlooked, it can be useful to consult a few existing templates and learn what fits and delete what doesn’t. Here are three sample templates to get you started.

• Microsoft offers a cash flow forecast template optimised for Excel and specifically designed for small businesses. This template automatically sums all values and totals for the period as they are added and automatically carries balances from one month to the next. The template covers a full fiscal year.
• NetSuite’s cash flow forecast template is a more focused option, forecasting only four weeks into the future. It’s intended to help businesses create an ongoing forecast that can be updated monthly. This template also includes an “Actual” row at the bottom of the sheet to record actual results as they come in. These can then be compared with forecasted figures.
• Smartsheet offers a comprehensive set of cash flow templates, from simple, small business-focused templates to more robust daily, quarterly, yearly, and three-yearly options. Smartsheet also includes specialised templates for nonprofits, as well as personal cash flow forecasts.
• Governments and regulatory bodies sometimes offer accounting guidance and templates for businesses as well. The Victorian State Government, for instance, has a very detailed cash flow forecast template that may help spark ideas.

Key takeaways for creating and using cash flow forecast templates

Most businesses have had to deal with unexpected expenses when equipment malfunctions or an order doesn’t go as planned. Without a magic crystal ball, these cash crunches can be hard to predict. But businesses can prepare and build resiliency by forecasting their cash flow to predict how much cash will be in their accounts in the weeks or months ahead. Cash flow templates that are tailored to the business’s needs ensure forecasts are based on accurate data, setting more realistic expectations.