• 063 04/11/2017
    I recently received an email asking if I was aware of a formula that could validate the information in two columns, and return the summed value for all matches. This is easier to grasp in context, which makes the video helpful. I was, admittedly, slightly long-winded in explaining the purpose of the formula. To avoid this lengthy explanation, skip to the two-minute mark once you understand the objective.

     

    The template used in the video is available for download: Download Template

    What you will learn in this video:

    1. How to use SumProduct as an array formula.
    2. What the double hyphen (or double minus) means in Excel formulas.
    3. How to create drop-down data validation lists.
    4. How to use F9 to audit formulas.

 



  • 062 04/10/2017

    Preface: I originally wrote this explanation of the IRR formula in response to a question asking, "Why is finance hard?".

    Even at the introductory level, finance requires so much new vocabulary that learning can feel like it involves a second language. In my opinion, it should be approached the same way.

    When a student is assigned a chapter for class, the objective becomes to finish the chapter. Students read it as though they were reading a language they understand. But if every other word is new, comprehension falls. A focus on vocabulary can accelerate the process even if it feels slower to stop and digest each word.

    Take the ubiquitous “IRR” as an example. A standard definition follows:

    The Internal Rate of Return ("IRR") is the rate ("r") at which the Net Present Value ("NPV") of all future cash inflows and outflows ("CF") for a project is zero.

    To someone new to finance, that definition means little. Worse still, the definition will likely by accompanied by an intimidating formula:

    IRR Formula

    To help make sense of this and demonstrate the power of really understanding new vocabulary, I created a video that explains the math by providing simple context. The math explained in the video can also be seen just below the video player.


    The video explains that if a sum invested achieves an IRR of 20%, it would mean that the investment grew at a rate of 20% each year. It follows that if an investment grew by 20% each year, the value of the investment after three years could be calculated as follows:

    Calculating the value of an investment.

    In the last step, the variable for rate "r" is substituted for 20%, and the variable for time period "t" is substituted for 3. The math should feel more approachable with familiar context. And notice that it starts to resemble the intimidating formula at the top (specifically "(1+r)^t"). Let’s take it one step further:

    IRR Formula

    (Note: For the last step, anything raised to the power of zero = 1, so we are dividing by 1.)

    (Note 2: The last step is a combination of two steps. Step 1 is subtracting the value of "CF" in time period zero from both sides. Step 2 is matching the format of the IRR formula, which is to sum all cash inflows and outflows divided by (1+r)^t. The latter is why (1+r)^0 is included in the denominator.) 

    That last line is the formula that this post starts with for one cash outflow (the investment you make), and one cash inflow (the value of your investment in “t” years).

    As a last step, let’s incorporate values for variables. Imagine you invested $10 in period zero (another way of saying “today”) and three years later received $17.28 in return. Using the formula for IRR above (where r = rate):

    IRR calculation.

    So what you are solving for is the rate (“r”) at which the value of $17.28 three years from now is worth $10 today (this is Net Present Value). If you plug 20% for r in the equation above the equation will be true. Going full circle, IRR = 20%.

    Focus on vocabulary and everything else will follow.

 



  • 061 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.

 




 



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.