A mission-critical financial report, the 13-week cash forecast helps businesses ensure they never run out of cash. While that sounds simple, running out of money is one of the most common reasons why new businesses close down in the first five years.
By creating a 13-week cash forecast for your company, you can ensure that you have enough cash on hand to cover your operational expenses and prepare for the unexpected.
Contents
Benefits of Creating a 13-Week Cash Forecast
The 13-week forecast allows businesses to predict its cash flow for a full fiscal quarter. Typically, the further a forecast extends, the less accurate it will be. Finance professionals like to use 13-week forecasts since they allow you to plan ahead while finding a balance between the forecast’s accuracy and strategic value.
“Having a robust 13-week cash flow forecast will assist in your communication with the banks and other key stakeholders as it better monitors debt covenants, debt service coverage ratio, cash conversion cycle, and debt capacity.”
– Deloitte
While no business aims to experience a cash shortage, it can be helpful to know when a shortage will arise as it gives the business time to be proactive and take measures like identifying and eliminating discretionary spending, increasing controls over cash and even exploring financing options to avoid missed payments.
In short, 13-week cash flow forecasts are beneficial because they help:
- Ensure your business is adequately capitalized
- Identify and plan for cash shortages
- Properly allocate cash surplus
- Plan for different scenarios (from best case to worst case)
Why Teams Fail to Properly Forecast Cash
There are always hurdles when it comes to forecasting cash flow. When teams fail to properly forecast cash, it tends to be for one or more of these reasons:
- Difficult-to-use spreadsheets – Love Excel or hate it, you can do almost anything you want with it, but that also means there’s a lot of room for error. Especially when you have multiple people working in the same spreadsheet. How do you know which macros and formulas they used?
- Lack of automation – Teams often spend too much time manually entering data into spreadsheets and double checking that it’s correct.
- Data wrangled from multiple sources – To properly forecast cash, it’s important to work with data from your ERP as well as your bank portal(s). This can be a major headache as your company grows multiplying the sources of its financial data.
- Multiple banks and subsidiaries – It is time-consuming to obtain data across all banks and accounts.
- No scenario planning – Teams that forget to plan for a multitude of scenarios fail to be proactive. An essential element of your cash forecast, scenario planning helps assess your capability to withstand disruption and the options you have to identify and respond to potential opportunities.
- No variance analysis – The most important and ignored area of cash forecasting is variance reporting. That’s because It’s time-consuming, however, when done right, variance reporting makes you better at forecasting cash flows over time.
As my colleague, Chris Brown, said about cash forecasting, “Let’s get real here, the weatherman isn’t always right either. Yet, there are things you can do to make your life a little less stressful.”
At Trovata, we set out to make cash flow forecasting easier and more accurate for our users. Where cash forecasting used to be a mess of disparate spreadsheets and scattered data, Trovata makes it simple by collecting and normalizing data and generating a forecast, all in one platform.
How to Generate a 13-Week Cash Forecast
Here are the steps to build a 13-week cash flow forecast:
- Define an objective for your forecast
- Tap into reliable data sources
- Consolidate historical data points into streams
- Determine the most effective way to project future cash flows at a stream level
- Monitor, learn, and refine your stream assumptions
- Explore running various scenarios in your 13-week forecast
Let’s take a closer look at how you can achieve these steps and what they would look like if you’re using Trovata.
Step 1: Define an Objective for Your 13-Week Cash Forecast
What are you hoping to learn from this forecast? Which accounts and/or business entities would you like to include in your model and why? How often do you plan to review and refine assumptions in your forecast?
Here are the most common 13-week cash forecast goals we hear from our customers:
- “Enhance visibility into our weekly cash flows.”
- “Manage the key components driving cash burn.”
- “Forecast how customer receipts will impact our cash flow.”
- “Forecast an ending cash balance for the quarter.”
Trovata’s forecasting solution is built to accommodate everything from a single account cash flow projection to a full global cash forecast broken down by business entity.
To get started, navigate to the “Forecasts” page on the main menu and click “New Forecast.”
Type in a name for your 13-week forecast and click “Next.”
From here, you can decide on the scope of your forecast by selecting one or more accounts that align with the objectivel of your 13-week forecast.
If your objective is to forecast just your US entity, then you’d select the accounts associated with that entity. This drives your starting balance and can be edited at any time if your objective changes.
Step 2: Tap into Reliable Data Sources
When it comes to building accurate models, a strong foundation is a prerequisite to success. Typically, treasury teams have a (somewhat manual) process to extract, clean, and normalize data at least once a day from bank portals and ERPs, increasing the potential risk or error.
We recommend gathering between 6 months to a year of historical balances and transactions to use in a forecast, but the more history, the better.
From your bank data, you’ll want to pull transactions and balances. From your ERP system, gather your receivables and payables.
In Trovata, we do the heavy lifting for you by integrating directly with your banking partners and automating the collection, normalization, and storage of balance and transaction activity as it happens. This is possible due to our library of secure, open banking APIs.
Removing this burden for our customers gives them back several hours of manual work, and gives them access to a secure and accurate data lake that can be used for forecasting and report building.
Step 3: Consolidate Historical Data Points into Streams
After you’ve gathered a reliable repository of transactions and balances for at least the last 6 months, the next step is to categorize the transaction activity into cash flows.
This could be as simple as categorizing all inflows and outflows or a bit more depth by grouping types of inflows and outflows.
Common inflow categories:
- Lockbox/Deposits: Deposits from checks or cash gained from sales
- Wire transfers: Wire transfers from sources outside the organization
- ACH: Deposits as a result of batch payments from clients
- Borrowing: Any cash gained from short-term loans
- FX: Cash obtained from differences in exchange rates
Common outflow categories:
- Payroll: Outflows as a result of paying employees
- Account Payables: Outflows from paying vendors
- Wires: Outflows via wire transfers to other bank partners or vendors
- Debt Payment & Debt Maturity: Outflows as a result of paying off debt
- FX: Outflows as a result of losing cash due to a lower exchange rate
In Trovata, traversing your transaction data and categorizing cash flows is as easy as a google search with Data Streams. Data Streams allow our users to analyze all transaction activity and query cash flow streams that can be added to one or more forecasts. Users also have the flexibility to upload a stream to capture activity outside of their Trovata instance.
If you are not sure where to begin on your stream building journey, we recommend breaking down your transaction activity by inflows and outflows starting with the highest volume account. Tags or “saved searches” can also be used to expedite stream creation for recurring transactions that have already been categorized.
To create data streams, click “Forecasts” from the navigation menu and then “Data Streams”. From here, select “New Stream” in the top right corner, followed by “Data Stream” to build a stream based on transaction activity.
Input a name for your stream and click “Next.”
Select “Analysis” to query all of your transaction activity.
Input a search term and/or leverage the filters to the right of the search bar to increase precision.
Pro Tip: you can also select one or more tags to include in a stream by using the “Tag Filters.”
Step 4: Determine the Most Effective Way to Project Future Cash Flows at a Stream Level
Some cash flow streams are a lot easier to forecast than others. Start with the predictable streams first, before moving onto the more challenging ones. For example, forecasting office rent may be a matter of factoring in several fixed monthly payments representing each office.
In Trovata, users can easily “repeat” history for streams with fixed recurring payments.
Other cash flow streams like revenue, vendor payments, and lockbox deposits require assumption based calculations rooted in historical activity to properly project accurate forecasted values.
A couple of common calculations that are used include:
- Exponential Moving Average (EMA)
- Simple Moving Average (SMA)
In Trovata, we allow Machine Learning to analyze your historical data and give you four distinct options to choose from at a stream level.
Continuing past the “New Stream” flow from Step 3, make sure your stream criteria is confirmed and you are capturing a date range greater than 6 months before you click “Next: Forecast Method”, in the top right corner and then click Machine Learning (ML).
Trovata will produce four distinct forecasts for a given stream and allow the user to decide which one fits best. Each model is unique, and its characteristics are displayed above the graph in the grey section.
Each ML model provides forecasted values (in green) and actual values to compare against (in blue). You can see the characteristics of each model above the graph in the grey section.
The 4 models and their characteristics:
- Model 1 – Accurate and fast, meshes well with weekly seasonal trends but poorly on stock-like graphs, built from multiple regressors.
- Model 2 – Meshes well with general seasonal trends but poorly on stock-like graphs, built from multiple regressors.
- Model 3 – Great at weekly, monthly, and quarterly seasonal trends, performs poorly on stock-like data, built from lagged based regressors.
- Model 4 – Works well for arbitrary stock-like data, performs poorly on fluctuating seasonal trends, built from a system of neural networks.
Once you’ve decided on a forecast model, click “Done” and make sure to add your stream to your forecast.
To add a stream to a forecast, first select the forecast, click the three dots in the top right corner, click “Settings”, select the “Data Sources” tab and click the CTA for “Add/Remove Sources.” Check the Data Stream that you wish to add and click “Done” to save your changes.
Step 5: Monitor, Learn, and Refine Stream Assumptions
At first, it’s best to monitor a new forecast daily in order to evaluate your initial assumptions.
This can be done most effectively by comparing your forecasted values to your actuals at a stream level, given that your data is being appropriately refreshed with reliable actuals.
In Trovata, we provide an automated feed of actual transactions directly from your banking provider that are seamlessly appended into the applicable forecast stream, so that you can focus on managing forecast assumptions and not waste time manually pulling, cleaning, and updating your model with different formats of downloaded transactions.
The best way to evaluate forecast performance is to review the variance of each stream.
In the screenshot above, you can see that each stream includes Forecasted Values, Actuals, and a variance percentage that compares the two. The configuration icon in the top right corner of the table allows you to hide rows for Actuals and variance percentages.
Depending on the stream type (Analysis-based, Invoice-based, or manual), you can work on refining assumptions for each stream in the “Data Stream” section of the app and have the changes impact all forecasts that they are part of.
Step 6: Explore Various Scenarios in Your 13-Week Forecast
After you’ve established a “base” 13-week forecast, we recommend duplicating this forecast in order to test different scenarios.
In Trovata, click the three dots in the top right corner and select the “Duplicate” button, and input a name.
Then, add factors to the duplicated version of your forecast, and review its impact in your new scenario.
To add a factor, click the three dots in the top right corner of the forecast, click “Settings,” and select the “Factors” tab on the far right side.
Here is where you can choose a stream featured in your forecast and add a percentage increase or decrease to the forecasted values during a specific time period.
In the example below, I decreased my two revenue streams by 25% each week between 11/1 and 12/31.
The flexibility of our forecasting tool encourages on-the-fly scenario testing, which is important to understand how various impacts to cash flows and market conditions may impact the overall health of a business.
Get Better Cash Insights to Drive Better Decisions
I hope this helped you better understand the benefits, challenges, and steps to make your 13-week cash flow forecast! When done right, cash forecasting helps your team be prepared for whatever scenarios it might face in the future, mitigating the disastrous effects of unforeseen events like the pandemic on your business operations.
I may be biased, but the key to creating a fast and reliable forecast quickly is to use a cash forecasting platform that makes the process seamless.
That’s because, without a platform like Trovata, if you want to run a scenario analysis or generate a forecast, you have to start with a clean data set every time. You’d have to go into every single one of your bank portals, consolidate your transactions and balances, and place them in one spreadsheet, hopefully without any errors. Time to try a better way to forecast cash flow, using automation and machine learning.
If you haven’t already, get started with Trovata for free!
