Project – Optimal WACC & capital budgeting" EXCEL" Facebook Inc.

User Generated

shgbba

Business Finance

Description

 

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

See attached:)

PERMNO
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407

Names
Date
20130131
20130228
20130328
20130430
20130531
20130628
20130731
20130830
20130930
20131031
20131129
20131231
20140131
20140228
20140331
20140430
20140530
20140630
20140731
20140829
20140930
20141031
20141128
20141231
20150130
20150227
20150331
20150430
20150529
20150630
20150731
20150831
20150930
20151030
20151130
20151231
20160129
20160229
20160331
20160429
20160531
20160630
20160729

Ticker
Symbol
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB
FB

Returns
0.163837
-0.120429
-0.061284
0.085575
-0.123195
0.02185
0.4791
0.12212
0.2164
-0.000498
-0.063639
0.162497
0.144943
0.094135
-0.12007
-0.007636
0.058883
0.063033
0.079655
0.029869
0.056402
-0.05124
0.036138
0.004118
-0.027044
0.040311
0.041091
-0.041902
0.005332
0.083028
0.096135
-0.048718
0.005256
0.13426
0.022261
0.004029
0.072138
-0.047144
0.067153
0.0305
0.010461
-0.038128
0.084529

Value-Weighted Return-incl.
dividends
0.054145
0.00834
0.035294
0.014947
0.019074
-0.015036
0.052717
-0.025727
0.03747
0.039895
0.024966
0.02614
-0.029987
0.046186
0.004501
0.00167
0.020223
0.027961
-0.020536
0.040206
-0.025132
0.021198
0.021159
-0.003623
-0.0272
0.056017
-0.010454
0.008716
0.010344
-0.019262
0.012113
-0.060046
-0.033769
0.074021
0.002448
-0.022287
-0.057094
0.000685
0.070518
0.011814
0.014316
0.003111
0.038775

13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13407
13...


Anonymous
Nice! Really impressed with the quality.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags