13-Week Cash Flow Model (TWCF)

How to Build a 13-Week Cash Flow Forecast?

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.

What is the Structure of a 13-Week Cash Flow Forecast?

While every 13-week cash flow model will show receipts and disbursements unique to its business and circumstances, most thirteen week cash flow models follow a similar structure:

Structure of a 13-Week Cash Flow Forecast.

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:

Get the Free Excel Template!

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

How to Analyze the 13-Week Cash Flow Model?

In corporate restructuring and bankruptcies, the 13-week cash flow model (TWCF) is a critical tool to understand the circumstances, which underpin the decision-making process (and guidance) provided by restructuring professionals on behalf of their client(s).

By identifying the immediate cash flow needs at the most granular level, the model helps distressed firms evaluate the immediate impact of various possible operational, financial, and strategic remedies:

Why is the TWCF Model Important?

A credible 13-week cash flow model (TWCF) can quite literally determine the outcome of an in-Court bankruptcy proceeding – i.e. (Chapter 11) or forced liquidation (Chapter 7).

The reality for many liquidity-constrained companies under financial distress is that even if they are viable as a “going concern”, the debtor (and advisors) 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 a credible 13-week cash flow forecast.

The TWCF is designed to increase transparency and trust between management, creditors and other stakeholders, i.e. the stakeholders with a vested interest in the restructuring.

What are the Components of the 13-Week Cash Flow Model?

As mentioned earlier, each thirteen-week cash flow model (TWCF) is unique, but there are several common elements you will encounter in nearly every model.

The structure of a standard 13-week cash flow model (TWCF) is as follows.

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 actual historical data).

The bottom section of the model usually contains a summarized 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.

To arrive at the consolidated 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-Forward Schedule

Forecasts for balance sheet items, most notably working capital items, are critical for a 13-week cash flow model.

The operating drivers (or model assumptions) regarding the timing of near-term vendor payments, payroll, and inventory purchases often have a 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 usually come from A/R aging, and 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 Schedule

Historical inventory data 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. Cash disbursement forecasts then reduce the roll-forward for wages. Because these are contractually defined payments, disbursements are usually fairly predictable, and companies can generate them from their payroll systems.

Accrued wages and benefits often represent the largest disbursement in most cases of financial distress.

How to Model the Borrowing Base (Revolver)?

For debtors quickly running out of cash, existing lines of credit and revolving credit facilities are often the last line of defense.

However, complicated borrowing base formulas constrain these credit facilities, along with other limits that can materially reduce additional cash availability.

Being able to model the actual availability a company has will be critical to quantify the amount of unmet funding needs and necessity of either DIP financing (or alternate financing strategies for an immediate cash injection).

Advanced Features in Restructuring Modeling (RX)

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 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.