Apple Inc Optimal WACC and Capital Budgeting Project Excel assignment details are attached. The company I chose is APPLE INC. attached is also the first pa

Apple Inc Optimal WACC and Capital Budgeting Project Excel assignment details are attached. The company I chose is APPLE INC. attached is also the first part of the project. You need to continue the project in part 2 in a new excel file. please message me for any questions 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 levered or equity beta (?L), compute the unlevered or 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 12. Use the 10-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) Compute the project’s weighted-average cost of capital (WACC) using cost of equity obtained
from Question 1-(c) and cost of debt obtained from Question 2-(a). Use the same current
structure (i.e., debt ratio) as in Question 1 and 2. Suppose your company is subject to a 35%
corporate tax.
(b) Create a table of WACC for each level of debt ratio.
(c) Draw a chart of the relation between WACC and debt ratio.
(d) Based on (b) and (c), 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 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
the full amount will be released at the end of the project.
(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.
Using the WACC from 3-(a), 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?
APPLE INC (AAPL) INCOME STATEMENT
Fiscal year ends in September. USD in millions except per share data.
2015-09 2016-09
Revenue
233,715 215,639
Cost of revenue
140,089 131,376
Gross profit
93,626
84,263
Operating expenses
Research and development
8,067
10,045
Sales, General and administrative
14,329
14,194
Total operating expenses
22,396
24,239
Operating income
71,230
60,024
Interest Expense
733
1,456
Other income (expense)
2,018
2,804
Income before taxes
72,515
61,372
Provision for income taxes
19,121
15,685
Net income from continuing operations
53,394
45,687
Net income
53,394
45,687
Net income available to common shareholders
53,394
45,687
Earnings per share
Basic
9.28
8.35
Diluted
9.22
8.31
Weighted average shares outstanding
Basic
5,753
5,471
Diluted
5,793
5,500
EBITDA
84,505
73,333
Source:
Apple Inc. (2019). Apple Inc (AAPL) : Income Statement. Retrieved from
https://financials.morningstar.com/income-statement/is.html?t=0P000000GY&culture=en&ops=clear
APPLE INC (AAPL)BALANCE SHEET
Fiscal year ends in September. USD in millions except per share data.
Assets
Current assets
Cash
Cash and cash equivalents
Short-term investments
Total cash
Receivables
Inventories
Deferred income taxes
Other current assets
2015-09
21,120
20,481
41,601
16,849
2,349
5,546
23,033
2016-09
20,484
46,671
67,155
15,754
2,132
21,828
Total current assets
Non-current assets
Property, plant and equipment
Gross property, plant and equipment
Accumulated Depreciation
Net property, plant and equipment
Equity and other investments
Goodwill
Intangible assets
Other long-term assets
Total non-current assets
Total assets
Liabilities and stockholders’ equity
Liabilities
Current liabilities
Short-term debt
Accounts payable
Taxes payable
Accrued liabilities
Deferred revenues
Other current liabilities
Total current liabilities
Non-current liabilities
Long-term debt
Deferred taxes liabilities
Deferred revenues
Other long-term liabilities
Total non-current liabilities
Total liabilities
Stockholders’ equity
Common stock
Retained earnings
Accumulated other comprehensive income
Total stockholders’ equity
Total liabilities and stockholders’ equity
89,378
106,869
49,257
61,245
(26,786) (34,235)
22,471
27,010
164,065 170,430
5,116
5,414
3,893
3,206
5,556
8,757
201,101 214,817
290,479 321,686
10,999
35,490
11,605
37,294
25,181
8,940
22,027
8,080
80,610
79,006
53,463
24,062
3,624
9,365
90,514
171,124
75,427
26,019
2,930
10,055
114,431
193,437
27,416
31,251
92,284
96,364
(345)
634
119,355 128,249
290,479 321,686
Source:
Apple Inc. (2019). Apple Inc (AAPL) :Balance Sheet. Retrieved from
http://financials.morningstar.com/balance-sheet/bs.html?t=AAPL®ion=usa&culture=en-US
APPLE INC (AAPL) Statement of CASH FLOW
Fiscal year ends in September. 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
Inventory
Accounts payable
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
Purchases of intangibles
Other investing activities
Net cash used for investing activities
Cash Flows From Financing Activities
Debt issued
Debt repayment
Common stock issued
Common stock repurchased
Dividend paid
Other financing activities
Net cash provided by (used for) financing activities
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-09
2016-09
53,394
11,257
1,382
3,586
11,647
611
(238)
5,400
5,874
45,687
10,505
4,938
4,210
484
1,095
217
1,791
(2,619)
81,266
65,824
(11,247) (12,734)
(343)
(297)
(166,402) (143,816)
121,985 111,794
(241)
(814)
(26)
(110)
(56,274) (45,977)
27,114
543
(35,253)
(11,561)
1,441
(17,716)
7,276
13,844
21,120
24,954
(2,500)
495
(29,722)
(12,150)
(1,560)
(20,483)
(636)
21,120
20,484
81,266
(11,488)
69,778
65,824
(13,548)
52,276
Source:
Apple Inc. (2019). Apple Inc (AAPL) : Cash flow Statement. Retrieved from
http://financials.morningstar.com/cash-flow/cf.html?t=AAPL®ion=usa&culture=en-US
2017-09
229,234
141,048
88,186
11,581
15,261
26,842
61,344
2,323
5,068
64,089
15,738
48,351
48,351
48,351
9.27
9.21
5,217
5,252
76,569
Y&culture=en&ops=clear
2017-09
20,289
53,892
74,181
17,874
4,855
31,735
128,645
75,076
(41,293)
33,783
194,714
5,717
2,298
10,162
246,674
375,319
18,473
49,049
25,744
7,548
100,814
97,207
31,504
2,836
8,911
140,458
241,272
35,867
98,330
(150)
134,047
375,319
a&culture=en-US
2017-09
48,351
10,157
5,966
4,840
(5,550)
(2,093)
(2,723)
9,618
(10,352)
(166)
63,598
(12,451)
(329)
(159,881)
126,339
(344)
220
(46,446)
lture=en-US
28,662
(3,500)
555
(32,900)
(12,769)
2,605
(17,347)
(195)
20,484
20,289
63,598
(12,795)
50,803
a)Diluted Earnings per share
b)average shares outstanding
(c) close prices
d) Annual effective tax rates for years 2015 – 2017 (Billion)
2015
$9.22
5.75
$105.26
26.4%
2016
$8.31
5.50
$115.82
25.6%
2017
$9.21
5.25
$169.23
24.6%
Fiscal year ends in September. USD in millions
except per share data.
Revenue
Cost of revenue
Gross profit
Operating income
Interest Expense
Income before taxes
Net income available to common shareholders
Earnings per share
Basic
Diluted
Weighted average shares outstanding
Basic
Diluted
EBITDA
Receivables
Inventories
Total current assets
Total non-current assets
Total assets
Total current liabilities
Long-term debt
Total non-current liabilities
Total liabilities
Total stockholders’ equity
2015-09
2016-09
2017-09
233,715
140,089
93,626
71,230
733
72,515
53,394
215,639
131,376
84,263
60,024
1,456
61,372
45,687
229,234
141,048
88,186
61,344
2,323
64,089
48,351
$
$
9.28 $
9.22 $
8.35 $
8.31 $
9.27
9.21
5,753
5,793
84,505
5,471
5,500
73,333
5,217
5,252
76,569
16,849
2,349
89,378
201,101
290,479
80,610
53,463
90,514
171,124
119,355
15,754
2,132
106,869
214,817
321,686
79,006
75,427
114,431
193,437
128,249
17,874
4,855
128,645
246,674
375,319
100,814
97,207
140,458
241,272
134,047
Liquidity ratios
Current ratio
Quick ratio
Formula
Current assets
Current liabilities
2015
2016
2017
89,378 106,869 128,645
80,610 79,006 100,814
Current assets-Inventories
Current liabilities
72,529
80,610
91,115 110,771
79,006 100,814
Financial Leverage Ratios
Total Debt Ratio
Total liabilities
Total assets
171,124 193,437 241,272
290,479 321,686 375,319
Debt-equity Ratio
Non-current assets
Shareholders’ equity
90,514 114,431 140,458
119,355 128,249 134,047
Inventory Turnover
Cost of goods sold
Inventories
140,089 131,376 141,048
2,349
2,132
4,855
Receivables Turnover
Sales
Trade receivables
233,715 215,639 229,234
16,849 15,754 17,874
Total Asset Turnover
Sales
Total assets
233,715 215,639 229,234
290,479 321,686 375,319
Efficiency Ratios
Coverage Ratios
Interest coverage ratio
Operating profit
Interest charges
72,515
733
61,372
1,456
64,089
2,323
Profitability Ratios
Gross profit margin
Gross profit
Sales
93,626 84,263 88,186
233,715 215,639 229,234
Net profit margin
Net Income
Sales
53,394 45,687 48,351
233,715 215,639 229,234
Return on Assets
Net Income
Total assets
53,394 45,687 48,351
290,479 321,686 375,319
Return on Equity
Net Income
Shareholders’ Equity
53,394 45,687 48,351
119,355 128,249 134,047
Price per share
$105.26 $115.82 $169.23
Valuation Ratios
Price-Earnings ratio
Price-Earnings ratio
Earnings per share (EPS)
$9.22
$8.31
$9.21
Book value per share
Total assets ($Million)
Number of shares outstanding (Billions)
290,479 321,686 375,319
5.75
5.50
5.25
Market-to-book ratio
Market price per share
Book value per share
$105.26 $115.82 $169.23
$50.49 $58.49 $71.49
2015
Ratios
2016
2017
Comment
1.11
1.35
1.28
Ok
0.90
1.15
1.10
Ok
0.59
0.60
0.64
Bad
0.76
0.89
1.05
Bad
59.64
61.62
29.05
Excellent
13.87
13.69
12.82
Excellent
0.80
0.67
0.61
Bad
98.93
42.15
27.59
Bad
40.06%
39.08%
38.47%
Bad
22.85%
21.19%
21.09%
Bad
18.38%
14.20%
12.88%
Bad
44.74%
35.62%
36.07%
OK
11.42
13.94
18.37
Excellent
11.42
13.94
18.37
50.49
58.49
71.49
2.08
1.98
2.37
Excellent
Excellent
APPLE INC PRO-FORMA INCOME STATEMENT
Fiscal year ends in September. USD in millions except per share data.
2015
2016
Revenue
233,715 215,639
Cost of revenue
140,089 131,376
Gross profit
93,626
84,263
Operating expenses
Research and development
8,067
10,045
Sales, General and administrative
14,329
14,194
Total operating expenses
22,396
24,239
Operating income
71,230
60,024
Interest Expense
733
1,456
Other income (expense)
2,018
2,804
Income before taxes
72,515
61,372
Provision for income taxes
19,121
15,685
Net income
53,394
45,687
Tax rate
Projected sales increase
23%
10%
2017
229,234
141,048
88,186
2018
230,677
140,228
90,449
11,581
15,261
26,842
61,344
2,323
5,068
64,089
15,738
48,351
10,094
14,884
24,978
65,471
2,323
5,068
63,160
14,527
48,633
Sales Trend
Capital expenditure (2017)
New Equipment (10%) of capital expenditure
Depreciation
APPLE INC (AAPL) PRO-FORMA BALANCE SHEET
Fiscal year ends in September. USD in millions
Assets
Current assets
Cash
Cash and cash equivalents
Short-term investments
Total cash
Receivables
Inventories
Deferred income taxes
Other current assets
Total current assets
Non-current assets
Property, plant and equipment
Gross property, plant and equipment
Accumulated Depreciation
Net property, plant and equipment
Equity and other investments
Goodwill
Intangible assets
Other long-term assets
Total non-current assets
Total assets
Liabilities and stockholders’ equity
Liabilities
Current liabilities
Short-term debt
Accounts payable
Taxes payable
Accrued liabilities
Deferred revenues
Other current liabilities
Total current liabilities
Non-current liabilities
Long-term debt
Deferred taxes liabilities
1.95%
12,795
1,280
128
2018
20,289
53,892
74,181
18,223
4,950
31,735
129,088
76,356
(41,165)
35,190
194,714
5,717
2,298
10,162
248,081
377,170
18,473
50,005
25,744
7,548
101,770
97,207
31,504
Deferred revenues
Other long-term liabilities
Total non-current liabilities
Total liabilities
Stockholders’ equity
Common stock
Retained earnings
Accumulated other comprehensive income
Total stockholders’ equity
Total liabilities and stockholders’ equity
2,836
8,911
140,458
242,228
35,867
99,225
(150)
134,942
377,170
Apple Inc. (2019). Apple Inc (AAPL) : Financials. Retrieved from
https://www.morningstar.com/stocks/xnas/aapl/quote.html
Apple Inc. (2019). Apple Inc (AAPL) :Balance Sheet. Retrieved from
http://financials.morningstar.com/balance-sheet/bs.html?t=AAPL®ion=usa&culture=en-US
Apple Inc. (2019). Apple Inc (AAPL) : Income Statement. Retrieved from
https://financials.morningstar.com/income-statement/is.html?t=0P000000GY&culture=en&ops=clear
Apple Inc. (2019). Apple Inc (AAPL) : Cash flow Statement. Retrieved from
http://financials.morningstar.com/cash-flow/cf.html?t=AAPL®ion=usa&culture=en-US
Apple Inc. (2019). Apple Inc: Historical share price. Retrieved from
https://www.morningstar.com/stocks/xnas/aapl/quote.html
Apple Inc. (2017). Annual report for the fiscal year ended September 30, 2017 . Retrieved from
http://files.shareholder.com/downloads/AAPL/6368681069x0x962680/D18FAEFF-460A-4168-993D-A60
Marketwatch.com. (2015). Apple Inc: Historical quotes. Retrieved from
https://www.marketwatch.com/investing/stock/AAPL/historical?siteid=mktw&date=2015-2017&x=13&y=5
Marketwatch.com. (2016). Apple Inc: Historical quotes. Retrieved from
https://www.marketwatch.com/investing/stock/AAPL/historical?siteid=mktw&date=2015-2017&x=13&y=5
Marketwatch.com. (2017). Apple Inc: Historical quotes. Retrieved from
https://www.marketwatch.com/investing/stock/AAPL/historical?siteid=mktw&date=2015-2017&x=13&y=5
AEFF-460A-4168-993D-A60CBA8ED209/_10-K_2017_As-Filed_.pdf

Purchase answer to see full
attachment