Cash sweep how to#

Financial Modeling: Handling the Revolver Circularity To reflect this, it’s preferable to reflect the additional required borrowings in long term debt. 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. 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. 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.

Formulas vary, but a typical formula is: 80% of “liquidation value” of inventory + 90% of accounts receivable. 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. There’s also a practical limitation on how much a company can draw on its revolver. 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. 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. Revolving Credit Facility: Borrowing Base (Inventory and A/R) 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: Let’s examine the revolver formula on the balance sheet more closely. 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. 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. Assuming again that you want to maintain at least $100 in cash during the forecast, is the “plug” cash or the revolver? Here we’ll change the income statement expenses from $800 to $1,500. 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 ExampleĪssuming you want to maintain at least $100 in cash during the forecast, is the “plug” cash or the revolver? Why?Īs you can see in the solution below, the “plug” here is cash.

Cash sweep how to#

All three statements interrelate correctly (see how to do this here). Below we present a simple income statement, balance sheet and cash flow statement. Modeling the Revolver in a 3 Statement Model (Step-by-Step)Ī simple sequence of exercises will highlight how these plugs work in a model.