Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Hedge Fund InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open

# Revolver Debt

Step-by-Step Guide to Understanding Revolver Debt

Last Updated October 29, 2023

## What is Revolver Debt?

In most 3-statement models, the revolver, or “revolving credit line”, acts as a plug to ensure that debt automatically gets drawn to handle projected losses.

Cash does the same thing when there’s a projected surplus, such that if the model projects either a:

1. Cash Surplus → The model simply adds the surplus to the prior year’s ending cash balance to arrive at the end-of-period cash on the balance sheet.
2. Cash Deficit → The model uses the revolver as a plug, such that cash losses lead to additional borrowing. In effect, this feature ensures the cash balance doesn’t go negative.

## Revolver Debt Calculator – Excel Template

Use the form below to download the Excel file that goes with this lesson:

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

Submitting...

## How to Model the Revolver in a Financial Model?

A simple sequence of exercises will highlight how these plugs work in a model. Below we present a simple income statement, balance sheet and cash flow statement.

All three statements interrelate correctly (see how to do this here).

## Revolver Calculation Example

Assuming you want to maintain at least \$100 in cash during the forecast, is the “plug” cash or the revolver? Why?

As you can see in the solution below, the “plug” here is cash. There is a surplus, so the model simply adds the excess cash generated during the period to the end-of-period cash balance:

## Revolver Calculation Exercise Example

Here we’ll change the income statement expenses from \$800 to \$1,500. Assuming again that you want to maintain at least \$100 in cash during the forecast, is the “plug” cash or the revolver?

In this case, the revolver becomes the “plug.” That’s because the business generated significant losses and in the absence of a revolver, cash balances would turn negative. Here is the answer:

## Revolver Formula in Excel

While the underlying logic in the example above is fairly straightforward, the Excel modeling required to make the plugs work dynamically is a little tricky.

Let’s examine the revolver formula on the balance sheet more closely. How does the revolver balance know it should grow if there’s a deficit, but to shrink and never dip below zero when there’s a surplus? The MIN function in the example below accomplishes this:

## Revolving Credit Facility: Borrowing Base (Inventory and A/R)

Of course, if you’ve built a model that’s showing sustained cash losses that a revolver is now funding, it may be worthwhile to revisit your other assumptions.

Why? Because in reality, companies primarily use a revolver to fund short-term working capital shortfalls, rather than long-term cash losses.

There’s also a practical limitation on how much a company can draw on its revolver. Specifically, the amount companies can borrow from the revolver is commonly constrained by a “borrowing base.”

The borrowing base represents the amount of liquid assets securing the revolver, which are usually accounts receivable and inventory.

Formulas can vary, but a typical formula is: 80% of “liquidation value” of inventory + 90% of accounts receivable.

## How to Interpret the Revolver Debt Balance?

If your model’s revolver balance is growing, perhaps you’re forecasting poor performance, too much spending on capital expenditures, dividends, high paydown of long-term debt, etc. In this case, you’ll want to revisit your

For example, if you’re forecasting operating losses and high dividend payments, you may want to reduce the dividend payout assumptions because companies generating operating losses won’t likely keep paying high dividends since they need to conserve cash.

However, if you believe your forecasts are reasonable, and you’re still forecasting losses, it’s likely the company will seek additional borrowing to address these losses down the road.

To reflect this concept, it’s preferable to reflect the additional required borrowings in long-term debt.

## How to Handle the Revolver Circularity in Excel?

The revolver is a way to handle a situation in which deficits are projected, while surpluses simply increase the cash balance.

A related issue that emerges in forecasting is that model plugs can create potentially problematic circularities in Excel.

For further learning on the mechanisms and how to handle the circularity created by the revolver, go to the “Circularity” section of this article on financial modeling best practices.

Step-by-Step Online Course

### Everything You Need To Master Financial Modeling

Enroll in The Premium Package: Learn Financial Statement Modeling, DCF, M&A, LBO and Comps. The same training program used at top investment banks.

Inline Feedbacks
Yohann
March 16, 2019 11:20 am

PS: “in the Excel spreadsheet, “minimum cash desired”, since hard-coded, should be in blue 😎
Y

Jeff Schmidt
March 18, 2019 7:24 pm

Yohann:

Yes, this should be a blue font. Good catch!

Best,
Jeff

Sal
May 6, 2021 4:23 pm

I dont see a modelling course for Asset Based Lending or which one should one go for to be able to model for an ABL structure?

Jeff Schmidt
May 6, 2021 5:03 pm

Sal:

Our regular Financial Statement Modeling and Valuation courses don’t explicitly model ABL revolvers (although we discuss typical borrowing base calculations). You could look into our Real Estate and/or Project Finance courses, however.

Best,
Jeff

Sal
May 6, 2021 5:33 pm
Reply to  Jeff Schmidt

Real Estate and Project Finance for working capital financing model? Really..thats odd!!

Jeff Schmidt
May 7, 2021 11:39 am

Sal: I just think those courses do a deeper dive on debt, but you are correct in that it technically doesn’t use working capital to determine a borrowing base. Our Thirteen Week Cash flow course does but the focus is on the Cash Flow model and not an ABL. Best,… Read more »

Sal
May 7, 2021 11:44 am
Reply to  Jeff Schmidt

What would you recommend Jeff- which course would be most similar to ABL, of the courses offered by your group then? Would appreciate your thoughts. Thank you.

Jeff Schmidt
May 7, 2021 2:26 pm

Sal:

Probably the 13-Week Cash Flow, although again it’s a fairly small part of the overall course.

Best,
Jeff

Zeco Markovic
September 2, 2020 7:29 pm

Hello, I’m just a bit confused about scenario one. You say we add the surplus to our cash line item and for 2010 you did that, where NI = 200, and Cash is 100. However, for the next few years you add the total net income to cash balance. Is… Read more »

Jeff Schmidt
September 3, 2020 8:36 am
Reply to  Zeco Markovic

Zeco: We are not adding net income to cash… but the total change in cash from the cash flow statement. It merely appears that is what we are doing. For scenario 2, we are borrowing enough to offset our cash deficit but this will not increase our total cash balance,… Read more »

Krystal
November 21, 2019 8:35 pm

What happens if there is an interest rate associated with the revolver? As in if the company had to pay 10% pa on the average drawn balances outstanding

Jeff Schmidt
November 25, 2019 1:45 pm

Krystal:

There is always an interest rate associated with the revolver. This interest expense reduces net income, which increases revolver borrowings. As the article mentions, we cover this in the Circularity section, which is linked at the bottom of this article.

Best,
Jeff

Hugh
October 16, 2021 3:04 am

That is where you will certainly get into circularity. I think there are 2 ways to get around this: (1) calculate the interest manually and hard code copy it into the model or (2) use the vba code to do so.

Jeff Schmidt
October 16, 2021 10:03 am

Hugh:

I agree with your point about this is how the model becomes circular. However, there are definitely other alternatives to hardcoding it into the model (I would never recommend this!), as well as needing VBA to resolve the circularity. See this lesson: https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/#circularity-header

Best,
Jeff

Andres
December 14, 2018 11:50 am

Thanks a lot! This was really helpful, I was having trouble modelling the revolving debt without creating circular references.

Haseeb Chowdhry
December 17, 2018 2:13 pm