File Home Insert Draw Page Layout A1 Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected Xfx Accounting Capstone A B C D E F G H 1 Accounting Capstone 2 Assignment 7-1 The tabs Sheet1 and Sheet 2 contain comparative income statements and balance 3 sheets for Orbit Company for the years ended 2021 and 2020. 4 5 Required: 6 1) Rename each tab as "Income Statement" and "Balance Sheet" appropriately. 2) Using Excel formulas, calculate common-size financial statements for each year covered. 7 8 3) Format the common-size data as percents with 2 decimal places. 9 10 10 11 123 12 13 14 15 16 4) Create a bar chart comparing 2021 and 2020 Sales, Cost of Goods Sold, Total Expenses, and Net Income. 5) Change the background color for the income statement header to a professional color of your choice. 6) Change the bar colors of your bar chart to colors that coordinate with your income statement header. 7) Create a pie chart showing all assets. 8) Calculate the following ratios for 2021: Current Ratio, Inventory Turnover Ratio, Debt Ratio, Profit Margin, Return on Total Assets Enter your answers in the workbook labeled "Ratios". 9) Based on your common-size income statement, prepare an analysis statement of how this company is doing. Enter your thoughts on the worksheet laheled "Analysis Statement" Instructions Ready Type here to search Income St Bal Sheet Ratios Analysis Statement K Home Insert Draw Page Layout Formulas Data Review View Help Al PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected! A 8 6 10 11 223 12 13 14 15 16 17 B Xfx Accounting Capstone C D E F G H 3) Format the common-size data as percents with 2 decimal places. 4) Create a bar chart comparing 2021 and 2020 Sales, Cost of Goods Sold, Total Expenses, and Net Income. 5) Change the background color for the income statement header to a professional color of your choice. 6) Change the bar colors of your bar chart to colors that coordinate with your income statement header. 7) Create a pie chart showing all assets. 8) Calculate the following ratios for 2021: Current Ratio, Inventory Turnover Ratio, Debt Ratio, Profit Margin, Return on Total Assets Enter your answers in the workbook labeled "Ratios". 9) Based on your common-size income statement, prepare an analysis statement of how this company is doing. Enter your thoughts on the worksheet labeled "Analysis Statement". When you have completed the required tasks, save your spreadsheet and upload this 18 file to the Exercise 7-1 folder in NS Online. 19 20 21 22 23 OrbitCo2021 (3) - Protected View Home Insert Draw Page Layout Formulas Search (Alt+Q) Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Prote A XVfx B D E G ORBIT COMPANY Comparative Income Statements For Years Ended December 31, 2021 and 2020 $ 340,000 $276,600 Sales Cost of goods sold 212,500 153,900 Gross profit 127,500 122,700 Selling expenses 46,920 50,800 Administrative expenses 29,920 22,800 Total expenses 76,840 73,600 Income before taxes 50,660 49,100 Income taxes 10,370 15,670 Net income $ 40,290 $ 33,430 A Xfx Assets B ORBIT COMPANY Comparative Balance Sheets December 31, 2021 and 2020 C D E G 2021 2020 Cash Accounts receivable Inventory $ 49,800 $ 73,500 65,810 50,625 275,656 251,800 Prepaid expenses Total current assets 1,250 1,875 392,516 377,800 Equipment, net 120,875 62,000 Total assets 513,391 439,800 Liabilities and Equity Accounts payable 53,141 114,675 Short-term notes payable 10,000 6,000 Total current liabilities 63,141 120,675 Long-term notes payable 65,000 48,750 Total liabilities 128,141 169,425 Equity Common stock, $5 par value 162,750 150,250 stock Retained earnings Paid-in capital in excess of par, common Total liabilities and equity $ 513,391 $439,800 37,500 0 185,000 120,125 C Instructions Income St Bal Sheet Ratios Analysis Statement Tune here to search 1 AutoSave on H Orbit () Potected View File Home insert Draw Page Layout Search (Alt+ Formulas Data Review View Help PROTECTED VIEW Be careful-files from the internet can conten vinees. Unless you need to edit it's safer to stay in P A1 A Ratio 2 Current Ratio Xv Ratio 3 Inventory Turnover 4 Debt Ratio 5 Profit Margin Ratio 6 Return on Total Assets 7 8 9 B Values Used in Formula Ratio Answer D E F 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Instructions Income St Sheet Ration Analysis Statement Ready B Type here to search 2 3 4 97 5 9 A B C D E F G H Instructions Income St Bal Sheet Ratios Analysis Statement W Type here to search A B C D E F G H Instructions Income St Bal Sheet Ratios Analysis Statement W Type here to search