Remember to carefully plan the layout of the requested schedules as described in class to provide a professional, clear and easily understood presentation. Worksheet requirements: 1. Watch the following YouTube videos: Excel is Fun Videos 19, 28 29 (specific segments as listed on Class Schedule) 2. Using the trial balances data on the following page complete the following in Excel using two worksheets (Total 345 points): a. For worksheet 1: i. Enter the data as provided (36 pts) ii. Use the SUMO function to check your data entry, (12 pts) iii. Format the columns using appropriate formats ($ signs in first and last numbers in a column; numbers line up, commas for 000's, underlining of numbers only (not entire cell) before/after totals) (48 pts) b. For worksheet 2 you will be performing a financial statement analysis: i. Create a comparative Income Statement and Balance Sheet for the three years using formulas to refer to your original data in worksheet 1. (66 pts) Format the financial statements using appropriate formats. (40 pts) ii. Prepare a common sized income statement and balance sheet for the current year (Use the column to the left of the most recent income statement and balance sheet). (25 pts) Prepare a trend analysis (use the columns immediately to the right of each year's financial statements) to show a year over year change and a year over year percentage change. (50 pts) Format appropriately. (50 pts) 2019 2017 $693,470 12,000 $526,945 24,859 Sales revenue Other revenue Cost of goods sold Operating expenses Interest expense Income Tax Expense Cash Accounts receivable, net Inventory Prepaid expenses Property & equipment (net) Accounts payable Accrued expenses Long-term debt Common stock Additional paid-in capital Retained earnings $283,877 176,820 12,188 74,479 66,650 89,627 94,071 12,430 755,857 2018 $534,293 16,854 $231,878 112,968 11,484 85,046 49,865 120,313 71,097 18,200 645,120 85,261 12,869 185,000 6,000 99,000 406,694 $1,345,971 $1,345,971 $257,334 103,469 14,280 74,687 22,553 89,515 55,116 5,060 594,330 50,464 10,600 178,000 6,000 99,000 516,465 $1,565,999 67,010 45,870 180,000 5,000 62,000 304,660 $1,216,344 $1,565,999 $1,216,344 Remember to carefully plan the layout of the requested schedules as described in class to provide a professional, clear and easily understood presentation. Worksheet requirements: 1. Watch the following YouTube videos: Excel is Fun Videos 19, 28 29 (specific segments as listed on Class Schedule) 2. Using the trial balances data on the following page complete the following in Excel using two worksheets (Total 345 points): a. For worksheet 1: i. Enter the data as provided (36 pts) ii. Use the SUMO function to check your data entry, (12 pts) iii. Format the columns using appropriate formats ($ signs in first and last numbers in a column; numbers line up, commas for 000's, underlining of numbers only (not entire cell) before/after totals) (48 pts) b. For worksheet 2 you will be performing a financial statement analysis: i. Create a comparative Income Statement and Balance Sheet for the three years using formulas to refer to your original data in worksheet 1. (66 pts) Format the financial statements using appropriate formats. (40 pts) ii. Prepare a common sized income statement and balance sheet for the current year (Use the column to the left of the most recent income statement and balance sheet). (25 pts) Prepare a trend analysis (use the columns immediately to the right of each year's financial statements) to show a year over year change and a year over year percentage change. (50 pts) Format appropriately. (50 pts) 2019 2017 $693,470 12,000 $526,945 24,859 Sales revenue Other revenue Cost of goods sold Operating expenses Interest expense Income Tax Expense Cash Accounts receivable, net Inventory Prepaid expenses Property & equipment (net) Accounts payable Accrued expenses Long-term debt Common stock Additional paid-in capital Retained earnings $283,877 176,820 12,188 74,479 66,650 89,627 94,071 12,430 755,857 2018 $534,293 16,854 $231,878 112,968 11,484 85,046 49,865 120,313 71,097 18,200 645,120 85,261 12,869 185,000 6,000 99,000 406,694 $1,345,971 $1,345,971 $257,334 103,469 14,280 74,687 22,553 89,515 55,116 5,060 594,330 50,464 10,600 178,000 6,000 99,000 516,465 $1,565,999 67,010 45,870 180,000 5,000 62,000 304,660 $1,216,344 $1,565,999 $1,216,344