Financial Modeling for Equity Research
A Step-by-Step Guide to Earnings Modeling
Template Download Page
Second Edition
Note to the community from the author: The second edition of the book has been out of print since 2017. I realize that counterfeit digital copies are being sold illegally on various websites. I have spent significant amount of time contacting the websites and explaining that the sellers are in violation of our copyright. The books are removed from the website and reposted by another user. At this point I have given up trying to get the illegal copies removed permanently. All of our books are sold exclusively through Amazon.com. If you buy a digital copy from another website they are violating our copyright. Despite this, I'm happy to provide the old templates for the second edition (although they are extremely out of date at this point and use an old methodology which I revised in the third edition.
For the third edition I completely re-wrote the book and the result is far better and more comprehensive compared to the second. The subject company for the third edition is FedEx Corp. It was written in 2019 so it is a bit outdated, but still relevant from an example standpoint. If you are interested in an updated version, please consider my latest book The Procrastinator's Guide to Financial Modeling, which covers Starbucks and was published in October of 2022.
For the third edition I completely re-wrote the book and the result is far better and more comprehensive compared to the second. The subject company for the third edition is FedEx Corp. It was written in 2019 so it is a bit outdated, but still relevant from an example standpoint. If you are interested in an updated version, please consider my latest book The Procrastinator's Guide to Financial Modeling, which covers Starbucks and was published in October of 2022.
Templates:
|
File 1—Blank Model Template: Use this template to create your own earnings model. None of the cells are locked so you can change the accounts and equations to fit any company you choose.
|
| ||
|
File 2—Apple Inc Back of the Envelope Model: The majority of the examples covered in this book are based on Apple Inc. This beginner model is perfect for those who have not had prior modeling experience. This model is covered in Chapter 2.
|
| ||
|
File 3—Apple Inc Tier 2 Earnings Model: This version of the model is more sophisticated and includes a breakdown of the company’s products. The Tier 2 model is covered in Chapter 3.
|
| ||
|
File 4—Apple Inc Tier 1 Earnings Model: The Tier 1 model is geared toward advanced analysts and includes financial statement integration, as well as a discounted cash flow valuation. This model is covered in Chapter 4.
|
| ||
|
File 5—Equity Risk Premium (ERP) Model: Using this simple model you can quickly estimate the market ERP based on volatility, changes in interest rates, and market return expectations. You can then derive a discount rate using your ERP estimate, and the Capital Asset Pricing Model (CAPM). This model is covered in Chapter 6.
|
| ||
|
File 6—Apple Inc Beta Calculation: This file demonstrates the calculation of beta, using an Excel-based regression, which is covered in Chapter 6.
|
| ||
|
File 7—Boeing Co Regression Model: This file calculates a simple linear regression and related statistical tests using Boeing’s revenue and the U.S. Census Bureau’s Industrial Production report. The Boeing regression model is covered in the Appendix.
|
| ||
|
File 8—Starbucks Inc Regression Model: This file demonstrates a multiple regression using Starbucks’ revenue and the U.S. Census Bureau’s Advanced Retail & Food Sales Report, Food Services & Drinking Places sales data. The Starbucks regression model is covered in the Appendix.
|
| ||
Latest Version of the Apple Inc Earnings Model
|
Note: Apple no longer discloses unit sales in their quarterly earnings releases. This version of the model reflects the new modeling approach which uses year-over-year growth rates to estimate product revenue. (Updated on 5/30/2019). Note that this book is no longer in print, and the model will not be maintained going forward. If you would like an updated model, you will need to add the latest reported results yourself.
|
| ||
Textbook Corrections:
In the discussion of target price bands on page 88, the text states: "Therefore, at the 95% confidence interval, the lower bound of the price band is equal to the target price × (1 – (2 × the standard deviation × mean monthly return)). The upper bound is equal to the target price × (1 + (2 × the standard deviation × mean monthly return))."
This should be changed to the following: "Therefore, at the 95% confidence interval, the lower bound of the price band is equal to the target price × (1 + (mean monthly return - (2 × the standard deviation))). The upper bound is equal to the target price × (1 + (mean monthly return + (2 × the standard deviation)))."
This should be changed to the following: "Therefore, at the 95% confidence interval, the lower bound of the price band is equal to the target price × (1 + (mean monthly return - (2 × the standard deviation))). The upper bound is equal to the target price × (1 + (mean monthly return + (2 × the standard deviation)))."