Corporate Finance WACC estimation
Calculation of Weighted Average Cost of Capital (WACC)
The Cost of Capital.
The WACC formula discussed below does not include Preferred Stock
(PS). Should your company have PS, be sure to adjust the equation for it, and see
the section in the chapter 13 on the Cost of Preferred Stock.
The WACC formula that we use is:
WACC = Ws *Rs + Wd*Rd*(1-T) or
We need to know how to calculate:
1. The weights (Wd and Ws )– note that: Wd + Ws = 1; so you only have to
calculate one of them). We need to calculate the weight of debt and the weight of
equity (for the cost of debt, this simply means: what proportion of the firm’s
financing is by debt?).
we will use the formula:
Wd = Book Value of Debt / [Market Value of Equity + Book Value of Debt]
The book value of debt is calculated by adding up ALL of the debt on the balance
sheet. This will typically be the sum of Notes Payable, Current Portion of LT Debt
and Long-Term Debt (Interest-bearing debt).
The market value of equity is the “Market Cap” and equals the number of
(common) shares outstanding multiplied by the price/share. Note that the “timing”
of this value should coincide with the book value of debt. For example, if you
calculate the book value of debt as of 12/31/17, then the market cap should also be
calculated for that date. Be very careful about using the reported Market Cap on
Schlumberger-Private
Yahoo.finance – it may not have the same “timing.”
2.
Rd: the cost of debt. There may be more than one acceptable approach to
calculate or estimate a company’s cost of debt. One relatively straightforward
method is to discover the company’s debt rating. This can usually be found on the
company’s 10K and doing a word search for ‘rating’ or ‘debt rating.’ If you can
find the debt rating for your company then you can carry out the following steps. If
you are not able to find a bond rating readily, you can register (for free) at
Moody's to find company ratings. If you cannot find a bond rating for your
company, you might try to estimate/guess what it is by considering your
company’s beta and comparing the bond ratings for companies with similar betas.
Once you have the actual bond rating or an estimate you can then find or estimate
your company’s cost of debt by first going to Federal reserve St. Louis
https://fred.stlouisfed.org/categories/32348 to find historical Corporate bond yields
based on the credit rating of your company. For example, if the Aaa yield is
5.00%, the Aa1 yield is 5.15% and the Aa2 yield is 5.30%, you can see a pattern
(equation): for every increase in risk (from Aaa to Aa1), there is a 0.15% increase
in the yield. If your company has an A1 rating, then it is two steps “below” the
Aa2 rating, so you should add approximately 0.30% more to the 5.30% for the A1
rating, giving you a cost of debt for your company of about 5.60%. Note that this
approach assumes a linear equation for the cost of debt (which may not be strictly
true).
To make it easy for you, for this project, you can just assume Aaa yield is 5.0% with a
0.15% increase in yield for every increase in risk (e.g., from Aaa to Aa1, Aa1 to Aa2,
and so on).
3.
The corporate tax rate (T or Tc). You may want to calculate
several/many historical effective tax rates for you company in this project. The
effective tax rate is the actual taxes paid divided by earnings before taxes (on the
income statement). You can calculate/consider these rates for the past 5-10 years
and determine the effective tax rate you want to use in your pro forma. Then you
adjust the rate to reflect the recent tax reform for future rate.
4.
Rs (or REquity) the cost of common equity. Use the Security Market Line
(SML) – this is why you learn how to calculate a company’s beta and also why
Schlumberger-Private
you learn how to find the appropriate risk-free rate and market-risk premium. The
model is also called Capital Asset Pricing Model (CAPM).
Schlumberger-Private
You can use CAPM model to predict the cost of common stock. The equation runs
as followings:
Rs = RRF + (RPM)*β
• β- find it from Yahoo Finance- Alternatively, you can also use historical stock
returns to estimate the beta
• RRF, please check the website or use 3.5%:
https://www.federalreserve.gov/datadownload/Choose.aspx?rel=H15
• RPM, please use 6.5% for expected market premium for this project.
Schlumberger-Private
Year
Div/Share LN(DIV)
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
0.71
0.73
0.76
0.79
0.81
0.83
0.86
0.90
0.93
0.96
1.00
-0.347
-0.308
-0.274
-0.235
-0.214
-0.184
-0.147
-0.105
-0.070
-0.041
0.000
Annual Div Growth Sales (in millions)
Annual Sales Growth
LnSales
$
2,924
3.9% $
3,171
8.45%
3.5% $
3,398
7.15%
4.0% $
3,611
6.26%
2.1% $
3,804
5.34%
3.1% $
4,012
5.47%
3.7% $
4,210
4.92%
4.3% $
4,428
5.19%
3.5% $
4,612
4.16%
3.0% $
4,760
3.20%
4.2% $
5,000
5.04%
7.98
8.06
8.13
8.19
8.24
8.30
8.35
8.40
8.44
8.47
8.52
Loglinear regression ( Dependent variable Log Sales revenue)
SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
0.99
0.99
0.99
0.02
11.00
ANOVA
df
Regression
Residual
Total
SS
1.00
9.00
10.00
Coefficients
Intercept
X Variable 1
MS
0.30
0.00
0.30
Standard Error
-96.56
3.88
0.05
0.00
0.30
0.00
t Stat
-24.86
26.99
F
728.41
P-value
0.00
0.00
Significance F
0.00
Lower 95%
Upper 95% Lower 95.0%
-105.34
-87.77
-105.34
0.05
0.06
0.05
rate of the coefficient; then substract 1.00
5.35%
= ANNUAL Growth RATE of GROWTH in Revenue
be sure to look at the Excel function in the cell!
Linear Regression for Dividend
SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
1.00
1.00
1.00
0.01
11.00
ANOVA
df
Regression
Residual
Total
SS
1.00
9.00
10.00
Coefficients
MS
0.13
0.00
0.13
Standard Error
0.13
0.00
t Stat
F
4449.54
P-value
Significance F
0.00
Lower 95%
Upper 95% Lower 95.0%
Intercept
X Variable 1
-68.62
0.03
1.03
0.00
-66.88
66.70
0.00
0.00
-70.94
0.03
rate of the coefficient; then substract 1.00
3.46%
= ANNUAL Growth RATE of GROWTH in Dividend
be sure to look at the Excel function in the cell!
-66.30
0.04
-70.94
0.03
Upper 95.0%
-87.77
0.06
Upper 95.0%
-66.30
0.04
Example
Corporate Valuation and Financial Planning
9-2 Financial Planning at DaDa, Inc.
The process used by DaDa to forecast the free cash flows from its operating plan is described in the sections below.
Setting Up the Model to Forecast Operations
We begin with DaDa's most recent financial statements and selected additional data.
Figure 9-1
DaDa’s Most Recent Financial Statements (Millions, Except for Per Share Data)
INCOME STATEMENTS
BALANCE SHEETS
2016
2017 Assets
Net sales
$
21,315 $ 22,386 Cash
$
COGS (excl. depr.)
17,462
18,490 ST Investments
Depreciation
981
1,011 Accounts receivable
Income from Equity Investmentss
- Inventories
Other operating expenses
545
1,393 Other Current Assets
EBIT
$
3,853 $
3,896 Total CA
$
Interest Income and Other, Net
426
666 Long Term Investments
$
Interest expense
81
92 Equity and Cost Investments$
PP&E
$
EBT
$
4,198 $
4,317 Goodwill
$
Intangible Asset
$
Interest expense
100
120 Deferred LT Asset Charges
Other L-T Assets
Pre-tax earnings
$
4,198 $
4,317 Total assets
$
Taxes (40%)
1,379
1,433
NI before pref. div.
$
2,819 $
2,884 Liabilities and equity
Preferred div.
8
8 Accounts payable
$
Net income
$
2,811 $
2,876 Accruals
Insurance Reserves
Store Value and Card Liab
Notes payable
Other Data
Total CL
$
Common dividends
$1,178
$1,450 Long Term Debt
Other L-T Liabilities
Addition to RE
$1,633
$1,426 Total liabilities
$
2016
2,129
134
769
1,379
347
4,758
1,142
355
4,534
1,720
516
885
403
14,313
731
1,999
246
1,171
400
4,547
3,185
690
8,422
$
$
$
$
$
$
$
$
$
$
$
2017
2,462
229
870
1,364
358
5,283
542
482
4,920
1,539
441
795
363
14,366
783
1,935
215
1,289
4,221
3,933
755
8,909
Tax rate
Shares of common stock
33%
1,500
Earnings per share
Dividends per share
$1.87
$0.79
Price per share
$40.00
33% Common stock
1,400 Additional Paid In Captial
Retained earnings
$2.05 Accum other Comprehensive Loss
$1.04 Total common equity
$
$
$27.00 Total liabs. & equity
$
2
41
5,950
(108)
5,884 $
7 $
14,313 $
1
41
5,563
(156)
5,450
7
14,366
The figure below shows all the inputs required to project the financial statements for the scenario that has been selected. The
scenario is named Status Quo because all operating ratios except the sales growth rate are assumed to remain unchanged. The initial
sales growth rate was chosen by DaDa's managers based on the existing product lines. The growth rate declines over time until it
eventually levels off at a sustainable rate. The other scenario is named Final because it is the set of inputs chosen by DaDa's
management team.
Section 1 shows the inputs required to estimate the items in an operating plan. For each of these inputs, Section 1 shows the
industry averages, the actual values for the past two years for DaDa, and the forecasted values for the next five years. The managers
assumed the inputs for future years (except the sales growth rate) would be equal to the inputs in the first projected year.
DaDa's managers assume that sales will eventually level off at a sustaniable constant rate.
Sections 2 and 3 show the data required to estimate the weighted average cost of capital. Section 4 shows the forecasted growth rate
in dividends.
Note: These inputs are linked throughout the model. If you want to change an input, do it here and not other places in the model.
Figure 9-2
DaDa's Forecast: Inputs for the Selected Scenario
Status Quo
Industry
DaDa
Inputs
Actual
Actual
1. Operating Ratios
2017
2016
2017
Sales growth rate
5%
3%
5%
COGS (excl. depr.) / Sales
76%
82%
83%
Depreciation / Net PP&E
9%
111%
127%
Other op. exp. / Sales
10%
3%
6%
Cash / Sales
1%
10%
11%
Acc. rec. / Sales
10%
4%
4%
Inventory / Sales
19%
6%
6%
Net PP&E / Sales
39%
4%
4%
Other L-T Assets/Sales
2%
2%
Other L-T Liabilities/Sales
3%
3%
Acc. pay. / Sales
4%
3%
3%
Accruals / Sales
7%
9%
9%
Tax rate
40%
33%
33%
2. Capital Structure
Actual Market Weights
% Long-term debt
22%
#REF!
#REF!
% Short-term debt
3%
#REF!
#REF!
% Preferred stock
2%
#REF!
#REF!
% Common stock
73%
#REF!
#REF!
3. Costs of Capital
Rate on LT debt
Rate on ST debt
Rate on preferred stock (ignoring flotation costs)
Cost of equity
4. Target Dividend Policy
Actual
Growth rate of dividends
3%
31.9%
2018
5.0%
76%
10%
9.5%
1%
10%
20%
45%
13%
13%
4%
6%
25%
28%
2%
3%
67%
9.0%
10.0%
8.0%
13.58%
3.5%
DaDa
Forecast
2019
2020
4.50%
4.00%
76%
76%
10%
10%
9.5%
9.5%
1%
1%
10%
10%
20%
20%
45%
45%
13%
13%
13%
13%
4%
4%
6%
6%
25%
25%
Target Market Weights
28%
28%
2%
2%
3%
3%
67%
67%
Forecast
9%
9%
10%
10%
8%
8%
14%
14%
3.5%
3.5%
2021
3.50%
76%
10%
9.5%
1%
10%
20%
45%
13%
13%
4%
6%
25%
2022
3.5%
76%
10%
9.5%
1%
10%
20%
45%
13%
13%
4%
6%
25%
28%
2%
3%
67%
28%
2%
3%
67%
9%
10%
8%
14%
9%
10%
8%
14%
3.5%
3.5%
9-3 Forecasting Operations
The figure below shows the forecasted items for the operating plan. For convenience, we repeat the inputs of operating ratios.
Section B1 shows the sales forecast. Each year's sales is equal to the previous year's sales multiplied by the forecasted sales growth
rate.
Section B2 shows the projections of operating assets and operating liabilities. The operating asset for a particular year is equal to
the product of that asset's ratio in Section A1 and that particular year's projected sales. The operating liabilities are projected in a
similar manner.
Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio
in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that
particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. NOPAT is EBIT(1-T),
where T is the tax rate.
Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio
in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that
particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. NOPAT is EBIT(1-T),
where T is the tax rate.
Section B4 shows the projections of free cash flows. NWC is equal to operating CA (i.e., cash, accounts receivable, and inventories
from Section B2) minus operating CL (i.e., accounts payable and accruals from Section 4). Total capital is equal to the sum of NWC
and net Fixed Asset (NFA) (from Section B2).
Section B5 shows the results of the operating plan. The first rows in Section B5 report the target WACC (calculated as shown in
Chapter 13), the return on invested capital, and the growth rate in FCF.
The continuing value, value of operations, and estimated intrinsic stock price are calculated using the FCF valuation model as
present in Chapter 9.
Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2.
Figure 9-3
DaDa's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data)
Status Quo
Industry
DaDa
DaDa
Panel A: Inputs
Actual
Actual
Forecast
A1. Operating Ratios
2017
2016
2017
2018
2019
2020
Sales growth rate
5%
3.2%
5.0%
5.0%
4.5%
4.0%
COGS (excl. depr.) / Sales
76%
81.9%
82.6%
76.0%
76.0%
76.0%
Depreciation / Net PP&E
9%
110.8%
127.1%
10.0%
10.0%
10.0%
Other op. exp. / Sales
10%
2.6%
6.2%
9.5%
9.5%
9.5%
Cash / Sales
1%
10.0%
11.0%
1.0%
1.0%
1.0%
Acc. rec. / Sales
10%
3.6%
3.9%
10.0%
10.0%
10.0%
Inventory / Sales
19%
6.5%
6.1%
20.0%
20.0%
20.0%
Net PP&E / Sales
39%
4.2%
3.6%
45.0%
45.0%
45.0%
Other L-T Assets/Sales
1.9%
1.6%
13.0%
13.0%
13.0%
Other L-T Liabilities/Sales
3.2%
3.4%
12.5%
12.5%
12.5%
Acc. pay. / Sales
4%
3.4%
3.5%
4.0%
4.0%
4.0%
Accruals / Sales
7%
9.4%
8.6%
6.0%
6.0%
6.0%
Tax rate
40%
32.9%
33.2%
25.0%
25.0%
25.0%
Panel B: Results
Actual
Forecast
B1. Sales Revenues
2017
2018
2019
2020
Net sales
$22,386
$23,505
$24,563
$25,546
B2. Operating Assets and Operating Liabilities
Cash
$2,462
$235
$246
$255
Accounts receivable
$870
$2,351
$2,456
$2,555
Inventories
$1,364
$4,701
$4,913
$5,109
Net PP&E
$795
$10,577
$11,053
$11,496
2021
3.5%
76.0%
10.0%
9.5%
1.0%
10.0%
20.0%
45.0%
13.0%
12.5%
4.0%
6.0%
25.0%
2022
3.5%
76.0%
10.0%
9.5%
1.0%
10.0%
20.0%
45.0%
13.0%
12.5%
4.0%
6.0%
25.0%
2021
$26,440
2022
$27,365
$264
$2,644
$5,288
$11,898
$274
$2,737
$5,473
$12,314
Other L-T Assets
Other L-T Liabilities
Accounts payable
Accruals
B3. Operating Income
COGS (excl. depr.)
Depreciation
Other operating expenses
EBIT
Net operating profit after taxes NOPAT(i.e., EBIT (1-T))
B4. Free Cash Flows
Change in Net Working Capital
CapEx (investing in PP&E and other L-T Asset)
FCF
Below is an alternative way to calculate FCF
Net operating working capital
Total operating capital (i.e., NWC+NFA)
FCF = NOPAT – Δ op capital
B5. Estimated Intrinsic Value
Target WACC
Return on invested capital
Growth in FCF
Long-term FCF Growth Rate
Continuing Value:
$363
$755
$783
$1,935
$3,056
$2,938
$940
$1,410
$18,490
$1,011
$1,393
$3,896
$2,603
$17,864
$1,058
$2,233
$2,351
$1,763
$3,321
$3,193
$1,022
$1,533
$3,437
$3,305
$1,058
$1,586
$3,557
$3,421
$1,095
$1,642
$18,668
$1,105
$2,333.49
$2,456
$1,842
$19,415
$1,150
$2,427
$2,555
$1,916
$20,094
$1,190
$2,512
$2,644
$1,983
$20,797
$1,231
$2,600
$2,737
$2,052
$2,956
$13,533
−$13,668
$222
$1,719
$1,007
$206
$1,719
$1,140
$188
$1,708
$1,277
$194
$1,768
$1,321
$1,980
$3,138
$2,300
$4,936
$18,569
−$13,668
$5,158
$19,405
$1,007
$5,365
$20,181
$1,140
$5,552
$20,887
$1,277
$5,747
$21,618
$1,321
82.9%
11.4%
9.5%
11.4%
9.5%
-107%
11.4%
9.5%
13.2%
11.4%
9.5%
12.0%
11.4%
9.5%
3.5%
3.5%
$1,092
$229
$1,321
$3,933
#REF!
#REF!
$1,400
#REF!
433
881
2300
$3,193
$3,070
$983
$1,474
=
$17,358
Present value of CV
+ Present value of FCF
$10,127
−$9,035
Value of operations
+ S-T investments & L-T Securities
Estimated total intrinsic value
− All debt
− Preferred stock
Estimated intrinsic value of equity
÷ Number of shares
Value of operations =
$1,092
Estimated intrinsic stock price =
Value of Operations:
9-4 Projecting DaDa's Financial Statements
Projecting 1 Year of Financial Statements
Figure 9-4, shown below, projects DaDa's financial statements for the upcoming year for the Status Quo scenario.
Operating items are projected in the identical manner as previously projected for the operating plan.
The preliminary short-term financial policy calls for no changes in notes payable, long-term bonds, preferred stock, and common
stock, so their values from the previous year are carried over.
The interest on notes payable and long-term bonds is based on the average amount of debt during the year, defined as the average of
the beginning debt (i.e., the debt at the end of the previous year) and the ending debt. An identical process is applied to preferred
dividends.
The preliminary short-term financial policy calls for dividends to grow at the same rate as the long-term sustainable growth rate in
earnings (which is the same as sales in the long-term).
Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and internal
sources. The sum of these three sources of financing is the total amount of additional preliminary financing.
Section 3 also calculates the total amount of additional assets required by the operating plan.
The difference between the total additional financing and the total additional assets is defined as the financing deficit (if the
difference is negative) or the financing surplus (if the difference is positive).
If there is a financing deficit, DaDa will draw on a line of credit. DaDa assumes that the LOC will be accessed on the last day of the
year, so the new line of credit (reflected in the end-of-year balance) will not accrue enough interest to matter. Therefore, the interest
on the LOC will be equal to the balance at the beginning of the year (which is the same as the balance at the end of the previous year).
If there is a financing surplus, DaDa will pay a special dividend.
Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2.
Figure 9-4
Projected Financial Statements (Millions of Dollars)
Status Quo
1. Balance Sheets
Most Recent
2017
Assets
Cash
$2,462.3
Accounts receivable
870.4
Inventories
1,364.0
Total current assets
$4,696.7
Net PP&E
795.4
Other L-T Assets
362.8
Total assets (TA)
$5,854.9
Liabilities and equity
Input
Basis for 2018 Forecast
1.00% × 2018 Sales
10.00% × 2018 Sales
20.00% × 2018 Sales
45.00% × 2018 Sales
13.00% × 2018 Sales
Forecast
2018
$235.05
$2,350.53
$4,701.06
$7,286.64
$10,577.39
$3,055.69
$20,919.72
Accounts payable
Accruals
Notes payable
Line of credit
Total CL
Other non-current Liabilities
Long-term bonds
Total liabilities
Preferred stock
Common stock
Retained earnings
Total common equity
Total liabs. & equity
2. Income Statement
Net sales
COGS (excl. depr.)
Depreciation
Other operating expenses
EBIT
Less: Interest on notes
Interest on bonds
Interest on LOC
Pre-tax earnings
Taxes (40%)
NI before pref. div.
Preferred div.
Net income
Regular common dividends
Special dividends
Addition to RE
$782.5
1,934.5
0.0
0.0
$2,717.0
$755.3
3,932.6
$7,404.9
#REF!
1.4
5,563.2
$5,564.6
#REF!
Most Recent
2017
$22,386.0
18,490.0
1,011.0
$1,393.0
$1,492.0
20.0
100.0
0.0
$1,372.0
1,433.2
-$61.2
8.0
-$69.2
$1,450.0
$0.0
-$1,519.2
4.00% × 2018 Sales
6.00% × 2018 Sales
Carry over from previous year
Draw on LOC if financing deficit
13% × 2018 Sales
Carry over from previous year
Carry over from previous year
Carry over from previous year
Old RE + Add. to RE
Check: TA − Total Liab. & Eq. =
Input
105%
76.00%
10.00%
9.50%
×
×
×
×
Basis for 2018 Forecast
2017 Sales
2018 Sales
2018 Net PP&E
2018 Sales
10.00% × Avg notes
9.00% × Avg bonds
11.50% × Beginning LOC
25.00% × Pretax earnings
8.00% × Avg pref. stock
104% × 2017 Dividend
Pay if financing surplus
Net income – Dividends
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
+ Increase in notes payable, long-term bonds, preferred stock, and common stock
+ Net income minus regular common dividends
Increase in financing
− Increase in total assets
Amount of deficit or surplus financing:
If deficit in financing (negative), draw on line of credit
Line of credit
$940.21
$1,410.32
$0.00
#REF!
#REF!
$2,938.16
$3,932.60
#REF!
#REF!
$1.40
#REF!
#REF!
#REF!
#REF!
Forecast
2018
$23,505.30
$17,864.03
$1,057.74
$2,233.00
$2,350.53
$0.00
$353.93
$0.00
$1,996.60
$499.15
$1,497.45
#REF!
#REF!
$1,500.75
#REF!
#REF!
$1,816.39
#REF!
#REF!
#REF!
$15,064.82
#REF!
#REF!
Note: If there is an initial balance on the on the LOC, the assumptio
balance will not change until the last day of the year. Ther
interest for the year is the based only on the beginning b
Note: If there is a LOC in the previous year, then it is necessary to su
Note: This is the planned increase in the retained earnings account.
If surplus in financing (positive), pay special dividend
Special dividend
#REF!
9-4 Analysis and Revision of the Preliminary Plan
Projected 5-Year Statements
Projected Financial Statements (Millions of Dollars)
Status Quo
1. Balance Sheets
Actual
2017
Assets
Cash
$2,462.3
Accounts receivable
870.4
Inventories
1,364.0
Total current assets
$4,696.7
Net PP&E
795.4
Other L-T Assets
362.8
Total assets (TA)
$5,854.9
Liabilities and equity
Accounts payable
$782.5
Accruals
1,934.5
Notes payable
0.0
Line of credit
0.0
Total CL
$2,717.0
Long-term bonds
3,932.6
Other non-current liabilities
755.3
Total liabilities
$7,404.9
Preferred stock
#REF!
Common stock
1.4
Retained earnings
5,563.2
Total common equity
$5,564.6
Total liabs. & equity
#REF!
Check: TA − Total Liab. & Eq. =
2. Income Statement
Actual
2017
Net sales
$22,386.0
COGS (excl. depr.)
18,490.0
Depreciation
1,011.0
Other operating expenses
$1,393.0
EBIT
$1,492.0
Less: Interest on notes
20.0
2018
2019
Forecast
2020
2021
2022
$235.1
2,350.5
4,701.1
$7,286.6
10,577.4
3,055.7
$20,919.7
$245.6
2,456.3
4,912.6
$7,614.5
11,053.4
3,193.2
$21,861.1
$255.5
2,554.6
5,109.1
$7,919.1
11,495.5
3,320.9
$22,735.5
$264.4
2,644.0
5,287.9
$8,196.3
11,897.8
3,437.2
$23,531.3
$273.7
2,736.5
5,473.0
$8,483.2
12,314.3
3,557.5 I added the line to show how you can include additional lines
$24,354.9
$940.2
1,410.3
0.0
#REF!
#REF!
3,932.6
2,938.2
#REF!
#REF!
1.4
#REF!
#REF!
#REF!
#REF!
$982.5
1,473.8
0.0
#REF!
#REF!
3,932.6
3,070.4
#REF!
#REF!
1.4
#REF!
#REF!
#REF!
#REF!
$1,057.6
1,586.4
0.0
#REF!
#REF!
3,932.6
3,305.0
#REF!
#REF!
1.4
#REF!
#REF!
#REF!
#REF!
$1,094.6
1,641.9
0.0
#REF!
#REF!
3,932.6
3,420.6
#REF!
#REF!
1.4
#REF!
#REF!
#REF!
#REF!
2018
$23,505.3
17,864.0
1,057.7
$2,233.0
$2,350.5
0.0
2019
$24,563.0
18,667.9
1,105.3
$2,333.5
$2,456.3
0.0
$1,021.8
1,532.7
0.0
#REF!
#REF!
3,932.6
3,193.2
#REF!
#REF!
1.4
#REF!
#REF!
#REF!
#REF!
Forecast
2020
$25,545.6
19,414.6
1,149.6
$2,426.8
$2,554.6
0.0
2021
$26,439.7
20,094.1
1,189.8
$2,511.8
$2,644.0
0.0
2022
$27,365.0
20,797.4
1,231.4
$2,599.7
$2,736.5
0.0
Interest on bonds
Interest on LOC
Pre-tax earnings
Taxes (40%)
NI before pref. div.
Preferred div.
Net income
Regular common dividends
Special dividends
Addition to RE
100.0
0.0
$1,372.0
1,433.2
-$61.2
8.0
-$69.2
$1,450.0
$0.0
-$1,519.2
353.9
0.0
$1,996.6
499.1
$1,497.4
#REF!
#REF!
$1,500.8
#REF!
#REF!
353.9
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
$1,553.3
#REF!
#REF!
353.9
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
$1,607.6
#REF!
#REF!
353.9
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
$1,663.9
#REF!
#REF!
353.9
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
$1,722.1
#REF!
#REF!
3. Incorporating the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
$1,816.4
$238.0
+ Increase in notes payable, long-term bonds, preferred stock and common
#REF! stock #REF!
− Previous line of credit
$0.0
#REF!
+ Net income minus regular common dividends
#REF!
#REF!
Increase in financing
#REF!
#REF!
− Increase in total assets
$15,064.8
$941.4
Amount of deficit or surplus financing:
#REF!
#REF!
Line of credit
#REF!
#REF!
Special dividend
#REF!
#REF!
$221.1
#REF!
#REF!
#REF!
#REF!
$874.4
#REF!
#REF!
#REF!
$201.2
#REF!
#REF!
#REF!
#REF!
$795.7
#REF!
#REF!
#REF!
$208.2
#REF!
#REF!
#REF!
#REF!
$823.6
#REF!
#REF!
#REF!
Note:
Note:
Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2.
Figure 9-5 (Status Quo Scenario) or Figure 9-6 (Final Scenario)
Summary of Important Inputs and Key Results for Selected Scenario (Millions Except Percentages and Per Share Data)
Status Quo
Panel A: Inputs
A1. Operating Ratios
Sales growth rate
COGS (excl. depr.) / Sales
Inventory / Sales
Net PP&E / Sales
Panel B: Key Results
B1. Operations
Free cash flow
Return on invested capital
Industry
Actual
Actual
2017
2017
5.0%
5.0%
75.5%
82.6%
19.0%
6.1%
38.5%
3.6%
Industry
Actual
Actual
2017
2017
NA
$2,300
15.0%
82.9%
DaDa
2018
5.0%
76.0%
20.0%
45.0%
2018
−$13,668
9.5%
2019
4.5%
76.0%
20.0%
45.0%
DaDa
2019
$1,007
9.5%
Forecast
2020
4.0%
76.0%
20.0%
45.0%
2021
3.5%
76.0%
20.0%
45.0%
2022
3.5%
76.0%
20.0%
45.0%
Forecast
2020
$1,140
9.5%
2021
$1,277
9.5%
2022
$1,321
9.5%
NOPAT/Sales
6.9%
11.6%
Total op. capital / Sales
46.0%
14.0%
Inventory turnover
5.0
14.3
Days sales outstanding
30.0
14.2
Fixed asset turnover
3.0
28.1
B2. Financing
Total liabilities / TA
45.0%
#REF!
Net income / Sales
6.2%
-0.3%
Return on assets (ROA)
11.0%
-1.2%
Return on equity (ROE)
19.0%
-1.2%
Times interest earned
10.0
12.4
Line of credit
NA
$0
Payout ratio
35.0%
-2094.0%
Regular dividends/share
NA
$1.04
Special dividends/share
NA
$0.00
Earnings per share
NA
−$0.05
B3. Estimated intrinsic value
12/31/2017 Estimated value of operations =
12/31/2017 Estimated intrinsic stock price =
7.5%
79.0%
4.0
36.5
2.2
7.5%
79.0%
4.0
36.5
2.2
7.5%
79.0%
4.0
36.5
2.2
7.5%
79.0%
4.0
36.5
2.2
7.5%
79.0%
4.0
36.5
2.2
#REF!
#REF!
#REF!
#REF!
6.6
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
1.5%
2.0%
Long-term FCF Growth Rate
2.5%
3.0%
$1,092
#REF!
Two-Factor Scenario Analysis
Operatining Expense Ratio
#REF!
0.0%
8.0%
0.5%
1.0%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
8.5%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
9.0%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
9.5%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
10.0%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
10.5%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
other places in the model.
See Tab "Div&Revenue Growth Analysis"
Actual Historical Financing
Long-term debt
Short-term debt
Preferred stock
Market value of equity = (Price x # shares)
Total
See the box to the right for
calculations of the actual capital
structures, based on market values,
See Tab "Div&Revenue Growth Analysis"
Percent long-term debt
Percent short-term debt
Percent preferred stock
Percent market value of equity
Total
2016
$3,185
$400
#REF!
$60,000
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
2017
$3,933
$0
#REF!
$37,800
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
o change inputs, do so in Figure 9-2.
Note:
You can also treat these items as constant, i.e., not change
with sale
You can also treat these items as constant, i.e., not change
with sale
If there is an initial balance on the on the LOC, the assumption is that the
balance will not change until the last day of the year. Therefore, the
interest for the year is the based only on the beginning balance.
If there is a LOC in the previous year, then it is necessary to subtract the previous year's line of credit. In other words, this is like paying off the old line of credit on the last day of the year and then drawing on a n
This is the planned increase in the retained earnings account.
I added the line to show how you can include additional lines
If there is an initial balance on the on the LOC, the assumption is that the
balance will not change until the last day of the year. Therefore, the
interest for the year is the based only on the beginning balance.
If there is a LOC in the previous year, then it is necessary to subtract the
previous year's line of credit. In other words, this is like paying off the
old line of credit on the last day of the year and then drawing on a new
line of credit.
o change inputs, do so in Figure 9-2.
Long-term FCF Growth Rate
3.5%
4.0%
4.5%
5.0%
5.5%
6.0%
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
Purchase answer to see full
attachment