Hi, I need help with the attached finance problems. The PDF has the problems, and the excel file has the data.

Please read through this work before claiming the lesson, the last problem has a long equation and requires making an Excel sheet. Thank you!

You just started a new job as a Finance Manager at XYZ Corp. As you are starting to get acquainted with the company, you requested the Balance Sheet as of December 31, 2015, the 2015 Income Statement and a few other items that you deemed appropriate. You can nd all of those in the table below and in the Excel le attached. Question 1. As you are trying to get a better grasp of the growth potential of the company, you decide to look the the IGR and SGR numbers. (a) Compute the IGR and SGR. (b) What do each of those mean? One of your analysts mentioned that the Marketing and Sales departments are forecasting a growth in sales of 10%. Additionally, the same analyst informed you that the rm is at capacity right now and any further growth would require an investment in xed assets of $5 million dollars. Before your meeting with the new CEO, you decided to get a sense of what the impact of such growth would be: (c) What is the EFN for a growth of 10% in sales and the capacity assumption above? Also assume that the dividend payout ratio remains constant, and that cost of goods sold, current assets and accounts payable grow proportionally to sales. (d) What does the number you computed mean? (e) Suppose you decide to raise any needed capital through long term debt with no interest payments in the coming year. What is your new Debt-Equity Ratio? Is it larger or smaller than the Debt-Equity Ratio you currently have? Does it violate the IGR or SGR you computed before? Why is that? Question 2. Your new CEO has been very supportive of new projects and asked you to assess the merits of a particular 3-year project. The information you were able to collect is the following Today Sales Variable Costs Fixed Costs Changes in NWC Net Capital Spending Year 1 Year 2 Year 3 (550,000) (900,000) 1,000,000 200,000 100,000 - 1,750,000 300,000 100,000 - 2,250,000 400,000 100,000 100,000 - You were also informed that historically the projects of this particular division have been discounted with a required return of 20%. Specically, the CEO asked you for the following measures for this project. Assume a straight-line depreciation over the 3 years for the $900,000 assets you need to acquire for the project. (a) The Payback Period and the Discounted Payback Period. Do those give rise to dierent decisions? If so, why? 2 (b) Internal Rate of Return (c) NPV (d) What is the meaning of the the values you got in (b) and (c)? After you computed the measures above, you learned that the Marketing and Sales departments put out a memo saying that there is a possibility of new entrants in the market competing directly with the product to be produced as part of this project. The memo also points out that, in the event that new companies are able to enter the market, the expected sales will be 10%, 15%, and 20% lower in years 1, 2, and 3, respectively. (e) Update the measures computed above in (a)-(c). (f) Given your answers to (a)-(c) and (e), how would you recommend your CEO to proceed? What other information and analysis would you like to be able to collect and run before you need to make a denitive decision? Question 3. The quarterly investors call is approaching and you were asked to comment on the EPS and projected EPS based on the growth forecast of 10%. (a) Compute the EPS for the calendar year of 2015 (b) What is the projected EPS with the same assumptions as in Question 1? You are a bit skeptical of the projected 10% growth in sales and decided to look at a much less aggressive long-run growth scenario of 3.5% growth in sales. (c) What is the projected EPS for a 3.5% growth in sales? If the dividend payout ratio remains the same, how much is paid per share? As some external nancing will be needed to accommodate any growth, you started looking into raising debt and/or equity. Since your company would be mostly described as a smallcap US company, you looked at market data to help you determine your costs of equity and debt. (d) Using the information on slide 8 on the deck from Week 6, what should be the risk premium for appropriate market for your company? Assume the risk free is given by 1mo Treasury Bills. (e) Looking at historical stock market data, you determined that your beta is roughly 1.5 with respect to the market benchmark you used above to compute the risk premium. What should your cost of equity be? (f) In order to get a little more comfortable with the number computed above, you decided to look at the cost of equity using the dividend growth corresponding to the 3.5% growth scenario from (c). What is the cost of equity using this approach? In order to determine your cost of debt, you decided to look at your long term debt, which is structured as a single 20yr bond with semi-annual coupons, a coupon rate of 10%, and is currently trading at 105%. 3 (g) What is your cost of debt? (h) What is your after-tax cost of debt? Your CEO is interested in knowing what is the minimum return the company should generate to make sure investors are satised, but is not sure which number to focus on. (i) What measure should you propose and how would you explain it to your CEO? (j) What is the value for the proposed measure? Question 4. Finally, you were asked to address the capital structure of your company. Because you know that bankruptcy is always a threat, you commissioned a study to inform you how the cost of equity and debt should look for dierent debt-to-equity levels. The rm you used came up we the following estimates: RD = RD,0 + and (D/E)5 190 (D/E)2 25 represent the cost of debt and equity when the rm is unlevered (i.e. RE = RE,0 + max(RE,0 RD , 0) (D/E) (1 TC ) + where RD,0 and RE,0 when D = 0). (a) Determine RD,0 and RE,0 . You can use the Excel solver/goal seek functions or nd the value by trial-and-error1 . Note that from the questions above you know the cost of equity and debt for the current D/E value. (b) Create a table in Excel with the following columns: D/E, cost of debt, cost of equity, and cost of capital (c) Plot the last three columns as curves depending on D/E. (d) Estimate the optimal capital structure. (e) How would you advise the CEO to proceed if issuing and retiring debt and equity incurs in no costs? (f) Does your answer to the previous question change in the case when issuing and retiring debt and equity incurs in costs? 1 If you were unable to determine the cost of debt and/or equity in the previous question, assume that for a D/E of 1, the cost of debt is 10.53% and the cost of equity is 20.08% 4 Income Statement 2015 Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes (40%) Net income Dividends Add to RE $43,000,000 $30,000,000 $5,000,000 $2,000,000 $6,000,000 $2,000,000 $4,000,000 $1,600,000 $2,400,000 Taxes: Shares Outstanding 40% 1,000,000 $600,000 $1,800,000 Balance Sheet, Dec 31, 2015 Assets Current Assets Cash Accounts Receivable Inventory Total CA Fixed Assets Net PP&E $25,000,000 Total Assets $28,500,000 $500,000 $1,000,000 $2,000,000 $3,500,000 Liabilities & Owners' Equity Current Liabilities Accounts Payable $1,000,000 Notes Payable $3,000,000 Total CL $4,000,000 Long Term Debt $10,000,000 Owners' Equity Common Stock $6,500,000 Retained Earnings $8,000,000 Total Equity $14,500,000 Total L & OE $28,500,000