Guys, I have a quick modeling problem.
I'm using a standard bulge (Citi) model and we have a client that is trying to raise debt capital to build a new facility.
The catch is, they want to draw down on the $120mm debt in different months to minimize interest. Here are the conditions:
-$120 debt (probably $60mm senior, $20mm revolver, $40mm subordinated)
-$100mm for new plant, $20mm working capital
If they want to start drawing down say $10-$15mm per month beginning in December and ending in August, how would you model that in? The model is setup where everything automatically records the debt as an immediate drawdown, sans the revolver. I can't figure out how to force the term loan and sub debt to gradually come in.
debt payments/additions reflected in cfi on cf statement which decreases/increases your overall cash balance on b/s, which is offset by change in debt on liability side of b/s, also going fwd new interest exp affects net income which flows into cash on b/s (by way of cfo) and also into retained earnings
can't you add lines for issuance/repayment of each type of loan, corresponding interest lines, and balances and input it manually? obv make sure things flow through
Easiest way is just to have a month-by-month input for drawdown and then calculate the appropriate balances. Might not be the most elegant solution but is the easiest to see what is happening, make draw-down schedule changes and avoid mistakes.
Thanks guys. I'll go with the monthly draw down if the addition of new rows doesn't work.
Appreciate the input.
dont tell me you work for Citi and you can't fix this problem... I'm assuming revolver is least expensive, so drawdown $X every month until it is fully drawn. then move onto the senior, then sub debt (assuming that is the order they will draw on. create a couple of if statements so that all you need is one new row for company drawdown and your model will automatically recognize the revolver is fully drawn and then to automatically draw the senior, then same for senior to sub.
all in, you will have one row for company cash needs, and all the input boxes that initially tied to the model will be useless.
Very helpful, thanks.
Glad to hear that. Let me know if you need anything further. it's not too complicated and most of the time i see people trying to over complicate things. literally break down each formula and idea into one step. if you need help with the if statements hit me back. i may be able to pump something out real quick and send it over... pretty slow night where im at
Except there are probably a bunch of covenants, CPs, etc in there which will not make oR3DL1N3o's method realistic - your revolver is for your W/C, your term loans are for your capex - stick to that. The above does not sound realistic.
Facere et optio exercitationem. Non at tempore sapiente est.
Quia et facilis rerum in et. Eveniet asperiores repellendus nostrum sit iste enim sint. Beatae quis eaque doloremque impedit sit perspiciatis mollitia. Velit rerum incidunt ex. Dolore maiores dolorum officiis rem quia.
Id perspiciatis laboriosam optio quam dolorem nisi velit. Voluptas sed sequi laborum magnam officia qui.
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...