What is a 13 week cash flow model
As the name suggests, a 13-week cash flow model is a weekly cash flow forecast. The 13 week cash flow uses the direct method to forecast weekly cash receipts less cash disbursements. The forecast is frequently used in turnaround situations when a company enters financial distress in order to provide visibility into the companyâs short-term options.
How the 13 week cash flow model is used in practice
In the example below, shutter-maker American Home Products filed this 13 week cash flow (“TWCF”) to support their request for a $400,000 Debtor-in-Possession (DIP) revolver in court:
Source: AHP 5/29/19 DIP Motion. Download the PDF.
AHP’s TWCF shows the company expects to need the additional financing almost immediately on June 7, 2019, followed by a second DIP draw July 5, 2019
While every 13-week cash flow model will show receipts and disbursements that are unique to its business and circumstances, most thirteen week cash flow models follow a generally similar structure:
Structure of a 13 Week Cash Flow Forecast.
Download the Free 13 Week Cash Flow Model Excel Template
Enter your name and email in the form below and download the free 13-Week Cash Flow Model Excel template:
The 13-week cash flow model is a tool for decision making
By identifying the immediate cash flow needs at the most granular level, the model helps distressed firms evaluate the immediate impact of a variety of possible operational, financial, and strategic remedies:
| Operational | Financial | Strategic |
|---|---|---|
|
|
|
Why is the TWCF So Important?
A credible TWCF often quite literally is the difference between survival and Chapter 7 liquidation.
The reality for many liquidity-constrained companies under financial distress is that even if they are viable as a going concern in the long run, they must convince prepetition lenders or a third party to extend debtor-in-possession (DIP) financing to bridge to a medium term and ultimately a long term plan. Securing this financing is almost always supported by credible 13-week cash flow forecast.
The TWCF is designed to increase the level of transparency and trust between management, creditors and other stakeholders.
13 Week Cash Flow is a Tool for Decision Making
Learn to Build a 13-Week Cash Flow Model From Scratch
Get the same training we deliver to some of the world's leading turnaround consulting & advisory firms, investment banks and distressed debt funds.
Enroll TodayModeling an integrated 13 Week Cash Flow Model
As I mentioned, every thirteen-week cash flow model is unique, but there are several common elements you will encounter in nearly every model.
13 Week Cash Flow Model Structure
The 13 Week Cash Flow Output
The 13 week cash flow output is the star of the show. It is a summary of cash receipts and cash disbursements over a 13-week period (usually with 1 week of actuals). The bottom of the summary will usually contain a cash forecast that identifies any additional revolver or DIP financing required to maintain a desired minimum cash balance. The screenshot of AHPâs 13 week cash flow above is an example of such a summary. In order to arrive at this summary, however, the other elements of the model below need to be constructed.
Cash to EBITDA Reconciliation
While the focus of the TWCF is on cash, reconciling the weekly cash forecast to a weekly EBITDA forecast helps management and other stakeholders connect the dots from managementâs profit forecasts which are used to support a sale or plan from emergence from bankruptcy to the companyâs short term liquidity issues.
Example of an EBITDA to Cash Reconciliation in a 13 Week Cash Flow Model
Working capital roll-forwards
Forecasts for balance sheet items, most notably working capital items are critical for a 13 week cash flow model. Assumptions about the timing of near term vendor payments, payroll, and inventory purchases often have material impact on the 13 week cash flow model. A properly constructed TWCF will reflect those assumptions in a âroll forwardâ â which identifies how key balance sheet items change week by week.
The Roll-Forward Summary Output:
Roll-Forward Summary
Accounts receivable roll-forward
Opening balances will usually come from A/R aging. Forecasts for future A/R driven off days sales outstanding (DSO) and even invoice-level assumptions for larger customers. Once combined with revenue forecasts, cash receipt projections can be made:
Inventory roll-forward
Historical inventory will usually comes from a company’s inventory ledger. The roll-forward adds inventory purchase forecasts and subtracts COGS forecasts (projected on the income statement). The purchase forecast is arrived at by forecasting inventory turnover / or days of inventory on hand (DIOH). Notice the inventory roll has no impact on cash disbursements directly – only indirectly via the AP roll-forward (below).
Accounts Payable Roll-Forward
Inventory purchases are referenced from the inventory roll-forward and inventory payments are back-solved based on both days payable outstanding (DPO) assumptions as well as vendor specific invoice reviews.
Accrued Wages Roll-Forward
Accrual-based wage expense forecasts come from the income statement. The roll-forward is then reduced by cash disbursement forecasts for wages. Because these are contractually defined payments, disbursements are usually fairly predictable and companies can generated them from their payroll systems. Accrued wages and benefits often represent the largest disbursement.
Borrowing base (revolver) modeling
For companies that are running out of cash, existing lines of credit and revolving credit facilities are often the last line of defense. However, these facilities are usually constrained by complicated borrowing base formulas and other limits that can materially reduce additional cash availability. Being able to model the actual availability a company has will critical for quantifying the amount of unmet funding needs requiring either DIP financing or an alternate strategy.
Additional TWCF Model Features
In addition to the elements discussed above, building an integrated 13-week cash flow model often involves the following modeling mechanics:
- Timing: Companies usually forecast on a monthly, quarterly or even an annual basis. Arriving at weekly basis forecasts thus often requires converting longer term forecasts.
- Weekly Updating: Unlike monthly, quarterly or annual models which have longer gaps between updates, the 13 week cash flow must be updated weekly. Every update adds risk of model error so it is important to construct a 13 week cash flow in a way that doesnât break the model every time you update it
- General Ledger and Accounts Mapping: One of the most time consuming parts of modeling the 13 week cash flow is identifying, aggregating and re-framing client data. Often the historical data that you need in order to build the 13 week cash flow model is scattered, incomplete with inconsistent (or outright incorrect) general ledger and expense categories. Understanding Excelâs data and reference functions can dramatically improve productivity when working with messy client data.