Hi there, I need help to answer the following question.

Please provide an excel sheet with the formulas and calculationsas well

Thank you

BF013 Financial Planning Assessment Brief A2 - Term 2, 2015 Question 1. IPO Costs When Microsoft went public, the company sold 2 million new shares (the primary issue). In addition, existing shareholders sold .8 million shares (the secondary issue) and kept 21.1 million shares. The new shares were offered to the public at $21, and the underwriters received a spread of $1.31 a share. At the end of the first day's trading the market price was $35 a share. a. How much money did the company receive before paying its portion of the direct costs? b. How much did the existing shareholders receive from the sale of their shares? c. If the issue had been sold to the underwriters for $30 a share, how many shares would the company have needed to sell to raise the same amount of cash? d. How much better off would the existing shareholders have been? Question 2. Issue Methods. Young Corporation stock currently sells for $30 per share. There are 1 million shares currently outstanding. The company announces plans to raise $3 million by offering shares to the public at a price of $30 per share. a. If the underwriting spread is 6%, how many shares will the company need to issue in order to be left with net proceeds of $3 million? b. If other administrative costs are $60,000, what is the dollar value of the total direct costs of the issue? c. If the share price falls by 3% at the announcement of the plans to proceed with a seasoned offering, what is the dollar cost of the announcement effect? Question 3. Rights Issues. Associated Breweries is planning to market unleaded beer. To finance the venture, it proposes to make a rights issue with a subscription price of $10. One new share can be purchased for each two shares held. The company currently has outstanding 100,000 shares priced at $40 a share. Assuming that the new money is invested to earn a fair return, give values for the following: a. b. c. d. e. Number of new shares Amount of new investment Total value of company after the issue Total number of shares after the issue Share price after the issue 1 Question 4. Leverage and the Cost of Capital. Astromet is financed entirely by common stock and has a beta of 1.0. The firm pays no taxes. The stock has a price-earnings multiple of 10 and is priced to offer a 10% expected return. The company decides to repurchase half the common stock and substitute an equal value of debt. Assume that the debt yields a risk-free 5%. Calculate the following: a. The beta of the common stock after the refinancing b. The required return and risk premium on the common stock before the refinancing c. The required return and risk premium on the common stock after the refinancing d. The required return on the debt e. The required return on the company (i.e., stock and debt combined) after the refinancing Question 5. Taxes and the Cost of Capital. Here is Establishments Industries' market-value Balance sheet (Figures in $ millions): Net working capital Long-term assets Value of firm $ 550 2,150 $2,700 Debt Equity $ 800 1,900 $2,700 The debt is yielding 7%, and the cost of equity is 14%. The tax rate is 35%. Investors expect this level of debt to be permanent. a. What is Establishment's WACC? b. How would the market-value balance sheet change if Establishment retired all its debt? Question 6. Payout Policy. House of Haddock has 5,000 shares outstanding and the stock price is $100. The company is expected to pay a dividend of $20 per share next year, and thereafter the dividend is expected to grow indefinitely by 5% a year. The president, George Mullet, now makes a surprise announcement: He says that the company will henceforth distribute half the cash in the form of dividends and the remainder will be used to repurchase stock. a. What is the total value of the company before the announcement? b. What is the total value after the announcement? 2 c. What is the value of one share before the announcement? d. What is the new growth rate in the dividend stream? (Check your estimate of share value by discounting this stream of dividends per share.) Question 7. International Investment Decisions. Carpet Baggers Inc. is proposing to construct a new bagging plant in a country in Europe. The two prime candidates are Germany and Switzerland. The forecast cash flows from the proposed plants are as follows: The spot exchange rate for euros is USD1.3 = EUR1, while the rate for Swiss francs is CHF1.5 = USD1. The interest rate is 5% in the United States, 4% in Switzerland, and 6% in the euro countries. The financial manager has suggested that if the cash flows were stated in dollars, a return in excess of 10% would be acceptable. a. What is the dollar NPV of the German project? b. What is the dollar NPV of the Swiss project? c. Should the company go ahead with the German project, the Swiss project, or neither? Question 8. Exchange Rate Risk. General Gadget Corp. (GGC) is a U.S.-based multinational firm that makes electrical coconut scrapers. These gadgets are made only in the United States using local inputs. The scrapers are sold mainly to Asian and West Indian countries where coconuts are grown. a. If GGC sells scrapers in Trinidad, what is the currency risk faced by the firm? 3 b. In what currency should GGC borrow funds to pay for its investment in order to mitigate its foreign exchange exposure? c. Suppose that GGC begins manufacturing its products in Trinidad using local (Trinidadian) inputs and labor. How does this affect its exchange rate risk? 4