pleass hslp ms on hos to complete this assignment with the steps i also attached

Font 10 N Company Name Actual Expenses Sheet First Two Quarters of 2021 February June Trends May Total lan Percent of Total March April Payroll Weet Empayer Coronto CPP EsployerContribution for Employer Contribution Banats Tural Pay Ant Pyro Peres CPP 10 14 2.21N tenet 1:47 Office Costs Rentalon Business Pro Business Property Useat lectrical Water Delivery tapes Free Omegangsposte stationary Minorquiment Conta internet Supplies Secury Total Obice Costs Advertising Costs Website hosting Web site updates Sheet1 Sheet2 ao Interneta Supplies Security Total Once Consts Advertising Costs Website hosting Website updates Website Preparation Printing Marketing events Miscellaneous expenses Total Advertising Costs Other Expenses Insurance Goods and Services Tax Total Other Expenses Service Costs Train Costs Accounting Local Collection Consulting Total Service Costs Monthly Totals Company Name Charity Drive - Customer Donation List First Nanti Last Name Address Damon H 5019 N Newport Ave Ang kaso 11 Jericho C Sherman Varren 12 S Fremont Ave Robert Wheaton 235 EBay Dr Stephanie Lopez 25 Seyahat LIU Xiang 1502 Norastlake Mugie ibor 155 Elmrest Circle Ramon Ligonde 157 Beacon Palms PI Rown Find 2180 Lake Ellen Di Kendra LUC 2209 Sunset View Brian Whittier 2235 Est Ave Nathan Boston 7721 E Osbome Ave Ved Bagou 721E Lang Ave Benjamin Yovan 2010 E Frierson : yan Coleman 650N Himes Ave, 320 Janice Murphy 5100 C Woodwin Ave auren Pime 1571 Tuin Palms Boran Najmick 5810 Osceola PI Antonio Dejohet 1605 E Okaloosa Ave Brent Yung 231 Frison St Taylor 9708 Soaring Train they Schroeder 715 E Orleans Ave, 10 Brandon Hurd 8106 Mabrey Ave. E Hector Ortiz 3812 N O Vay "hillip Draper 1959 Th H Cemy Grant 5868 Bugwater Dr. #16D ci Cartey 65 N Hilton Ave, 1120 demy Chen 88 Anglers Pointer vonne Zhang 9900 Versalles Or ooke Parham 4917 E 42nd S Bne Mason 510 Voodlawn Ave Bruce 1920 S Lemon St Buce 1921 IVA Sheet1 Sheet2 City Provind Postal Co Phone Numb Emil Item Donated Value Chegory Vasaga Beach ON G9D405 (415) 555-0018 Damon Hilms.com Massage 50 Other Orangeville ON 12V5A2016)665.01 Ankaroogmail.com Hockey Tickets 75 Product Pickering ON YOB 9A416555-0122 Sherman Vangma.com MP3 Player 200 Equipment Tororo ON NJA 121 (415)555-0721 Robert Wheon@gmail.com Hotel Accommodations 100 Get Certificate Hamilton ON NOR121 141655 025. Stephanie Lopeng.com Hotel Accommodations 100 G Certi Colingvood ON TSG 7V (416) 655-0155 Xianggmail.com Restaurant Git Certifica 60 Get Certime Mark de ON HIGH4 (416) 555-015 Margelbokom acom CW Vash Cud 50 Other Vanaga Beach ON EB2A3 (416) 655.07 Runon Ligonde@gmail.com Automotive Gift Certifio 50 GR Certificate Toronto ON STHOF (416) 555-021 Kevin Feldml.com 45kg (b) of Fresh FG 100 Product Toronto ON TAN 736 (415) 555-022 Kendaluch@gmx.com Le Making Class 25 Other Toronto ON XIA 500 (495) 555-0721 Vittirgmailom Sabuchs Gift Card 10 Gert London Springs ON P73102 (495505-077 Nhan Bostonim.com Movie Tickets. Popoort 20 Product London Springs ON PICOV (451555-072 Ned Bayoum.com Free Photography Sitting 45 Service Toronto ON VEP OTO011555-0123 Benyamin Yovanomaloom Fondue Set 20 Product Vasaga Beach ON V5P OTO (416) 555 0654 Coleman@gmail.com Fondue Set 20 Product Souborough ON NZA M5 (416) 556-0512 Janke Murphyma.com Yoga Class 100 Service Scarborough ON EIC (416) 555-074. Len Palmer.com Gym Membership 260 Other Etobicoke ON AIE 509 (486) 555.058. Gotan Nymick@gmail.com Veb Site Services 100 Service Pickering ON NSV OH5 (415) 555-013 Antonio Dejonette@gmaile YMCA Swimming Lessol 65 Service Toronto ON EB 4X9 (811) 555.0129 Brem. Yunggmail.com Floral Altangement 75 Gift Certificate Etobicoke ON NEG2A2 (416) 555 0961 Anna Taylor@gmail.com Laptop Computer 1000 Equipment Toronto ON S4P 11 (416) 5550715 Ashley Schroeder@gmail.com Digital Camera 400 Equipment Scarborough ON V4B 472 (416) 555-0819 Brandon Hurd@gmail.com Grocery Gift Certificate 50 GR Corte Collingwood ON M5A 822 (455)555-030E Hectonigmail.com Restaurant ir Certificat 40 Gertificate Hamilton ON M4C556 (055) 555-005 Philip Daliper Bgmail.com 1 Year Free Collee 100 Product Trois-Rivieres G9D405 (812) 555-0061 Remy Grant@gmail.com Gift Basket 75 Product Saint-Jean-sur-Richeli ac J2W5A2 (418) 5550651 Rick Carrey@gmail.com Toy Store Gire Certificate 50 Gift Certificate Dawson YT YOB 9A9 (416) 555-08BE Jeremy Chen@gmail.com Automotive Gift Certifice 100 Gift Certifioste Brantford ON NA IZI (416) 565-039 Yvonne Zhang@gmail.com Restaurant Gift Certifio 75 Gift Certifione Branford ON NIA 121 (416) 555-0491 Brooke Parham@gmail.com MP3 Player 145 Equipment Edmonton AB T5G 7V (416) 555-0510 Jane Mason@gmail.com Gym Membership 200 Gift Certificate Montres OC H3G 1H4 (416) 555-092 Alendea Bruce@gmail.com Restaurant Got Certificat 45 Gift Certificate Etedeton MR FB2214161855-187: Henna Brice mail.com Bestur GCactic 15. Gift Certificate end O Espinosa Kerry 2100 Lake Ellen Di London 13 Estes Nicole 6722 Colonistid Riverview 0 FS Aaron 5094 Cozy Place London 1 Foley Amber 2048 Round Heights Regina Gvan Theodore 6800 Silver Shadow Gate Mock 3 Holden Lauren 6833 Silver Shadow Me Toronto 34 Irvin Connie 2058 Golden Close Toronto Jenkins David 6674 Quaking Alley Hall as 6 Jones Soon 4949 Green Farm Charlottetown 37 Kenter Jason 7779 Merry Deer End Whitehorse 18 Luch Matthew $887 Harvest Timber Gard Summerside 59 Martinez Eugene 1694 Red Apple Heath Steinbach 30 MoBride Jul 3280 Crystal Butterfly Drm. Lethbridge Nelson Shane 552 Thunder Promenade Brandon 52 Owens Tyler 699 Iron Branch Terrace Brandon 35 Reyes Michelle 698 Iron Branch Terrace Kelowna 54 Romero Melissa 530 Umber Grounds Vancouver 55 Shaw Debby 3453 Rustio Cloud Towntir Nanaimo 56 Switzer Nicholas 1000 Emerald Concession Barrie 57 Towers Jeffery 1108 Pleasant Dale Park Erobook 4 58 Trujillo Juan 2898 Cinder Vynd Pickering 55 Valdez Terni 5656 Dewy Sale Toronto RO Wang Chao-Jun 4126 Tawny Cicle Etobicoke 21 Wilborn Ryan 6450 Sleepy Sky Jetty Toronto 22 Dwyer Jack 2643 Middle Oak Forest Souborough 79 Brown Sara 8733 Hazy Trace Collingwood 74 Hamlin 2496 Lazy Nectar Tumabot Hamilton 25 Thomas Grant 623 Lost Rise London Springs To Marino Timothy 6110 Wishing Line Vasaga Beach T2 Chaplin Lon 2151 Broad Blossom Busy Brampton 78 Trent 4872 Sunny Log Abbey Mississaug TS Liston Frank 4327 Shady Crossing Mississauga 60 Nichols Cathy 6303 High Pointe Brampton 81 Brett Anna 6303 High Pointe Hamilton 82 Phelps 8500 Cotton Fox Passage Brampton Chaplin Lon 1421Easy Range Brampton 34 Brown Kurt 3982 Cotton Cider Com Brampton 35 Penninge Chad 6010 Iron Bank Mississauga Sheet1 Sheet2 ON NE ON SW BC ON ON NS PE YT PE ME MB MB BC BC BC ON ON ON ON ON ON ON ON ON ON ON ON ON ON ON ON a NSV OH56471605.002 Espinoza Kerry Dgmail.com prop Compu 200 G Certime EB 4X9 (547565.125 Etter Nicomail.com Dights Camera 45 Gift Certificate NGG2A 1647) 555.307 FC Aronoms.com Grocery Out Cartole 45 GiCalificate SAP 11 (6471655-120 Foley Amber@gmat.com Pestaurant Gut Certo 145 Equipment VB4224715523 Gallivan Theodore@gmail.com Free Coffee 40 GH Certoate MSA82216471656 207 Holden Lauren Bgmail.com Barket 30 Gint Certified MAC55671647/665099 Irwin Connema.com Rest Certificat 45 Gr Certifiome Y5F 6x0647 56572 Senki widmom MP3 Piger 145 Equipment CIC 45 (647) 555-937 Jones Soongmai.com Resturant Gilt Certi 40 GR Certificate YIA 1Y2 1647) 555-276 Kenwerton@gmail.com Movie Tickets.Popcort 30. Gut Certificate CIN7R4 (64755-013 Luck Mathewogmail.com Digital Camera 125 Equipment RSG SMZ (647555-446 Martinez Eugengmail.com Fondue Set 100 Product TIKONI (6471 555.726 MoBride Julie@gmail.com Yoga Class 75 Product B7C0Y51647) 555-837 Nelson Shangmail.com Gym Membership 50 GR Certificate F7C OYS. 1647555-873 Owens Tygmail.com Web Site Services 100 Git Certoate VIY 360 (647) 585 980 Reyes Michele@gmail.com YMCA Svimming Lesso 75 Out Certifica V7G853 (647)555-0011 Romero Melissa@gmail.com Floral Artangement 145 Equipment V3M U LED EDIT Bloom Laptop Computer 200 Gift Certificate L4MTI (647) 555-735 Switzer Nicholas@gmail.com Digital Camera 45 Gift Certoate Y5F6XO (647) 555-064 Towers Jeffery@gmail.com Grocery Gift Certificate 45 Get Certificate CIC 4P5 (647) 555-023 Trillo Juan@gmail.com Restaurant Gift Certific 5 Equipment VIA TV (647) 555-068 Valdez Terri@gmail.com 1 Yem Free Coifee 40 Gift Certificate CWM RA [47555 012 MargCho lui gmail,con st Parket 30 Certificate RSG BMW 16473555-673 Vibon yangmail.com Gym Membership 250 Other TKN1 1805) 555-307 Dyer Jack@gmail.com Web Site Services 100 Service R1C0Y5 (905) 555 983 Brown S.Margmail.com YMCA Swimming Lesson 65 Service R7C 0Y5 (905) 555-002 Harri Rosema.com Floral Arrangemen 75 Gift Certificate VIY 30 (905) 555-1254 Thomas Grant@gmail.com Laptop Computer 1000 Equipment V70 883 (905) 555-987 Marino Timothy@gmail.com Digital Camera 400 Equipment VSV EU (905) 65-120 Chaplog.com Grocery Gift Certificate 50 GR Certificate Restaurant Gift Certificat 40 Gift Certificate L4M6T1 (905) 555-238 Trent Maria@gmail.com G9D 405 (905) 555-287 Liston Frank@gmail.com 1 Year Free Colle 900 Product J2V5A2 (905) 555-039 Nichols Cathygmail.com Gift Basket 75 Product YOB SAS (905) 555-782 Brett Anna@gmail.com Toy Store Gift Certificate 50 Gift Certificate NBR 121 (905) 555-934 Phelps. Jeff@gmail.com Automotive Gift Certifio 100 Gift Certificate NR 121 (905) 555-276 Chaplin Lon@gmail.com 75 GR Certificate Restaurant Gift Certifica T5G 7 (905) 555-012 Brown Kurt@gmail.com MP3 Player 145 Equipment 200 Gift Certificate H3GH4 (805) 555-446 Pennington Chad@gmail.com Gym Membership Rose Maria ON ON ON ON Grader Instruction Excel 2019 Project Excel Assignment #3 - Visual Data 2 Project Description: It is time to calculate and visually analyze you company's expenses for the first two quarters of the year. In addition, you decided to setup a charity drive to help with the community and to help advertise your business. You created a list of clients who have successfully donated something to your charity drive, and you want to make the list more visually appealing Using Excel with the information you have already toamned and with the new tools you just learned in Topic 5 and Topic 6, completo the following assignment Stops to Perform Step Instructions Points Possible 1 Open the file named Hum365 Excel Assign VisualData.xis Grader has automatically 0 added your last name to the beginning of the Sename, so it should be easy to locate Ensure you have saved these files correctly within your File Management structure When you open the Excel Workbook, you will see two worksheet tabs at the bottom 2 Change the Workbook Thome to lon Boardroom, the colors to Slipstream, and the Fonts to 2 Century Gothic Sheet contains information about your business expenses. It is situp for the first two quarters of the year however you need to fill in the expense data Change Company Name to your Company Name 3 For the Payroll section, complete the following Collonge 87 67 type in how much woges you spend each month on your staff's pay. Your choice Accesibility investigate 09 English (United States 3 For the Payroll section, complete the following: Cell range B7 G7, type in how much wages you spend each month on your staff's pay. Your choice In cell B8, insert a formula to calculate the Employer Contribution for CPP, CPP is the Canadian Pension Plan and employers are responsible to pay some of its contribution. This is calculated by multiplying your monthly wages by the CPP Rate Copy this formula across to the other months. Remember to use proper absolute referencing. In cell B9, insert a formula to calculate the Employer Contribution for El Elis Employment Insurance, which helps those who lose a job or cannot find a job, and as a business owner, you are required to pay a certain percentage. This is calculated by multiplying your monthly wages by the El Rate. Copy this formula across to the other months. Remember to use proper absolute referencing. In cell B10, insert a formula to calculate the Employer Contribution for Benefits. Benefits are not offered by all companies, but they do help employees subsidize medical and dental payments. This is calculated by multiplying your monthly wages by the Benefits Rate. Copy this formula across to the other months. Remember to use proper absolute referencing In cell B11, insert a function to total each month for the Payroll section For the Office Costs section, complete the following: For all the Office Costs expenses, type in how much you spend on each expense for each month. Your choice In cell B23, insert a function to total each month for the Office Costs section Excel Assignment 3 Visual Data 2 Created On 07/30/2021 Paragraph 1234567 Styles 8 9 10 11 12 13 14. 1. 15:16 11:17 I 1 Points Possible Instructions Step 1 5 For the Advertising Costs section, complete the following For all the Advertising Costs expenses, type in how much you spend on each expense for each month. Your choice. In cel B32, insert a function to total each month for the Advertising Costs section. 8 For the Other Expenses section, complete the following For all the Other Expenses Costs expenses, type in how much you spend on each expense for each month. Your choice In cell 838, insert a function to total each month for the Other Expenses section 7 For the Service Costs section, complete the following: For all the Service Costs expenses, type in how much you spend on each expense for each month. Your choice In cell B43, insert a function to total each month for the Service Costs section I In cell B45, insert a function to add up each sections totals using the total cells for each section cu 5 5 10 In cell H7.insert a function to total each expense for each section. Starting in cell 17, insert a formula to calculate the Percent of Total for each expense within the different sections. This is calculated by dividing each item by the total of the entire section For example to calculate the Wages Percent of Total, you would divide the Wages total by the Total Payroll Amount. Remember to use absolute referencing where required Based on only the monthly data for Office Costs Section, create a 3-D Clustered Column chart Move this chart properly to a new sheet and rename it Office Costs Expenses. Move the new chart sheet to the end of your workbook to the right of Sheet2) Change the Tab Color to Orange, Accent 5 (Column Row 1 - Under Theme Colors) 2 11 12 Format the chart as follows Chart Title Rename to OFFICE COSTS EXPENSES Apply Bold Legend Remove it Chart Style Apply Style 2 Axis Labels Add a Primary Horizontal ads title called Office Expenses Add a Primary Vertical was title called Dollar Amounts 13 Based on only the monthly data for the Advertising Costs section, create a 2-Line chart Size the chart to Heights and Width 12" and move it to cell A47 Switch the Row Columns 2 Ebe Armenia Created On: 07/30/2021 Esco, en Proved Groete Points Possible Step Instructions 14 Format the chart as follows Chart Title Rename to Advertising Costs Legend Remove it Chart Style Apply Styles Trendline Add a Linear Trendline based on Series 4 Z Paragraph Styles 1234567891011121:13:14:|||||15 16 17 15 Based on the Percent of Total data for the Payroll section, create a 3-D Pie Chart. Move this 2 chart to a new sheet and rename it Payroll for First Two Quarters Move the chart sheet the end of your workbook and change the tab color to Black Text 1 (Column 2, Row 1 - Under Theme Colors) 4 16 Format the chart as follows: Chart Title Rename to Payroll for First Two Quarters 2021 Apply Bold Chart Style and Layout Apply Style 4 Apply Layout 8 Legend Ensure it is moved to the right side of the chart 3 17 Retum to your Actual Expense Sheet In cel J7 insert a Line Sparkline to show the trends of each Payroll expense for the first two quarters of the year. Duplicate this for Other Expenses (J35) and Service Costs (441) Format these Line Sparklines as follows Add High Point and Markers Apply the Red Sparkline Style Acoent 6 (no dark or light) (column 8. row 3) Change the High Point Marker Color to Light Blue (under standard colors) 18 10 In cell U14, insert a Column Sparkline to show the trends of each Office Costs for the first two quarters of the year. 3 20 Format this Column Sparkline as follows Add First Point Apply the Dark Green Sparkline Style Colorful #4 column 4. row 6) 21 In cell 320, insert a Win Loss Sparkline to show the trends of each Advertising Costs for the first two quarters of the year 3 22 Format this Win Loss Sparkline as follows Add Lost Point Apply the Blue Sparkline Style Accent Darker 50% column Brow 1) Change the Spardine Color to Dark Blue (under standard colors) Ei Emphasis Heading 1 Heading 2 1 Norma 23 Paragraph Styles DL120SEG 39.100.120613.. 14.04.15 0:16 Sheet2 contains a list of your clients who donated items and values to your charity drive you are organizing You want to make this list more appealing Change Company Narne to your Company Name Simultaneously freeze the first four rows and the first column Format your client list using the Green Table Style Light 12. Rename the table to Clieatlist and remove any duplicate records that match First and Last Name Insert a row above Row 50 Add your ClientID as 15. and enter your FirstName and Last Name. For the rest of the information, you can make up the information 25 For Table Style Options, add the First Column and Total Row 26 Make a copy of this worksheet. Rename it Donation List Sorted and move it to the right of Sheet2 tab. Change the tab color to Light Blue (under Standard Colors) 24 27 Change the Table Style to Green, Table Style Medium 5 (Column 5. Row 1 under Medium) Rename the Table Name to Saitedhist On the Donation List Sorted worksheet, apply a multiple sort to your list as follows Client-smallest to largest Last Name - A to Z. then by Category - Z to A, and then by Value - largest to smallest Apply the Purple Gradient Data Bar conditional formatting to the Value column Apply the conditional formatting Yellow Fill with Dark Yellow Text to all the text that equals Gift Certificate in the Category column. In the Total Row, total the Value column and count the First Name column 28 29 30 Return to Sheet2. apply the following filters: Display only those clients from ON Display only the values greater than 100 31 In the Total Row, average the gift values 32 Rename the Sheet1 tab to Quarterly Expenses and change the tab olour to Dark Blue (under Standard Colors) Rename the Sheet2 tab to Donation List Filtered and change the tab colour to Blue (under Standard Colors) ility Investinate 108210345 28.9 3 33 For all worksheets (not the Charts), add the following headers and footers Header Center Section Excel Assignment 3 Footer: Left Section Student Name (replace this with your name) Right Section: insert the Sheet Name code Excel Assignment 83. Visual Dat Created On: 07/30/2020 Excel 2010 Pro Grade-Instructions Points Possibl Instructions Step 2 34 For all worksheets (not the Charts), change all margins (except the Header Footer) to 0.5 N 35 Center horizontally and vertically on the page Change the page orientation for the Quarterly Expenses worksheet to Landscape Scale it to 1 page For the Donation List Filtered worksheet Set the Print Area to A4 L108 change the orientation to Landscape and scale to 1 page 4 36 5 37 For the Donation List Sorted worksheet, change the page orientation to Landscape Move the first vertical page break to the right of Category Move the horizontal page break to above Row 53 Return to Nommal View For printing purposes, repeat the first four rows 12.1.3: 456789 NUOUS 5 For the Donation List Sorted worksheet, change the page orientation to Landscape. Move the first vertical page break to the right of Category Move the horizontal pege break to above Row 53 Retum to Normal View For printing purposes, repeat the first four rows N On the Quarterly Expenses worksheet format your money numbers as Accounting and your Percent numbers as Peccents with 1 decimal place On both Donation List worksheets format your value column as Currency with no decimals Autofit your worksheets (not the Charts) to ensure all values are displayed. Save the file and submit the file Hum365_Excel Assign3_VisualData esix to MxLab IT as directed. You should have a total of FIVE worksheet tabs! Review your grade (it will be graded automatically). Once you are satisfied with your grade, you can close the file and the Excel application If you are not satisfied, you can fix anything in your workbook and resubmit to obtain a higher grade. You can resubmit up to 3 times, To help you compare your document, you can assignment example Hum365 Excel Assign3 VisualData Example.pd, which is a file that was downloaded with this assignment Total Points 100 Font 10 N Company Name Actual Expenses Sheet First Two Quarters of 2021 February June Trends May Total lan Percent of Total March April Payroll Weet Empayer Coronto CPP EsployerContribution for Employer Contribution Banats Tural Pay Ant Pyro Peres CPP 10 14 2.21N tenet 1:47 Office Costs Rentalon Business Pro Business Property Useat lectrical Water Delivery tapes Free Omegangsposte stationary Minorquiment Conta internet Supplies Secury Total Obice Costs Advertising Costs Website hosting Web site updates Sheet1 Sheet2 ao Interneta Supplies Security Total Once Consts Advertising Costs Website hosting Website updates Website Preparation Printing Marketing events Miscellaneous expenses Total Advertising Costs Other Expenses Insurance Goods and Services Tax Total Other Expenses Service Costs Train Costs Accounting Local Collection Consulting Total Service Costs Monthly Totals Company Name Charity Drive - Customer Donation List First Nanti Last Name Address Damon H 5019 N Newport Ave Ang kaso 11 Jericho C Sherman Varren 12 S Fremont Ave Robert Wheaton 235 EBay Dr Stephanie Lopez 25 Seyahat LIU Xiang 1502 Norastlake Mugie ibor 155 Elmrest Circle Ramon Ligonde 157 Beacon Palms PI Rown Find 2180 Lake Ellen Di Kendra LUC 2209 Sunset View Brian Whittier 2235 Est Ave Nathan Boston 7721 E Osbome Ave Ved Bagou 721E Lang Ave Benjamin Yovan 2010 E Frierson : yan Coleman 650N Himes Ave, 320 Janice Murphy 5100 C Woodwin Ave auren Pime 1571 Tuin Palms Boran Najmick 5810 Osceola PI Antonio Dejohet 1605 E Okaloosa Ave Brent Yung 231 Frison St Taylor 9708 Soaring Train they Schroeder 715 E Orleans Ave, 10 Brandon Hurd 8106 Mabrey Ave. E Hector Ortiz 3812 N O Vay "hillip Draper 1959 Th H Cemy Grant 5868 Bugwater Dr. #16D ci Cartey 65 N Hilton Ave, 1120 demy Chen 88 Anglers Pointer vonne Zhang 9900 Versalles Or ooke Parham 4917 E 42nd S Bne Mason 510 Voodlawn Ave Bruce 1920 S Lemon St Buce 1921 IVA Sheet1 Sheet2 City Provind Postal Co Phone Numb Emil Item Donated Value Chegory Vasaga Beach ON G9D405 (415) 555-0018 Damon Hilms.com Massage 50 Other Orangeville ON 12V5A2016)665.01 Ankaroogmail.com Hockey Tickets 75 Product Pickering ON YOB 9A416555-0122 Sherman Vangma.com MP3 Player 200 Equipment Tororo ON NJA 121 (415)555-0721 Robert Wheon@gmail.com Hotel Accommodations 100 Get Certificate Hamilton ON NOR121 141655 025. Stephanie Lopeng.com Hotel Accommodations 100 G Certi Colingvood ON TSG 7V (416) 655-0155 Xianggmail.com Restaurant Git Certifica 60 Get Certime Mark de ON HIGH4 (416) 555-015 Margelbokom acom CW Vash Cud 50 Other Vanaga Beach ON EB2A3 (416) 655.07 Runon Ligonde@gmail.com Automotive Gift Certifio 50 GR Certificate Toronto ON STHOF (416) 555-021 Kevin Feldml.com 45kg (b) of Fresh FG 100 Product Toronto ON TAN 736 (415) 555-022 Kendaluch@gmx.com Le Making Class 25 Other Toronto ON XIA 500 (495) 555-0721 Vittirgmailom Sabuchs Gift Card 10 Gert London Springs ON P73102 (495505-077 Nhan Bostonim.com Movie Tickets. Popoort 20 Product London Springs ON PICOV (451555-072 Ned Bayoum.com Free Photography Sitting 45 Service Toronto ON VEP OTO011555-0123 Benyamin Yovanomaloom Fondue Set 20 Product Vasaga Beach ON V5P OTO (416) 555 0654 Coleman@gmail.com Fondue Set 20 Product Souborough ON NZA M5 (416) 556-0512 Janke Murphyma.com Yoga Class 100 Service Scarborough ON EIC (416) 555-074. Len Palmer.com Gym Membership 260 Other Etobicoke ON AIE 509 (486) 555.058. Gotan Nymick@gmail.com Veb Site Services 100 Service Pickering ON NSV OH5 (415) 555-013 Antonio Dejonette@gmaile YMCA Swimming Lessol 65 Service Toronto ON EB 4X9 (811) 555.0129 Brem. Yunggmail.com Floral Altangement 75 Gift Certificate Etobicoke ON NEG2A2 (416) 555 0961 Anna Taylor@gmail.com Laptop Computer 1000 Equipment Toronto ON S4P 11 (416) 5550715 Ashley Schroeder@gmail.com Digital Camera 400 Equipment Scarborough ON V4B 472 (416) 555-0819 Brandon Hurd@gmail.com Grocery Gift Certificate 50 GR Corte Collingwood ON M5A 822 (455)555-030E Hectonigmail.com Restaurant ir Certificat 40 Gertificate Hamilton ON M4C556 (055) 555-005 Philip Daliper Bgmail.com 1 Year Free Collee 100 Product Trois-Rivieres G9D405 (812) 555-0061 Remy Grant@gmail.com Gift Basket 75 Product Saint-Jean-sur-Richeli ac J2W5A2 (418) 5550651 Rick Carrey@gmail.com Toy Store Gire Certificate 50 Gift Certificate Dawson YT YOB 9A9 (416) 555-08BE Jeremy Chen@gmail.com Automotive Gift Certifice 100 Gift Certifioste Brantford ON NA IZI (416) 565-039 Yvonne Zhang@gmail.com Restaurant Gift Certifio 75 Gift Certifione Branford ON NIA 121 (416) 555-0491 Brooke Parham@gmail.com MP3 Player 145 Equipment Edmonton AB T5G 7V (416) 555-0510 Jane Mason@gmail.com Gym Membership 200 Gift Certificate Montres OC H3G 1H4 (416) 555-092 Alendea Bruce@gmail.com Restaurant Got Certificat 45 Gift Certificate Etedeton MR FB2214161855-187: Henna Brice mail.com Bestur GCactic 15. Gift Certificate end O Espinosa Kerry 2100 Lake Ellen Di London 13 Estes Nicole 6722 Colonistid Riverview 0 FS Aaron 5094 Cozy Place London 1 Foley Amber 2048 Round Heights Regina Gvan Theodore 6800 Silver Shadow Gate Mock 3 Holden Lauren 6833 Silver Shadow Me Toronto 34 Irvin Connie 2058 Golden Close Toronto Jenkins David 6674 Quaking Alley Hall as 6 Jones Soon 4949 Green Farm Charlottetown 37 Kenter Jason 7779 Merry Deer End Whitehorse 18 Luch Matthew $887 Harvest Timber Gard Summerside 59 Martinez Eugene 1694 Red Apple Heath Steinbach 30 MoBride Jul 3280 Crystal Butterfly Drm. Lethbridge Nelson Shane 552 Thunder Promenade Brandon 52 Owens Tyler 699 Iron Branch Terrace Brandon 35 Reyes Michelle 698 Iron Branch Terrace Kelowna 54 Romero Melissa 530 Umber Grounds Vancouver 55 Shaw Debby 3453 Rustio Cloud Towntir Nanaimo 56 Switzer Nicholas 1000 Emerald Concession Barrie 57 Towers Jeffery 1108 Pleasant Dale Park Erobook 4 58 Trujillo Juan 2898 Cinder Vynd Pickering 55 Valdez Terni 5656 Dewy Sale Toronto RO Wang Chao-Jun 4126 Tawny Cicle Etobicoke 21 Wilborn Ryan 6450 Sleepy Sky Jetty Toronto 22 Dwyer Jack 2643 Middle Oak Forest Souborough 79 Brown Sara 8733 Hazy Trace Collingwood 74 Hamlin 2496 Lazy Nectar Tumabot Hamilton 25 Thomas Grant 623 Lost Rise London Springs To Marino Timothy 6110 Wishing Line Vasaga Beach T2 Chaplin Lon 2151 Broad Blossom Busy Brampton 78 Trent 4872 Sunny Log Abbey Mississaug TS Liston Frank 4327 Shady Crossing Mississauga 60 Nichols Cathy 6303 High Pointe Brampton 81 Brett Anna 6303 High Pointe Hamilton 82 Phelps 8500 Cotton Fox Passage Brampton Chaplin Lon 1421Easy Range Brampton 34 Brown Kurt 3982 Cotton Cider Com Brampton 35 Penninge Chad 6010 Iron Bank Mississauga Sheet1 Sheet2 ON NE ON SW BC ON ON NS PE YT PE ME MB MB BC BC BC ON ON ON ON ON ON ON ON ON ON ON ON ON ON ON ON a NSV OH56471605.002 Espinoza Kerry Dgmail.com prop Compu 200 G Certime EB 4X9 (547565.125 Etter Nicomail.com Dights Camera 45 Gift Certificate NGG2A 1647) 555.307 FC Aronoms.com Grocery Out Cartole 45 GiCalificate SAP 11 (6471655-120 Foley Amber@gmat.com Pestaurant Gut Certo 145 Equipment VB4224715523 Gallivan Theodore@gmail.com Free Coffee 40 GH Certoate MSA82216471656 207 Holden Lauren Bgmail.com Barket 30 Gint Certified MAC55671647/665099 Irwin Connema.com Rest Certificat 45 Gr Certifiome Y5F 6x0647 56572 Senki widmom MP3 Piger 145 Equipment CIC 45 (647) 555-937 Jones Soongmai.com Resturant Gilt Certi 40 GR Certificate YIA 1Y2 1647) 555-276 Kenwerton@gmail.com Movie Tickets.Popcort 30. Gut Certificate CIN7R4 (64755-013 Luck Mathewogmail.com Digital Camera 125 Equipment RSG SMZ (647555-446 Martinez Eugengmail.com Fondue Set 100 Product TIKONI (6471 555.726 MoBride Julie@gmail.com Yoga Class 75 Product B7C0Y51647) 555-837 Nelson Shangmail.com Gym Membership 50 GR Certificate F7C OYS. 1647555-873 Owens Tygmail.com Web Site Services 100 Git Certoate VIY 360 (647) 585 980 Reyes Michele@gmail.com YMCA Svimming Lesso 75 Out Certifica V7G853 (647)555-0011 Romero Melissa@gmail.com Floral Artangement 145 Equipment V3M U LED EDIT Bloom Laptop Computer 200 Gift Certificate L4MTI (647) 555-735 Switzer Nicholas@gmail.com Digital Camera 45 Gift Certoate Y5F6XO (647) 555-064 Towers Jeffery@gmail.com Grocery Gift Certificate 45 Get Certificate CIC 4P5 (647) 555-023 Trillo Juan@gmail.com Restaurant Gift Certific 5 Equipment VIA TV (647) 555-068 Valdez Terri@gmail.com 1 Yem Free Coifee 40 Gift Certificate CWM RA [47555 012 MargCho lui gmail,con st Parket 30 Certificate RSG BMW 16473555-673 Vibon yangmail.com Gym Membership 250 Other TKN1 1805) 555-307 Dyer Jack@gmail.com Web Site Services 100 Service R1C0Y5 (905) 555 983 Brown S.Margmail.com YMCA Swimming Lesson 65 Service R7C 0Y5 (905) 555-002 Harri Rosema.com Floral Arrangemen 75 Gift Certificate VIY 30 (905) 555-1254 Thomas Grant@gmail.com Laptop Computer 1000 Equipment V70 883 (905) 555-987 Marino Timothy@gmail.com Digital Camera 400 Equipment VSV EU (905) 65-120 Chaplog.com Grocery Gift Certificate 50 GR Certificate Restaurant Gift Certificat 40 Gift Certificate L4M6T1 (905) 555-238 Trent Maria@gmail.com G9D 405 (905) 555-287 Liston Frank@gmail.com 1 Year Free Colle 900 Product J2V5A2 (905) 555-039 Nichols Cathygmail.com Gift Basket 75 Product YOB SAS (905) 555-782 Brett Anna@gmail.com Toy Store Gift Certificate 50 Gift Certificate NBR 121 (905) 555-934 Phelps. Jeff@gmail.com Automotive Gift Certifio 100 Gift Certificate NR 121 (905) 555-276 Chaplin Lon@gmail.com 75 GR Certificate Restaurant Gift Certifica T5G 7 (905) 555-012 Brown Kurt@gmail.com MP3 Player 145 Equipment 200 Gift Certificate H3GH4 (805) 555-446 Pennington Chad@gmail.com Gym Membership Rose Maria ON ON ON ON Grader Instruction Excel 2019 Project Excel Assignment #3 - Visual Data 2 Project Description: It is time to calculate and visually analyze you company's expenses for the first two quarters of the year. In addition, you decided to setup a charity drive to help with the community and to help advertise your business. You created a list of clients who have successfully donated something to your charity drive, and you want to make the list more visually appealing Using Excel with the information you have already toamned and with the new tools you just learned in Topic 5 and Topic 6, completo the following assignment Stops to Perform Step Instructions Points Possible 1 Open the file named Hum365 Excel Assign VisualData.xis Grader has automatically 0 added your last name to the beginning of the Sename, so it should be easy to locate Ensure you have saved these files correctly within your File Management structure When you open the Excel Workbook, you will see two worksheet tabs at the bottom 2 Change the Workbook Thome to lon Boardroom, the colors to Slipstream, and the Fonts to 2 Century Gothic Sheet contains information about your business expenses. It is situp for the first two quarters of the year however you need to fill in the expense data Change Company Name to your Company Name 3 For the Payroll section, complete the following Collonge 87 67 type in how much woges you spend each month on your staff's pay. Your choice Accesibility investigate 09 English (United States 3 For the Payroll section, complete the following: Cell range B7 G7, type in how much wages you spend each month on your staff's pay. Your choice In cell B8, insert a formula to calculate the Employer Contribution for CPP, CPP is the Canadian Pension Plan and employers are responsible to pay some of its contribution. This is calculated by multiplying your monthly wages by the CPP Rate Copy this formula across to the other months. Remember to use proper absolute referencing. In cell B9, insert a formula to calculate the Employer Contribution for El Elis Employment Insurance, which helps those who lose a job or cannot find a job, and as a business owner, you are required to pay a certain percentage. This is calculated by multiplying your monthly wages by the El Rate. Copy this formula across to the other months. Remember to use proper absolute referencing. In cell B10, insert a formula to calculate the Employer Contribution for Benefits. Benefits are not offered by all companies, but they do help employees subsidize medical and dental payments. This is calculated by multiplying your monthly wages by the Benefits Rate. Copy this formula across to the other months. Remember to use proper absolute referencing In cell B11, insert a function to total each month for the Payroll section For the Office Costs section, complete the following: For all the Office Costs expenses, type in how much you spend on each expense for each month. Your choice In cell B23, insert a function to total each month for the Office Costs section Excel Assignment 3 Visual Data 2 Created On 07/30/2021 Paragraph 1234567 Styles 8 9 10 11 12 13 14. 1. 15:16 11:17 I 1 Points Possible Instructions Step 1 5 For the Advertising Costs section, complete the following For all the Advertising Costs expenses, type in how much you spend on each expense for each month. Your choice. In cel B32, insert a function to total each month for the Advertising Costs section. 8 For the Other Expenses section, complete the following For all the Other Expenses Costs expenses, type in how much you spend on each expense for each month. Your choice In cell 838, insert a function to total each month for the Other Expenses section 7 For the Service Costs section, complete the following: For all the Service Costs expenses, type in how much you spend on each expense for each month. Your choice In cell B43, insert a function to total each month for the Service Costs section I In cell B45, insert a function to add up each sections totals using the total cells for each section cu 5 5 10 In cell H7.insert a function to total each expense for each section. Starting in cell 17, insert a formula to calculate the Percent of Total for each expense within the different sections. This is calculated by dividing each item by the total of the entire section For example to calculate the Wages Percent of Total, you would divide the Wages total by the Total Payroll Amount. Remember to use absolute referencing where required Based on only the monthly data for Office Costs Section, create a 3-D Clustered Column chart Move this chart properly to a new sheet and rename it Office Costs Expenses. Move the new chart sheet to the end of your workbook to the right of Sheet2) Change the Tab Color to Orange, Accent 5 (Column Row 1 - Under Theme Colors) 2 11 12 Format the chart as follows Chart Title Rename to OFFICE COSTS EXPENSES Apply Bold Legend Remove it Chart Style Apply Style 2 Axis Labels Add a Primary Horizontal ads title called Office Expenses Add a Primary Vertical was title called Dollar Amounts 13 Based on only the monthly data for the Advertising Costs section, create a 2-Line chart Size the chart to Heights and Width 12" and move it to cell A47 Switch the Row Columns 2 Ebe Armenia Created On: 07/30/2021 Esco, en Proved Groete Points Possible Step Instructions 14 Format the chart as follows Chart Title Rename to Advertising Costs Legend Remove it Chart Style Apply Styles Trendline Add a Linear Trendline based on Series 4 Z Paragraph Styles 1234567891011121:13:14:|||||15 16 17 15 Based on the Percent of Total data for the Payroll section, create a 3-D Pie Chart. Move this 2 chart to a new sheet and rename it Payroll for First Two Quarters Move the chart sheet the end of your workbook and change the tab color to Black Text 1 (Column 2, Row 1 - Under Theme Colors) 4 16 Format the chart as follows: Chart Title Rename to Payroll for First Two Quarters 2021 Apply Bold Chart Style and Layout Apply Style 4 Apply Layout 8 Legend Ensure it is moved to the right side of the chart 3 17 Retum to your Actual Expense Sheet In cel J7 insert a Line Sparkline to show the trends of each Payroll expense for the first two quarters of the year. Duplicate this for Other Expenses (J35) and Service Costs (441) Format these Line Sparklines as follows Add High Point and Markers Apply the Red Sparkline Style Acoent 6 (no dark or light) (column 8. row 3) Change the High Point Marker Color to Light Blue (under standard colors) 18 10 In cell U14, insert a Column Sparkline to show the trends of each Office Costs for the first two quarters of the year. 3 20 Format this Column Sparkline as follows Add First Point Apply the Dark Green Sparkline Style Colorful #4 column 4. row 6) 21 In cell 320, insert a Win Loss Sparkline to show the trends of each Advertising Costs for the first two quarters of the year 3 22 Format this Win Loss Sparkline as follows Add Lost Point Apply the Blue Sparkline Style Accent Darker 50% column Brow 1) Change the Spardine Color to Dark Blue (under standard colors) Ei Emphasis Heading 1 Heading 2 1 Norma 23 Paragraph Styles DL120SEG 39.100.120613.. 14.04.15 0:16 Sheet2 contains a list of your clients who donated items and values to your charity drive you are organizing You want to make this list more appealing Change Company Narne to your Company Name Simultaneously freeze the first four rows and the first column Format your client list using the Green Table Style Light 12. Rename the table to Clieatlist and remove any duplicate records that match First and Last Name Insert a row above Row 50 Add your ClientID as 15. and enter your FirstName and Last Name. For the rest of the information, you can make up the information 25 For Table Style Options, add the First Column and Total Row 26 Make a copy of this worksheet. Rename it Donation List Sorted and move it to the right of Sheet2 tab. Change the tab color to Light Blue (under Standard Colors) 24 27 Change the Table Style to Green, Table Style Medium 5 (Column 5. Row 1 under Medium) Rename the Table Name to Saitedhist On the Donation List Sorted worksheet, apply a multiple sort to your list as follows Client-smallest to largest Last Name - A to Z. then by Category - Z to A, and then by Value - largest to smallest Apply the Purple Gradient Data Bar conditional formatting to the Value column Apply the conditional formatting Yellow Fill with Dark Yellow Text to all the text that equals Gift Certificate in the Category column. In the Total Row, total the Value column and count the First Name column 28 29 30 Return to Sheet2. apply the following filters: Display only those clients from ON Display only the values greater than 100 31 In the Total Row, average the gift values 32 Rename the Sheet1 tab to Quarterly Expenses and change the tab olour to Dark Blue (under Standard Colors) Rename the Sheet2 tab to Donation List Filtered and change the tab colour to Blue (under Standard Colors) ility Investinate 108210345 28.9 3 33 For all worksheets (not the Charts), add the following headers and footers Header Center Section Excel Assignment 3 Footer: Left Section Student Name (replace this with your name) Right Section: insert the Sheet Name code Excel Assignment 83. Visual Dat Created On: 07/30/2020 Excel 2010 Pro Grade-Instructions Points Possibl Instructions Step 2 34 For all worksheets (not the Charts), change all margins (except the Header Footer) to 0.5 N 35 Center horizontally and vertically on the page Change the page orientation for the Quarterly Expenses worksheet to Landscape Scale it to 1 page For the Donation List Filtered worksheet Set the Print Area to A4 L108 change the orientation to Landscape and scale to 1 page 4 36 5 37 For the Donation List Sorted worksheet, change the page orientation to Landscape Move the first vertical page break to the right of Category Move the horizontal page break to above Row 53 Return to Nommal View For printing purposes, repeat the first four rows 12.1.3: 456789 NUOUS 5 For the Donation List Sorted worksheet, change the page orientation to Landscape. Move the first vertical page break to the right of Category Move the horizontal pege break to above Row 53 Retum to Normal View For printing purposes, repeat the first four rows N On the Quarterly Expenses worksheet format your money numbers as Accounting and your Percent numbers as Peccents with 1 decimal place On both Donation List worksheets format your value column as Currency with no decimals Autofit your worksheets (not the Charts) to ensure all values are displayed. Save the file and submit the file Hum365_Excel Assign3_VisualData esix to MxLab IT as directed. You should have a total of FIVE worksheet tabs! Review your grade (it will be graded automatically). Once you are satisfied with your grade, you can close the file and the Excel application If you are not satisfied, you can fix anything in your workbook and resubmit to obtain a higher grade. You can resubmit up to 3 times, To help you compare your document, you can assignment example Hum365 Excel Assign3 VisualData Example.pd, which is a file that was downloaded with this assignment Total Points 100