- 16th April 2018
- Posted by: Manolis
What are weekly cash flow forecasts?
How are weekly cash flow forecasts useful?
How to construct a weekly cash flow forecast
Why Every Business Should Build Weekly Cash Flow Forecasts
“The fact is that one of the earliest lessons I learned in business was that balance sheets and income statements are fiction, cash flow is reality.”
– Chris Chocola
When most finance professionals hear the term “13 week cash forecast,” they view it as a burden—one more task to appease an overbearing lender. Most finance professionals do not get nearly as excited about building it as they do about building a projection model for an acquisition or investment. It doesn’t help that companies generally tend not to focus on their liquidity needs until they are forced to do so. Therefore, people often only prioritize the weekly cash forecasts in distressed situations, when it is too late to take corrective actions. And even still, the analysis is often hastily executed and inaccurate.
Through my time in private equity and consulting, I have witnessed how beneficial the compilation of weekly cash forecasts can be, in industries ranging from distribution and manufacturing, to fitness and services. In almost all cases, the involved companies wished that they had conducted the analysis sooner. Therefore, it’s my strong belief that weekly cash forecasts are crucial for businesses large and small, healthy or distressed, and across all sectors.
What Are Weekly Cash Flow Forecasts?
Weekly cash forecasts are used to project a company’s liquidity over the medium term, estimating the timing and amounts of cash inflows and outflows. The weekly interval forces companies to understand the details of their business at a more granular level. For example, cash inflows could be large one week if a large amount of receivables are collected, but outflows could be huge the next if payroll and rent are due. Breaking the business down on a weekly basis captures the granular movements that can be overlooked if using a month, quarterly, or yearly interval. Why not do a daily forecast, then? In my experience, it can be excessive since it introduces seven times the variables as a weekly forecast, and may not improve the accuracy of the forecast. Therefore, the weekly interval provides a happy medium in achieving granularity without overwhelming detail.
With regards to an optimal forecasting period, the industry standard is 13 weeks, the number of weeks in a fiscal quarter. Ideally, a company should understand how revenues and costs will be incurred in this time frame. If you only project out four to eight weeks, it will be difficult to effectively react to liquidity issues. The optimal time period should extend far enough into the future to give your team time to react, but not so far out that the degree of certainty becomes nil.
Arguments Against Weekly Forecasts Are Short-sighted
I’ve heard, and some of you may have even used, one or more of the following reasons to not do the forecast:
- “That’s great, but these were all distressed situations. My business is healthy, so this is a waste of time.”
- “I have a small team (or no team) and I don’t have time to put together another forecast”
- “My company is growing well, and there is nothing on the horizon to lead me to believe otherwise”
- “My business is different than the ones you described, so cash forecasting doesn’t apply to my company”
- “My shareholders/lenders/parent company believes in us and has deep pockets. Even if we have a problem, they will fund any cash shortfall we might have”
While relatable, these are all short-sighted. Every company, no matter how strong, will inevitably face difficult times. Let’s not forget The Great Recession, when blue chip companies on top of the world (e.g., Goldman Sachs, Morgan Stanley, Lehman Brothers, Bear Stearns, etc.) were brought to their knees. Now, would a weekly cash forecast have prevented the disaster? Perhaps not. But, what I can say with certainty, is that none of these companies had a good handle on their liquidity needs, something which could have mitigated the eventual damage. There are always limits on liquidity and it behooves an operator to know those limits.
Why Are Weekly Cash Flow Forecasts Useful?
Forces discipline through “cash is king” mentality
Operators cannot hide behind accounting tricks to hide underperformance. GAAP can help conceal business issues, but it is hard to cover problems when focusing on cash. Focusing on the near and medium term time frames can uncover potential issues quickly and can help businesses subject to seasonality get through off-seasons.
Enhances understanding of customers and suppliers
The customer and supplier stratification process provides insights into key customers and suppliers, depending on the situation:
- If a customer is paying slowly: It can be used as an excuse to call a customer. Instead of simply demanding payment, it can be another revenue generating opportunity—assuming your company still wants business from this customer
- If certain suppliers offer early pay discounts: Having a handle on cash flow can enable a company to take advantage of these discounts and increase profitability
- If certain vendors are relaxed on the enforcement of their terms: A company can stretch some of their flexible suppliers to decrease net working capital and increase cash
Helps businesses understand the cost of growth
Growing companies are often cash-constrained because capital expenditures and inventory investments must be made ahead of the revenue associated with the growth. Understanding near-term liquidity needs enables a company to plan for this growth and raise the appropriate financing. It thus also helps a company avoid failure to deliver or worse, major financial distress.
Reduces cost of capital
By understanding liquidity, a company can minimize borrowing on credit to fund interim payments like payroll and rent. In other cases, a company can reduce the amount of cash kept on hand and instead deploy the capital through re-investment into the business, debt reduction or dividends.
Increases communication with other departments
In order to properly complete the cash flow forecast, the finance team must communicate with colleagues in sales, purchasing, accounts payable, accounts receivable, human resources, etc. It forces the finance forecasting experts to gain a fuller understanding of the business and how it operates.
When and for What Types of Businesses Is It Appropriate?
In many cases, financial stress can be avoided by understanding incoming and outgoing cash flow, and taking appropriate corresponding action. In all cases, a business can benefit from cash forecasting. Remember, CASH IS KING! It’s my strong belief that companies big and small, early and mature, should utilize this tool. The weekly cash flow forecast can even be tailored to every type of business.
Invaluable for companies in dire situations
When I saw my first weekly cash forecast in the fall of 2008, I admit that I was skeptical of its value. At the time, I was working for a distribution business that served companies in the transportation and construction industries. However, with the forecast, we were able to glean insights into when people were coming into our stores, and when cash was actually hitting our bank account. Despite the tough economic environment, basic “blocking and tackling” business performance improved as did cash flow. We realized that sales spiked on certain days of the month (the 1st, 10th, 20th) due to certain buying patterns. The company capitalized on this trend by running promotions those days to increase the average dollars per order. I quickly became a believer.
In another situation with a services business, we averted disaster because the weekly cash forecast accurately projected that we would run out of cash in the following month if the bank forced a mandatory repayment. The business did over $100 million a year in sales, but we were going to have a $1 million hole due to the major seasonality of the company. We used the forecast to convince the bank to reduce the amount of debt repayment they were requiring and let us get through the seasonal cash crunch to more profitable times. In addition to helping us avoid a crisis, the accurate insights into the business built our credibility with the bank when we presented a re-forecast to renegotiate our covenant package.
The exercise is also useful for healthy companies
Though counterintuitive, when business is great, it could make sense to get in the habit of weekly forecasting. Continue testing and honing the forecast so that when an issue eventually presents itself, the company can take the appropriate action to cut costs, hoard cash, and survive the rough patch.
For example, a company I worked with was performing well and had ample liquidity, but prudently still chose to pursue the exercise. It ended up being the right decision. Many suppliers in the industry offered early pay discounts, which the company had not leveraged in the past because it prioritized working capital. However, after running some sensitivities, we concluded that the uptick in profitability would be more valuable than the near-term drain from paying some costs sooner. In this scenario, the weekly cash forecast helped us achieve increased profitability, stronger relationships with suppliers, and higher operational efficiency.
How to Construct a Weekly Cash Flow Forecast
Weekly cash forecasts are not nearly as difficult as they are often made out to be. In this section, I’ll guide you step by step. First, here are a couple items to keep in mind:
- The model includes two major components. One side of the equation is cash receipts (revenue), and the other is cash disbursements (cash payments). We’ll address both.
- Garbage in, garbage out. Your analysis is only as good as the numbers and data you are inputting. Do your best to obtain accurate information before inputting into your model.
- Build for longer than 13 weeks, then roll it forward. Though the typical period of the forecast is 13 weeks, it is advisable to build one for a longer period and then roll it forward. After the 13 week period, update the assumptions as actual performance comes in and measure the projection against actual performance. As stated previously, use a period that gives your team time to react, but isn’t so far out that the predictions become highly inaccurate.
- Use “shoe box accounting.” The most difficult part for finance professionals is changing their mentality from a GAAP accrual method to cash accounting. Think of your business as a shoe box, and the only concern is the cash that goes in and goes out—hence “shoe box accounting.” Forget GAAP items like revenue and COGS recognition, GAAP rent expense, bonus accruals, PIK interest and goodwill impairment—they mean nothing in this analysis. Sometimes the GAAP complexities and attributions shroud the true financial state of a company. Instead, focus on cold, hard cash: cash receipts, payments for inventory or services, cash interest payments, cash rent, mandatory debt principal repayments, etc.
This is particularly important if your business has a large deferred revenue component and collects cash payments in advance of recognizing revenue. In a school I was involved with in the past, tuition was paid in advance of the school year. Therefore, July had a major influx of cash, but no revenue. The subsequent months recognized revenue, but no cash came in.
Let’s get started. For the purposes of this exercise, we will use a consulting business that is paid a flat weekly rate per consultant. It also has a variable, but predictable, multiplier based on weekly hours worked. In addition, they receive large success fees at the end of certain projects. We will also assume that the business rents office space and also has debt.
Step 1: Set Up the Spreadsheet
The first step is setting up the spreadsheet. Add week-ending dates across the top; my personal preference is for the date to be the Friday at the end of the week, but you can also use the Monday at the beginning of the week, either way is fine. Down the left-hand column, create rows for cash receipts (recurring and one-time in nature) and disbursements (mandatory and discretionary). Fill in and analyze 3-4 weeks of actual data to draw a trend, and then project from there.
Please note that discretionary disbursements include those costs where the company has a bit of leeway as to when to pay, in contrast to something like payroll, which must be paid on the same day every month.
The spreadsheet below projects beyond 13 weeks, but I only show those 13 weeks. To keep rolling the cash flow forecast forward, you can simply unhide the next column and hide the most distant actual week. In the below example, I’ve included both recurring and one-time cash receipts, typical disbursements such as payroll and rent, as well as discretionary disbursements such as recruiters and key vendor payments.
Step 2: Focus on Cash Receipts, Choosing between four types
After setting up the spreadsheet, focus on cash receipts, which is often the most difficult thing to project. You will need an Accounts Receivable Aging, a report that details accounts receivables by the length of time an invoice has been outstanding if business sells on credit. You’ll also need a sales pipeline or forecast. Clearly, it is critical to make this a collaborative exercise with sales and marketing colleagues. Next, understand how the business makes sales and collects cash. Generally, there are four models:
Type A. Contractual Cash Receipts: Fixed timing and amount
This type of cash receipt is by far the easiest to account for: Simply go through each contract and schedule out when payments are due. First calculate the weekly revenue, then consider the payment terms. A key data point to understand is the portion of contracted revenue that either 1) is paid late or 2) resulted in bad debt. If 5% of payments come in late, make sure your cash flow projection reflects that. If 2% of contracted payments never come in, make sure to reduce the contracted revenue by 2%.
In the sample consulting firm, the business is paid a weekly rate per consultant. We have assumed $5,000 per week per consultant. I’ve also assumed a small amount of bad debt (2%), but no late payments. Revenue earned in a month is due on the 10th of the following month. This means that weeks including the 10th will have larger inflows, while other weeks with expenditures like rent and payroll can lead to major cash drains.
Type B. Recurring Cash Receipts: Revenue with some pattern
This type of receipt is a bit trickier, but there are ways to use historical data to forecast. In my opinion, these are the most interesting types of business to forecast; though they require more analysis, one can get closest to reality. I have worked with many businesses with this model, and it wasn’t until we looked at the data that we uncovered important patterns. Most retailers fit this business model, as do service and manufacturing businesses that take inbound orders from repeat customers. For retailers, certain days (weekends, holidays, etc.) are often large revenue days while others (like Mondays) are slower. In the case of service and/or manufacturing businesses, often these customers will order or require service in a regular timeframe.
I. The first step, assuming the data is available, is to lay out sales by segment, branch, business line, or however is best to evaluate the business, by day. Depending on your business, you’ll want to use data going back a few months or even a couple years. If the business isn’t very seasonal, a few months will do. If it is seasonal from month-to-month, it’s best to use a couple years of data.
II. Next, look at the daily revenue on a monthly or weekly basis. Calculate what percentage of revenue comes in on the first, second, third, etc. day of the month on average. You’ll also want to calculate the percentage that comes on a Monday, Tuesday, etc. for weekly revenue. Before long, insightful patterns will start to emerge. In my experience, companies often had a monthly rhythm in that X% of revenues came in on the 1st, 2nd, 3rd, etc. It wasn’t perfect, but it was much more accurate than assuming revenues came in ratably over a month, and it gave a better starting point to project cash flows.
III. Lastly, aggregate the data from the last few months (average the % of monthly revenue to come in on each day of the month) and use that as the basis for future months. Please note that it is a good idea to use a few data points for 30 day months and 31 day months. Make an estimate if February (28 days) data is not available.
In the example below, we have used a much simpler assumption for these recurring payments. Our consulting firm will ask for extra payment when their consultants do extra work that is beyond the scope of the project. On average, the firm asks for an incremental $1,000 per week for roughly 20% of its consultants, based on historical averages. In these instances, the client companies pay the incremental fee 90% of the time (making bad debt 10%), on the same terms as the contacted weekly rate.
To further demonstrate this step, I have included an example from a retail company completely unrelated to the sample consulting firm. For the most part, we expect projected weekly sales to break down in a similar manner to historical performance. Since Saturday and Sunday have accounted for ~27% and 26% of weekly sales, respectively, we project similar numbers into the future. We also have to make estimates for certain anomalies, such as holidays and larger-than-normal days, like Black Friday. Others are highlighted, including Labor Day, Thanksgiving, and Christmas.
Type C. One-Time Lump Sum Cash Receipts: One-time in nature
This type of receipt is tough to project and requires extensive communication with the sales team. It is also advisable to use conservative estimates based on what the sales team says since their role always has an inherent unpredictability and is dependent on other parties. As a result, your business should avoid using these payments to fund operations and should think of these payments as “found money.”
In the below example, the firm has received large client retainers months in advance of starting a project, compensated for the time required to find appropriate staffing ($50,000 in September and $75,000 in November). These retainers are difficult to project in terms of magnitude and timing. In this example, the numbers reflect the reports by the sales team. However, in real life, it would be advisable to delay the timing of these payments by 3-4 weeks and also cut them in half.
Type D. Hybrid Cash Receipts: Combined elements of all three models
Projecting cash flow for a company with a hybrid business model may seem daunting, but it shouldn’t be. You can simply segment out the lines of revenue by type and use the forecasting methodology for each.
Keep in mind that what matters is when cash hits the general ledger or bank account, not when a sale is made. Sometimes the sale and payment occur simultaneously, which is ideal. However, other times, as is often the case with businesses that make a lot of sales on credit cards, there is a lag of a couple days. This may seem minor, but it is not—make sure to account for this difference such as recording the cash as coming in a couple days after the sale is made.
The consulting firm in our example combines cash receipt types. The contractual payments and predictable payments should fund operations, while the large client retainers ($50,000 and $75,000) should be treated as nice bonuses if and when they do come in.
Step 3: Focus on Cash Disbursements
On the other side of the equation for cash flow projections are cash disbursements, otherwise known as cash payments. In a well-run organization, these are much easier to project since there are generally good controls on who is authorized to spend and when payments are made. However, I have seen organizations without good controls be continually surprised with large expenses. If nothing else, the weekly cash forecast will force organizational discipline on who can spend and what their limits are.
I. The first step is to list out what payments must be made on certain dates. This list should include items like payroll, interest, debt principal amortization, rent, and utilities are payments with which a Company typically does not have much leeway to delay. Missing payroll is catastrophic and forces the business to shut its doors. Missing interest or principal payments puts the business in default with its lenders, and late rent payments result in large fines, if not foreclosure.
The first snapshots detail payroll, payments to the consultants, and the debt service schedule. The last snapshot includes the full mandatory payments roll-up including the aforementioned items as well as rent and utilities.
Consultants (1099 contractors)
Debt Service Schedule
Full Mandatory Disbursements Roll-Up
II. After scheduling the fixed payments, you must look at what is left in accounts payable. In this part of the exercise, stratify vendors into critical and non-critical vendors. Critical vendors are ones that can shut down operations if they decide to stop serving you: Think of Google for a digital search business or an aluminum supplier for a soda can manufacturer. They are the highest priority and must be paid before other vendors to ensure smoothly functioning operations.
In our example, the software provider and travel agent are critical vendors. The company uses a proprietary software for its work, and they use a third-party travel agent to get the best rates possible to get their consultants to the engagements.
III. The remaining vendors are considered less critical vendors, ones that a company can typically stretch without significant near-term harm. In the best case scenario, which you should strive towards, everyone is paid on time. However, in times of distress, you might be forced to use this area to free up some cash. If need be, a vendor such as an executive recruiting firm or an office supply vendor can be stretched a bit further. It’s also important to understand if there are contractual late fees, which you should avoid incurring. Obviously, understand the needs of the individual business; you should only stretch these vendors if necessary.
In the example below, pushing the Professional Fee and Recruiting payments into weeks where the expected cash balance is a bit higher and not below the $100,000 comfort level is a potential solution to stay above $100,000 (or whatever level the business finds comfortable for operations).
IV. Subtract disbursements from cash receipts for net cash flow. Add or subtract to beginning weekly balance to get ending weekly balance (which will be next week’s opening balance). The weeks indicated in red have negative net cash flow. If there is a deficit in the week end cash balance, either draw on the credit facility (if available), or figure out how to increase receipts or decrease disbursements. If you’ve identified an issue, talk about it with key managers. You can plan, do not wait for the crisis!
In closing, weekly cash forecasting can significantly improve discipline and operational control. While it will not solve all problems for a fundamentally challenged business, it can improve business processes and functionality. Weekly forecasting leads to insights into the company as to where the business can save money, potentially increase revenues and increase cash flow. Even more importantly, if done properly with critical thinking and proper insight, it can help managers get ahead of any potential problems and plan appropriately. After all, Richard Branson puts it best: “Never take your eyes off the cash flow because it’s the lifeblood of business.”
UNDERSTANDING THE BASICS
What is a weekly cash flow forecast?
Weekly cash forecasts are used to project a company’s liquidity over the medium term, estimating the timing and amount of cash inflows and outflows. Breaking the business down weekly captures the granular movements that can be overlooked if using a month, quarterly, or yearly interval.
Why do I need a cash flow forecast?
How do you make a cash flow forecast?