EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Sales 1.3

Project Description:

You work for Rockville Auto Sales and have been asked to aid in the development of a spreadsheet to manage sales and inventory information. You will start the task with a prior worksheet that contains vehicle information and sales data for 2018. You need to convert the data to a table. You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and then format the table.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Open the downloaded file exploring_e04_grader_h1.xlsx.	0.000
2	Freeze the first row on the Fleet Information worksheet.	3.000
3	Convert the data to a table, name the table Inventory, and apply the Table Style Medium 19.	10.000
4	Remove duplicate records.	2.000
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.000
6	Repeat the field names on all pages.	5.000
7	Change page breaks so each vehicle make is printed on a separate page.	5.000
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.	5.000
9	Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Table Style Dark 11.	10.000
10	Type % of sticker in cell E1.	2.000
11	Create a formula with structured references to calculate the percentage of the Sticker Price in column E.	8.000
12	Format the range E2:E30 with Percent Style Number Format.	5.000
13	Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price.	5.000
14	AutoFit the width of columns B:E to show the total values.	5.000
15	Apply Solid Fill Blue Data Bars conditional formatting to the % of sticker data.	8.000
16	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 sale price.	5.000
17	On the First Quarter Sales worksheet, rename the table FirstQuarter.	5.000
18	Filter the data to display January, February, and March sales.	2.000
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.	5.000
20	Select Landscape orientation for all sheets.	5.000
21	Save and close the workbook. Submit the workbook as directed.	0.000
	Total Points	100.000

