3 Statement Model - Having Some Problems

I am working on my modeling skills and while I have learned a lot from Macabacus and the two Josh’s ibanking book and some guides, I am still struggling with constructing a fully integrated 3-statement model. My main sources of confusion stem from three areas.

1) The first is in constructing the debt schedule. I think I am getting close but one of the biggest things I don’t understand is how to you differentiate between short-term and long-term debt. For instance, if I have a company with a simple bank loan that is amortized on a b-annual or quarterly basis, a portion of that loan is always going to be moving from long-term to short-term liabilities. How exactly do I go about modeling that out?

2) I’m also having a tough time understanding how to accurately forecast deferred tax assets/liabilities.

3) The last issue I’m having is tackling some of the more esoteric or specialized line items. I have been using a number of different companies as examples and have come across things like, “Salary continuation plan”, “Cash surrender value of life insurance”, “Checks outstanding in excess of bank balance”, “Interest rate swaps”, “Assets held for sale” etc… Outside the realm of debt/equity, revenue/costs and working capital items, how do you forecast for things that don’t really have any discernible trend and aren’t covered in depth in any equity research or company disclosure?

Any assistance or people who can share models via a site like dropbox or something would be greatly appreciated.

 
f1781109:

I am working on my modeling skills and while I have learned a lot from Macabacus and the two Josh’s ibanking book and some guides, I am still struggling with constructing a fully integrated 3-statement model. My main sources of confusion stem from three areas.

1) The first is in constructing the debt schedule. I think I am getting close but one of the biggest things I don’t understand is how to you differentiate between short-term and long-term debt. For instance, if I have a company with a simple bank loan that is amortized on a b-annual or quarterly basis, a portion of that loan is always going to be moving from long-term to short-term liabilities. How exactly do I go about modeling that out?

2) I’m also having a tough time understanding how to accurately forecast deferred tax assets/liabilities.

3) The last issue I’m having is tackling some of the more esoteric or specialized line items. I have been using a number of different companies as examples and have come across things like, “Salary continuation plan”, “Cash surrender value of life insurance”, “Checks outstanding in excess of bank balance”, “Interest rate swaps”, “Assets held for sale” etc… Outside the realm of debt/equity, revenue/costs and working capital items, how do you forecast for things that don’t really have any discernible trend and aren’t covered in depth in any equity research or company disclosure?

Any assistance or people who can share models via a site like dropbox or something would be greatly appreciated.

1) You are thinking too much into it. It won't matter much since it is on the same part of the balance sheet and keeps your formulas the same. We put our "total debt" into a place below the CL when we model it out and I am using a BB template. Current Liabilities really only need to be AP and Other.

If you really insist, make current portion anything in the past twelve months for each debt tranche. It's just a simple sum function of each amount due in the next 12 months per tranche.

For example if you have mandatory amort of $1 million each year for TLA + mandatory amort of $500K for TLB each year, then you simply sum the $1 million + $500K for current portion. Make sure to subtract whatever is in the current portion against the LT balance for each tranche as it flows from the debt schedule to the BS.

Remember that current portion is only mandatory repayment, not optional or additional. So, that's why it really doesn't matter since any residual CF (less min cash balance) should be used to pay down debt beyond the mandatory amort.

2) Again, you are over thinking this. If it's a DTA, it declines and lowers tax liability until it is used up (it's the same as prepaid expense). If it's a DTL it is the opposite. I don't know the exact rules, but either depreciate the value linearly or have the DTA get used up as much as possible each year until it disappears (how we did it in a recent transaction).

http://www.investopedia.com/exam-guide/cfa-level-1/liabilities/tax-defe…

3) As I said before, it isn't critical to have each BS line item broken out perfectly. You want the key ones, as you mentioned for WC, Debt, and Cash. When you build out detail, the IS is much more important to drill down.

This is our model's BS items. Could we break it down further? Sure, but it isn't necessary on the BS b/c the important accounts are the ones we just mentioned.

Assets:
Cash and Cash Equivalents
Accounts Receivable, net
Inventory
Other Current Assets
Total Current Assets

PPE, net        
Goodwill        
Deferred Financing fees     
Other Assets        
Total Assets        


Accounts Payable        
Other current Liabilities       
        Total current liabilities

Total debt:     

    Revolver    
    Term Loan A 
    Term Loan B 
    Term Loan C 
    Senior Sub. Notes   
    Mezzanine   
    Preferred   
Total debt:     

Deferred taxes      
Other Liabilities       
Total Liabilities       

Preferred equity        
Common equity       
Shareholders' Equity        
 

Thanks for the detailed response, much appreciated!

I have a couple quick follow-up questions.

This might be a dumb question but what do you do about all the line items that aren’t accounted for in your template? The reason I ask is two-fold:

A) Some of the values for these line items are quite significant. For instance, if I don’t project out “Cash surrender value of life insurance” then to make the BS balance, I would most likely have a corresponding drop in shareholder equity. So are you just lumping these values into other buckets or straight-lining them?

B) What about for presentation sake? My limited exposure to these models has just been things I can find online so I don’t know how a “professional” model should look but all the examples I have seen show a couple years of historical data and then the projections.

I know I am probably over thinking much of this but I just don’t have any formal guidance and am trying to get a handle on some of this stuff so I’m prepared for interviews in the future.

 
f1781109:

Thanks for the detailed response, much appreciated!

I have a couple quick follow-up questions.

This might be a dumb question but what do you do about all the line items that aren’t accounted for in your template? The reason I ask is two-fold:

A) Some of the values for these line items are quite significant. For instance, if I don’t project out “Cash surrender value of life insurance” then to make the BS balance, I would most likely have a corresponding drop in shareholder equity. So are you just lumping these values into other buckets or straight-lining them?

B) What about for presentation sake? My limited exposure to these models has just been things I can find online so I don’t know how a “professional” model should look but all the examples I have seen show a couple years of historical data and then the projections.

I know I am probably over thinking much of this but I just don’t have any formal guidance and am trying to get a handle on some of this stuff so I’m prepared for interviews in the future.

A) You can add and delete line items, particularly if the additional BS items aren't part of WC. Generally, I will lump the meaningless items into an "other" section. Although should still work, you should also be using a FIG model.

B) For presentation, it depends on what the MD wants. Generally, it is 2-3 years of historicals, an LTM projection based off the target date and then 5 years of projections. Our models run out 10 years, but years 5-10 have a minimal effect on the valuation since typically the growth rate is declined towards the perpetuity growth rate (inflation, generally).

Are you using Wall St. Prep, BIWS or Mabacus?

 
peinvestor2012:
f1781109:

Thanks for the detailed response, much appreciated!
I have a couple quick follow-up questions.
This might be a dumb question but what do you do about all the line items that aren’t accounted for in your template? The reason I ask is two-fold:
A) Some of the values for these line items are quite significant. For instance, if I don’t project out “Cash surrender value of life insurance” then to make the BS balance, I would most likely have a corresponding drop in shareholder equity. So are you just lumping these values into other buckets or straight-lining them?
B) What about for presentation sake? My limited exposure to these models has just been things I can find online so I don’t know how a “professional” model should look but all the examples I have seen show a couple years of historical data and then the projections.
I know I am probably over thinking much of this but I just don’t have any formal guidance and am trying to get a handle on some of this stuff so I’m prepared for interviews in the future.

A) You can add and delete line items, particularly if the additional BS items aren't part of WC. Generally, I will lump the meaningless items into an "other" section. Although should still work, you should also be using a FIG model. But, if you have significant items, just add them in. The model should still balance, as your are simply lining up the model w/the actuals.

B) For presentation, it depends on what the MD wants. Generally, it is 2-3 years of historicals, an LTM projection based off the target date and then 5 years of projections. Our models run out 10 years, but years 5-10 have a minimal effect on the valuation since typically the growth rate is declined towards the perpetuity growth rate (inflation, generally).

Are you using Wall St. Prep, BIWS or Mabacus?

 

I am using amalgamation of Macabacus and a couple other models I found online to try to build my own. One may also be from BIWS but I'm not sure.

I think I am starting to understand everything. I’m still a little fuzzy about the debt schedule and partitioning out the current portion of long-term debt but what you said made sense so I think I just need to play around with it for a bit.

I do have 2 quick hopefully final follow-up questions. You mentioned I should be using a FIG model. I’m not quite sure what your rationale is. The companies I have been looking at have mostly been consumer or industrial entities. I was trying to stay away from banks and insurance companies as I know that is a whole other animal.

Lastly, I was just curious how people at a BB come up with projected interest rates for all of the debt pieces with floating rates. I have read a bit about bootstrapping a LIBOR curve but wasn’t sure if that was how you all go about it or if there is some other method/source.

Thanks again, your responses have been extremely helpful.

 

The debt schedule is one of the toughest parts to understand. That's why you need a good template and just look in the formulas to understand what each line items pulls from and flows to.

