I'm building a model and am trying to have it so the company automatically draws on their revolver if the cash balance dips negative. For example, if in Q4 their cash goes negative, I want the model to automatically draw on the revolver to get cash back to zero. However, this seems to cause a circular reference and the model just keeps iterating and won't give me an accurate projection...
Any thoughts?
Revolver Drawing with Negative Cash Balance
Many operating models will be built on the assumption that a company will draw on its revolver if it runs short on cash during a given period. If you need a review of the revolver concept, check out the WSO Dictionary.
CF From Ops
(-) Capex
(-) Mandatory Repayments (revolver isn’t mandatory, only Term Loan amortizations / maturities)
(-) Min cash balance (plug here or in your assumptions page)
(+) Cash on balance sheet
= Excess Cash Available for Debt ServiceFrom here, if this is negative, you get your revolver to auto draw the shortfall. If it’s a surplus, you can sweep it or let it accumulate as a cash balance. This will ensure you have your minimum cash balance and the revolver draws and pays down around this balance. Try it and you'll see you cash balance will always equal that min cash balance plug.
You can also view a separate thread on this topic.
Circular Reference Error in Excel
If you are encountering circular references when building out your model - you should go to options and make sure that iterative calculations are turned on.
To get to the menu option: Alt + T + O then go to Formulas, check iterative calculations and set to 100
Looking to Become a Modeling Master?
Sign up for our financial modeling training course to learn all this and more. Check out our 15% off discount to Wall Street Prep's Financial Modeling Courses.
http://macabacus.com/lbo-model/interest-rates - download the full LBO model and see how the do the debt schedule
Basically, find your cash flow available for debt repayment
then find your mandatory debt repayment
Then figure out how much cash there is left - if its below the minimum cash balance, zero, or whatever you have as the revolver trigger at...
if your cash flow after mandatory repayment is negative, then you borrow that amount, or 0
http://www.wallstreetoasis.com/forums/model-help-0
Just turn it to manual (Alt + T + O -> Calculation) and have it iterate 100 times?
I'm going to go and do what Marcus did
Cash should never dip below zero because you should be drawing on your revolver if FCF is negative.
If you are doing a simple operating model without a cash sweep, then under the cash balance line on the balance sheet, input =Max(ending cash flow from CFS, 0). Then, under short term liabilities, add a revolver/ST debt line, and that function should be =-Min(ending cash flow from CFS, 0); or you can do =Max(-ending cash flow from CFS, 0).
If doing a full cash sweep model, then you should have a line for 'cash flows available for debt repayment,' which should flow into the cash sweep tab. At that point, if you have negative cash available for debt repayment, you should input =-Min(cash flow available for debt repayment, beginning revolver balance) for the 'revolver issuance / (repayment)' line in the debt calculation tab, which will then flow down to the long term debt cash sweep as well, which will then flow into your balance sheet.
Hope that helps.
" Cash on the balance sheet" line.
I have a similar question,
I am working on a debt schedule and following a similar route: Cash available/(required) before debt+ beginning cash on the balance sheet - mandatory debt repayment - minimum cash balance=cash before discretionary (repayment) /borrowing. Then I calculate the revolver by -MIN(CASH AVAILABLE BEFORE DEBT, BEGINNING BALANCE OF THE REVOLVER).
The questions I have (and it may sound naive to some more advanced guys on here) refers to beginning cash from the balance sheet item that actually relies on debt schedule. I use the schedule to calculate that number, and when I put it back on the debt schedule, my numbers go crazy and so does everything else.
What do I do?
Modeling Problem - Revolver (Originally Posted: 07/19/2012)
Hey guys.
I have been recently practising financial modeling by myself prior to my on-board training, and I got really confused by "revolver" item. Actually I could sometimes easily build a perfect model but to be honest, I've never really understood the logic behind revolver. Could anybody give me some help? Thanks a lot!
Leo
If your mandatory debt payments > available cash flow, you get money from the revolver. It's basically like a credit card.
What happens when you run out of cash in your operation - you don't have negative cash on you B/S instead you borrow money. A revolver is a line of credit that is available for the company to draw upon on a flexible basis as opposed to a term loan or bond. For simple models you can use it as a plug to keep your B/S in check.
It's not a matter of debt payments, you don't even need debt on a company other than a revolver to have a revolver. however, as stated above, the revolver is kinda like a credit card. typically you will have size of the revolver say 10 million, and you will pay a rate, say 1% on the undrawn potion of the revolver. then when ever you are not generating enough free cash flow, for whatever reason, you can draw down on the revolver and pay a rate, say 8% for the drawn down portion. then once you have excess cash you can pay it down again. there term revolver comes from the fact that it is a revolving line of credit.
Lol 8% for a revolver? Hardly
Modeling question w/Revolver (Originally Posted: 12/12/2012)
Struggling to model this revolver in. We have it set to draw down if cash gets low, so the min function works. But, I also need it to draw down a few months out when a new CapEx project occurs.
Right now, the line item in the CFF is linked to a term loan (which I want to be drawn down secondarily) and draws and immediately pays out for construction in progress, in CFI. I cannot figure out how to link the revolver in without messing up the existing min function in borrowing (repayment) line item of the balance sheet.
Any ideas? I realize this may not have been the best explanation, but I can try and expand upon it if anyone thinks they need a bit more clarification.
Also, right now, the revolver is pulling around $3 million for a few months when cash is needed, but I want the full $10 million drawn at a certain point.
Break it out into steps for your waterfall, and adjust the waterfall based on seniority based on your deal (you mention CWIP, etc):
Waterfall: Take your CFADS (however you get to it from Net income / EBITDA) Less: Mandatory Amortization of your TL, etc = Cash flow available for revolver Plus: Draws on Revolver Less: Revolver Repayments = Cash flow available for 1st lien sweep Less: 1st Lien Sweep = Cash flow available for 2nd lien sweep Less: 2nd lien Sweep = Cash flow available to equity
You can layer other/different steps in as needed obviously
Your revolver math should something along these lines: Beginning balance = prior period ending balance Plus: Draws = if(cash flow available for revolver)0, min(cash flow available for revolver,(beginning balance + draws) Ending Balance = Beginning balance + Draws - Repayments
More logic needed if you want to incorporate the max draw on the revolver, make sure the revolver doesn't draw after you pay off your 1st Lien, etc.
Thanks for the reply Wookie. This is the part I am struggling with. As they draw down on the debt to build, they are actually beginning with a sub debt tranche (I realize this is unusual, but it's what the Boss thinks will work best for a unitranche raise). I want to model it as drawing the Sub Debt, then Revolver, then TL A, based on the structure of the transaction.
So, for instance, they are acquiring $13mm in Sub Debt in May '13, then I want to draw the Revolver (say $10mm estimate) all the way down, even though they only require around $2mm to meet the minimum cash balance. How I got it to work was to draw all of TL A, which is obviously a point or two more expensive.
Any thoughts now?
Sorry, I'm not following. your debt is not funded? acquiring sub debt??
To draw the full revolver amount, just add an if statement within the draw portion of the logic to coincide with your capex draw or specific date
Why not have two revolver lines (one goes to construction account, other to borrowing account) and just show the sum as the true revolver balance, writing in that the sum cannot exceed the commitment.
This way your waterfall goes
sub debt excess revolver availability term loan
where excess revolver is amount on the revolver less the accumulated drawdown to finance operating cash shortfalls
in this case you would exclude your construction CF line item from the cash shortfall calculation for determining excess revolver
This worked. Thanks for the help guys. I appreciate it.
Modeling help needed - balancing and debt revolver problem (Originally Posted: 11/24/2013)
Hi. I have seen other threads about this and I've tried implementing Marcus Halberstrams suggestions from the model help O thread (can't post links). However, I don't really know what I'm doing and I have a deadline coming up (interview).
Without the revolver, the company runs out of cash the last couple of years and we don't want that...
Also the b/s doesn't quite balance and I can't see what I've done wrong. If anyone wants to have a quick look I would very much appreciate it.
File is attached and you wanna look at "Model New". Thanks in advance!
The several things I noticed: biggest issue is the revolver which you have flip flopping between positive/negative values. Although this isn't actually affecting articulation because your BS and SCF agree on the revolver going down by $215M to -$107M. Then you have "Amortization of deferred costs" on the CF statement every year as if you are amortizing on a SL basis over 10-years, but the balance sheet items stay constant over the whole time period. Then with the capital leases I'm not sure how the $10,000 number under financing cash flows connects with what is going on on the balance sheet, seems like it doesn't. For unearned revenue, the item on the SCF is not considering the change in non-current unearned revenue.
Then this is actually your biggest issue (as to why things don't balance) I think: I don't know whats going on with the historical data, but something is up. You have total liabilities + equity = $959M in 2013, equal to total assets. But this is a hardcoded number; actually adding up your "total liabilities" and "total equity" accounts for 2013 gives $1,066,797 million. So your balance sheet doesn't balance for the historical period if you use the numbers you actually have there - so that is def causing problems going forward.
In general you need to get rid of anything hard coded, everything should be driven by formulas in the forecast years. Otherwise you will run into problems. If the statement of cash flows is driven entirely by formulas connecting it to the IS/BS, you will never have an issue where things don't balance.
I think it's also very helpful to have separate schedules for important items, such as for the revolver, for the retained earnings account, etc. I sent you a PM.
Extelleron is correct. Way too many hardcoded numbers in your "Model New" sheet (your computation sheet).
It is best to model out in detail the "collapsed" accounts like PPE (row 40) which have impacts on all 3 F/S. Refer back to your Accounting101 notes on what is the equation on the closing PPE balance.
In fact, think back to your debit/credit double entries when you are modelling ANY accounts. You have Dr. Financial expense 9,648 (cell E27) and what have you Cr. ?
Guys, thanks a lot. Very unselfish of you to give me this good feedback. Extelleron, model looks really good! Next time I will definitely be more elaborate.
I have listed all your points and will attend to them one by one. I might return later if I have any problems. Cheers
Yeah and by the way, I didn't need the debt revolver in the end as some assumption changes that I hadn't done yet made the cash flow positive.
Revolver Debt Paydown Schedule (Originally Posted: 10/01/2015)
I have a debt schedule that is sweeping all cash to pay down a revolver. But the revolver paydown is using more cash to pay down debt than there is actually debt (eg, in one month the beginning balance is $3MM and the paydown is $13MM, resulting in a negative end balance/negative beginning balance in the next period).
Is this incorrect? Should I cap the paydown somehow..?
Yeah, just use a max/min function between your BoP debt and your cash flow available for paydown.
Urgent help needed - balance sheet won't balance (revolver maxed) (Originally Posted: 02/12/2016)
Hey,
I hoped some of you may be able to help. I'm doing a DCF and have a company that has an existing revolver capped at GBP x million. In some scenarios where the company is loss making the cash is eaten away by CAPEX, and the revolver gets maxed out. As a consequence, the balance sheet no longer balances. How do you guys go around this? Do you have a line in equity that issues more capital or something?
I'll give an SB to anyone who saves me! Thanks!
Usually revolver facilities have an accordion feature that can flex up maximum level by by 5-10%. You could adjust the growth / other assumptions (e.g., capex) to ensure you're not overdrawing during the negative LFCF periods.
Thanks, Messagevik, that makes sense. Still, is this a "normal" issue or am I likely making a mistake somewhere? I'm stress-testing the model and worry when the b/s won't balance... The model works with realistic assumptions, but I thought that basic tests should be met at all times, no matter how ridiculous the inputs?
i mean, mechanically it's fine. i guess if you wanted to capture all possible scenarios, you could build in an equity raise line in the cashflow build so your balance sheet balances.
Perhaps add in a line to show how much the capital shortfall is in order to indicate the amount of additional financing needed.
Error deleniti enim ut officia repellendus ea. Voluptates et ullam est ea quod autem.
Dicta iste aliquid repudiandae voluptatibus. Et corrupti odio eum at ea unde tempora tempora. Ex eos nihil quia corrupti. Temporibus excepturi ut et ut. Maiores non veniam quasi dicta. Ut vel impedit vel. Nesciunt dolorem ex qui voluptates repudiandae. Dolorem aut modi dignissimos quos ex.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Adipisci porro est aut odit vel et. Accusantium ipsa nobis aut quas iste. Qui perferendis deleniti autem. Numquam similique asperiores est harum molestiae est. Et dolorem quia corporis sit quo maxime.
Voluptatem quo nobis consectetur molestias. Ducimus quo assumenda voluptatem cum illo rerum omnis.
Ea et ea voluptates ipsum. Sunt repellendus id recusandae est quia consequatur debitis. Velit at enim esse est provident sit dolor tenetur.