Read the case below and carry out the tasks given to Chris. Your answers should be in Excel that must be uploaded on Blackboard by April 2 (Tuesday) midnight (11:59 pm). Make sure answers to each task are in separate worksheets and clicking answers display formulas with cell references. Also, if you need to make use of answers in prior tasks, do not type them directly in a new cell, but reference them. This helps the instructor to determine whether wrong answers in one task are solely caused by wrong answers in others. Chris Guthrie was recently hired by S&S Air, Inc, to assist the company with its financial planning and to evaluate the company's performance. Chris graduated from college five years ago with a finance degree. He has been employed in the finance department of a Fortune 500 company since then. S&S Air was founded 10 years ago by friends Mark Sexton and Todd Story. The company has manufactured and sold light airplanes over this period, and the company's products have received high reviews for safety and reliability. The company has a niche market in that it sells primarily to individuals who own and fly their own airplanes. The company has two models: the Birdie, which sells for $103,000, and the Eagle, which sells for $178,000. Although the company manufactures aircrafts, its operations are different from commercial aircraft companies. S&S Air builds aircraft to order. By using prefabricatedparts, the company can complete the manufacture of an airplane in only five weeks. The company also receives a deposit on each order, as well as another partial payment before the order is complete. In contrast, a commercial airplane may take one and one-half to two years to manufacture once the order is placed. Task 1 (25 points) Conduct ratio analyses for S&S Air Guidelines: 1. Use information in the file named "Financial Statements and Industry Table" 2. Calculate each of the ratios listed in the table for the light aircraft industry. Task 2 (15 points) Use DuPont Identity to evaluate S&S Air's ROE. Identify the reason why S&S Air is performing above many of its industry peers. Guideline: 1. Use information in the file named "Financial Statements and Industry Table" After Chris completed the ratio analysis for S&S Air, Mark and Todd approached him about planning for next year's sales. The company had historically used little planning for investment needs. As a result, the company experienced some challenging times because of cash flow problems. The lack of planning resulted in missed sales, as well as periods when Mark and Todd were unable to draw alaries. To this end, they would like Chris to prepare a financial plan for the next year, so the company can begin to address any outside investment requirements. Task 3 (20 points) Calculate the intenal growth rate and the sustainable growth rate of S&S Air What do these numbers mean? Guideline: 1. Use information in the file named "Financial Statements and Industry Table". Assume that S&S Air maintains its dividend payout ratio of 2018 in 2019. 2. Task 4 (40 points) S&S Air is planning to grow at its sustainable growth rate next year. Prepare S&s Air's 2019 pro forma income statement and balance sheet. Also, calculate the company's extemal financing needed (EFN), assuming the company is operating in full capacity. Recalculate the debt equity ratio when EFN is fully financed by long-term debt. Compare this with the original debt- equity ratio. Are they different? Explain why you have such an answer. Guidelines 1. Use information in the file named "Financial Statements and Industry Table". 2. When preparing the 2019 pro forma income statement, apply percentage of sales approach to depreciation and interest expenses as well. . Assume that S&S Air maintains its dividend payout ratio of 2018 in 2019. 4. As for balance sheet, prepare the partial (EFN in a separate row) and the full pro forma (EFN fully absorbed by long-term debt) S&S AIR, INC 2018 Income Statement Sales Cost of goods sold Other expenses Depreciation EBIT Interest Taxable Income Taxes (21%) Net Income 46,298,115 33,736,91.3 5,870,865 2,074,85.3 4,615,484 725,098 3,890,386 816,981 3,073,405 Dividends Add to retained earnings 705,000 2,368,405 S&s AIR, ING 2018 Balance Sheet ia s and Equi Curreht assets Current liabilities Cash Accounts receivable Inventory Total current assets 524,963 843,094 1,235,161 2,603,218 Accounts payable Notes payable Total 568,356 1,939,553 2,507,909 Long-term debt 7,300,000 Fixedassets Net Plant and equipment 20,381,945 Shareholder equtiy Common stock Retained earnings Total 460,000 12,717,254 22,985,163 Total liabilities and equity 22,985,163 Light Airplane Industry Ratios wer Quartile edian urrent ratio uick ratio ash ratio otal asset turnover otal debt ratio ebt-equity ratio quity multiplier imes interest earned ash coverage ratio rofit margin eturn on assets eturn on equity 0.50 0.21 0.08 0.68 0.44 0.68 1.68 5.18 5.84 0.04 0.06 0.10 1.43 0.35 0.21 0.85 0.52 1.08 2.08 8.06 9.41 0.05 0.11 0.18 1.89 0.62 0.39 1.38 0.61 1.56 2.56 9.83 10.27 0.07 0.13 0.26 Read the case below and carry out the tasks given to Chris. Your answers should be in Excel that must be uploaded on Blackboard by April 2 (Tuesday) midnight (11:59 pm). Make sure answers to each task are in separate worksheets and clicking answers display formulas with cell references. Also, if you need to make use of answers in prior tasks, do not type them directly in a new cell, but reference them. This helps the instructor to determine whether wrong answers in one task are solely caused by wrong answers in others. Chris Guthrie was recently hired by S&S Air, Inc, to assist the company with its financial planning and to evaluate the company's performance. Chris graduated from college five years ago with a finance degree. He has been employed in the finance department of a Fortune 500 company since then. S&S Air was founded 10 years ago by friends Mark Sexton and Todd Story. The company has manufactured and sold light airplanes over this period, and the company's products have received high reviews for safety and reliability. The company has a niche market in that it sells primarily to individuals who own and fly their own airplanes. The company has two models: the Birdie, which sells for $103,000, and the Eagle, which sells for $178,000. Although the company manufactures aircrafts, its operations are different from commercial aircraft companies. S&S Air builds aircraft to order. By using prefabricatedparts, the company can complete the manufacture of an airplane in only five weeks. The company also receives a deposit on each order, as well as another partial payment before the order is complete. In contrast, a commercial airplane may take one and one-half to two years to manufacture once the order is placed. Task 1 (25 points) Conduct ratio analyses for S&S Air Guidelines: 1. Use information in the file named "Financial Statements and Industry Table" 2. Calculate each of the ratios listed in the table for the light aircraft industry. Task 2 (15 points) Use DuPont Identity to evaluate S&S Air's ROE. Identify the reason why S&S Air is performing above many of its industry peers. Guideline: 1. Use information in the file named "Financial Statements and Industry Table" After Chris completed the ratio analysis for S&S Air, Mark and Todd approached him about planning for next year's sales. The company had historically used little planning for investment needs. As a result, the company experienced some challenging times because of cash flow problems. The lack of planning resulted in missed sales, as well as periods when Mark and Todd were unable to draw alaries. To this end, they would like Chris to prepare a financial plan for the next year, so the company can begin to address any outside investment requirements. Task 3 (20 points) Calculate the intenal growth rate and the sustainable growth rate of S&S Air What do these numbers mean? Guideline: 1. Use information in the file named "Financial Statements and Industry Table". Assume that S&S Air maintains its dividend payout ratio of 2018 in 2019. 2. Task 4 (40 points) S&S Air is planning to grow at its sustainable growth rate next year. Prepare S&s Air's 2019 pro forma income statement and balance sheet. Also, calculate the company's extemal financing needed (EFN), assuming the company is operating in full capacity. Recalculate the debt equity ratio when EFN is fully financed by long-term debt. Compare this with the original debt- equity ratio. Are they different? Explain why you have such an answer. Guidelines 1. Use information in the file named "Financial Statements and Industry Table". 2. When preparing the 2019 pro forma income statement, apply percentage of sales approach to depreciation and interest expenses as well. . Assume that S&S Air maintains its dividend payout ratio of 2018 in 2019. 4. As for balance sheet, prepare the partial (EFN in a separate row) and the full pro forma (EFN fully absorbed by long-term debt) S&S AIR, INC 2018 Income Statement Sales Cost of goods sold Other expenses Depreciation EBIT Interest Taxable Income Taxes (21%) Net Income 46,298,115 33,736,91.3 5,870,865 2,074,85.3 4,615,484 725,098 3,890,386 816,981 3,073,405 Dividends Add to retained earnings 705,000 2,368,405 S&s AIR, ING 2018 Balance Sheet ia s and Equi Curreht assets Current liabilities Cash Accounts receivable Inventory Total current assets 524,963 843,094 1,235,161 2,603,218 Accounts payable Notes payable Total 568,356 1,939,553 2,507,909 Long-term debt 7,300,000 Fixedassets Net Plant and equipment 20,381,945 Shareholder equtiy Common stock Retained earnings Total 460,000 12,717,254 22,985,163 Total liabilities and equity 22,985,163 Light Airplane Industry Ratios wer Quartile edian urrent ratio uick ratio ash ratio otal asset turnover otal debt ratio ebt-equity ratio quity multiplier imes interest earned ash coverage ratio rofit margin eturn on assets eturn on equity 0.50 0.21 0.08 0.68 0.44 0.68 1.68 5.18 5.84 0.04 0.06 0.10 1.43 0.35 0.21 0.85 0.52 1.08 2.08 8.06 9.41 0.05 0.11 0.18 1.89 0.62 0.39 1.38 0.61 1.56 2.56 9.83 10.27 0.07 0.13 0.26