A page of notes about industry, innovation, disruption, accounting (sometimes) and of course...models.


 

  • 064 02/27/2017

    This three-statement model for Amazon.com, Inc. will be used in the next installment of the Integrating Financial Statements series. While I have not typically posted a work in progress, I believe this will be helpful to ASM visitors in the interim.

    The objective of the lesson that follows the Home Depot model, which focuses on projecting stockholders’ equity, is to demonstrate that there are many ways to manage the assumptions and drivers for the balance sheet. In contrast to the model for Home Depot, which models each debt tranche, the model for Amazon.com holds debt constant, thereby eliminating the need for a supporting debt schedule. The interest rate used in the projected periods is backed out from the most recent historical period as the quotient of interest expense and total debt.

    This approach would be more common at a buy-side firm (such as a hedge fund). Unless you are engaged by the company, as an investment banker for example, it is unlikely that you would have sufficient data to project each tranche of debt individually. The challenge is that annual reports generally only disclose when the balance comes due, but not whether or not the company intends to refinance. As such, if you were to only model what information you have, then you assume most companies pay down debt as it comes due in the projected years (assuming they have the cash balance to meet that obligation without refinancing).

    Disclosures:

    This next installment in the Integrating Financial Statements series is still primarily focused on the mechanics of building a three-statement model, and does not focus on assumptions or drivers. Everything is projected using historical averages. It follows that this model should absolutely not be used to arrive at a valuation of Amazon.com, Inc.

    The model does not project capital leases or finance leases.

    Additional Information:

    Because the company relies so heavily on Restricted Stock Units (RSU) to compensate employees, I added a tab explaining how restricted stock and restricted stock units work, which pulls from PWC’s publication PWC Accounting Guide to Stock Based Compensation (Second Edition).

    ASM Visitors: If you have any comments or suggestions related to the model, please feel free to reach out via the contact page.

    Download Financial Model: Amazon.com, Inc.

 



 



  • 062 01/30/2017

    In this post you will find a template for the acquisition of a fictional 160 unit multifamily property. The template was developed in collaboration with a couple friends that are real estate professionals. 


    As with most of the financial models on this website, the data contained in this workbook is entirely fictional. None of it should be used to develop assumptions about different properties.

    COMPONENTS

    The components of the workbook are listed in the sequence that an analyst would likely take in constructing this template from scratch. The order emphasizes raw data and input-related worksheets first, and then works towards the output pages that display the outcome. Most of the worksheets contain some inputs, but the majority of the raw data can be found on the tab titled “Rent Roll Analysis” and in the Trailing 12 Month Income Statement on the tab titled “Trailing-12 & Pro Forma.”

    Rent Roll Analysis: An organized view of the raw data that will be used to develop revenue assumptions for the projected period. It includes a list of all units at the property and their status. The information includes the unit type, square footage of the unit, the lease start date, current rent and whether or not the unit is vacant. 

    Rent Roll Summary: On account of the amount of information contained on the “Rent Roll Analysis” worksheet, a worksheet summarizing this information is included in the model.

    Trailing-12 & Pro Forma: This tab has two components that both require the same line items. The advantage to having it all on one screen is that it reduces the updates required when raw data is received. It is unlikely that income statement data for different properties will be identical, and this format makes updating the Trailing-12 template and the Pro Forma template a one-time adjustment (versus having this information split on two different tabs). The two components can be described as follows:

    Trailing-12: A summarized view of the income statement data provided. This tab groups information into totals to limit the number of variables required in developing a pro forma and projecting the outcome. 

    Pro Forma: Using multiple annualized periods of trailing 12 month income statement data, the Pro Forma using assumptions developed to project the first year of the forecast on the worksheet titled “Projection.”

    Projection: This worksheet stores several acquisition scenarios, an eleven year forecast and calculates the returns achieved.

    Amort. Schedule: The amortization schedule pulls many assumptions related to the “New Loan” from the “Projection” tab. Interest payments and principal payments are calculated on this worksheet on account of the amount of space required. It would not be as practical to fit this as a schedule on the “Projection” tab. (Think of this tab as the supporting debt schedule you would see in an integrated financial statement model.)

    Description: The “Description” tab summarizes the details of the transaction.

    ITEMS FOR FURTHER CONSIDERATION:

    Calculation of Residual Value is a big assumption that relies heavily on one input: the “Residual Cap Rate” (visible on the tab titled “Projection”). This is used to value the building at exit (Exit Year Cash Flow / Residual Cap Rate = Gross Residual Value). For a great property with no significant improvements made over the hold period, the rule of thumb is to increase the year 1 cap rate by 50 to 75 basis points (“bps”).

    Revenue is generally the focus for potential margin improvement in due diligence. Costs are typically pretty well known and there is generally less room for improvement in costs (unlike an operating business). So the question becomes what can a new owner do to push up rents are add incremental fees (priority parking / storage / pet fee / etc.).

    Vacancy is typically not included in the historical trailing twelve month data. Most owners simply net this out of top line, but it is an important factor to consider in developing a projection. For a solid, stable property 4% to 5% is typically used to account for regular turnover in units.

    VOCABULARY:

    Acquisition Reserves: If the property is acquired with the intention of making significant upgrades to the property, the capital required to make those upgrades would be included in the financial model under this line item (visible on the tab titled “Projection”).

    Capitalization Rate (“Cap Rate”): The quotient of net operating income and property asset value.

    Loan to Value (“LTV”) Ratio: The sum of the loan expressed as a percentage of the purchase price of the property. If a $100M property was acquired with $50M of debt, the LTV would be 50%.

    Loss to Lease (“LTL”): The discount to market rent assumed by the landlord in leasing a unit.

    Market Rent: The rent the unit would command in the marketplace today. In other words, you might have a unit that was leased several years ago. If the market had improved significantly in the interim, the market rent might be substantially higher than the rent currently earned on that unit.

    Unleveraged IRR / Leveraged IRR: This calculates the IRR with and without leverage. In other words, the unleveraged IRR is the IRR achieved in the absence of the “New Loan” as it is described in the financial model. Typically only institutional investors contemplate making the acquisition without leverage of any kind.

    *Ignore print formatting. I will upload a new version this evening.

 




 



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.