Home Insert Draw Page Layout Formulas D A1 4 x fx Vin # Prius 2009 A B C 1 Vin I Make Model 2 2906934387 BMW 5 Series 3 6393487147 Honda Civic 4 9356019403 Toyota 58000086254 BMW 5 Series 6 9340076442 BMW 5 Series 7 7433901231 Chevy Malibu 8 1328395941 Chevy Malibu 9 8137529693 BMW 5 Series 10 9650039042 Ford F-150 11 1344751603 Toyota Prius 12 3732185591 BMW 5 Series 13 8251440185 Ford F-150 14 9414505869 Toyota Prius 15 4942016124 Ford F-150 16 4094989639 BMW 5 Series 17 4700777743 BMW 5 Series 18 1380128961 BMW 5 Series 19 3860813150 Ford F-150 20 2069016783 Honda Civic 21 2488607421 BMW 5 Series 22 7541058788 Chevy Malibu 23 3345743062 Ford F-150 24 1889854267 BMW 5 Series 25 8131908841 BMW S Series 26 7679546980 BMW 5 Series 27 1360867906 Chevy Malibu 28 5739567111 Honda Civic 29 6007144763 BMW 5 Series 30 3695501103 Ford F-150 31 2482451414 Ford F-150 32 7397531553 BMW 5 Series 33 3133070112 Honda Civic 34 5322014014 Ford F-150 35 5726149503 Ford F-150 36 4820173885 Ford F-150 37 2706112648 BMW 5 Series 38 7377188604 Ford F-150 39 5293323010 Honda Civic 40 2406189439 Honda Civic 41 3578026701 Honda Civic 42 1462643171 Toyota Prius 43 5122993766 Toyota Prius 44 7092958553 Chevy Malibu 45 8156049535 Honda Civic 46 9305933928 Ford F-150 47 9537026620 Toyota Prius 48 8019012354 BMW 5 Series 49 2727121774 Thus PRIRE Fleet Information D E F Year Mileage Sticker Price 2015 25709 $ 30,000.00 2018 26609 $ 30,000.00 2000 96905 $ 22,000.00 2017 28681 $ 30,000.00 2007 18384 $ 34,000.00 2010 37085 $ 27,000.00 2002 48868 $ 25,000.00 2001 61480 $ 22,000.00 2012 28541 $ 30,000.00 2004 42890 $ 25,000.00 2001 36595 $ 27,000.00 2011 76254 $ 22,000.00 2004 21380 $ 30,000.00 2006 63737 $ 22,000.00 2017 92048 $ 22,000.00 2017 44667 $ 25,000.00 2007 11158 $ 34,000.00 14095 $ 34,000.00 2014 34428 $ 27,000.00 2008 68172 $ 22,000.00 2011 77099 $ 22,000.00 2007 31562 $ 27,000.00 2012 14427 $ 34,000.00 2004 14966 $ 34,000.00 2010 7877 $ 40,000.00 2004 70797 $ 22,000.00 2013 99041 $ 22,000.00 2008 95344 $ 22,000.00 2015 99021 $ 22,000.00 2014 83281 $ 22,000.00 2018 36000 $ 27,000.00 2009 46323 $ 25,000.00 2011 46216 $ 25,000.00 2015 6971 S 40,000.00 2016 6921 $ 40,000.00 2012 22089 $ 30,000.00 2016 52774 $ 22,000.00 2001 30840 $ 27,000.00 2010 49544 $ 25,000.00 2003 34825 $ 27,000.00 2006 8887 $ 40,000.00 84930 S 22,000.00 2014 11654 $ 34,000.00 2005 3997 S 40,000.00 2011 40807 $ 25,000.00 2008 27785 5 30,000.00 2017 50120 S 22,000.00 26A77 nnnnn Sales Information First Qu 2014 AutoSave Home Formulas C Insert Draw Page Layout A x V fx Vin # A1 B c D E 48 8019012354 BMW 49 3737821324 Toyota 50 7596630806 Ford 51 5718003239 BMW 52 8010477805 BMW 53 3814357405 Chevy 54 1213463078 Toyota 55 1563229851 Chevy 56 9027521957 Ford 57 8893900890 BMW 58 7454528548 BMW 59 8054683334 Chevy 60 5742570103 BMW 61 8163497575 Ford 62 5276867505 Honda 63 1409363873 Chevy 64 1089138532 BMW 65 6015917869 Toyota 66 9885757629 Ford 67 5306541595 BMW 68 2747847524 Chevy 69 7718052943 BMW 70 2976194513 Toyota 71 8281760371 Chevy 72 4730270762 Honda 73 5161605236 Honda 74 7473005747 BMW 75 6891374035 Honda 76 5463769134 Ford 77 3867060534 Honda 78 3849719202 BMW 79 7200319445 Honda 80 8725850239 Honda 81 5903493846 BMW 82 2373147058 BMW 83 6161188244 BMW 84 4913951544 Chevy 85 8806645278 Honda 86 5250708704 BMW 87 8357455137 Ford 88 4370047837 Toyota 89 6290615001 Honda 90 8279091856 Honda 91 1218797573 Ford 92 1218797573 Ford 93 2700353328 BMW 94 6729292004 Ford 95 9622942163 BMW 5 Series Prius F-150 5 Series 5 Series Malibu Prius Malibu F-150 5 Series 5 Series Malibu 5 Series F-150 Civic Malibu 5 Series Prius F-150 5 Series Malibu 5 Series Prius Malibu Civic Civic 5 Series Civic F-150 Civic 5 Series Civic Civic 5 Series 5 Series 5 Series Malibu Civic 5 Series F-150 Prius Civic Civic F-150 F150 5 Series F-150 2017 50120 $ 22,000.00 2001 35426 $ 27,000.00 2003 81699 $ 22,000.00 2016 39202 $ 27,000.00 2009 50527 $ 22,000.00 2016 54060 $ 22,000.00 2000 93430 $ 22,000.00 2002 13764 $ 34,000.00 2002 90009 $ 22,000.00 2006 79293 $ 22,000.00 2002 37753 $ 27,000.00 2012 36934 $ 27,000.00 2011 78360 $ 22,000.00 2004 91310 $ 22,000.00 2018 39645 $ 27,000.00 2004 80047 $ 22,000.00 2012 95298 $ 22,000.00 2001 87698 $ 22,000.00 2004 19686 $ 34,000.00 2013 81096 $ 22,000.00 2014 50650 $ 22,000.00 2013 55145 $ 22,000.00 2003 27493 $ 30,000.00 2012 44985 $ 25,000.00 2007 8541 $ 40,000.00 2013 31271 $ 27,000.00 2003 41747 $ 25,000.00 2003 75313 $ 22,000.00 2012 99161 $ 22,000.00 2003 52586 $ 22,000.00 2006 33661 $ 27,000.00 2005 23147 $ 30,000.00 2016 57432 $ 22,000.00 2001 91463 $ 22,000.00 2009 40492 $ 25,000.00 2012 65420 $ 22,000.00 2003 40380 $ 25,000.00 2012 76217 $ 22,000.00 2006 61969 $ 22,000.00 2016 17398 $ 34,000.00 2012 38800 5 27,000.00 2009 96345 5 22,000.00 2003 4129 $ 40,000.00 2010 11098 5 34,000.00 2010 11098 5 34,000.00 2004 97812 5 22,000.00 2001 63166 5 22.000.00 2009 57736 $ 22.000.00 Fleet Information Sales Information First A B C 6827470475224 Chevy Tandu 69 7718062943 BMW 5 Series 70 2976194513 Toyota Prius 71 8281760371 Chevy Malibu 72 4730270762 Honda Civic 73 5161605236 Honda Civic 74 7473005747 BMW 5 Series 75 6891374035 Honda Civic 76 5463769134 Ford F-150 77 3867060534 Honda Civic 78 3849719202 BMW 5 Series 79 7200319445 Honda Civic 80 8725850239 Honda Civic 81 5903493846 BMW 5 Series 82 2373147058 BMW 5 Series 83 6161188244 BMW 5 Series 84 4913951544 Chevy Malibu 85 8806645278 Honda Civic 86 5250708704 BMW 5 Series 87 8357455137 Ford F-150 88 4370047837 Toyota Prius 89 6290615001 Honda Civic 90 8279091856 Honda Civic 91 1218797573 Ford F-150 92 1218797573 Ford F-150 93 2700853328 BMW 5 Series 94 6729292004 Ford F-150 95 9622942163 BMW 5 Series 96 1532392596 Honda Civic 97 1532392596 Honda Civic 98 6851036813 Toyota Prius 99 5095450285 Ford F-150 100 1531278978 Honda Civic 101 4174658862 Ford F-150 102 4174658862 Ford F-150 103 6556316751 Ford F-150 104 6287671745 Honda Civic 105 106 107 D E F 2014 SU650 22,0UU.UU 2013 55145 $ 22,000.00 2003 27493 $ 30,000.00 2012 44985 $ 25,000.00 2007 8541 $ 40,000.00 2013 31271 $ 27,000.00 2003 41747 $ 25,000.00 2003 75313 $ 22,000.00 2012 99161 $ 22,000.00 2003 52586 $ 22,000.00 2006 33661 $ 27,000.00 2005 23147 $ 30,000.00 2016 57432 $ 22,000.00 2001 91463 $ 22,000.00 2009 40492 $ 25,000.00 2012 65420 $ 22,000.00 2003 40380 $ 25,000.00 2012 76217 $ 22,000.00 2006 61969 $ 22,000.00 2016 17398 $ 34,000.00 2012 38800 $ 27,000.00 2009 96345 $ 22,000.00 2003 4129 $ 40,000.00 2010 11098 $ 34,000.00 2010 11098 $ 34,000.00 2004 97812 $ 22,000.00 2001 63166 $ 22,000.00 2000 57736 $ 22,000.00 2018 3308 $ 40,000.00 2018 3308 $ 40,000.00 2013 79226 $ 22,000.00 2018 70078 $ 22,000.00 2018 82011 $ 22,000.00 2009 54406 $ 22,000.00 2009 54406 $ 22,000.00 2016 58076 $ 22,000.00 2007 49295 $ 25,000.00 + 13 fx NN A B D Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/18 $ 30,000.00 $ 30,000.00 36393487147 9/27/18 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/18 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/18 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/18 $ 34,000.00 $ 27,200.00 77433901231 4/21/18 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/18 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/18 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/18 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/18 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/18 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/18 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/18 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/18 $ 22,000.00 $ 15,400.00 16 4094989639 9/20/18 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/18 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/18 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/18 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/18 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/18 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/18 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/18 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/18 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/18 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/18 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/18 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/18 S 22,000.00 $ 22,000.00 29 6007144763 10/26/18 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/18 $ 22,000.00 $ 22,000.00 31 32 mm B C D E 1 Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/18 $ 30,000.00 $30,000.00 3 6393487147 9/27/18 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/18 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/18 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/18 $ 34,000.00 $ 27,200.00 7 7433901231 4/21/18 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/18 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/18 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/18 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/18 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/18 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/18 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/18 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/18 $ 22,000.00 $ 15,400.00 164094989639 9/20/18 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/18 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/18 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/18 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/18 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/18 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/18 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/18 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/18 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/18 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/18 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/18 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/18 $ 22,000.00 $ 22,000.00 29 | 6007144763 10/26/18 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/18 S 22,000.00 $ 22,000.00 31 Total $ 785,000.00 $ 649,300.00 % of sticker 100% 9056 60% 9096 80% 60% 70% 90% 9096 100% 70% 9056 100% 70% 60% 9056 60% 90% 100% 9056 80% 80% 80% 70% 70% 100% 100% 80% 100% 83% 1 0 2 5 10 3 4 5 5 6 5 Open the downloaded file exploring_604_grader_h1.xlsx Freeze the first row on the Fleet Information workshoot. Convert the data to a table, name the table Inventory, and apply the Table Stylo Medium 19. Remove duplicato records Sort the table by Make in alphabetical order, add a second level to sort by Year Smallest to Largest, and a third level to sort by Sticker Price Smallest to Largest. Repeat the field names on all pages. Change page breaks so each vehicle make is printed on a separate page. Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side. Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Table Style Dark 11. Type % of sticker in coll E1 Create a formula with structured references to calculate the percentage of the Sticker Price in 7 5 8 6 9 10 10 2. 11 5 column E 12 UR 13 6 0 14 15 8 16 8 Format the range E2:E30 with Percent Style Number Format. Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price AutoFit the width of columns BE to show the total values, Apply Sold Fill Blue Data Bars conditional formatting to the % of sticker data. Select the range E2 E30 and create a new conditional formatting rule that uses a formula to apply yellow fill and bold font to values that sold for less than or equal to 70% of the sticker price. Be sure to select a Rule Type that will use a formula to determine which calls to format. On the First Quarter Sales worksheet, rename the table EartQuarter Fiter the data to display January, February, and March sales Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side 17 18 0 0 6 19 Created On: 07/05/2019 XL 16 CHO Rockville Auto Sales CAP V3 Grader - Instructions Excel 2016 Step Instructions Points Possible 20 5 Select Landscape orientation for all shoots 21 Save and close the workbook Submit the workbook as directed Home Insert Draw Page Layout Formulas D A1 4 x fx Vin # Prius 2009 A B C 1 Vin I Make Model 2 2906934387 BMW 5 Series 3 6393487147 Honda Civic 4 9356019403 Toyota 58000086254 BMW 5 Series 6 9340076442 BMW 5 Series 7 7433901231 Chevy Malibu 8 1328395941 Chevy Malibu 9 8137529693 BMW 5 Series 10 9650039042 Ford F-150 11 1344751603 Toyota Prius 12 3732185591 BMW 5 Series 13 8251440185 Ford F-150 14 9414505869 Toyota Prius 15 4942016124 Ford F-150 16 4094989639 BMW 5 Series 17 4700777743 BMW 5 Series 18 1380128961 BMW 5 Series 19 3860813150 Ford F-150 20 2069016783 Honda Civic 21 2488607421 BMW 5 Series 22 7541058788 Chevy Malibu 23 3345743062 Ford F-150 24 1889854267 BMW 5 Series 25 8131908841 BMW S Series 26 7679546980 BMW 5 Series 27 1360867906 Chevy Malibu 28 5739567111 Honda Civic 29 6007144763 BMW 5 Series 30 3695501103 Ford F-150 31 2482451414 Ford F-150 32 7397531553 BMW 5 Series 33 3133070112 Honda Civic 34 5322014014 Ford F-150 35 5726149503 Ford F-150 36 4820173885 Ford F-150 37 2706112648 BMW 5 Series 38 7377188604 Ford F-150 39 5293323010 Honda Civic 40 2406189439 Honda Civic 41 3578026701 Honda Civic 42 1462643171 Toyota Prius 43 5122993766 Toyota Prius 44 7092958553 Chevy Malibu 45 8156049535 Honda Civic 46 9305933928 Ford F-150 47 9537026620 Toyota Prius 48 8019012354 BMW 5 Series 49 2727121774 Thus PRIRE Fleet Information D E F Year Mileage Sticker Price 2015 25709 $ 30,000.00 2018 26609 $ 30,000.00 2000 96905 $ 22,000.00 2017 28681 $ 30,000.00 2007 18384 $ 34,000.00 2010 37085 $ 27,000.00 2002 48868 $ 25,000.00 2001 61480 $ 22,000.00 2012 28541 $ 30,000.00 2004 42890 $ 25,000.00 2001 36595 $ 27,000.00 2011 76254 $ 22,000.00 2004 21380 $ 30,000.00 2006 63737 $ 22,000.00 2017 92048 $ 22,000.00 2017 44667 $ 25,000.00 2007 11158 $ 34,000.00 14095 $ 34,000.00 2014 34428 $ 27,000.00 2008 68172 $ 22,000.00 2011 77099 $ 22,000.00 2007 31562 $ 27,000.00 2012 14427 $ 34,000.00 2004 14966 $ 34,000.00 2010 7877 $ 40,000.00 2004 70797 $ 22,000.00 2013 99041 $ 22,000.00 2008 95344 $ 22,000.00 2015 99021 $ 22,000.00 2014 83281 $ 22,000.00 2018 36000 $ 27,000.00 2009 46323 $ 25,000.00 2011 46216 $ 25,000.00 2015 6971 S 40,000.00 2016 6921 $ 40,000.00 2012 22089 $ 30,000.00 2016 52774 $ 22,000.00 2001 30840 $ 27,000.00 2010 49544 $ 25,000.00 2003 34825 $ 27,000.00 2006 8887 $ 40,000.00 84930 S 22,000.00 2014 11654 $ 34,000.00 2005 3997 S 40,000.00 2011 40807 $ 25,000.00 2008 27785 5 30,000.00 2017 50120 S 22,000.00 26A77 nnnnn Sales Information First Qu 2014 AutoSave Home Formulas C Insert Draw Page Layout A x V fx Vin # A1 B c D E 48 8019012354 BMW 49 3737821324 Toyota 50 7596630806 Ford 51 5718003239 BMW 52 8010477805 BMW 53 3814357405 Chevy 54 1213463078 Toyota 55 1563229851 Chevy 56 9027521957 Ford 57 8893900890 BMW 58 7454528548 BMW 59 8054683334 Chevy 60 5742570103 BMW 61 8163497575 Ford 62 5276867505 Honda 63 1409363873 Chevy 64 1089138532 BMW 65 6015917869 Toyota 66 9885757629 Ford 67 5306541595 BMW 68 2747847524 Chevy 69 7718052943 BMW 70 2976194513 Toyota 71 8281760371 Chevy 72 4730270762 Honda 73 5161605236 Honda 74 7473005747 BMW 75 6891374035 Honda 76 5463769134 Ford 77 3867060534 Honda 78 3849719202 BMW 79 7200319445 Honda 80 8725850239 Honda 81 5903493846 BMW 82 2373147058 BMW 83 6161188244 BMW 84 4913951544 Chevy 85 8806645278 Honda 86 5250708704 BMW 87 8357455137 Ford 88 4370047837 Toyota 89 6290615001 Honda 90 8279091856 Honda 91 1218797573 Ford 92 1218797573 Ford 93 2700353328 BMW 94 6729292004 Ford 95 9622942163 BMW 5 Series Prius F-150 5 Series 5 Series Malibu Prius Malibu F-150 5 Series 5 Series Malibu 5 Series F-150 Civic Malibu 5 Series Prius F-150 5 Series Malibu 5 Series Prius Malibu Civic Civic 5 Series Civic F-150 Civic 5 Series Civic Civic 5 Series 5 Series 5 Series Malibu Civic 5 Series F-150 Prius Civic Civic F-150 F150 5 Series F-150 2017 50120 $ 22,000.00 2001 35426 $ 27,000.00 2003 81699 $ 22,000.00 2016 39202 $ 27,000.00 2009 50527 $ 22,000.00 2016 54060 $ 22,000.00 2000 93430 $ 22,000.00 2002 13764 $ 34,000.00 2002 90009 $ 22,000.00 2006 79293 $ 22,000.00 2002 37753 $ 27,000.00 2012 36934 $ 27,000.00 2011 78360 $ 22,000.00 2004 91310 $ 22,000.00 2018 39645 $ 27,000.00 2004 80047 $ 22,000.00 2012 95298 $ 22,000.00 2001 87698 $ 22,000.00 2004 19686 $ 34,000.00 2013 81096 $ 22,000.00 2014 50650 $ 22,000.00 2013 55145 $ 22,000.00 2003 27493 $ 30,000.00 2012 44985 $ 25,000.00 2007 8541 $ 40,000.00 2013 31271 $ 27,000.00 2003 41747 $ 25,000.00 2003 75313 $ 22,000.00 2012 99161 $ 22,000.00 2003 52586 $ 22,000.00 2006 33661 $ 27,000.00 2005 23147 $ 30,000.00 2016 57432 $ 22,000.00 2001 91463 $ 22,000.00 2009 40492 $ 25,000.00 2012 65420 $ 22,000.00 2003 40380 $ 25,000.00 2012 76217 $ 22,000.00 2006 61969 $ 22,000.00 2016 17398 $ 34,000.00 2012 38800 5 27,000.00 2009 96345 5 22,000.00 2003 4129 $ 40,000.00 2010 11098 5 34,000.00 2010 11098 5 34,000.00 2004 97812 5 22,000.00 2001 63166 5 22.000.00 2009 57736 $ 22.000.00 Fleet Information Sales Information First A B C 6827470475224 Chevy Tandu 69 7718062943 BMW 5 Series 70 2976194513 Toyota Prius 71 8281760371 Chevy Malibu 72 4730270762 Honda Civic 73 5161605236 Honda Civic 74 7473005747 BMW 5 Series 75 6891374035 Honda Civic 76 5463769134 Ford F-150 77 3867060534 Honda Civic 78 3849719202 BMW 5 Series 79 7200319445 Honda Civic 80 8725850239 Honda Civic 81 5903493846 BMW 5 Series 82 2373147058 BMW 5 Series 83 6161188244 BMW 5 Series 84 4913951544 Chevy Malibu 85 8806645278 Honda Civic 86 5250708704 BMW 5 Series 87 8357455137 Ford F-150 88 4370047837 Toyota Prius 89 6290615001 Honda Civic 90 8279091856 Honda Civic 91 1218797573 Ford F-150 92 1218797573 Ford F-150 93 2700853328 BMW 5 Series 94 6729292004 Ford F-150 95 9622942163 BMW 5 Series 96 1532392596 Honda Civic 97 1532392596 Honda Civic 98 6851036813 Toyota Prius 99 5095450285 Ford F-150 100 1531278978 Honda Civic 101 4174658862 Ford F-150 102 4174658862 Ford F-150 103 6556316751 Ford F-150 104 6287671745 Honda Civic 105 106 107 D E F 2014 SU650 22,0UU.UU 2013 55145 $ 22,000.00 2003 27493 $ 30,000.00 2012 44985 $ 25,000.00 2007 8541 $ 40,000.00 2013 31271 $ 27,000.00 2003 41747 $ 25,000.00 2003 75313 $ 22,000.00 2012 99161 $ 22,000.00 2003 52586 $ 22,000.00 2006 33661 $ 27,000.00 2005 23147 $ 30,000.00 2016 57432 $ 22,000.00 2001 91463 $ 22,000.00 2009 40492 $ 25,000.00 2012 65420 $ 22,000.00 2003 40380 $ 25,000.00 2012 76217 $ 22,000.00 2006 61969 $ 22,000.00 2016 17398 $ 34,000.00 2012 38800 $ 27,000.00 2009 96345 $ 22,000.00 2003 4129 $ 40,000.00 2010 11098 $ 34,000.00 2010 11098 $ 34,000.00 2004 97812 $ 22,000.00 2001 63166 $ 22,000.00 2000 57736 $ 22,000.00 2018 3308 $ 40,000.00 2018 3308 $ 40,000.00 2013 79226 $ 22,000.00 2018 70078 $ 22,000.00 2018 82011 $ 22,000.00 2009 54406 $ 22,000.00 2009 54406 $ 22,000.00 2016 58076 $ 22,000.00 2007 49295 $ 25,000.00 + 13 fx NN A B D Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/18 $ 30,000.00 $ 30,000.00 36393487147 9/27/18 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/18 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/18 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/18 $ 34,000.00 $ 27,200.00 77433901231 4/21/18 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/18 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/18 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/18 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/18 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/18 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/18 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/18 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/18 $ 22,000.00 $ 15,400.00 16 4094989639 9/20/18 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/18 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/18 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/18 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/18 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/18 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/18 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/18 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/18 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/18 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/18 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/18 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/18 S 22,000.00 $ 22,000.00 29 6007144763 10/26/18 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/18 $ 22,000.00 $ 22,000.00 31 32 mm B C D E 1 Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/18 $ 30,000.00 $30,000.00 3 6393487147 9/27/18 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/18 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/18 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/18 $ 34,000.00 $ 27,200.00 7 7433901231 4/21/18 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/18 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/18 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/18 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/18 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/18 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/18 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/18 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/18 $ 22,000.00 $ 15,400.00 164094989639 9/20/18 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/18 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/18 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/18 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/18 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/18 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/18 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/18 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/18 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/18 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/18 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/18 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/18 $ 22,000.00 $ 22,000.00 29 | 6007144763 10/26/18 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/18 S 22,000.00 $ 22,000.00 31 Total $ 785,000.00 $ 649,300.00 % of sticker 100% 9056 60% 9096 80% 60% 70% 90% 9096 100% 70% 9056 100% 70% 60% 9056 60% 90% 100% 9056 80% 80% 80% 70% 70% 100% 100% 80% 100% 83% 1 0 2 5 10 3 4 5 5 6 5 Open the downloaded file exploring_604_grader_h1.xlsx Freeze the first row on the Fleet Information workshoot. Convert the data to a table, name the table Inventory, and apply the Table Stylo Medium 19. Remove duplicato records Sort the table by Make in alphabetical order, add a second level to sort by Year Smallest to Largest, and a third level to sort by Sticker Price Smallest to Largest. Repeat the field names on all pages. Change page breaks so each vehicle make is printed on a separate page. Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side. Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Table Style Dark 11. Type % of sticker in coll E1 Create a formula with structured references to calculate the percentage of the Sticker Price in 7 5 8 6 9 10 10 2. 11 5 column E 12 UR 13 6 0 14 15 8 16 8 Format the range E2:E30 with Percent Style Number Format. Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price AutoFit the width of columns BE to show the total values, Apply Sold Fill Blue Data Bars conditional formatting to the % of sticker data. Select the range E2 E30 and create a new conditional formatting rule that uses a formula to apply yellow fill and bold font to values that sold for less than or equal to 70% of the sticker price. Be sure to select a Rule Type that will use a formula to determine which calls to format. On the First Quarter Sales worksheet, rename the table EartQuarter Fiter the data to display January, February, and March sales Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side 17 18 0 0 6 19 Created On: 07/05/2019 XL 16 CHO Rockville Auto Sales CAP V3 Grader - Instructions Excel 2016 Step Instructions Points Possible 20 5 Select Landscape orientation for all shoots 21 Save and close the workbook Submit the workbook as directed