Wall Street Prep

Modeling the Revolving Credit Line in Excel (With Free Template)

In most 3-statement models, the revolving credit line (“revolver”) 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 ...

  1. ... a cash surplus, the model simply add the surplus to the prior year’s ending cash balance to arrive at the end-of-period cash on the balance sheet.
  2. ... a cash deficit, the model uses the revolver as a plug such that any cash losses lead to additional borrowing. This ensures that cash doesn’t go negative.

How a revolver works in a 3 statement 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).

Exercise 1

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

revolver1

Download Excel file

Solution 1

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:

revolver2

Exercise 2

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?

revolver3

Solution 2

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:

revolver4

The revolver formula

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. Here is the free excel template. Let’s examine the revolver formula on the balance sheet more closely. How does the revolver balance know to 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:

revolver5

Revolvers are secured by accounts receivable and inventory

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. That’s because in reality, companies primarily use a revolver to fund short-term working capital shortfalls as opposed to funding long sustained cash losses.

There’s also 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 vary, but a typical formula is: 80% of “liquidation value” of inventory + 90% of accounts receivable.

Master financial modeling! Used at top investment banks, Wall Street Prep's Premium Package will you set you apart in interviews and on the job.
Learn More

Growing revolver balances are a sign that model assumptions need revisiting

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 income statement assumptions. 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, it's preferable to reflect the additional required borrowings in long term debt.

Circularity

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. To learn more about why and how to deal with circularity, go to the "Circularity" section of this article about financial modeling best practices.

Master financial modeling! Used at top investment banks, Wall Street Prep's Premium Package will you set you apart in interviews and on the job.
Learn More
Comments

4
Leave a Reply

avatar
newest oldest most voted
Yohann
Yohann

Thanks, definitely helpful.
PS: "in the Excel spreadsheet, "minimum cash desired", since hard-coded, should be in blue 😎
Y

Jeff Schmidt
Jeff Schmidt

Yohann:

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

Best,
Jeff

Andres
Andres

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

Haseeb Chowdhry
Haseeb Chowdhry

Andres - great to hear - thanks for your kind words!

X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.