How to Create and Use a Cash-Flow Forecast Template

It’s important that small-business owners are 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 if they experience financial hardship? What if they go out of business and never pay their bill, or decide to part ways with your services?

Understanding and quantifying the long-term impact of these unexpected events is crucial to determining how to weather the storm. One of the simplest – and most effective – ways to do so is by generating a cash-flow forecast.

This article explains how you can quickly create a cash-flow forecast for your small business based on a personalized and reusable cash-flow forecast template.

What Is a Cash-Flow Forecast?

Cash flow tracks incoming and outgoing cash and is typically recorded on the aptly named cash-flow statement, one of three key financial statements. 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 certain period. The forecast is usually built in a spreadsheet or within accounting software.

By forecasting cash flow, businesses can help track their expected cash surpluses and deficits. This can help make it easier to effectively time expenses, manage payment terms, and decide on external financing options to avoid a cash crunch – and ultimately build a steadier financial future.

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

Why Use a Forecast Template?

Because there’s no need to make a cash-flow forecast from scratch every time, a cash-flow forecast template can provide a structure for quick and easy data entry and analysis. It can also save 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 standardized format can help accountants easily compare financial periods so that they can quickly identify trends with greater accuracy.

How to Create Your Own Cash-Flow Forecast Template

A cash-flow forecast example template should be catered 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 in any spreadsheet program. Consider using this basic format:

First, 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. But remember: the further out the forecast extends, the less accuracy accountants should expect as both market conditions and customer demand can evolve in unexpected ways.

Then, lay out your rows. Down the left-most 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 and 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 [...] can help make it easier to more effectively time expenses, manage payment terms, and decide on external financing options to avoid a cash crunch and build a more sustainable financial future. 

How to Use a Cash-Flow Forecast Template

Using a cash-flow forecast template is straightforward: 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 to 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 actually changes hands.

Once all financial records are collected, accountants can fill out the template with the following five steps. To illustrate how it works in practice, consider building on the above template 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 can include projected sales, utilities, and any additional transactions that are likely to arise. Depending on the industry, these costs may vary significantly from period to period or stay relatively constant.

The company adds the cash inflows it expects to receive from sales, other sources such as asset appreciation, and miscellaneous funds from a winter fundraiser. The company also adds 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. This will serve as the opening balance for the next period.

The company adds up its total forecasted incoming cash, outgoing cash, 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.

5. Repeat these steps for each month (or week, or other period) until the full forecast is completed.  

Bonus Tips for Cash-Flow Forecasts

Cash-flow forecast templates can 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 own forecast.

• Look to the past. To create an accurate projection, first analyze historic sale and expense trends to identify any 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. Additionally, some businesses rely on demand forecasting tools to create a more accurate estimate of future sales and revenue.
• Set a realistic time frame. The farther out the forecast goes, the longer it may take to make and the less accurate it can be. 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 minimize the amount of data needed to collect and input. A quick, focused forecast can provide immediate insights for decision makers, helping to ensure 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 own 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 optimized 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 specialized templates for nonprofits, as well as personal cash-flow forecasts. These diverse templates offer different models to draw from when creating your own templates.
• Governments and regulatory bodies sometimes offer accounting guidance and templates for businesses as well. The Victoria State Government in Australia, for instance, has a very detailed cash-flow forecast template that may help spark ideas even for U.S. businesses. Just try to make sure to make any relevant adjustments before using it.

The Bottom Line

Most businesses have had to deal with unexpected expenses when equipment malfunctions or an order doesn’t go as planned. And 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. And with a cash-flow template tailored to your business’s needs, you can ensure that your forecasts are based on accurate data and closely align with realistic expectations.

Photo: Getty Images