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 firms 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 firms 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 3 Billion note denominated in Swiss Francs (CHF) in 2017. 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 2018 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 firms overall value as well as the change in the capital structure and cost of capital.

MUST BE DONE IN EXCEL and show formulas!!!

BALALANCE SHEET			EXHIBIT 1
(USD in millions)
	Year End 2015
Assets			Liabilities
Current assets			Current liabilities
			Short-term debt	$3,897.00
Cash and cash equivalent	$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 liabilites	$25,351.00
Non-current assets
			Stockholder's Equity
Gross prop., plant & Eq	$12,427.00		Common stock	$22,340.00
Accumulated Deprec.	($9,095.00)		Retained earnings	$16,054.00
Total noncurrent assets	$3,332.00		Total stockholders' equity	$38,394.00
Total assets	$63,748.00		Total liability & stockholders' equity	$63,748.00

Income Statement		EXHIBIT 2
(USD in millions)	2011	2012	2013	2014	2015
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
R&D	($5,823.00)	($5,488.00)	(5,942.00)	($6,294.00)	($6,207.00)
Sales, general & administrative	($11,720.00)	($11,969.00)	($11,802.00)	($11,437.00)	($11,861.00)
Restructuring, merger & 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,230.00	$7,734.00	$9,764.00	$7,194.00	$8,343.00

Sales Forecast Assumptions
Exchange Rate Assumptions	FY2016	FY2017	FY2018	FY2019	FY2020
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
(Units in Millions)
Product A
	FY2016	FY2017	FY2018	FY2019	FY2020
U.K. Division (Pounds)
UNITS	510	528	546	565	585
Price per Unit	6.65	14.47	14.76	15.05	15.35
Japanese Division (Yen)
Units	400	404	408	412	416
Price per Unit	1,117.76	1,161.61	1,165.20	1,189.67	1,214.65
U.S. Division (Dollar)
Units	620	639	658	677	698
Price per Unit	$9.98	$10.19	$10.40	$10.62	$10.85
Product B
	FY2016	FY2017	FY2018	FY2019	FY2020
U.K. Division
Units	315	318	321	325	328
Price per Unit	12.67	12.76	12.92	13.05	13.18
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
Price per Unit	$19.00	$19.19	$19.38	$19.58	$19.77
Product C
	FY2016	FY2017	FY2018	FY2019	FY2020
U.K. Division (Pounds)
Units	140	146	151	157	164
Price per Unit	33.33	33.99	34.67	35.37	36.07
Japanese Division (Yen)
Units	95	98	102	105	109
Price per Unit	5,598.88	5,654.87	5,711.42	5,768.53	5,826.22
U.S. Division (Dollar)
Units	205	215	226	237	249
Price per Unit	$49.99	$49.99	$49.99	$51.00	$52.00