Vin # Make 2906934387 BMW 6393487147 Honda 9356019403 Toyota 8000086254 BMW 9340076442 BMW 7433901231 Chevy 1328395941 Chevy 8137529693 BMW 9650039042 Ford 1344751603 Toyota 3732185591 BMW 8251440185 Ford 9414505869 Toyota 4942016124 Ford 4094989639 BMW 4700777743 BMW 1380128961 BMW 3860813150 Ford 2069016783 Honda 2488607421 BMW 7541058788 Chevy 3345743062 Ford 1889854267 BMW 8131908841 BMW 7679546980 BMW 1360867906 Chevy 5739567111 Honda 6007144763 BMW 3695501103 Ford 2482451414 Ford 7397531553 BMW 3133070112 Honda 5322014014 Ford 5726149503 Ford 4820173885 Ford 2706112648 BMW 7377188604 Ford 5293323010 Honda 2406189439 Honda 3578026701 Honda 1462643171 Toyota 5122993766 Toyota 7092958553 Chevy 8156049535 Honda 9305933928 Ford 9537026620 Toyota 8019012354 BMW Model 5 Series Civic Prius 5 Series 5 Series Malibu Malibu 5 Series F-150 Prius 5 Series F-150 Prius F-150 5 Series 5 Series 5 Series F-150 Civic 5 Series Malibu F-150 5 Series 5 Series 5 Series Malibu Civic 5 Series F-150 F-150 5 Series Civic F-150 F-150 F-150 5 Series F-150 Civic Civic Civic Prius Prius Malibu Civic F-150 Prius 5 Series 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 2009 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 $ 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 2014 84930 $ 22,000.00 2014 11654 $ 34,000.00 2005 3997 $ 40,000.00 2011 40807 $ 25,000.00 2008 27785 $ 30,000.00 2017 50120 $ 22,000.00 3737821324 Toyota 7596630806 Ford 5718003239 BMW 8010477805 BMW 3814357405 Chevy 1213463078 Toyota 1563229851 Chevy 9027521957 Ford 8893900890 BMW 7454528548 BMW 8054683334 Chevy 5742570103 BMW 8163497575 Ford 5276867505 Honda 1409363873 Chevy 1089138532 BMW 6015917869 Toyota 9885757629 Ford 5306541595 BMW 2747847524 Chevy 7718062943 BMW 2976194513 Toyota 8281760371 Chevy 4730270762 Honda 5161605236 Honda 7473005747 BMW 6891374035 Honda 5463769134 Ford 3867060534 Honda 3849719202 BMW 7200319445 Honda 8725850239 Honda 5903493846 BMW 2373147058 BMW 6161188244 BMW 4913951544 Chevy 8806645278 Honda 5250708704 BMW 8357455137 Ford 4370047837 Toyota 6290615001 Honda 8279091856 Honda 1218797573 Ford 1218797573 Ford 2700853328 BMW 6729292004 Ford 9622942163 BMW 1532392596 Honda 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 F-150 5 Series F-150 5 Series Civic 2001 2003 2016 2009 2016 2000 2002 2002 2006 2002 2012 2011 2004 2018 2004 2012 2001 2004 2013 2014 2013 2003 2012 2007 2013 2003 2003 2012 2003 2006 2005 2016 2001 2009 2012 2003 2012 2006 2016 2012 2009 2003 2010 2010 2004 2001 2000 2018 35426 81699 39202 50527 54060 93430 13764 90009 79293 37753 36934 78360 91310 39645 80047 95298 87698 19686 81096 50650 55145 27493 44985 8541 31271 41747 75313 99161 52586 33661 23147 57432 91463 40492 65420 40380 76217 61969 17398 38800 96345 4129 11098 11098 97812 63166 57736 3308 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 27,000.00 22,000.00 27,000.00 22,000.00 22,000.00 22,000.00 34,000.00 22,000.00 22,000.00 27,000.00 27,000.00 22,000.00 22,000.00 27,000.00 22,000.00 22,000.00 22,000.00 34,000.00 22,000.00 22,000.00 22,000.00 30,000.00 25,000.00 40,000.00 27,000.00 25,000.00 22,000.00 22,000.00 22,000.00 27,000.00 30,000.00 22,000.00 22,000.00 25,000.00 22,000.00 25,000.00 22,000.00 22,000.00 34,000.00 27,000.00 22,000.00 40,000.00 34,000.00 34,000.00 22,000.00 22,000.00 22,000.00 40,000.00 1532392596 Honda 6851036813 Toyota 5095450285 Ford 1531278978 Honda 4174658862 Ford 4174658862 Ford 6556316751 Ford 6287671745 Honda Civic Prius F-150 Civic F-150 F-150 F-150 Civic 2018 2013 2018 2018 2009 2009 2016 2007 3308 79226 70078 82011 54406 54406 58076 49295 $ $ $ $ $ $ $ $ 40,000.00 22,000.00 22,000.00 22,000.00 22,000.00 22,000.00 22,000.00 25,000.00 Vin # Date sold Sticker Price 2906934387 3/18/2018 $ 30,000.00 6393487147 9/27/2018 $ 30,000.00 9356019403 7/22/2018 $ 22,000.00 8000086254 7/1/2018 $ 30,000.00 9340076442 12/31/2018 $ 34,000.00 7433901231 4/21/2018 $ 27,000.00 1328395941 1/31/2018 $ 25,000.00 8137529693 3/4/2018 $ 22,000.00 9650039042 10/16/2018 $ 30,000.00 1344751603 4/18/2018 $ 25,000.00 3732185591 6/15/2018 $ 27,000.00 8251440185 3/21/2018 $ 22,000.00 9414505869 8/6/2018 $ 30,000.00 4942016124 7/22/2018 $ 22,000.00 4094989639 9/20/2018 $ 22,000.00 4700777743 4/11/2018 $ 25,000.00 1380128961 1/6/2018 $ 34,000.00 3860813150 12/17/2018 $ 34,000.00 2069016783 8/3/2018 $ 27,000.00 2488607421 3/21/2018 $ 22,000.00 7541058788 6/1/2018 $ 22,000.00 3345743062 10/9/2018 $ 27,000.00 1889854267 1/25/2018 $ 34,000.00 8131908841 11/4/2018 $ 34,000.00 7679546980 11/2/2018 $ 40,000.00 1360867906 1/22/2018 $ 22,000.00 5739567111 5/29/2018 $ 22,000.00 6007144763 10/26/2018 $ 22,000.00 3695501103 2/14/2018 $ 22,000.00 Sale Price $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ### 27,000.00 13,200.00 27,000.00 27,200.00 16,200.00 17,500.00 19,800.00 27,000.00 ### 18,900.00 19,800.00 ### 15,400.00 13,200.00 22,500.00 20,400.00 30,600.00 ### 19,800.00 17,600.00 21,600.00 27,200.00 23,800.00 28,000.00 ### ### 17,600.00 ### Vin # Date sold Sticker Price Sale Price % of sticker 2906934387 3/18/2018 $ 30,000.00 ### Err:509 6393487147 9/27/2018 $ 30,000.00 $ 27,000.00 Err:509 9356019403 7/22/2018 $ 22,000.00 $ 13,200.00 Err:509 8000086254 7/1/2018 $ 30,000.00 $ 27,000.00 Err:509 9340076442 12/31/2018 $ 34,000.00 $ 27,200.00 Err:509 7433901231 4/21/2018 $ 27,000.00 $ 16,200.00 Err:509 1328395941 1/31/2018 $ 25,000.00 $ 17,500.00 Err:509 8137529693 3/4/2018 $ 22,000.00 $ 19,800.00 Err:509 9650039042 10/16/2018 $ 30,000.00 $ 27,000.00 Err:509 1344751603 4/18/2018 $ 25,000.00 ### Err:509 3732185591 6/15/2018 $ 27,000.00 $ 18,900.00 Err:509 8251440185 3/21/2018 $ 22,000.00 $ 19,800.00 Err:509 9414505869 8/6/2018 $ 30,000.00 ### Err:509 4942016124 7/22/2018 $ 22,000.00 $ 15,400.00 Err:509 4094989639 9/20/2018 $ 22,000.00 $ 13,200.00 Err:509 4700777743 4/11/2018 $ 25,000.00 $ 22,500.00 Err:509 1380128961 1/6/2018 $ 34,000.00 $ 20,400.00 Err:509 3860813150 12/17/2018 $ 34,000.00 $ 30,600.00 Err:509 2069016783 8/3/2018 $ 27,000.00 ### Err:509 2488607421 3/21/2018 $ 22,000.00 $ 19,800.00 Err:509 7541058788 6/1/2018 $ 22,000.00 $ 17,600.00 Err:509 3345743062 10/9/2018 $ 27,000.00 $ 21,600.00 Err:509 1889854267 1/25/2018 $ 34,000.00 $ 27,200.00 Err:509 8131908841 11/4/2018 $ 34,000.00 $ 23,800.00 Err:509 7679546980 11/2/2018 $ 40,000.00 $ 28,000.00 Err:509 1360867906 1/22/2018 $ 22,000.00 ### Err:509 5739567111 5/29/2018 $ 22,000.00 ### Err:509 6007144763 10/26/2018 $ 22,000.00 $ 17,600.00 Err:509 3695501103 2/14/2018 $ 22,000.00 ### Err:509 Total Err:508 Err:508 Err:508