Grader - Instructions Excel 2016 Project BISM200 Test 2-1 pt 1 Project Description: Compiling monthly Income Statements. Steps to Perform: Step Points Possible Instructions 1 Open the downloaded workbook named Test 2 pt 1 start.xlsx and save it as Test 2 pt 1_LastFirst.xls 0 2 Import Income Statement Jan.csv, removing the first two columns. Ensure that the Use First Row as Header option is used, if applicable. Also if the import results in a Table, convert the table to a range. Name the new sheet Jan 1 3 On a new tab, import Income Statement Feb.csv, using the same method above. Name the new sheet Feb Perform the same actions for Mar and Apr files. 3 4 Group Jan, Feb, Mar, and Apr. Clear Format only. Copy field A1 thru A21 of the Income Statement to Column A into the grouped worksheets. Group all worksheets including Income Statement. Apply Title format to A1,A2, and A3. Apply Heading 1 format to A5, A11, and A21. Format numeric fields to Number format with 2 decimals and comma separator. Format Column Width for A, B, and C to a value of 25. 2.5 5 On the Income Statement Sheet, insert a hyperlink at B27 to hardware.ca site. Use Financial Breakdown as the label on the spreadsheet. Add a ScreenTip stating YTD Financial Data (no period). 3 6 In cell C6 in the Income Statement sheet, enter a 3-D formula to sum the monthly amount from each of the Jan Feb Mar and Apr sheets. Fill results for all data fields on the Income Statement sheet. 2 7 In cell C23, use a 3D formula to sum the Net Income from each of Jan Feb and Mar worksheets. 1 8 Enter a formula in C25 to return the average sales per month for Jan Feb Mar and Apr. You may get a divide by zero message for the cell, if the formula is correct, it will count. 1 9 Apply Bold format to C10, C19, and C21 on the Income Statement worksheet. 1 Created On: 04/16/2020 1 BISM200 Test 2-1 pt 1 Grader - Instructions Step 10 Excel 2016 Project Points Possible Instructions 0 Save and submit your final results for grading. Total Points Created On: 04/16/2020 2 14.5 BISM200 Test 2-1 pt 1 76cfce0fca7a5864b44a50181781ea8e007ea7f5 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 69739 1 T001 2 T002 3 T003 4 April 5 T004 6 L001 57881.25 7 L002 -5788 8 L003 52093.25 9 L004 23152.5 10 L005 28940.75 11 T005 12 L006 8682.19 13 L007 578.81 14 L008 549.87 15 L009 1736.44 16 L010 289.41 17 L011 115.76 18 L012 2200 19 L013 14152.48 20 21 L014 14788.27 Page 1 9158c8f86f322a7d22369a3d17903053ea55dc63 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 69737 1 T001 2 T002 3 T003 4 February 5 T004 6 L001 52500 7 L002 -5250 8 L003 47250 9 L004 21000 10 L005 26250 11 T005 12 L006 7875 13 L007 525 14 L008 498.75 15 L009 1575 16 L010 262.5 17 L011 105 18 L012 2200 19 L013 13041.25 20 21 L014 13208.75 Page 1 587393d4c79e807dacf2ac84a9c5653137919972 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 69736 1 T001 2 T002 3 T003 4 January 5 T004 6 L001 50000 7 L002 -5000 8 L003 45000 9 L004 20000 10 L005 25000 11 T005 12 L006 7500 13 L007 500 14 L008 475 15 L009 1500 16 L010 250 17 L011 100 18 L012 2200 19 L013 12525 20 21 L014 12475 Page 1 424adb0daba9145327161f1e92ee110ccc6286e3 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 69738 1 T001 2 T002 3 T003 4 March 5 T004 6 L001 55125 7 L002 -5513 8 L003 49612 9 L004 22050 10 L005 27562 11 T005 12 L006 8268.75 13 L007 551.25 14 L008 523.69 15 L009 1653.75 16 L010 275.63 17 L011 110.25 18 L012 2200 19 L013 13583.32 20 21 L014 13978.68 Page 1 Canada Hardware Income Statement For the Year Ending 2020 YTD Revenue Sales Sales Discounts Net Sales Cost of Goods Sold Gross Profit Operating Expenses Salaries & Wages Depreciation Expenses Office Expenses Rent Expense Travel Expenses Maintenance Expenses Advertising Expenses Total Operating Expenses Net Income Previous YTD Net Income Average Sales per Month Detail