To clarify, once you have a debt schedule in place, the only tweak you are looking to make is on the balance sheet. So, assuming you built it out annually, the sum of the ending balance from your debt schedule (BB - mandatory amort - additional repayment (add'l borrowings if you need to increase the debt tranche) = EB) is your LT debt. The current portion is any mandatory amort. for the year/period.

My bad on the FIG, haven't see a cash surrender line item before (at least not one that I paid much attention to). So, its an asset for the company via an insurance policy if I am understanding what I read?

At any IB, you come up with IRs based on market contacts and knowledge. We have constant discussions, meetings with commercial/corporate bankers to get a feel for where the market pricing and appetite is. Because it is always in flux and deal specific, there is no place that I know that really tracks such information well.

If you want a reference, start with Libor (30-day is what we generally use, but others will use 3-mo., 1 year, etc... depends on the bank too). For TLA, add 250 basis points. Add another 100 to TLB. For sub/mezz, unless you know how to model an equity component, just assume a fixed coupon of somewhere between 12-20%, depending on how risky the deal is (consider company's risk, deal's leverage, etc.). At the end of the day, it is an Art for a reason. There is no right answer.

 
bootstrapping a LIBOR curve

Bootstrapping starts with the shortest term swap and steps through them all in ascending order of maturity. At every step, forward rates inferred from the preceding swaps are considered as known, and subsequent forward rates are constrained to recover the price of the current swap.

Build the short end the first 3 monthsof the curve using LIBOR deposit rates. Build the long end of the curve using swap rates as par coupon rates.

Winners bring a bigger bag than you do. I have a degree in meritocracy.
 

Wow, I can’t thank you guys enough, I really appreciate the insight.

I promise this is my final question. I could be over thinking this too but how do you guys account for timing differences between your debt and forecast periods?

For instance, let’s say I have a company whose fiscal year-end is 12/31 and I am going to model it on an annual basis. Last year the company took out a plain vanilla 15-year bank loan of $1 million with equal monthly payments. However, the start date on the loan was May.

I learned quite a bit about accounting for partial periods and such working through DCFs and wasn’t sure if a similar adjustment would be applied or if you ever actually built out a full loan amortization table for each piece of amortizable debt used that to drive the mandatory repayment line instead of the more simplified amortization methods I have seen in examples.

Anyway, thank you both again, this has been a tremendous help.

 
Best Response
f1781109:

Wow, I can’t thank you guys enough, I really appreciate the insight.

I promise this is my final question. I could be over thinking this too but how do you guys account for timing differences between your debt and forecast periods?

For instance, let’s say I have a company whose fiscal year-end is 12/31 and I am going to model it on an annual basis. Last year the company took out a plain vanilla 15-year bank loan of $1 million with equal monthly payments. However, the start date on the loan was May.

I learned quite a bit about accounting for partial periods and such working through DCFs and wasn’t sure if a similar adjustment would be applied or if you ever actually built out a full loan amortization table for each piece of amortizable debt used that to drive the mandatory repayment line instead of the more simplified amortization methods I have seen in examples.

Anyway, thank you both again, this has been a tremendous help.

Yes, model it out through the rest of the year using a conversion (i.e. 5/12). After the first period, the rest should be full annuals and all be the same.

 

Fantastic!!! I know I said the last question but this is it as I pretty much have this thing working.

The one last item I’m struggling with conceptually is the priority of optional repayments. I understand in the wild various credit agreements will dictate the repayment terms but most of the models I have been looking at approach this from a more automatic standpoint. Usually, the revolver is paid off in full, then the senior debt etc… However, in comparing this to company’s actual financials, it appears that maybe at one point in the year the revolver was paid down, then drawn upon while an extra payment was made to a bank note.

I understand that you can’t capture every nuance of each business but was wondering if there was a more thorough approach or how you supported your assumptions.

 
f1781109:

Fantastic!!! I know I said the last question but this is it as I pretty much have this thing working.

The one last item I’m struggling with conceptually is the priority of optional repayments. I understand in the wild various credit agreements will dictate the repayment terms but most of the models I have been looking at approach this from a more automatic standpoint. Usually, the revolver is paid off in full, then the senior debt etc… However, in comparing this to company’s actual financials, it appears that maybe at one point in the year the revolver was paid down, then drawn upon while an extra payment was made to a bank note.

I understand that you can’t capture every nuance of each business but was wondering if there was a more thorough approach or how you supported your assumptions.

That is where it gets tricky. The revolver is the cheapest financing option, so it isn't always repaid if you can prepay part of TLA or TLB instead. It really depends.

What you are referring to is the circular reference related to excess cash flow. When you model the CF statement, you arrive at CF before principal repayments after going through CFO and CFI. Now, you get to CFF and incorporate the mandatory amortization. You also should put in a minimum cash balance. The number is rather arbitrary, but generally it is at least a few hundred thousand, up to a few million, depending on the size of your acquisition (if you are doing a BB sized deal, then use a few million, if a MM sized LBO, then maybe $500K).

So, to get CF available for optional/additional debt repayment, you use CF before principal repayments + minimum cash balance (calculated as cash balance from last period less minimum cash balance req'd) - mandatory debt amort.

So, in essence, you are taking BB of Cash + CFO + CFF - minimum cash balance - mandatory debt amort.

Generally speaking, sub or mezz debt is not prepayable without penalties and generally is left as is until the repayment date (normally just a one time repayment of principal + accumulated/deferred interest (PIK)). So you are only using excess cash flow to repay senior tranches, assuming they allow it.

 

Provident sint eos eos quia. Consequuntur illo in aliquam saepe. Et consectetur voluptatibus dolorem eos est.

Dolor ut facere possimus ea. Aut quos est aliquam nobis consequatur minus quia. Quod assumenda nihil delectus ratione totam modi. Odit in molestiae quia cum.

Aperiam dolorem explicabo blanditiis sit voluptatem minima dolore voluptatum. Ab unde amet perferendis facere ullam ea aut quo.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”