Final Exam
Be careful with numbers
Do not make any mistake please
BALANCE SHEET
(USD in millions)
YearEnd 2019
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
Income Statements
(USD in millions) 2015 2016 2017 2018 2019
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 2020 FY 2021 FY 2022 FY 2023 FY 2024
U.K. Division (Pounds)
Units 315 318 321 325 328
Exchange Rate Assumptions FY 2020 FY 2021 FY 20222 FY 2023 FY 2024 Price perUnit £ 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 perdollar) ¥ 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 perUnit $ 19.00 $ 19.19 $ 19.38 $ 19.58 $ 19.77
Product A Product C
FY 2020 FY 2021 FY 202 FY 2023 FY 2024 FY 2020 FY 2021 FY 2022 FY 2021 FY 2024
U.K. Division (Pounds) U.K. Division (Pounds)
Units 510 528 546 565 585 Units 140 146 151 157 164
Price perUnit £ 6.65 £ 14.47 £ 14.76 £ 15.05 £ 15.35 Price perUnit £ 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 perUnit $ 9.98 $ 10.19 $ 10.40 $ 10.62 $ 10.85 Price perUnit $ 49.99 $ 49.99 $ 49.99 $ 51.00 $ 52.00
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 2019 :
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/2023 $ 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
- EXHIBIT 1
EXHIBIT 3