help with calculating number 4 on the work sheet

Yacht Industry Ratios Lower Quartile Median Upper Quartile Current ratio 0.86 1.51 1.97 101 Ba 2 Quick ratio 0.43 0.75 1.01 01 61 be 3 Total asset turnover 1.1 1.27 1.46 1. 52 4 Inventory turnover 12.18 5 Receivables turnover 14.38 16.43 21 . 39 9 10.25 Debt ratio 17.65 22.43 37, 74 7 Debt-equity ratio 0.32 0.56 0.61 0.83 & Equity multiplier 1.13 1.44 1.83 9 Interest coverage 2.13 2.44 10 Profit margin 5.72 8.21 10.83 1 Return on assets 5.02% 7.48% 9.05% 12 Return on equity 7.05% 10.67% 14.16% 14.06% 19.32% 26.41%Project 1_Last Name e Project tab on Blackboard. Objective: This is a mini case study to help the company to evaluate the amount of external fund needed to support future growth opportunity. You will learn how to develop your quantitative and analytical skills. Mini Case: Ratios and Financial Planning at East Coast Yachts Lisa wants to find out how East Coast Yachts is performing relative to its peers. Additionally, she wants to find out the future financing necessary to fund the company's growth. In the past, East Coast Yachts experienced difficulty in financing its growth plan, in large part because of poor planning. Lisa would like to estimate the amount of capital the company would have to raise next year so that East Coast Yachts would be better prepared to fund its expansion plans. Requirements: 1. East Coast Yachts uses a small percentage of preferred stock as a source of financing. In calculating the ratios for the company, should preferred stock be included as part of the company's total equity? 2. (Excel) Calculate all of the ratios listed in the industry table for East Coast Yachts. 13. Compare the performance of East Coast Yachts to the industry as a whole. For each ratio, comment on why it might be viewed as positive or negative relative to the industry. 4. (Excel) Calculate the sustainable growth rate for East Coast Yachts. 15 . 5 2 a. Calculate external funds needed b. Prepare pro forma income statement and balance sheet using this sustainable growth rate c. Do you obtain the same numbers of EFN from part a and part b? What source of financing word should East Coast Yachts use? d. Recalculate the ratios in the previous question. What do you observe? 5. (Excel) East Coast Yachts is unlikely to be willing to raise external equity capital, in part because the shareholders don't want to dilute their existing ownership and control positions. However, East Coast Yachts is planning for a growth rate of 20 percent next year. a. Calculate external funds needed b. Prepare pro forma income statement and balance sheet C . What are your conclusions and recommendations about the feasibility of East Coast's expansion plans? 6. East Coast Yachts doesn't want to use external financing, what is the maximum growth rate? Note: When you work on the calculations, the formula you use in the Excel spreadsheet should reference he data from the Income Statement or Balance Sheet. Excel has to know the location and the cell address. Please round the number to two decimal places. You will be asked to re-work on the project if you do ot use reference cells to calculate.East Coast Yachts 2020 Balance Sheet Current assets Cash and equivalents $ 10,107,000 Accounts receivable 16,813,300 Inventory 18,135,700 Other 1,054,900 Total current assets $ 46,110,900 Fixed assets Property, plant, and equipment 412,032,000 Less accumulated depreciation 102,452,000 Net property, plant, and equipment $ 309,580,000 Intangible assets and others 6,772,000 Total fixed assets $ 316,352,000 Total assets $ 362,462,900 Current liabilities Accounts payable $ 40,161,400 Accrued expenses 5,723,700 Total current liabilities $ 45,885,100 Long-term debt $ 152,374,000 Total long-term liabilities $ 152,374,000 Stockholders' equity Preferred stock 1,773,000 Common stock 31,802,000 Capital surplus 27,348,000 Accumulated retained earnings 146,052,800 Less treasury stock 42,772,000 Total equity $ 164,203,800 Total liabilities and shareholders' equity $ 362,462,900Financial information: (All information is available in the Excel file) East Coast Yachts 0 , 1552 2020 Income Statement Sales $ 550,424,000+ Costs of goods sold 397,185,000+ 65,778,000 + Selling, general, and administrative 3ee4rTttrff Depreciation 17,963,000 EBIT $ 69,498,000 Interest expense 9,900,000 - cost -lerings EBT $ 59,598,000 + Taxes (25%) 14,899,500 Net income $ 44,698,500 Dividends $ 19,374,500 - 0. 1352 Retained earnings 25,324,000