Main topics: Accounting, projecting the three statements, and valuation/DCF analysis Page 5 Outline for This Tutorial: • Part 1: Differences on the Financial
This programme will help you create financial models that can be understood and easily implemented to gain insights into the opportunities and risks being faced
23 jan 2016 · Be sure to tune into our YouTube Channel for our earnings preview and The Gutenberg® Approach to Financial Modeling Equity Valuation
new possibilities and reshapes the value and supply chain of financial intermediation, disrupting the existing business models
Financial Modeling Business Valuation Finance, Accounting Law Feedback Ratings* Faculty 4 5/5 Program 4 3/5 December 19, 20 and 21, 2019
Amazon Web Services, YouTube and Instagram: Hidden Gems - Valuation of Hidden Gems during the course, including M&A valuation, financial modeling,
13 août 2020 · hold for citizens from the EU 2 The value of media consumption crucially depends Pioneering models on media outlets' content choice by
Chapter 1 Introduction to Financial Modeling ....................................................................................... 1-4
Section 1: Earnings Model Layout ..................................................................................................... 1-4
Section 2: Income Statement Modeling ............................................................................................ 1-5
Section 3: GAAP & Non-GAAP Earnings ............................................................................................. 1-7
Section 4: Model Calibration ............................................................................................................. 1-8
Section 5: Balance Sheet Modeling ................................................................................................... 1-9
Section 6: Cash Flow Statement Modeling ........................................................................................ 1-9
Section 7: Financial Statement Links ............................................................................................... 1-10
Retained earnings ....................................................................................................................... 1-10
Income Statement links to the Cash Flow Statement ................................................................... 1-10
Balance Sheet links to the Cash Flow Statement .......................................................................... 1-11
Property, Plant, & Equipment ...................................................................................................... 1-11
Cash Flow links to the Balance Sheet ........................................................................................... 1-11
Section 8: How to use the Earnings Model ...................................................................................... 1-12
Chapter 2 DCF Inputs (ERP, Beta, CAPM, & WACC) ............................................................................. 2-13
Section 1: Calculating the Equity Risk Premium ............................................................................... 2-13
Section 2: Review of the Historic Constant Sharpe ERP ................................................................... 2-14
Section 3: Deriving Beta Using Excel ............................................................................................... 2-15
Section 4: Using Beta and the CAPM to calculate the Required Return on Equity ............................ 2-17
Section 5: Calculating the Weighted Average Cost of Capital........................................................... 2-17
Chapter 3 Discounted Cash Flow Valuation ......................................................................................... 3-18
Section 1: Our DCF Approach .......................................................................................................... 3-18
Section 2: Stage-One Calculation .................................................................................................... 3-18
Section 3: Stage-Two Calculation .................................................................................................... 3-19
Section 4: Final DCF-Based Valuation .............................................................................................. 3-19
Market ERP ................................................................................................................................. 3-19
CAPM ......................................................................................................................................... 3-19
WACC ......................................................................................................................................... 3-19
Volatility ..................................................................................................................................... 3-19
Interest Rates.............................................................................................................................. 3-19
Beta ............................................................................................................................................ 3-19
Section 5: DCF Sensitivity Analysis .................................................................................................. 3-20
© Gutenberg Research LLC 2016. All rights reserved.Section 6: Final Note on DCF Valuation ........................................................................................... 3-20
Chapter 4 Market Multiple Valuation ................................................................................................. 4-21
Section 1: Which Multiple to Use .................................................................................................... 4-21
Industry/Sector ........................................................................................................................... 4-21
Cyclical vs non-cyclical ................................................................................................................ 4-21
Lifecycle stage............................................................................................................................. 4-21
Section 2: Separating Cash from the Valuation ............................................................................... 4-22
Section 3: Calculating Share Value Using the Multiple ..................................................................... 4-22
Chapter 5 Using Regression Analysis to Predict Earnings ..................................................................... 5-23
Section 1: Data Selection ................................................................................................................ 5-23
Section 2: Running the Regression .................................................................................................. 5-25
Section 3: Model Testing ................................................................................................................ 5-26
P-value and t-stat review ............................................................................................................ 5-26
Breusche-Pagan test ................................................................................................................... 5-27
Serial correlation test .................................................................................................................. 5-28
The normal distribution assumption ............................................................................................ 5-29
Model stability ............................................................................................................................ 5-29
Other Considerations .................................................................................................................. 5-29
F-stat review ............................................................................................................................... 5-30
Significance F .............................................................................................................................. 5-30
Multicolinearity .......................................................................................................................... 5-30
Section 4: Analyzing the Output ...................................................................................................... 5-30
Section 5: Back Testing ................................................................................................................... 5-32
Section 6: Model Limitations & Improvements ............................................................................... 5-32
Terms of Use ...................................................................................................................................... 5-34
© Gutenberg Research LLC 2016. All rights reserved.To get started it is important to take a few minutes to get comfortable with how our models are setup.
We use a basic cell color coding scheme: blue cells represent our key assumptions, purple cells represent
management guidance, and orange cells represent consensus estimates. Historic values come fromcompany reports (SEC filings, investor press releases, and presentations). Some future period cells are not
color coded, which indicates that the cell is populated based on an equation, or it does not represent a
significant assumption in the model.We have two categories of models: Tier 1 and Tier 2. Apple is a Tier 1 model which includes the Income
Statement, segment details, Balance Sheet, Cash Flow Statement, and uses two valuation methods: 1) A
market multiple, and 2) Discounted Cash Flow (DCF) approach. Our Tier 2 models include the Income Statement, segment details and a market multiple valuation only.The column headings in our models have two shades of gray. The dark gray represents historic details,
which come from the company͛s Securities and Exchange Commission (SEC) filings, press releases, or
investor presentations. The light gray columns represent future period forecasts.If you drill into the equations on the Income Statement you will notice that it is simply a summary of the
results in the assumptions from the segment details section. The segment details are company specific,
so this section will change for each model. We choose the categories to use in the segment section based
on the information the company provides in their SEC filings and quarterly presentations. For Apple the segment section starts with a breakdown of revenue by geography, however since thesecells are not blue, they are merely informational based on our key assumptions in blue, which start with
the Average Selling Price (ASP) by Product and unit sales growth rates.Now you should be able to see how the smaller pieces fit together. If you know a new iPhone will launch
next quarter, you can increase the ASP and the unit growth rate. You can also increase the unit growth
© Gutenberg Research LLC 2016. All rights reserved.rates for the December quarter to reflect the holiday shopping season, or decrease the iPad growth rates
to reflect the trend of decreasing overall demand.The ASP and unit growth rates are used to derive the top-line revenue estimate. After that we need to
make an assumption for gross margin. If you think Apple will have a favorable mix in iPhone Plus and high
storage capacity models, you can increase the gross margin, or if you think they will sell a greater
proportion of lower-margin iPhone C models you can decrease the gross margin. This will now bring you
down to the gross profit line on the income statement. Management typically gives a target range for the
corporate average gross margin (not at a product level), which we use for our baseline gross margin estimate. From there we need to make some assumptions about the operating expense (opex) items. For most of our models we have separate assumptions for each opex line, in this case SG&A and R&D. We calculateeach as a percentage of total sales and use the historic trends to help inform what the future ratio should
be each quarter. Then each expense line will calculate automatically based on our ratio estimate, and our
new forecasted sales.We take a similar approach with the provisions for taxes except we calculate the historic effective tax rate
as a percentage of pre-tax earnings.The share count can be a bit complex since it tends to fluctuate over time based on a number of different
factors. We strip out the impact of share repurchases to calculate a trend in the percentage change over
time. Then we set the share price and dollar amount of repurchases to estimate the new share count.After all of these assumptions have been setup, the EPS calculation is easy: Net income divided by total
shares outstanding = EPS.Most companies use non-GAAP adjustments to show what they believe to be is a more accurate
representation of the company͛s true earning capacity. The non-GAAP adjustments are relatively
consistent between companies (usually adjustments for stock based compensation, amortization of
intangibles, and the tax impact of these items). The adjustments are typically divided between reductions
in costs of goods sold, reductions of operating expense, and adjustments directly to net income. Apple
generally does not disclose non-GAAP adjustments so we will use our Google (NASDAQ:GOOGL) Model to demonstrate how we treat non-GAAP items. Google reports stock-based compensation broken out between cost of goods sold, Research &Development, Sales & Marketing, and General & Administrative expenses. The tax effect of these
adjustments are reported as a direct adjustment to net income. You can see that we report the non-GAAP
Income Statement embedded in the GAAP Income Statement. The stock-based compensationadjustments sections are referenced in rows 12 and 19, and the tax impact is reflected in row 29. Although
it appears that the GAAP and Non-GAAP Income Statements are mixed together, the calculations areactually separate. This allows us to have two distinct bottom-line diluted EPS estimates, one on a GAAP
basis and one on a non-GAAP basis.To predict the future period non-GAAP adjustments, we apply a quarterly average of the total stock-based
compensation as a percentage of revenue, or if the company gave specific guidance on the expectation
for the next quarter we use the guidance. The breakdown of stock-based compensation to cost of goods
sold, and the three opex categories is based on the preǀious period͛s percentages for each. If the company
includes amortization of intangibles in their non-GAAP adjustments the value tends to stay relatively
consistent each quarter, so we usually set the future periods equal to the latest reported value.We keep our models representing a ͞baseline" type of scenario. This allows our users to easily incorporate
their opinions into the model by changing the key assumptions throughout the financial statements. To
maintain the baseline view, we calibrate the model to meet consensus revenue and EPS estimates prior
to the quarterly earnings release. We then update the Ƌuarter͛s actual results the night of the release,
and re-calibrate the future periods to meet management͛s new guidance the night of the earnings call.
Management typically give a range for each guided line item, so we calibrate to the mid-point and insert
a comment with the full details. After all the analysts have had a chance to update their forecasts to
include the latest results, we re-calibrate our model again to meet the new consensus estimates (typically
a few days after the release).Our Tier 1 models use the changes in working capital to calculate the cash flow from operations, which is
used in our free cash flow calculation. This will impact the discounted cash flow valuation. As a result, we
tend to focus Balance Sheet forecasts on accounts receivable, inventory, and accounts payable. To do this
we apply the operating ratios calculated in the section below the Balance Sheet, and make assumptions
about these ratios in future periods. These ratios and the results from operations reflected in our future
Income Statement, drive the future balances for these assets and liabilities.Capital expenditures (capex) is also used in the free cash flow calculation so we need to estimate the
change to this line as well. We do this by adjusting the capex growth rate at the bottom of the Cash Flow
Statement. The Property, Plant & Equipment (PP&E) balance on the Balance Sheet will automatically adjust based on the capex assumptions and the depreciation reflected in the Cash Flow Statement.Most of the other Balance Sheet accounts are very difficult to forecast and are not as important in our
valuation approach so we set them equal to the last reported value.Apple has plenty of cash, so a shortfall is less likely than for a developing company such as Telsa Motors
(NASDAQ:TSLA). Tesla is ramping new production lines at an astonishing rate, and burning throughsubstantial cash each quarter. If we take a quick look at our Tesla Model (below), we can see that simply
© Gutenberg Research LLC 2016. All rights reserved.changing the capex for 2016 to equal that of 2015, under our consensus-based operating scenario, will
result in a cash shortfall without the issuance of additional debt or equity. Of course there are a number
of operating assumptions we need to make for this analysis, such as the ratios for the cash conversion
cycle inputs, and that there would be no change to the company͛s financing terms (or mix of leased vs
purchased cars). Still this shows how valuable Cash Flow Statement Modeling can be, particularly for
developing companies.Going back to our Apple model, our main reason for modeling the cash flow statement is to arrive at a
free cash flow estimate to use in our DCF valuation. There are only a few calculations which are unique to
our cash flow statement: 1) Depreciation & amortization, and 2) Property, Plant & Equipment (PP&E).Depreciation & amortization is calculated based on the ratio of depreciation & amortization to average
property plant & equipment and acquired intangible assets (this is only an approximation since intangibles
are tested for impairment over time not amortized on a straight-line basis). The capex outflow (shown in
the Cash Flow Statement as ͞Payments for acquisition of PPE") is estimated using a capex growth rate.
The remaining cash flow items represent changes in Balance Sheet accounts.are also paid out of retained earnings, and if the company does not disclose treasury shares separately in
the equity section, we also subtract share repurchases from this account. The beginning retained earnings
balance of $92.3B, plus net income of $18.2B, minus dividends of $2.9B, minus share repurchases of $6.0B, equals the ending retained earnings balance of $101.6B. (Refer to tick mark ͞a" below) Income Statement links to the Cash Flow Statement - GAAP net income is the starting point for the Cash Flow Statement. Depreciation and amortization expense are also added back to net income on thecash flow statement, although many companies do not isolate these items as separate lines on the Income
Balance Sheet links to the Cash Flow Statement - All of the changes in the Balance Sheet accounts are
reflected in the Cash Flow Statement. For example, accounts receivable increased by $7.3B [$24.1B minus
$186.8B equals $7.3B], which is a use of cash reflected on the Cash Flow Statement. (Refer to tick mark
Property, Plant, & Equipment - The Balance Sheet and Cash Flow Statement are also linked by Property,
Plant, & Equipment (PP&E). The beginning PP&E balance of $22.4B, plus capex outflows on the Cash Flow
Statement of $3.5B, minus depreciation of $2.4B, equals the ending PP&E balance of $23.5B (see
comment about amortization in Section 9 above). (Refer to tick mark ͞d" below)Cash Flow links to the Balance Sheet - The Cash Flow Statement will add all the sources and uses of cash
to the beginning cash balance to get to the ending cash balance which will flow to the Balance Sheet.
(Refer to tick mark ͞e" below)would take to beat the consensus EPS estimate. Or use it to analyze where the consensus estimates stand
relatiǀe to management͛s guidance for the quarter, and whether or not the forecast makes sense relative
to historic results. After the company reports results, you can fill in the new guidance and use the model
to assess whether or not the after-hours price move makes sense.None of the cells are locked so you can change anything. Keep in mind that it is better to stick to our
primary assumption cells in blue when changing the estimates to maintain the integrity of the underlying
equations in the model. Up nedžt͙We will continue with the Apple Inc edžample in Chapter 2 with the calculation of the inputs to our Discount Cash Flow valuation model. © Gutenberg Research LLC 2016. All rights reserved.The market ERP is a measure of the total return an investor requires in excess of the risk-free rate (typically
a U.S. Treasury security), to compensate for the additional risk of the investment. There is a direct
relationship between the ERP and reƋuired return, which means that as an inǀestment͛s risk increases,
investors will expect a higher return, and conversely, as risk decreases the required return on equity will
also decrease.There are many different approaches to measuring the market͛s EƋuity Risk Premium. Each method has
different assumptions, pros, cons, and results. We use the Constant Sharpe approach. If you would like to
follow along, our ERP calculation spreadsheet is available for download on our education page at
www.GutenbergResearch.com/Education. The source links are included in the file so you can update the
There are two primary steps to calculate the Constant Sharpe ERP. First is a calculation of the market͛s
historic average Sharpe ratio, which is the average S&P500 total return, less the risk-free rate (we use the
can see we used data back through 1963 to calculate the excess return over the risk-free rate each year,
then we took the average and divided it by the standard deviation in returns to arrive at the Constant
The second step is the application of volatility to the Constant Sharpe. The volatility measure for the
S&P500 is the Chicago Board of Trade͛s (CBOE) Volatility index known as the VIX. The VIX is a measure of
implied short-term volatility of S&P500 options, quoted in percentage terms. As market uncertaintyincreases, the VIX increases, and equity valuations fall. When market uncertainty decreases, the VIX
decreases, and equity valuations rise.To link the ERP to market volatility we multiply the Constant Sharpe by the VIX. Using a constant Sharpe
ratio of 0.30 and the current average VIX index value for the fourth quarter (quarter-to-date, as of
December 2, 2015) of 16.4%, we estimate the ERP to be 5.0%. Section 2: Review of the Historic Constant Sharpe ERPThe chart below plots our Constant Sharpe-based ERP through history. Three periods stand out when the
ERP increased well above the long-term average for a sustained period of time. All three were driven by
events which triggered periods of greater financial uncertainty. The first spike occurred in 1998 around
the time of Long-Term Capital Management͛s collapse. The second occurred in 2001 after the September
higher discount rates during these periods resulted in lower net present values of cash flows, since the
discount rate is in the denominator of the discounting equation, and lower stock valuations to reflect the
heightened level of risk. © Gutenberg Research LLC 2016. All rights reserved.The ERP from Section 2 represents the risk premium for the entire market, however for our purposes we
need a risk premium for a specific stock. We use beta to make the link between the broader market and
the company we are analyzing.Beta is a measure of correlation between the change in value of an individual stock, and the change in
value for an index. Many financial data providers publish beta for stocks; however, if you do not have
access to these sources you can derive beta by regressing the stock͛s returns against the S&P500 (the
regression beta coefficient is equal to the stock͛s beta). In this section we will demonstrate this calculation
using Excel and our Apple example.First download the daily price of Apple shares and the S&P500 Index value (this will be our market proxy).
Then calculate the percentage change for each observation.Next you will need the Data Analysis Tool in Excel. If you have not installed it go to ͞File", ͞Options", ͞Add-
ins", ͞Analysis ToolPak", ͞Go" to install.Now you can run the regression. Select ͞Data Analysis" on the ͞Data" tab, then select ͞Regression". The
first variable is the percentage change in Apple shares and the second variable is the percentage change
in the index. Select an output range and click ͞OK". 2% 4% 6% 8% 10% 12% 14% 16% 18%The regression Beta is the slope coefficient in the regression equation, which is 0.895 in this example. The
interpretation of the result is simple: the slope represents the expected percentage change in the value
of Apple shares based on the percentage change in the S&P500 index. Beta is the key to linking the ERP
for the entire market back to Apple shares, which is the next step in the equation. Notice that the t-Stat
is very high and the P-value is very low. This indicates that the correlation between the two variables is
statistically significant, and the S&P500 index is likely the correct market proxy to measure Apple against.
The R Square is relatively low which also makes sense since there are many other factors which cause
Apple shares to go up or down in value besides what the general market does (interpretation of the R
Square: approximately 27% of the variation in Apple shares is explained by changes in the S&P500 index).
For additional details on regression analysis, please refer to Chapter 5 in this series.Beta and the ERP are not direct inputs into the Weighted Average Cost of Capital (WACC) calculation,
which is the discount rate we use to value shares. Instead these metrics are used to derive the required
return on equity. To do this we utilize the Capital Asset Pricing Model (CAPM) which states that the
required return on equity for a stock is equal to the risk-free rate, plus beta times the ERP. After inputting
the beta, ERP, and risk-free rate into the CAPM equation, we can solve for the required return.We calculate the required return on equity in the DCF section of each of our Tier 1 models. In our Apple
example the required return on equity is 6.5% calculated as the risk-free rate of 2.1%, plus a beta of 0.895
times the market risk premium of 4.9%. Section 5: Calculating the Weighted Average Cost of CapitalNow that we have the required return on equity, we can calculate the Weighted Average Cost of Capital
(WACC). This calculation is also done in the DCF section of our Tier 1 models. We simply apply the weight
of eƋuity in the company͛s capital structure to the cost of eƋuity, and add the result to the weight of debt
times the after tax cost of debt. The result is the final WACC which we use as the discount rate in our
valuation.Rather than calculating the WACC, some analysts use a competitor͛s WACC, a competitor͛s beta in the
calculation, or use a sector average WACC. You can change any of the inputs in our models, however, if
you choose one of these options keep in mind that you will need to make an adjustment to account for
the differences in capital structure between the companies. Up nedžt͙ Now that we have our WACC and cash flow estimates from Chapter 1, we can calculate a DCF-based valuation, which we will demonstration in Chapter 3. © Gutenberg Research LLC 2016. All rights reserved.For our Tier 1 models we use a two-stage DCF approach, projecting five years of cash flows in our first
stage, which represents the ͞high-growth" stage. Then we calculate a terminal value by assuming the
company reaches a constant growth stage in the sixth year. We should point out that this is a rather large
assumption for most companies since they will likely go through many business cycles throughout their
existence, rather than reaching a flat growth rate held in perpetuity, however, once the model is
established and calibrated it does give a comparable valuation over time. Also, since we add an additional
year to the forecast every four quarters, the constant growth stage will be pushed out if management is
able to continue to execute on growth.In our Tier 1 models we calculate the free cash flow at the bottom of the Cash Flow Statement as the cash
from operations, less capital expenditures, plus after-tax interest expense. We then discount the free cash
flow using the WACC calculated in Chapter 3. This calculation is performed for each of the five stage-one
years. The sum of the five discounted cash flows is saved in the DCF section of our model.However, it is actually a simple perpetuity equation. If you break the equation down, you can see that it
represents the Cash Flow for the sixth year, divided by WACC, minus the constant growth rate. Since this
represents the terminal value at the end of the fifth year, we discount the result back five years to present
value terms. The numerator, which represents the cash flow of year six, is the complex part with the
following three parts: Part 1: Cash Flow From Operations (CFO) = CFO of year 5 x (1 + constant CFO growth rate) Part 2: Minus capex = Average capex to sales ratio x [sales x (1 + constant sales growth rate)] Part 3: Plus the after-tax cost of debt = After tax cost of debt x long-term debtNow that we have the stage-one and stage-two cash flows in present value terms, all we have to do is add
them together with the adjusted net cash per share (the adjustment to cash per share is discussed in
Chapter 4). The result is the final DCF-based share valuation.At this point you should be able to recognize how the different inputs impact valuation under different
scenarios. As you think about the impact keep the basic equations in mind:Market ERP = Require Return on Equity for the Market - Risk-Free Rate, or Constant Sharpe × Volatility
CAPM = Company Specific Required Return Equity = Risk-Free Rate + Beta × Market Risk Premium WACC = Weight of Debt × After-Tax Cost of Debt + Weight of Equity × Required Return on Equity Using these equations, we can draw the following conclusions about our DCF inputs:Volatility - The higher the VIX, the greater the cost of equity. Increases in volatility will have a greater
impact on companies with a greater proportion of their capital structure coming from equity rather than
debt. This scenario can tilt if a large debt balance results in a high probability of default, in which case the
company͛s credit spread would widen and the cost of debt and equity would spike.Interest Rates - An increase in market interest rates will increase the required return on equity through
the CAPM, and result in lower asset values. If a company has fixed rate liabilities, higher rates will not
impact the cost of debt in the WACC when rates change. If the company͛s debt has capsͬfloors or
adjustable features, then changes in interest rates can have an impact on the cost of debt. Beta - A high beta coefficient will result in greater variability in the cost of equity. © Gutenberg Research LLC 2016. All rights reserved.impact would be on share price. For example, if you think the Federal Reserve will increase rates over the
next year, try increasing the risk-free rate. Or if you think volatility will spike, you can change the VIX
assumptions. Keep in mind we have not locked any of the cells in our models so you can change anything
you want; However, we suggest sticking to the blue cells to maintain the integrity of the equations.
You may be thinking ͞I haǀe been using market multiples and missing the risk in my valuation all this
time͍͊" That is not necessarily the case. Market multiples are ǀery useful tools, arguably more useful than
a discounted cash flow analysis which requires many more assumptions; However, it is important toremember that if you are going to use a multiple approach, you must adjust the multiple during times of
increased risk to reflect the greater required return on equity. Regardless of the valuation approach you
choose, keeping the fundamental risk/return theory in mind will ultimately lead to better investment
decisions. Up nedžt͙In Chapter 4 we use the forecast from our earnings model to value shares using a market multiple approach. © Gutenberg Research LLC 2016. All rights reserved.Industry/Sector - Each sector has a set of metrics which are widely accepted by analysts and investors
including which multiples to use for valuation. For banks it is the Price to Book Value multiple, for energy
companies it is the EV to EBITDA multiple, and for developed companies with relatively stable earnings it
is the Price to Earnings (PE) ratio.Cyclical vs non-cyclical - If the company͛s industry is currently in decline, then the company could be
operating at a loss. In this case the PE ratio could not be used for valuation. As an alternative it may be
appropriate to use a Price to EBITDA ratio or Price to Sales ratio. Another alternative would be to forecast
earnings on a normalized basis assuming a return to average market conditions at some point in the future
and apply a PE ratio to this later stage in the business cycle. Lifecycle stage - Companies in the development stage have much different earnings profiles comparedto those which have reached maturity. Developing companies may not be profitable or may have volatile
earnings streams with high growth rates, in which case it may make more sense to apply a multiple to a
later stage of earnings rather than using a Next Twelve Month (NTM) PE. For example, Netflix is currently
trading at a PE multiple of 647x based on the NTM consensus EPS estimate, while Apple trades at 12x. The
reason that inǀestors are willing to pay so much more for Netflidž͛ earnings is that the company is currently
in a hyper-growth stage. As a result, investors are valuing the company on the ultimate long-term earning
capacity which will not be reached until many years from now; Therefore, the NTM earnings are lessrelevant for valuation purposes. For companies in a hyper-growth stage a growth adjustment will make
the PE ratio more meaningful. Continuing with our Apple Inc (NASDAQ:AAPL) Model example, we have selected to use the PE ratio forvaluation based on consensus forward earnings expectations. For most of our models we use the average
NTM PE multiple. Keep in mind that this will typically result in a value which is close to the current price
Most companies can be valued by applying a market multiple to forecasted earnings, however there are a few things we need to consider when deciding which multiple to use. In this chapter we will be review some of the key factors to consider when choosing a multiple, and discuss how to use the earnings forecasted in Chapter 1 to derive a market multiple-based valuation.due to the nature of the calculation. We do this to isolate the impact of valuation changes to earnings
results or other news, and to reflect the ͞baseline" scenario for our model users.Other than using a historic average multiple you may want to consider using the multiple of a competitor,
a sector average, or a cyclical peak/trough multiple. The multiple is easy to change in our models which
gives our users plenty of flexibility to exercise judgment in the valuation section.After we haǀe established which multiple to use we must decide if any of the company͛s assets should be
removed and valued separately. For most of our models we subtract the net cash from the sharevaluation. It is important to note that we are using net cash after deducting debt because we do not want
to give a valuation benefit to companies that rack-up large debt balances and let the cash sit on the
balance sheet.If cash is generated from foreign operations the company would be subject to a repatriation tax if they
chose to bring it back to the United States. Tax laws are complex; we take a simplistic approach for an
approdžimation of the impact by applying the company͛s effectiǀe tadž rate to the offshore portion of the
cash balance. Section 3: Calculating Share Value Using the MultipleNow that we have the inputs, the calculation is straight forward: the PE multiple, times the sum of the
next four quarters of diluted EPS (calculated in Chapter 1), plus adjusted net cash per share = the implied
share valuation. For our Tier 1 models we apply a 50% weight to our DCF-based valuation and a 50% weight to our market multiple valuation to arrive at our implied 12-month target share price. Up nedžt͙We demonstrate how to use regression analysis with leading indicators to predict earnings. © Gutenberg Research LLC 2016. All rights reserved.The first step is selecting the appropriate variables to use in the model. It can be difficult to find variables
with strong explanatory power. Typically, we try to find data which is effective at predicting top-line
revenue, since anything below the revenue line can be distorted over time due to fluctuations in profit
margins. These fluctuations can impact the regression results. We start by generating a list of potential
leading indicators for a company͛s reǀenue. Then we test each ǀariable using the steps in this chapter to
find the best predictors.For Boeing and Starbucks we found the U.S. Census Bureau͛s reports to be helpful in proǀiding leading
indicators. For Boeing we selected the Aircraft & Parts Shipments section, including defense and non-
defense shipments, in the Industrial Production report, which is published on a monthly basis. The final
monthly report for each calendar quarter is typically released about a week before Boeing reports quarterly results which makes it a good data point for prediction.The two variables (Boeing͛s Reǀenue and the Shipment data) appear to have a strong correlation based
on the time series plot below (we will test the correlation in Section 2). In this edžample Boeing͛s reǀenue
is what we would like to predict, so it is the dependent variable (Y variable), or the variable which
͞depends" on the results of another ǀariable. Aircraft & Parts Shipments from the Industrial Production
report is the independent variable (X variable). Regression analysis is a mathematical process which estimates the relationship between two or more variables. As investors we must forecast our expectations for future periods. Regression models can be helpful tools when reliable data is available ahead of a company͛s press release. In this chapter we will demonstrate how we use regression models for earnings projections using a simple linear regression example for Boeing Co (NYSE:BA) and a multiple regression for Starbucks Corp (NASDAQ:SBUX). We will also discuss the limitations associated with using regression analysis to predict results.For Starbucks we chose the Food Services & Drinking Places sales data, contained in the Advanced Retail
Θ Food Sales report. Similar to our Boeing edžample, this report is also aǀailable prior to the company͛s
release and shows a strong correlation to Starbucks͛ revenue.In the chart below there is a clear de-coupling between the two variables in the fourth calendar quarter
each year. This is because Starbucks͛ fourth calendar quarter sales typically outperform the Food Service
& Drinking Places total results. If we do not include an adjustment to account for this, our model will likely
have a larger standard error.1 We will be using a second independent variable to account for seasonality,
which is a dummy2 variable which represents the fourth calendar quarter. Starbucks Revenue vs Food Services & Drinking Places Sales Source: U.S. Census Bureau, Company reports, and Gutenberg® estimates.observation and the model predicted value for that observation). The smaller the standard error the better the
model will be at predicting results.Next we must run the regression models for Boeing and Starbucks. We will be using Excel for this analysis
since most of our users have access to this program. If you have not installed the Excel Data Analysis add-
in, please refer to the instructions in Chapter 2, Section 3. Use the following steps to run the regression (starting with the Boeing Model):Step 1: From the Data tab select ͞Data Analysis", then ͞Regression". Select the quarterly percentage
change in Boeing͛s reǀenue for the Y input range (dependent variable), and the percentage change in
shipments for the X input range (independent variable).Step 2: Check the ͞Labels" bodž, which indicates that the Y and X ranges include the data headings.
Step 3: Check the ͞Constant is Zero" bodž. For this regression we want to set the constant in the regression
equation equal to zero. You can try running the regression with this box checked and unchecked to see
which result is better. Step 4: Select the ͞Output Range" where you would like the regression results to be populated. Step 5: Click the residual details you would like to analyze (we will discuss this in Section 3).We follow a similar procedure to run the Starbucks multiple regression, except the X range includes two
columns of data, and we do not check the ͞Constant is Zero" bodž.Before we can draw any conclusions about the correlation between the variables, we must test the validity
of the models. To do this we perform a series of statistical tests, including the following (starting with the
P-value and t-stat review - These two statistics are used to test whether or not the relationship between
the variables in the regression is different from zero. If the t-stat is greater than +2.793 or less than -2.79,
then a statistically significant relationship exists at the 99% confidence interval (also known as the 1%
leǀel of significance). In this case the model͛s t-stat of 12.65 is well above the critical t-value of 2.79,
therefore, a statistically significant relationship exists between the variables (pure statisticians would say
that we reject the notion that no relationship exists, or we reject the null hypothesis). The P-value
represents the lowest level of significance the model can reach while still showing a statistically significant
relationship. In this case the P-value is close to zero which means we would reach the same conclusion
even at a confidence interval greater than 99%.Breusche-Pagan test - To make sure the independent variables do not explain variation in the residuals,
a situation known as heteroskedasticty, we run the Breusche-Pagan test by regressing the independent
variable against the squared residuals. The resulting R-square was relatively low with a high p-value, and
a t-stat between the critical-t points, therefore heteroskedasticity is not an issue with this model
(correlation between shipments and the squared residuals is not statistically different from zero).In addition to the Breusche-Pagan test we can also plot the residual with a linear trend line showing the
R Square to determine if the independent variables explain variation in the residuals (this was the box we
checked in Section 2, Step 5). In the Boeing example the trend line and R Square are close to zero so the
residuals and independent variable are not correlated. © Gutenberg Research LLC 2016. All rights reserved.Serial correlation test - Serial correlation is the relationship of a variable with itself over time. If serial
correlation is present, past observations will influence future results. To ensure our model does not exhibit
signs of serial correlation we ran the Durbin-Watson (DW) test, by calculating the DW statistic4. Durbin-
Watson statistics range in value between 0 and 4, a value near 0 indicates positive serial correlation, a
value near 4 indicates negative serial correlation, a value near 2 indicates no significant serial correlation5.
The Boeing model DW statistic is near 2 which indicates that serial correlation is not likely an issue for this
model.difference of the actual residuals and the residuals on a one-period lag squared. Next divide the result by the
squared residuals.The normal distribution assumption - To test that the data does not violate the assumption of a normal
distribution, we calculate the skew6 and kurtosis7. Excel has equations for these formulas=SKEW(residuals) and =KURT(residuals). If you feel you need additional evidence, you can also run a Chi-
sq test, and plot the residuals in a histogram to see if they appear to be normally distributed. In our Boeing
model the skew (0.02, below +/-1) and kurtosis (-0.53, below 3) show that the sample is approximately
normally distributed.Model stability - To test the stability of the model we removed two random observations from the sample
and re-ran the regression. The new regression passed all the prior tests with a similar standard error
compared to the first model. This proves that the regression model is fairly stable.Other Considerations -Text book examples for regression analysis tend to be relatively straightforward.
In the real world data and external conditions will change over time which makes a regression model less
powerful, and in some cases ineffective. Even if your model passes all the statistical tests it is important
to think qualitatively about whether or not there is a true economic reason for the ǀariables͛ correlation
normal distribution would have a skew of 0. A skew of +/-1 can be considered approximately normally distributed.
and if the direction of the coefficients makes sense. Using our Boeing example, it stands to reason that
U.S. Aircraft Θ Parts Shipments could haǀe predictiǀe power for Boeing͛s reǀenue, and the positiǀe
regression coefficient makes sense as it implies a direct relationship (when shipments increase Boeing͛s
revenue increases).In addition, it is important to assess whether or not the population has changed over time. If there have
been significant changes, for example if Boeing began selling medical devices or hamburgers, then the
model could not be used (non-homogenous population through time).Another potential issue is that the Boeing Model uses U.S. Shipment data as a proxy for global revenue.
Using a proxy to represent a larger population of data has problems (see limitations in section 6). In
addition, if Boeing͛s global share of reǀenue shifts significantly this model may fail to proǀide a reliable
estimate.The first five tests are relevant for the both the Boeing and Starbucks regression models; However, since
the Starbucks example is a multiple regression, we have a few additional tests to run, including the
following:F-stat review - This statistic is similar to the t-stat, except it is used for multiple regressions. If the F-stat
is greater than or less than the critical F-values (must look up for the relevant degrees of freedom on a
critical F chart), then a statistically significant relationship exists. In our Starbucks model the F-stat of 29.88
is well above the critical F-value of 2.86, therefore, a statistically significant relationship exists between
the variables (pure statisticians would say that we reject the notion that no relationship exists, or we reject
the null hypothesis).Significance F -The Significance F has the same interpretation as the P-value for a simple linear regression:
It represents the lowest level of significance the model can reach while still showing a statistically
significant relationship. In this case the Significance F value is close to zero which means we would reach
the same conclusion even at a confidence interval greater than 99%.Multicolinearity - To test for multicolinearity we regress the two independent variables against each
other to ensure they are not correlated. For our Starbucks regression model the resulting regression
between the calendar fourth quarter dummy variable and the percentage change in U.S. Food Service and
Drinking Places Sales is not statistically significant at the 95% confidence interval, therefore
multicolinearity is not a significant issue.them to predict future period revenue, which we can include in our earnings models discussed in Chapter
For our Boeing example we can conclude that 84.3% of the variation in the percentage change in Boeing͛s
revenue is explained by the percentage cha