How can I build a dynamic revolving credit facility in excel that once a min cash balance is set the revolver and cash balances increase to meet the minimum required balance? The problem is a circular reference when I calculate the required draw from the revolver, add it to the revolver, and add it to cash. Is there a particular cell I should be putting a circuit breaker in or setup that I should be using? Thank you very much for your help.
| +169 | The "Not So Obvious" things that get you a return offer? | 20 | 7h | |
| +102 | Is my life over after not getting GS? | 25 | 5h | |
| +65 | Best IB group on the Street | 28 | 2s | |
| +56 | BIG FOUR ARE PARADISE | 15 | 8h | |
| +49 | Thoughts and tips on how to speak like an investment banker. | 25 | 7h | |
| +47 | Tell me one good reason why Jefferies isn’t going to be a top bank in the next 5 years | 23 | 29m | |
| +36 | UBS Outlook | 28 | 1d | |
| +34 | How to deal with egotistical team? | 6 | 3d | |
| +33 | Highest Paid Bankers in Toronto? | 51 | 6h | |
| +25 | Got RBC offer but I have cold feet accepting. | 33 | 1d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
You'll need to do a few things:
1- In your cash flow statement, have a separate line in CF from financing for Revolver drawdown (paydown)
2- In your cash flow statement, but off the screen create a line item called "Cash surplus deficit before revolver drawdown (paydown)"; this should be: =Sum(CF from ops, CF from investing and CF from financing (excluding the "Revolver drawdown (paydown)" line you added in step #1, minimum cash balance - current beginning cash balance)
3- In your debt schedule, in your Revolver section, where you increase/decrease borrowings your formula should be the following: =MIN(-MIN(Cash_Surplus_Deficit_Before_Revolver_Paydown_Drawdown,Beginning_Revolver_Balance), Revolver_Limit-Beginning_Revolver_Balance)
I now have two lines in my debt schedule that I would like to link to the model - my new ending cash balance and new ending revolver balance. How can I do this without producing a circular error or in what cells would I have to put circuit breakers? Again, thank you.
You're supposed to have circularity.
Change the settings in your excel to allow for iterations. Google to figure out how to do it.
Marcus, in my debt tab I have a revolver balance (which is equal to beg bal + increase/decrease in borrowings) and a cash balance (which is equal to beg balance + change in cash from cf + change in borrowings from revolver). I am now back in the balance sheet and link the total debt to include the revolver balance and set the cash amount on the bs to equal the balance that was on the debt tab.
I have my excel set to calculate automatically with 1,000 iterations.
But when I do this linking and the circularity occurs, nothing happens (the revolver does not change). And then the balance sheet is completely unbalanced. Is there something that is off or that I am missing?
This convo has turned waaaayyyy too complicated...
CF From Ops (-) Capex (-) Mandatory Repayments (revolver isnt mandatory, only Term Loan amortizatons / maturities) (-) min cash balance (plug here or in your assumptions page) (+) cash on balance sheet = Excess Cash Available for Debt Service
From here, if this is negative, you get your revolver to auto draw the shortfall. If its 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 paysdown around this balance. Try it and you'll see you cash balance will always equal that min cash balance plug.
What if we were to include a releveraging facility also. Where would that sit in Cash flows?
When I change the new revolver balance in my b/s, I get the circularity problem again. What cell can I put a circuit breaker in to fix the problem?
Interest Calc. on the Income Statement...
If(cellx=0,0,Interest Link)
If the topiс is still up-to-date, I can share the solution of this problem (Automatic algebraic approach for revolving loan facility in annual financial models without macro and circular links). Please feel free to contact me.
Mollitia et delectus dolores corrupti eum non illum. Sed voluptate odio mollitia ab consequatur magni. Fugit voluptates saepe itaque veniam. Qui explicabo eum id ea. Voluptatibus velit et beatae similique cum dolorem recusandae aperiam.
Rerum quia eum dolorem ratione non modi. Molestiae architecto fugit dolorum at autem. Quod qui aperiam et accusantium eius facere rerum.
Distinctio autem omnis molestiae ut. Eligendi consequatur fuga ut molestias laborum inventore. Nihil impedit hic temporibus et commodi. Alias est sit sed harum nam ipsum nulla laborum. Accusamus corporis iste quasi possimus.
Ut quo omnis veniam quod ab. Cum quas aperiam et voluptates nesciunt. Voluptate quod cum sed sed possimus ratione. Aut quae vero esse quia ipsa. Deserunt nisi distinctio hic sed omnis omnis necessitatibus.
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...