MBA 7294

BALANCE SHEET

(USD in millions)

Year End 2017

Assets Liabilities

Current assets Current liabilities

Short-term debt $ 3,897.00

Cash and cash equivalents $ 6,877.00 Non-current liabilities

Short-term investments $ 53,539.00 Long-term debt $ 21,457.00

Total current assets $ 60,416.00 Total liabilities $ 25,354.00

Non-current assets

Stockholders' equity

Gross property, plant and equipment $ 12,427.00 Common stock $ 22,340.00

Accumulated Depreciation $ (9,095.00) Retained earnings $ 16,054.00

Total non-current assets $ 3,332.00 Total stockholders' equity $ 38,394.00

Total assets $ 63,748.00 Total liabilities and stockholders' equity $ 63,748.00

EXHIBIT 1

Income Statements

(USD in millions) 2013 2014 2015 2016 2017

Revenue $ 43,218.00 $ 46,061.00 $ 48,607.00 $ 47,142.00 $ 49,161.00

Cost of revenue $(16,682.00) $(17,852.00) $(19,167.00) $(19,373.00) $(19,480.00)

Gross profit

$ 26,536.00

$ 28,209.00

$ 29,440.00

$ 27,769.00

$ 29,681.00

Research and development $ (5,823.00) $ (5,488.00) $ (5,942.00) $ (6,294.00) $ (6,207.00)

Sales, General and administrative $(11,720.00) $(11,969.00) $(11,802.00) $(11,437.00) $(11,861.00)

Restructuring, merger and acquisition $ (799.00) $ (304.00) $ (105.00) $ (418.00) $ (484.00)

Operating income $ 8,194.00 $ 10,448.00 $ 11,591.00 $ 9,620.00 $ 11,129.00

Interest Expense $ (628.00) $ (596.00) $ (583.00) $ (564.00) $ (566.00)

Income before taxes $ 7,566.00 $ 9,852.00 $ 11,008.00 $ 9,056.00 $ 10,563.00

Provision for income taxes $ (1,335.00) $ (2,118.00) $ (1,244.00) $ (1,862.00) $ (2,220.00)

Net income $ 6,231.00 $ 7,734.00 $ 9,764.00 $ 7,194.00 $ 8,343.00

EXHIBIT 2

Final Project

MBA 7294 1. Calculate the Geometric average rate of growth in Revenue for company.

2. Create a series of common sized income statements for all prior years.

3. Develop pro forma income statements for the next 5 years using the Revenue data from exhibit 3 & 4.

4. Estimate Free Cash Flow for the next 5 years assuming depreciation of 5% of sales and capital expenditures of 4% of Sales.

5. Construct a pie chart of the firm’s capital structure (market values) and calculate the Weighted average cost of capital assuming the following data:

a. Market Value of Equity: 108 B

b. Beta: 1.2

c. Expected Return on the market: 9%

d. Risk Free rate: 1.8%

e. Assume a marginal tax rate of 33%

f. Use the data from exhibit 4 for the firm’s debt

6. Calculate the value of the firm using the following assumptions:

a. Terminal growth rate: 3%

b. Terminal weighted average cost of capital: 11%

7. Construct Pro forma balance sheets and statements of cash flow for the next 5 years.

8. The firm is considering issuing a 10 year 3 Billion note denominated in Swiss Francs (CHF) in 2019. The exchange rate is $1 = 1CHF. The proceeds will be

used to build a factory in the U.S. The coupon rate is expected to be 1.5%. The factory will produce a new product to be sold in the U.S. Product D is

expected to add $1 billion in sales per year starting in 2020 with a 40% cost of goods sold. Additionally, selling, general, and administrative costs are

expected to increase by $90 million per year. Please illustrate the impact to firm’s overall value as well as the change in the capital structure and cost of

capital.

The company offers 3 products sold in three markets. Please use the data below to develop the sales forecast for the pro forma income statements.

Sales Forecast Assumptions

Product B

FY 2018 FY 2019 FY 2020 FY 2021 FY 2022

U.K. Division (Pounds)

Units 315 318 321 325 328

Exchange Rate Assumptions FY 2018 FY 2019 FY 2020 FY 2021 FY 2022 Price per Unit £ 12.67 £ 12.79 £ 12.92 £ 13.05 £ 13.18

Pound (in dollars per Pound) $ 1.40 $ 1.42 $ 1.44 $ 1.46 $ 1.48

Yen (in Yen per dollar) ¥ 112.00 ¥ 114.00 ¥ 116.00 ¥ 118.00 ¥ 120.00 Japanese Division (Yen)

Units 305 308 311 314 317

Price per Unit ¥ 2,128.00 ¥ 2,130.00 ¥ 2,132.00 ¥ 2,133.00 ¥ 2,134.00

U.S. Division (Dollar)

Units 210 213 216 220 223

(Units in Millions) Price per Unit $ 19.00 $ 19.19 $ 19.38 $ 19.58 $ 19.77

Product A

Product C

FY 2018 FY 2019 FY 2020 FY 2021 FY 2022 FY 2018 FY 2019 FY 2020 FY 2021 FY 2022

U.K. Division (Pounds) U.K. Division (Pounds)

Units 510 528 546 565 585 Units 140 146 151 157 164

Price per Unit £ 6.65 £ 14.47 £ 14.76 £ 15.05 £ 15.35 Price per Unit £ 33.33 £ 33.99 £ 34.67 £ 35.37 £ 36.07

Japanese Division (Yen) Japanese Division (Yen)

Units 400 404 408 412 416 Units 95 98 102 105 109

Price per Unit ¥ 1,117.76 ¥ 1,161.61 ¥ 1,165.20 ¥ 1,189.67 ¥ 1,214.65 Price per Unit ¥ 5,598.88 ¥ 5,654.87 ¥ 5,711.42 ¥ 5,768.53 ¥ 5,826.22

U.S. Division (Dollar) U.S. Division (Dollar)

Units 620 639 658 677 698 Units 205 215 226 237 249

Price per Unit $ 9.98 $ 10.19 $ 10.40 $ 10.62 $ 10.85 Price per Unit $ 49.99 $ 49.99 $ 49.99 $ 51.00 $ 52.00

EXHIBIT 3

To construct the pro forma income statements, please us the following data.

The products are all produced in the United States.

Please use the following data below to calculate the weighted average cost of goods sold.

Product A target: 38% (as a percent of sales)

Product B target: 41% (as a percent of sales)

Product C target: 39% (as a percent of sales)

Please assume the cost (as a percent of sales) for the following items will remain the same as year 2017 :

Research and development

Sales, General and administrative

Restructuring, merger and acquisition

Please assume an average tax rate of 21%.

The Short – Term debt has a cost of .5%.

The company has the following long term debt outstanding.

Debt

Currency Maturity Amount (USD) Face Value (local fx) Yield to Maturity Coupon

GBP 2/20/2020 $ 1,400.00 £ 1,000.00 2.010% 2.010%

USD 5/1/2025 $ 5,000.00 N/A 1.691% 1.691%

JPY 7/1/2026 $ 4,000.00 ¥ 448,000.00 1.210% 1.210%

USD 1/1/2027 $ 4,057.00 N/A 3.246% 3.246%

GBP 2/20/2028 $ 7,000.00 £ 5,000.00 3.625% 3.625%

EXHIBIT 4