• 098 03/29/2020

    Content Creation Initiative

    ASimpleModel.com is pleased to announce a content creation initiative with Katten Muchin Rosenman LLP (“Katten”).

    Through this initiative we intend to create content highlighting the relationships between the legal documents required to close a transaction and the financial models used to communicate potential outcomes.

    To date, Katten and ASM have co-created an LBO Case Study as part of this effort. This case study contains examples of the documentation required to secure a transaction under letter of intent. Moving forward we intend to continue creating educational content to better explain this process as well as the documents that detail an acquisition.
     
    The founder of ASimpleModel.com has worked on control-equity, non-control equity and preferred equity investments with partners who are now at Katten for over a decade. They have always been excellent to work with, and ASM is looking forward to growing this relationship.
     
    About Katten:
    Katten is an innovative law firm with 600+ attorneys throughout the United States. To learn more about Katten please visit Katten.com.
     
    For examples of co-created educational content please see the links that follow:
    1. LBO Case Study (PDF | Post)
    2. Letter of Intent (PDF | Post)
    3. Subordinated Debt Term Sheet (PDF)
    4. Senior Lender Term Sheet (PDF)
 



  • 097 03/26/2020

    As a continuation of the post titled Modeling a Crisis, the following template has been made available: Budget Revision Template. This template was designed to help small to medium-sized business owners adjust their company's budget for an unexpected change in revenue.   

    The videos available below provide a walk-through of the income statment and the cash flow worksheet in an effort to make it easier to comprehend. A few text explanations of the worksheets, and some links with additional instruction can be found beneath the videos as well. 

    Budget Revision Worksheet

    Revenue: Input historical revenue as well as the original forecast for revenue. The values for the projected period can be revised with percentages in each corresponding period. After the new revised revenue line item in the model, discounts can be applied to arrive at net revenue.

    Cost Structure: Each line item under COGS and SG&A can be toggled to reflect either a fixed, variable or semi-variable cost structure. For a better understanding of the formulas on the tab titled Budget Revision please see the link that follows: Forumla's for Fixed, Variable and Semi-Variable Cost Structure. The only line item that is not projected in this manner is payroll under SG&A. This has its own tab to allow for some additional flexibility.

    Cash Flows Worksheet

    This tab starts with Net Income from the Budget Revision worksheet and makes adjustments to arrive at an estimate for cash flow for each projected period. The template requires some balance sheet information for this calculation. As it relates to working capital, three accounts are included: Accounts Recievable, Inventory and Accounts Payable. Otherwise a debt payments projection is required. The remaining line items pull from the Budget Revision tab.

    If you are not familiar with a three-statement model, the two videos that follow will help with some of the concepts discussed in the video:

    1. Three Statement Model Part I
    2. Three Statement Model Part II
 



  • 096 03/24/2020

    In this article we will explore quick formula edits that will facilitate toggling between fixed, variable and semi-variable expense line items. Creating a projection that allows you to select how expenses should be categorized between these three expense types on a line-by-line basis will make the overall model-building process more efficient. The alternative, of modeling each item individually, can become cumbersome.

    The first video will demonstrate how to use an =IF() function to build a formula that allows the user toggle between fixed and variable costs. But before viewing the video two concise definitions may help:
     
    Fixed Cost: A fixed cost does not fluctuate with a company’s revenue or production. Common examples include leases (e.g. rent expense) and executive salaries. In an Excel model, these expenses will typically either be straight-lined or will grow with inflation.
     
    Variable Cost: A variable cost fluctuates with a company’s production. A common example would be the cost of materials required to manufacture a particular good. For every unit sold the company will require the same amount of material. If unit data is available these costs can be projected on a unit by unit basis (number of units produced x cost per unit). It is also common to see these line items projected based on a historical percentage of revenue. 

    Semi-Variable Cost: In addition to fixed and variable costs, companies can have semi-variable costs, which include components of both cost categories. A common example is a manufacturer’s cost of electricity. The amount of electricity required to keep the lights on in a climate-controlled environment may be pretty consistent, whereas the amount of electricity required to operate manufacturing-related machinery would fluctuate with units produced. 
     
    The degree to which a semi-variable cost is either fixed or variable can at times be difficult to ascertain by looking at recent historical data. As a proxy, percentages can be applied to a fixed component calculation and a variable component calculation. The video that follows demonstrates how the formula and associated inputs work together.

    Whenever possible, I think it is better to distinguish between fixed and variable. If a line item doesn’t fit perfectly in either category, then it may make sense to explore whether or not the line item has supporting detail that could be used for this purpose. When that detail is not available the formula presented in the video can serve as a helpful estimate.
     

     

 




 



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.