Application Exercises: Project 2 - Excel Exploring Microsoft Excel 2016, Chapter 4 Used Car Sales You have recently been promoted to Assistant General Manager of the used car department. The GM has asked you to take a look at last month's sales numbers for the Miami location. He wants to take a look at overall sales, the percent profit of each transaction, and the top 10 profit transactions to see if there are trend in cars he should go after at the next auction. Setup Work You will make formatting changes so that headings are always visible when scrolling and set print area. a. Open 04_test_chap_data and save as 04_test_LastFirst. b. Display the All Sales worksheet and freeze row 7 so that when a user scrolls down, they will still be able to see the titles. c. Set the Print Area to the range F7:132 using absolute field references. Profit Analysis You will provide an analysis profit of cars sold and if each car is meeting the 15% profit set as the standard. d. Display the Profit worksheet and create a table named Profit for the range A7:132 e. Set the table style to Table Style Medium 1 and turn off the Filter Button. f. Delete the Days on Lot column and add a new column after Profit named Profit %. In cell 18, use a structured reference to create the formula to calculate the percent of profit over the purchase price (Profit/Purchase Price). g. Add data bars with a style of Gradient Fill Light Blue Data Bar to the Profit column. h. Add a Total Row for columns Purchase Price, Sale Price, and Profit. i. Set the conditional formatting for the Profit % column so that any value that is less than 15% displays with Light Red Fill with Dark Red Text Create a custom sort by Vehicle Type using a custom list of Car, Truck, SUV, Van. Top 10 You will find the top 10 transactions by gross profit to see if there are any trends in car type, body style or vehicle year K. Display the Top 10 worksheet and create a table named Top for the range A7: 132