• 089 11/13/2019

    I have always tried to use arrays in Excel without creating what is referred to as an "Array Formula." An array formula is easily identified by the fact that you have to press Ctrl+Shift+Enter to create one, which is why they are sometimes referred to as CSE formulas. It has been my opinion that you can accomplish everything an array formula is capable of by nesting functions that create arrays in other functions.* The formulas this creates are more flexible, which makes building large models a little simpler. 

    The first video below explains the difference between an array (as it is used in this post) and an array formula (as it is used by the Excel community). It then demonstrates how you can use regular formulas with nested functions. The second video demonstrates how this knowledge might be applied in a financial model, and the third video provides a more complex example. If you are new to advanced formulas I would recommend starting with the first video. [Templates coming soon.]

    VIDEO 1:

    VIDEO 2:

    VIDEO 3:

     

    *Applies to my experience building financial models. 

     

 



  • 088 07/31/2019

    A series of videos explaining the most common distribution waterfall used in private equity is available below. (For a written explanation please click here.) 

    A standard distribution waterfall is made complicated only by the amount of vocabulary used to describe how it works. The math is otherwise simple, as these videos demonstrate. 
     
    UPDATE: All videos have now been combined into one for convenience. The individual videos remain available below should you want to jump to a specific part.

     
    Whiteboard Visual Overview: A visual overview of what takes place in a distribution waterfall.

    First Excel Example: The first Excel template excludes the 20% catch-up provision to make the math easy to follow.

    Catch-Up Calculation Whiteboard Example: The language describing the catch-up provision in a distribution waterfall has resulted in many emails asking for clarification. This video demonstrates how simple it is.

    First Catch-Up Example: The second Excel template introduces the catch-up provision. This example makes it very easy to check your own work should you mirror the exercise yourself. 

    Private Equity Catch-Up Provision: The final example introduces language that would be found in legal documents detailing a distribution waterfall, and demonstrates how small changes to language can be meaningful. It follows with the calculation for the most standard private equity distribution waterfall, and concludes with some thoughts on how to check your work. 

    If you are new to private equity, a description of fund structure might be helpful before proceeding with the videos: Private Equity Fund Structure.
     
    This series was created in response to feedback for the LBO Case Study [WIP]
     
 



  • 087 04/07/2019
    We are in the process of developing a LBO case study for the Leveraged Buyout Model video series as a test for the content in that series. This is currently a work in process, but you can download the write up and the Excel file at the bottom of this post. 
     
    As part of the process I have been attempting to think through what I would like an analyst to know if they were supporting me at my place of work. From my perspective, an understanding of how a transaction comes together and the legal documents required to guide the process is incredibly valuable. For this reason the case study asks the student or candidate to assume the role of an independent sponsor and use examples of senior and subordinated lender terms sheets as well as a letter of intent to pull the data required to build the financial model.
     
    I thought I would post this now, as a work in progress, to ask the ASM community for any additional suggestions before this goes live as part of the video series. If you have any suggestions for additional content or questions as it relates to this case study specifically please reach out through the contact page. I would welcome any feedback.
     
    Download: LBO Case Study
    Download: Excel File
     
    Related Links:
    1. A Primer on the Letter of Intent (LOI)
    2. Senior Debt Term Sheet Introduction
    3. LBO Pro Forma Balance Sheet Adjustments 
     
 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.