all work must be done in excel showing all calculations
Instructions Please complete the following: · Problem P13-1 · Problem P13-15 · Problem P13-21 · Problem P14-8 · Problem P14-14 Carry all calculations to TWO decimal places to reduce rounding error, especially in multi-step problems. There are several assigned problems where the solution will require graphing. This will require using the graphing function in Excel. · If you submit your answer without an Excel graph points will be deducted. · If you submit your answer by drawing a graph even if it is correct, points will be deducted. Remember to show all calculations. You must show your calculation and solution. please see the example below. If you do not show how you arrived at your answers, you will not receive credit. Merely showing the formula without the calculations is not sufficient. The problems should be set up in columns or using other appropriate formats—do not hide all steps in formulas behind one answer. I must still be able to see how you arrived at the answer. EXAMPLE OF HOW WORK SHOULD LOOK Please see below Example (please be sure to show the formula in answers and calculations) · Problem P13-1 EPS calculations and degree of financial leverage: Northwestern Savings and Loan has a current capital structure consisting of $250,000 of 16% annual interest debt and 2,000 shares of common stock. The firm pays taxes at the rate of 21%. a. Using EBIT values of $80,000 and $12,000, determine the associated earnings per share (EPS). b. Using $80,000 of EBIT as a base, calculate the degree of financial leverage (DFL). c. Rework parts a and b, assuming that the firm has $100,000 of 16% (annual interest) debt and 3,000 shares of common stock. · Problem P13-15 Modigliani & Miller Proposition: A certain firm with no debt that operates in perfect capital markets generates a 7.5% return for its shareholders and can issue debt at a cost of 5%. Determine the firm’s ROE at the following debt-to- equity ratios o.5, 1.0, and 1.5. · Problem P13-21 Agency Cost and Capital Structure: Galaxy Enterprises has earnings before interest and taxes of $9 million and $90 million of debt outstanding with a required rate of return of 7.3%. The required rate of return on assets in the industry is 12%. The corporate tax rate is 21%, but there are no personal taxes. The present value of Galaxy’s bankruptcy cost is $18 million. Compute Galaxy’s firm value. · Problem P14-8 Dividend Constraints: A firm has $800,000 in paid-in capital, retained earnings of $40,000 (including the current year’s earnings), and 25,000 shares of common stock outstanding, In current year, it has $29,000 of earnings. 1. What is the most the firm can pay in cash dividends to each common stockholder? ( Assume that legal capital includes all paid-in-capital.) 2. How would an $0.80/share dividend affects the firm’s balance sheet? 3. If the firm cannot rise new external funds, what do you consider the key constraints with respect to the magnitude of the firm’s dividend payment? why? · Problem P14-14 Stock dividend: Investor: Sarah owns 400 shares of Nutri-Foods. The firm has 40,000 shares outstanding. The firm most recently had earnings available for common stockholders of $80,000,and it’s stock has been selling for $22 per share. The firm intends to retain its earnings and pay a 10% stock dividend 1. How much does the firm currently earn per share? 2. What proportion of the firm does Sarah currently own? 3. What proportion of the firm will Sarah own after the stock dividend? Explain 4. What should be the stock price after the stock dividend ? 5. Discuss what effect, if any, the payment of stock dividends will have on Sarah’s share ownership and earnings of Nurti-Foods.