Follow the Leaders
Cash is the fuel of business. Run out of it, and your business engine shuts down, leaving you with a machine that doesn’t work.
For something so crucial to the life and success of a business, you would think it would be given more prominence in strategic planning and day-to-day decision-making, especially since important decisions with long-term financial impact are often made. And some of those decisions involve future investments, balloon payments, and contingencies. The best tool to predict and manage cash levels is a Cashflow Forecast.
Cashflow Forecasting not only helps prevent disasters but it also improves the speed and confidence in your business decisions. Specifically, this tool will:
Perhaps you have a Cashflow Forecast gathering dust on your CFO’s desk. That could be because you have enough of a cash position to absorb disruptions. It may not be scenario-based, unable to flex to changing circumstances, or model decision-making. It also could be because it took great effort to build and is challenging to update and adjust.
This article is written for small- to mid-sized organizations that use spreadsheet software, such as Excel, for their financial forecasting. The goal is to give you some best practices to consider in building a Cashflow Forecast. Hopefully, it will provide you some basic tenets on building that will make this a less frustrating experience.
I used to think long-term forecasting was nice to have, but I did not see it make a big impact. Then life experiences (and a great mentor) showed me this tool’s true value and power; perhaps the following examples can help explain. The first time I was involved in long-term forecasting was during a period of leveraged business growth when we also aimed to double our days in cash. I’ve selectively used it to model the cash impact of a series of loan balloon payments. I’ve also used it to model the needed adjustments and survival from a set of mandated minimum wage increases, which was set to double our wage costs.
A few years back, I saw the tragic results of not having this tool. I was called to help an organization on the precipice of bankruptcy where the use of the tool could have saved them from making a series of fatal business decisions. Like their industry peers, they expanded their footprint but ignored their industry’s operating metrics and certainly did not have a handle on the impact to their cash position. Blindly, they ran out of cash and seemed shocked that it was happening. And my most powerful use of cash modeling was during the shutdown in 2020 during the COVID pandemic. Modeling out the weekly cash requirements, we were able to determine what we could afford to do during the shutdown. It kept us in control and allowed us to avoid a lot of damage by overreacting.
A good model takes planning and an understanding of your business. But if built with the goal of updating with actuals and multiple scenarios, it will save a lot of time down the road. With a regular update of actuals replacing forecasted periods, you will be able to measure progress and see trends. We recommend purchasing forecast software if your budget allows it. Although I have worked with a custom forecasting model, the bulk of my experience is working in Excel, a tool that is within reach of most businesses.
The forecast interval can be monthly, yearly, or weekly. Often the bigger the disruption, the smaller the time intervals to focus on. During the freefall of cash due to the shutdown from COVID in 2020, we had weekly intervals forecasted. Monthly is the most common, with yearly totals. Daily is used more when a monitoring standpoint is needed and when cash is particularly tight.
The forecast length depends on the degree of change and the impact of future events. For instance, if you are a startup, going out for more than three years might not make much sense. Many strategic plans are for three to five years. Even with a three-year strategic plan, the extra two years can help model the changes that are happening in year three, for instance. If you have a major financial event, such as a purchase, financial payout, loan balloon, etc., that is six or seven years out; then it would be important to expand your forecast out to that event. If your business is investment intensive, taking the forecast out to that life cycle makes sense.
Scenario Planning is key to helping prepare for the future. Consider building a new product line, vertical growth, or territory expansion into your model. Another source is contingency modeling utilizing your work with an operational SWOT (Strengths, Weaknesses, Opportunities, and Threats) analysis.
Think about how your internal operating needs can direct the required modeling. Consider some of the following:
Consider external impacts from the economy or regulation.
Your forecast will include estimated information based on past, current, and projected financial conditions. Those metrics will speak to the health of your company. In a forecast, they can show you trends and warn you if you are projected to fall outside of healthy levels. These vary and can be unique to your industry; some to consider are below. The power of the forecast is that it will show you positive and negative trends in these metrics.
When building the Cashflow Forecast model, you want to anticipate future updates and scenario building. If you research cash flow models, it will be a unique model that doesn’t resemble anything you currently have. Don’t overcomplicate it. Instead, leverage your existing systems and reports you already use. Start with an existing income statement and balance sheet from your accounting software. Those two statements will give you the building blocks. Have the income statement separate each major business segment. You could bring in a cash flow statement, but I think it is easier to build it by adding and subtracting the differences between cash and accrual basis.
Download these financials from your system and have your model feed from these reports. Call this your “data” or “input” tab in Excel. This is key because you want to be able to update your model with history and new budgets without starting from scratch or manually keying in this information to update. Alternatively, with a fixed format, a cut-and-paste method can be just as effective.
Our preference is that the sections of the Cashflow Forecast model first start with rows of variables, second the income statement, third balance sheet, fourth cash basis adjustments, and at the bottom key metric calculations. The top rows of variables are fields that the model will feed from. For instance, interest rates, margins, inflation rates, wage increase rates, etc. Having these on separate tabs is possible, but I like having them all on one tab labeled “Detailed CFM.”
Build a Summary tab that condenses the information from the “Detailed CFM” tab. This will give you a summary view of the key information the detailed model produces. This can be any number of key drivers, such as cash balances, days in cash, topline revenues, net income, wage costs/ratios, margins, etc. Build your variables on this Summary tab and have the Detailed CFM reference them. This would let you change variables, such as inflation or price increases, on the Summary tab and instantly see the results.
Use this article as a roadmap that can be customized for your systems. Update it as needed but try for every six months. Model a scenario. It’s a model that will increase in functionality and power. The more you exercise its use, the stronger it will become. You will find that it will be a very popular report for your management team, bankers, and board. But there is a warning; if you tell these groups you have it, they will want to see it.
Helen Dow joined Warren Whitney as Director of Warren Whitney. She has over 30 years of experience helping companies improve their financial performance and enhance all finance and accounting functions. Helen generally serves Warren Whitney’s clients in the role of Fractional CFO/Controller. To learn how Helen or our other consultants can support your business, contact Stephanie Ford at firstname.lastname@example.org or 804.282.9566.