Exp19_Excel_Ch04_CapAssessment_Rockville_Auto_Sales
The Focused
Exp19_Excel_Ch04_CapAssessment_Rockville_Auto_Sales_Instructions.docxGrader - Instructions Excel 2019 Project
Exp19_Excel_Ch04_CapAssessment_Rockville_Auto_Sales
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.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Download and open the file named Exp19_Excel_Ch04_Cap_AutoSales.xlsx. Grader has automatically added your last name to the beginning of the filename. |
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 |
|
21 |
Save and close EXP19_Excel_CH04_Cap_AutoSales.xlsx. Exit Excel. |
0 |
|
Total Points |
100 |
Created On: 11/06/2020 1 Exp19_Excel_Ch04_CapAssessment - Rockville Auto Sales 1.4
