Modelling exams: How to balance with minimum cash and debt paydown?
Hey guys,
I've always had a problem thats a bit of a pain on lbo models for exams. In my models I make from scratch the balance sheet ALWAYS has the same problem. Everything is perfect, except for the change in working capital and debt schedule. I know what the errors are exactly, so let me explain.
The model always has assets higher by the same amount that working capital increased (eg. you're supposed to be paying cash for the working capital change). However, how would I do this in a model with a minimum cash balance? Considering I can't change the minimum cash balance, should I make the senior debt slightly higher by that balance (considering you cant pay down as much senior debt?).
Additionally, when it comes to debt paydown and building the debt schedule, the model will unbalance when I link it up and factor in the cash flow sweep to pay down the senior debt and increase the mezz debt. Should I just make up for this difference in equity (aka. increase the equity value to make up for debt paydown so it keeps capital structure neutral)?
I always learned to do the balance sheet before debt schedule and minimum cash before the cash flow statement, so I'm wondering how to do this now.
Thanks in advance.
Changes in working capital affect cash. If the company decides to pay some bills (decrease in accounts receivable), you would pay for it in cash (decrease in cash). If you don't have enough cash on the balance sheet, you would either pull on the revolver (if the company has one) or contribute additional equity to make up for the cash short fall. Once you have the above concept working correctly, adding a minimum cash balance is easy. You will have a beginning cash balance balance, your change in cash, and then your ending cash balance. If your ending cash balance is less than the minimum cash requirement, then you would contribute addition to make up the difference. 500 beginning balance, -600 change in cash, -100 ending cash balance....If you want to have a minimum cash of 200, then you would need to contribute an additional 300 through equity or revolver draw.
I'm not clear on what the issue is with the second question. Why would you be paying down the senior debt and at the same time increasing the mez debt? That doesn't make sense to me. I would be happy to take a look at the model and give some insight. PM me a dropbox link to the model if you are interested in me taking a look.
Like your P&L, your balance sheet should be built out only partly. I build out my balance sheet fully to the opening balance sheet, project the non-debt balance sheet items, and then build out the debt schedule. Then I link it to the balance sheet and related expense accounts on the P&L. Once this is done the net income should flow into equity and the balance sheet should be complete.
You shouldn't be increasing your mezz just because you're paying down term debt. I'm not sure what you're trying to do here, either.
I think you guys helped me figure it out a bit. Since minimum cash is displayed constant on the balance sheet, the model was not balancing because of the change in working capital (a positive increase), which should decrease the cash balance.
Additionally, when I added in a paydown in debt, it would unbalance by the value that the debt was payed down.
To balance these 2 items, I think I just needed to increase the value of equity to reflect revenue being used for cash sweep and not cash accumulation, as well as some revenue being used to increase working capital.
Beatae quia cupiditate et dolor reiciendis consectetur. Inventore dolorum et aliquid. Id et corrupti qui eos voluptas ea voluptatem.
Velit quia molestiae molestias quisquam voluptatum et sint. Rem ipsam quos enim rerum sapiente accusamus earum quam. Sit similique maiores repellendus. Cum in esse est mollitia labore nihil. Tempora itaque voluptates ut possimus dolor ut deserunt.
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...