Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel.	0
2	Freeze the first row on the Fleet Information worksheet.	5
3	Convert the data to a table, name the table Inventory, and apply the Gold, Table Style Medium 19.	10
4	Remove duplicate records.	3
5	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.	5
6	Repeat the field names on all pages.	5
7	Change page breaks so each vehicle make is printed on a separate page.	5
8	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.	6
9	Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Green, Table Style Dark 11.	10
10	Type % of sticker in cell E1.	2
11	Create a formula with structured references to calculate the percentage of the Sticker Price in column E.	5
12	Format the range E2:E30 with Percent Style Number Format.	5
13	Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price.	7
14	AutoFit the width of columns B:E to show the total values.	0
15	Select the range E2:E30. Apply Solid Fill Blue Data Bars conditional formatting to the % of sticker data.	8
16	With the range E2:E30 selected, 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.	5
17	On the First Quarter Sales worksheet, rename the table FirstQuarter.	2
18	Filter the data to display January, February, and March sales.	6

19	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.	6
20	Select Landscape orientation for all sheets.	5

please use clear steps

Vin # Make Model Year Mileage Sticker Price 2 2906934387 BMW 5 Series 2015 25709 $ 30,000.00 3 6393487147 Honda Civic 2018 26609 $ 30,000.00 4 9356019403 Toyota Prius 2000 96905 22,000.00 5 8000086254 BMW 5 Series 2017 28681 $ 30,000.00 6 9340076442 BMW 5 Series 2007 18384 34,000.00 7 7433901231 Chevy Malibu 2010 37085 $ 27,000.00 8 1328395941 Chevy Malibu 2002 48868 $ 25,000.00 9 8137529693 BMW 5 Series 2001 61480 $ 22,000.00 10 9650039042 Ford F-150 2012 28541 $ 30,000.00 11 1344751603 Toyota Prius 2004 42890 25,000.00 12 3732185591 BMW 5 Series 2001 36595 $ 27,000.00 13 8251440185 Ford F-150 2011 76254 $ 22,000.00 14 9414505869 Toyota Prius 2004 21380 30,000.00 15 4942016124 Ford F-150 2006 63737 $ 22,000.00 16 4094989639 BMW 5 Series 2017 92048 $ 22,000.00 17 4700777743 BMW 5 Series 2017 44667 25,000.00 18 1380128961 BMW 5 Series 2007 11158 $ 34,000.00 19 3860813150 Ford F-150 2009 14095 $ 34,000.00 20 2069016783 Honda Civic 2014 34428 $ 27,000.00 21 2488607421 BMW 5 Series 2008 68172 $ 22,000.00 22 7541058788 Chevy Malibu 2011 77099 $ 22,000.00 23 3345743062 Ford F-150 2007 31562 27,000.00 24 1889854267 BMW 5 Series 2012 14427 $ 34,000.00 25 8131908841 BMW 5 Series 2004 14966 34,000.00 26 7679546980 BMW 5 Series 2010 7877 40,000.00 27 1360867906 Chevy Malibu 2004 70797 $ 22,000.00 28 5739567111 Honda Civic 2013 99041 $ 22,000.00 29 6007144763 BMW 5 Series 2008 95344 $ 22,000.00 30 3695501103 Ford F-150 2015 99021 $ 22,000.00 31 2482451414 Ford F-150 2014 83281 22,000.00 32 7397531553 BMW 5 Series 2018 36000 $ 27,000.00 33 3133070112 Honda Civic 2009 46323 $ 25,000.00 34 5322014014 Ford F-150 2011 46216 25,000.00 35 5726149503 Ford F-150 2015 6971 $ 40,000.00 36 4820173885 Ford F-150 2016 6921 40,000.00 37 2706112648 BMW 5 Series 2012 22089 30,000.00 38 7377188604 Ford F-150 2016 52774 $ 22,000.00 39 5293323010 Honda Civic 2001 30840 27,000.00 40 2406189439 Honda Civic 2010 49544 25,000.00 41 3578026701 Honda Civic 2003 34825 $ 27,000.00 42 1462643171 Toyota 2006 8887 40,000.00 43 5122993766 Toyota Prius 2014 84930 $ 22,000.00 44 7092958553 Chevy Malibu 2014 11654 $ 34,000.00 45 8156049535 Honda Civic 2005 3997 $ 40.000.00 Fleet Information Sales Information Prius First Quarter Sales 3 1 Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/21 $ 30,000.00 $ 30,000.00 3 6393487147 9/27/21 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/21 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/21 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/21 $ 34,000.00 $ 27,200.00 7 7433901231 4/21/21 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/21 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/21 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/21 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/21 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/21 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/21 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/21 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/21 $ 22,000.00 $ 15,400.00 16 4094989639 9/20/21 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/21 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/21 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/21 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/21 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/21 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/21 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/21 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/21 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/21 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/21 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/21 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/21 $ 22,000.00 $ 22,000.00 29 6007144763 10/26/21 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/21 $ 22,000.00 $ 22,000.00 21 Vin # Date sold Sticker Price Sale Price % of sticker 100% 90% 60% 90% 80% 60% 70% 90% 90% 100% 70% 90% 100% 70% 60% 2 2906934387 3 6393487147 4 9356019403 5 8000086254 6 9340076442 7 7433901231 8 1328395941 9 8137529693 10 9650039042 11 1344751603 12 3732185591 13 8251440185 14 9414505869 15 4942016124 16 4094989639 17 4700777743 18 1380128961 19 3860813150 20 2069016783 21 2488607421 22 7541058788 23 3345743062 24 1889854267 25 8131908841 26 7679546980 27 1360867906 28 5739567111 29 6007144763 30 3695501103 31 Total 3/18/21 $ 9/27/21 $ 7/22/21 $ 7/1/21 $ 12/31/21 $ 4/21/21 $ 1/31/21 $ 3/4/21 $ 10/16/21 $ 4/18/21 $ 6/15/21 $ 3/21/21 $ 8/6/21 $ 7/22/21 $ 9/20/21 $ 4/11/21 $ 1/6/21 $ 12/17/21 $ 8/3/21 $ 3/21/21 $ 6/1/21 $ 10/9/21 $ 1/25/21 $ 11/4/21 $ 11/2/21 $ 1/22/21 $ 5/29/21 $ 10/26/21 $ 2/14/21 $ $ 30,000.00 $ 30,000.00 30,000.00 $ 27,000.00 22,000.00 $ 13,200.00 30,000.00 $ 27,000.00 34,000.00 $ 27,200.00 27,000.00 $ 16,200.00 25,000.00 $ 17,500.00 22,000.00 $ 19,800.00 30,000.00 $ 27,000.00 25,000.00 $ 25,000.00 27,000.00 $ 18,900.00 22,000.00 $ 19,800.00 30,000.00 $ 30,000.00 22,000.00 $ 15,400.00 22,000.00 $ 13,200.00 25,000.00 $ 22,500.00 34,000.00 $ 20,400.00 34,000.00 $ 30,600.00 27,000.00 $ 27,000.00 22,000.00 $ 19,800.00 22,000.00 $ 17,600.00 27,000.00 $ 21,600.00 34,000.00 $ 27,200.00 34,000.00 $ 23,800.00 40,000.00 $ 28,000.00 22,000.00 $ 22,000.00 22,000.00 $ 22,000.00 22,000.00 $ 17,600.00 22,000.00 $ 22,000.00 785,000.00 $ 649,300.00 90% 60% 90% 100% 90% 80% 80% 80% 70% 70% 100% 100% 80% 100% 83% Vin # Make Model Year Mileage Sticker Price 2 2906934387 BMW 5 Series 2015 25709 $ 30,000.00 3 6393487147 Honda Civic 2018 26609 $ 30,000.00 4 9356019403 Toyota Prius 2000 96905 22,000.00 5 8000086254 BMW 5 Series 2017 28681 $ 30,000.00 6 9340076442 BMW 5 Series 2007 18384 34,000.00 7 7433901231 Chevy Malibu 2010 37085 $ 27,000.00 8 1328395941 Chevy Malibu 2002 48868 $ 25,000.00 9 8137529693 BMW 5 Series 2001 61480 $ 22,000.00 10 9650039042 Ford F-150 2012 28541 $ 30,000.00 11 1344751603 Toyota Prius 2004 42890 25,000.00 12 3732185591 BMW 5 Series 2001 36595 $ 27,000.00 13 8251440185 Ford F-150 2011 76254 $ 22,000.00 14 9414505869 Toyota Prius 2004 21380 30,000.00 15 4942016124 Ford F-150 2006 63737 $ 22,000.00 16 4094989639 BMW 5 Series 2017 92048 $ 22,000.00 17 4700777743 BMW 5 Series 2017 44667 25,000.00 18 1380128961 BMW 5 Series 2007 11158 $ 34,000.00 19 3860813150 Ford F-150 2009 14095 $ 34,000.00 20 2069016783 Honda Civic 2014 34428 $ 27,000.00 21 2488607421 BMW 5 Series 2008 68172 $ 22,000.00 22 7541058788 Chevy Malibu 2011 77099 $ 22,000.00 23 3345743062 Ford F-150 2007 31562 27,000.00 24 1889854267 BMW 5 Series 2012 14427 $ 34,000.00 25 8131908841 BMW 5 Series 2004 14966 34,000.00 26 7679546980 BMW 5 Series 2010 7877 40,000.00 27 1360867906 Chevy Malibu 2004 70797 $ 22,000.00 28 5739567111 Honda Civic 2013 99041 $ 22,000.00 29 6007144763 BMW 5 Series 2008 95344 $ 22,000.00 30 3695501103 Ford F-150 2015 99021 $ 22,000.00 31 2482451414 Ford F-150 2014 83281 22,000.00 32 7397531553 BMW 5 Series 2018 36000 $ 27,000.00 33 3133070112 Honda Civic 2009 46323 $ 25,000.00 34 5322014014 Ford F-150 2011 46216 25,000.00 35 5726149503 Ford F-150 2015 6971 $ 40,000.00 36 4820173885 Ford F-150 2016 6921 40,000.00 37 2706112648 BMW 5 Series 2012 22089 30,000.00 38 7377188604 Ford F-150 2016 52774 $ 22,000.00 39 5293323010 Honda Civic 2001 30840 27,000.00 40 2406189439 Honda Civic 2010 49544 25,000.00 41 3578026701 Honda Civic 2003 34825 $ 27,000.00 42 1462643171 Toyota 2006 8887 40,000.00 43 5122993766 Toyota Prius 2014 84930 $ 22,000.00 44 7092958553 Chevy Malibu 2014 11654 $ 34,000.00 45 8156049535 Honda Civic 2005 3997 $ 40.000.00 Fleet Information Sales Information Prius First Quarter Sales 3 1 Vin # Date sold Sticker Price Sale Price 2 2906934387 3/18/21 $ 30,000.00 $ 30,000.00 3 6393487147 9/27/21 $ 30,000.00 $ 27,000.00 4 9356019403 7/22/21 $ 22,000.00 $ 13,200.00 5 8000086254 7/1/21 $ 30,000.00 $ 27,000.00 6 9340076442 12/31/21 $ 34,000.00 $ 27,200.00 7 7433901231 4/21/21 $ 27,000.00 $ 16,200.00 8 1328395941 1/31/21 $ 25,000.00 $ 17,500.00 9 8137529693 3/4/21 $ 22,000.00 $ 19,800.00 10 9650039042 10/16/21 $ 30,000.00 $ 27,000.00 11 1344751603 4/18/21 $ 25,000.00 $ 25,000.00 12 3732185591 6/15/21 $ 27,000.00 $ 18,900.00 13 8251440185 3/21/21 $ 22,000.00 $ 19,800.00 14 9414505869 8/6/21 $ 30,000.00 $ 30,000.00 15 4942016124 7/22/21 $ 22,000.00 $ 15,400.00 16 4094989639 9/20/21 $ 22,000.00 $ 13,200.00 17 4700777743 4/11/21 $ 25,000.00 $ 22,500.00 18 1380128961 1/6/21 $ 34,000.00 $ 20,400.00 19 3860813150 12/17/21 $ 34,000.00 $ 30,600.00 20 2069016783 8/3/21 $ 27,000.00 $ 27,000.00 21 2488607421 3/21/21 $ 22,000.00 $ 19,800.00 22 7541058788 6/1/21 $ 22,000.00 $ 17,600.00 23 3345743062 10/9/21 $ 27,000.00 $ 21,600.00 24 1889854267 1/25/21 $ 34,000.00 $ 27,200.00 25 8131908841 11/4/21 $ 34,000.00 $ 23,800.00 26 7679546980 11/2/21 $ 40,000.00 $ 28,000.00 27 1360867906 1/22/21 $ 22,000.00 $ 22,000.00 28 5739567111 5/29/21 $ 22,000.00 $ 22,000.00 29 6007144763 10/26/21 $ 22,000.00 $ 17,600.00 30 3695501103 2/14/21 $ 22,000.00 $ 22,000.00 21 Vin # Date sold Sticker Price Sale Price % of sticker 100% 90% 60% 90% 80% 60% 70% 90% 90% 100% 70% 90% 100% 70% 60% 2 2906934387 3 6393487147 4 9356019403 5 8000086254 6 9340076442 7 7433901231 8 1328395941 9 8137529693 10 9650039042 11 1344751603 12 3732185591 13 8251440185 14 9414505869 15 4942016124 16 4094989639 17 4700777743 18 1380128961 19 3860813150 20 2069016783 21 2488607421 22 7541058788 23 3345743062 24 1889854267 25 8131908841 26 7679546980 27 1360867906 28 5739567111 29 6007144763 30 3695501103 31 Total 3/18/21 $ 9/27/21 $ 7/22/21 $ 7/1/21 $ 12/31/21 $ 4/21/21 $ 1/31/21 $ 3/4/21 $ 10/16/21 $ 4/18/21 $ 6/15/21 $ 3/21/21 $ 8/6/21 $ 7/22/21 $ 9/20/21 $ 4/11/21 $ 1/6/21 $ 12/17/21 $ 8/3/21 $ 3/21/21 $ 6/1/21 $ 10/9/21 $ 1/25/21 $ 11/4/21 $ 11/2/21 $ 1/22/21 $ 5/29/21 $ 10/26/21 $ 2/14/21 $ $ 30,000.00 $ 30,000.00 30,000.00 $ 27,000.00 22,000.00 $ 13,200.00 30,000.00 $ 27,000.00 34,000.00 $ 27,200.00 27,000.00 $ 16,200.00 25,000.00 $ 17,500.00 22,000.00 $ 19,800.00 30,000.00 $ 27,000.00 25,000.00 $ 25,000.00 27,000.00 $ 18,900.00 22,000.00 $ 19,800.00 30,000.00 $ 30,000.00 22,000.00 $ 15,400.00 22,000.00 $ 13,200.00 25,000.00 $ 22,500.00 34,000.00 $ 20,400.00 34,000.00 $ 30,600.00 27,000.00 $ 27,000.00 22,000.00 $ 19,800.00 22,000.00 $ 17,600.00 27,000.00 $ 21,600.00 34,000.00 $ 27,200.00 34,000.00 $ 23,800.00 40,000.00 $ 28,000.00 22,000.00 $ 22,000.00 22,000.00 $ 22,000.00 22,000.00 $ 17,600.00 22,000.00 $ 22,000.00 785,000.00 $ 649,300.00 90% 60% 90% 100% 90% 80% 80% 80% 70% 70% 100% 100% 80% 100% 83%