Building a cash flow forecast template is an important skill every corporate finance professional should possess. All businesses are concerned with maximizing cash flow, so having a system in place to forecast and monitor cash flows is critical for a finance department to execute on its budget. A template provides a systematic approach to forecasting cash flows that keeps the guesswork to a minimum and allows for extended periods to be forecasted.
Here are some helpful pieces of advice to take into consideration when building a cash flow forecast template.
Start With A Beginning Balance
This might seem obvious, but it is extremely important to point out. Make your starting cell the beginning cash balance for the period of time you are forecasting. The beginning balance will be used to calculate, or “roll,” the ending balance. The basic formula is “beginning balance” + “Cash Received” – “Cash Paid” = “Ending” Balance.”
A simple way to begin building your cash flow forecast is to start with your beginning balance for the period and tie it to a bank statement or some other documentation.
Notice that the manual input is in blue. Formatting is an important part of keeping track of which data fields are inputs and which are formula-driven. Some words of advice: choose a color to be used that will represent a manual data input. In most scenarios, blue is used to denote a manual data input.
Create Flexible Date Schema
Aggregating data by month does two things: first, it creates a monthly cash flow forecast template, and second, it allows you to easily create a 12-month cash flow forecast template. By creating columns for each month in the forecast period it creates a dynamic model that can be used in multiple ways.
This is an example of a date schema that will allow you to build a monthly, quarterly, bi-annual, and 12-month rolling cash flow forecast:
Notice how the columns are grouped to present data by month but also are consolidated enough to fit multiple months into one worksheet. This is an ideal cash flow forecast format for most businesses.
Establish Data Inputs
Every good model should have data inputs isolated and static. One of the worst things any financial model can have is stale data hidden within cells. While there are many approaches to creating layouts for models, one simple approach is to have one column for each time period in the model devoted to manual data inputs.
In a cash flow forecast template, it is important to identify all of the activities in the organization that drive cash receipts and payments. In most cases, fixed costs like salaries, payroll taxes and benefits, and rent expense are easy to identify. Remember, to exclude non-cash items on the income statement, like cost of goods sold, and instead replace with cash activity related to acquiring inventory.
Some words of advice: when establishing data fields, use names that match identically to your budget. This will allow you to link the budget to your forecast to save time and make the model more dynamic. The idea is to create a model that is flexible enough to use regularly, while also being a tool to measure the organization’s adherence to its fiscal budget.
Build In Formulas and Links
Once you have your data structured and you have a monthly template in place, it is time to make sure all of your calculation fields make sense. In most cases, subtotals are used to aggregate cash flows into receipts and expenditures. This makes rolling the beginning balance easier as you can simply add and subtract the appropriate subtotals.
It would be wise to link your budget column to a data set that comes from a unified source. This is one limitation of Microsoft Excel—decentralized and separately managed workbooks for various types of information. One way to bifurcate external links is to simply copy and paste the relevant data into a tab labeled “Data Input”.
See the example below:
Some words of advice, when creating data tabs: clearly label them, and always ensure to maintain the integrity of the source data. Link the “budget” column in your cash flow forecast template to the relevant time period and data point in the source data. This will allow you to copy and paste data from various time periods without having to update your column headings for a specific date. For example, using the source data above, we can switch our bi-monthly cash flow forecast template to a quarterly template:
Other Types of Cash Flow Forecast Templates
Some businesses call for daily monitoring of cash flow. Certain industries are more cash intensive than others and require a good understanding of what the cash balance will be at any given time. You can utilize the same budget you might use for a monthly, or even quarterly, cash flow forecast simply by identifying the days that fixed costs get paid.
An example of a daily cash flow forecast using the same data as the previous examples might look something like this:
Daily cash flow forecast template
When building daily cash flow forecast templates, be sure to only include payments on the expected date that they will occur. For example, payroll might only be paid on the first and the fifteenth of the month.
Daily cash forecasts can also be used as an internal control as it forces users to monitor cash balances daily against expected cash movements. Any organization can benefit from a more robust internal control environment.
Often, organizations have multiple bank accounts and a requirement that each bank account be monitored daily. This can present a challenge because it requires more manual inputs than a traditional cash flow forecast template.
One approach is to use bank accounts as column headings, being sure to transpose account numbers exactly how they appear on a banking export. This creates some efficiencies in being able to export bank balances at the beginning of each day to reconcile cash from the prior day.
The forecast uses projected cash activity under each account and then compares it to the actual ending balance of the account. An example of this type of forecast might look like this:
Although a cash flow forecast in this way does require manual input, it creates a template for both forecasting cash flows and monitoring bank accounts for control purposes, making it an ideal choice for organizations with multiple accounts.
Using Datarails to Build Cash Flow Forecast Templates
Datarails’ cash flow forecasting software replaces spreadsheets with real-time data and integrates fragmented workbooks and data sources into one centralized location. This allows users to work in the comfort of Microsoft Excel with the support of a much more sophisticated data management system at their disposal.
Every finance department knows how tedious building a cash forecast template can be. Integrating cash flow forecasts with real-time data and up-to-date budgets is a powerful tool that makes forecasting cash easier, more efficient, and shifts the focus to cash analytics.
Regardless of the budgeting approach your organization adopts, it requires big data to ensure accuracy, timely execution, and of course, monitoring. With Datarails’ financial planning solution, budgeting processes, profit and loss & balance sheet statement creation, as well as scenario planning are all automated and simplified. Datarails is an enhanced data management tool that can help your team of FP&A analysts create and monitor cash flow against budgets faster and more accurately than ever before.
