Q1 to Q4 in Excel
Assignments Chapter (3) Case: Ratios and Financial Planning at S&S Air, Inc. Due April 16, 2020 11:59 PM . Closes April 16, 2020 11:59 PM Instructions Dear students, this is your first case assignment for FIN220. This is an individual work. Here are some instructions for you: 1. Read the case carefully. 2. Transfer all the information (Financial Statements and Industry Average) into an EXCEL spreadsheets. 3. Answer questions 1 through 4 on the EXCEL spreadsheets, as a College of Business Administration student of University of Bahrain. 4. Please write your Name, Student ID, and Tutorial Section on your EXCEL spreadsheets. 5. Submit your assignment in one xlsx format. You can choose to submit through this link or Blackboard. Make sure you only submit on one of these two platforms, NOT BOTH! In order to be able to get a full mark, make sure that you have put an extraordinary (Full point) effort! Excellent work is: Well organized Excel "workbook(s)" with strong structure. Clear focus. Complete - Fully answers the questions. Demonstrates insight into the issue. All the best CHAPTER CASE Ratios and Financial Planning at S&S Air, Inc. partial payment before the order is complete. In con. trast, a commercial airplane may take one and one-half to two years to manufacture once the order is placed. Mark and Todd have provided the following finan- cial statements. Chris has gathered the industry ratios for the light airplane manufacturing industry. S&S AIR, INC 2019 Income Statement Chris Guthrie was recently hired by S&S Alr, Inc., to Cassist 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 For tune 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 com- pany'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 $53,000, and the Eagle, which sells for $78,000 While the company manufactures aircraft, its opera. tions are different from commercial aircraft companies. S&S Air builds aircraft to order. By using prefabricated parts, the company is able to complete the manufacture of an airplane in only five weeks. The company also re- ceives a deposit on each order, as well as another Sales Cost of goods sold Other expenses Depreciation EBIT Interest Taxable income Taxes (21%) Net income Dividends Additions to retained earnings $26,501,600 19.780.200 3,166,700 864,500 $ 2,690,200 479,200 $ 2,211,000 464,310 $ 1,746,690 $270,600 1,476,090 ceives a deposit on each order, as well as another S&S AIR, INC. 2019 Balance Sheet Assets Liabilities and Equity Current assets Current liabilities Cash $ 481,852 Accounts payable 944,698 Accounts receivable 2,025,778 Notes payable 1,909,248 Inventory 1,634,820 Total current liabilities $ 2,853,946 Total current assets $ 4,142,450 Long-term debt $ 5,060,000 Net plant and $16,256,698 Shareholder equity equipment Common stock $ 190,000 Retained earnings 12,295,202 Total equity $12,485,202 Total assets $20,399,148 Total liabilities and equity $20,399,148 Fixed a Light Airplane Industry Ratios Lower Quartile Median Upper Quartile 50 1.43 .84 1.89 1.05 .39 1.13 .68 4.89 6.27 Current ratio Quick ratio Cash ratio Total asset turnover Inventory turnover Receivables turnover Total debt ratio Debt-equity ratio Equity multiplier Times interest earned Cash coverage ratio Profit margin Return on assets Return on equity .21 .85 6.15 9.82 .52 1.08 2.08 8.06 8.43 6.75% 10.53% 16.54% 1.58 5.18 5.84 4.05% 6.05% 9.93% 10.89 11.51 .61 1.56 2.56 9.83 10.27 8.47% 13.21% 26.15% You CTO Profit margin Return on assets Return on equity 4.05% 6.05% 9.93% 6.75% 10.53% 16.54% 8.47% 13.21% 26.15% QUESTIONS 1. Calculate the ratios for S&S Air that are shown for the industry 2. Mark and Todd agree that a ratio analysis can pro vide a measure of the company's performance. They have chosen Boeing as an aspirant com- pany. Would you choose Boeing as an aspirant company? Why or why not? 3. Compare the performance of S&S Air to the indus- try. For each ratio, comment on why it might be viewed as positive or negative relative to the in- dustry. Suppose you create an inventory ratio cal culated by inventory divided by current liabilities. How do you think S&S Air's ratio would compare to the industry average? Calculate the internal growth rate and sustainable growth rate for S&S Air. What do these numbers mean
