Model Help - Dynamic revolving credit facility
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.
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)
Quia fugiat officiis neque placeat. Dolor et illo fugit quia sunt minima soluta.
Velit harum ut labore cumque minima. Numquam voluptatem aspernatur nam nihil. Rerum qui voluptatibus non consectetur ut.
Nisi maxime natus dolores veritatis qui. Vel aliquam optio quasi molestiae ipsum. Ut dolor fugit aut velit expedita itaque adipisci sit.
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...