Project – Part II
Optimal WACC & capital budgeting
Directions to Download Data
1. Log into https://wrds-web.wharton.upenn.edu/wrds/.
ID: fin3320
PW:Csulafin2019
2. Go to HOME GET DATA CRSP Annual Update Stock / Security Files
Monthly Stock File.
3. Set Data Range as “2013-01 to 2017-12”, apply company code as “TICKER”, and type in your company’s ticker.
2013-01
2017-12
4. Select TICKER, HOLDING PERIOD RETURN, and VW Return (includes distributions) as query variables
5. The query output is in Excel Spreadsheet, select YYMMDDn8 as Date Format, and SUBMIT QUERY.
Exercise
1.
(3 pts; Cost of equity) Using the downloaded information on your company, complete the following
exercises by Excel.
(a) Based on the monthly return series of your company and the value-weighted return as the
market return, run a regression to find the beta of your company.
(b) Using beta from (a), what is your company’s cost of equity, according to the CAPM?
• Risk-free rate: Use the 1-year U.S. treasury yield from
https://www.treasury.gov/resource-center/data-chart-center/interestrates/Pages/TextView.aspx?data=yield.
• historical market risk premium: 7.5% per annum.
(c) With your beta from (a) as equity beta (βL), compute the asset beta (βU) of the firm. Compute
the current debt-to-equity ratio with information from Part I.
(d) Create a table of the company’s levered beta (β𝐿𝐿) at different levels of debt ratio from 0%to
99% at 10%p intervals.
(e) Using different levels of betas from (d), continue to create a table of cost of equity for each
level of debt ratio, according to the CAPM. Apply the most recent effective corporate tax of
your firm found on the Internet.
2. (3 pts; Cost of debt) Cost of Debt; Ex-ante cost of debt
(a) Compute your company’s cost of debt based on the “synthetic” ratings and default spreads
from the tables available from Ch 11. Use the 1-year U.S. treasury bond yield as the long-term
bond yield. (https://www.treasury.gov/resource-center/data-chart-center/interestrates/Pages/TextView.aspx?data=yield) Use the relevant information on financial statements
from Part I.
(b) Create a table of cost of debt for each level of debt ratio based from 0%to 99% at 10%p
intervals. Make sure you turn on iterative calculation and run iteration. (HINT: Your initial value
for the ex-ante cost of debt should be equal to the minimum cost of debt found on the
synthetic rating table.)
3. (3 pts; WACC & optimal WACC) Suppose your company tries to figure out the weighted-average cost
of capital.
(a) Create a table of WACC for each level of debt ratio.
(b) Based on (a), what is your firm’s lowest WACC? What is your firm’s optimal or target debt ratio?
4. (3 pts; Free cash flows) Suppose your company is contemplating a new investment project based on
the following information. (Hint: 2018 is “Year 0.”)
o
o
o
o
o
o
The project requires purchase of a new equipment in 2018 (=Year 0) worth the larger between 200% of the
2017 capital expenditure and 10% of the 2017 total assets. Equipment purchase price is the larger between
(1) 200%* the capital expenditure in 2017 and (2) 10%*the total assets in 2017. See below for the formula.
Equipment purchase price = max(200%*2017 capex, 10%*2017 total assets)
With this new project, in 2019 (=Year 1), the project will generate as much as 25% of the 2017 revenue
(=25%*sales of 2017). From 2020 to Year 2028, the revenue from this project will grow by 1% every year (=
(100%+1%)*sales of previous year).
Assume your COGS will be 70% of revenue in the same year, and all other operating costs (excluding
depreciation) will remain constant at 5% of the new equipment purchase price.
Assume the equipment will be depreciated based on a straight-line method for 12 years, 2 years more than
the project years. (Hint: This means the salvage book value is 2/12 of the original purchase price.)
At the end of project years, the salvage market value of the equipment will be 30% of the original purchase
value.
Your investment requires net working capital investment of 5% of the revenue in the subsequent years, and
(a) Compute the free cash flows from 2018 to 2028. Continue to assume the same effective
corporate tax bracket and no loss carryover for tax purposes, i.e. a negative taxable income will
result in a corporate tax of $0.
(b) Using the WACC from 3-(b), compute the NPV, IRR, and PI of this project. Do you think the
company should choose to invest in this project? Why or why not?
FACEBOOK INC A (FB) Statement of CASH FLOW
Fiscal year ends in December. USD in millions except per share data.
Cash Flows From Operating Activities
Net income
Depreciation & amortization
Deferred income taxes
Stock based compensation
Change in working capital
Accounts receivable
Prepaid expenses
Accounts payable
Accrued liabilities
Other working capital
Other non-cash items
Net cash provided by operating activities
Cash Flows From Investing Activities
Investments in property, plant, and equipment
Acquisitions, net
Purchases of investments
Sales/Maturities of investments
Other investing activities
Net cash used for investing activities
Cash Flows From Financing Activities
Debt repayment
Common stock repurchased
Excess tax benefit from stock based compensation
Other financing activities
Net cash provided by (used for) financing activities
Effect of exchange rate changes
Net change in cash
Cash at beginning of period
Cash at end of period
Free Cash Flow
Operating cash flow
Capital expenditure
Free cash flow
2015-12
3,688
1,945
(795)
2,960
784
(973)
(144)
18
513
1,370
17
8,599
(2,523)
(313)
(15,938)
9,238
102
(9,434)
(119)
1,701
1,582
(155)
592
4,315
4,907
8,599
(2,523)
6,076
2016-12
2017-12
2018
10,217
2,342
(457)
3,218
758
(1,489)
(159)
14
1,014
1,378
30
16,108
15,934
3,025
(377)
3,723
1,887
(1,609)
(192)
43
309
3,336
24
24,216
15,934
3,025
(377)
3,723
1,887
(1,969.48)
(192)
51.41
309
3,336
24
25,751
(4,491)
(123)
(22,341)
15,155
61
(11,739)
(6,733)
(122)
(25,682)
12,432
67
(20,038)
(6,733)
(122)
(25,682)
12,432
67
(20,038)
(1,976)
(1,976)
2
(310)
(63)
3,996
4,907
8,903
(3,259)
(5,235)
233
(824)
8,903
8,079
(3,259)
(5,235)
233
(824)
8,903
8,079
16,108
(4,491)
11,617
24,216
(6,733)
17,483
24,216
(6,733)
17,483
(312)
FACEBOOK INC A (FB) CashFlowFlag INCOME STATEMENT
Fiscal year ends in December. USD in millions except per share data.
Revenue
Cost of revenue
Gross profit
Operating expenses
Research and development
Sales, General and administrative
Total operating expenses
Operating income
Interest Expense
Other income (expense)
Income before taxes
Provision for income taxes
Net income from continuing operations
Net income
Preferred dividend
Net income available to common shareholders
Earnings per share
Basic
Diluted
Weighted average shares outstanding
Basic
Diluted
EBITDA
2015
17,928
2,867
15,061
2016
27,638
3,789
23,849
4,816
4,020
8,836
6,225
23
(8)
6,194
2,506
3,688
3,688
19
3,669
5,919
5,503
11,422
12,427
10
101
12,518
2,301
10,217
10,217
29
10,188
1
1
4
3
2,803
2,853
8,162
2,863
2,925
14,870
Pro-forma Income Statement
2017
40,653
5,454
35,199
7,754
7,242
14,996
20,203
6
397
20,594
4,660
15,934
15,934
14
15,920
5
5
2,901
2,956
23,625
2018
51,465
6641.322087
44,823
7,754
7,242
14,996
29,827
6
397
29,424
4,660
24,764
15,934
14
2015-12
Diluted earning per share
Average shares outstanding
Close prices
Annual effective tax
1.29
2.85
104.66
40.46
2016-12 2017-12
3.49
5.39
2.93
2.96
115.05
176.46
18.38
22.63
FACEBOOK INC A (FB) CashFlowFlag BALANCE SHEET
Fiscal year ends in December. USD in millions except per share data.
Assets
Current assets
Cash
Cash and cash equivalents
Short-term investments
Total cash
Receivables
Prepaid expenses
Total current assets
Non-current assets
Property, plant and equipment
Gross property, plant and equipment
Accumulated Depreciation
Net property, plant and equipment
Goodwill
Intangible assets
Other long-term assets
Total non-current assets
Total assets
Liabilities and stockholders' equity
Liabilities
Current liabilities
Short-term debt
Capital leases
Accounts payable
Taxes payable
Accrued liabilities
Deferred revenues
Other current liabilities
Total current liabilities
Non-current liabilities
Capital leases
Deferred taxes liabilities
Other long-term liabilities
Total non-current liabilities
Total liabilities
Stockholders' equity
Additional paid-in capital
Retained earnings
Accumulated other comprehensive income
Total stockholders' equity
Total liabilities and stockholders' equity
2015-12
2016-12
4,907
13,527
18,434
2,559
659
21,652
8,903
20,546
29,449
3,993
959
34,401
7,819
(2,132)
5,687
18,026
3,246
796
27,755
49,407
11,803
(3,212)
8,591
18,122
2,535
1,312
30,560
64,961
201
7
196
302
192
56
1,273
1,925
331
90
2,152
2,875
107
163
2,994
3,264
5,189
2,892
2,892
5,767
34,886
9,787
(455)
44,218
49,407
38,227
21,670
(703)
59,194
64,961
Pro-forma balance sheet
2017-12
2018
8,079
33,632
41,711
5,832
1,020
48,563
18,337
(4,616)
13,721
18,221
1,884
2,135
35,961
84,524
8,079
33,632
41,711
5,832
1,020
48,563
19,010.30
(4,683.33)
14,326.97
18,221
1,884
2,135
35,961
86,854.94
380
230
685
98
2,367
3,760
380
230
685
98
2,367
3,760
6,417
6,417
10,177
6,417
6,417
10,177
40,584
33,990
(227)
74,347
84,524
40,584
33,990
(227)
74,347
84,524
Liquidity Ratios
Current Ratio
Quick Ratio
Financial Leverage ratios
Total Debt Ratio
Dept-equity Ratio
Effifiency Ratios
Inventory turnover
Receivable turnover
Total Assets turnover
Coverage Ratios
Interest Coverage Ratio
Profitability ratios
Gross Profit Margin
Net Profit Margin
Return on Assets
Return on Equity
Valuation Ratios
Price-Earnings Ratio
Market-to-Book Ratio
2015
11.25
10.91
2015
0.11
0.12
2015
0.00
-4.13
2.76
2015
269.30
2015
84%
21%
0.07
0.08
2015
81.13
0.02
2016
11.97
11.63
2016
0.09
0.10
2016
0.00
-3.70
2.35
2016
1251.80
2016
86%
37%
0.16
0.17
2016
32.97
0.05
2017
12.92
12.64
2017
0.12
0.14
2017
0.00
-4.50
2.08
2017
3432.33
2017
87%
39%
0.19
0.21
2017
32.74
0.04
REMARK
EXCELLENT
EXCELLENT
EXCELLENT
EXCELLENT
EXCELLENT
BAD
BAD
EXCELLENT
EXCELLENT
EXCELLENT
EXCELLENT
EXCELLENT
BAD
OKAY
Purchase answer to see full
attachment