EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Sales 1.3
- From Business, General Business
- Academia
- Rating : 60
- Grade : A+
- Questions : 0
- Solutions : 4595
- Blog : 1
- Earned : $25563.50
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
2
Freeze the first row on the Fleet Information worksheet.
3
3
Convert the data to a table, name the table Inventory, and apply the Table Style Medium 19.
10
4
Remove duplicate records.
2
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.
5
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
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.
8
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.
5
14
Manually set the width of columns B:E to show the total values.
Settings on PC are the following: Column B - 10.86, Column C - 13.43, Column D - 11.86, Column E - 12.71
Setting on a MAC are the following: Column B - 0.96, Column C - 1.18, Column D - 1.04, Column E - 1.13
5
15
Apply Solid Fill Blue Data Bars conditional formatting to the % of sticker data.
8
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
17
On the First Quarter Sales worksheet, rename the table FirstQuarter.
5
18
Filter the data to display January, February, and March sales.
2
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
20
Select Landscape orientation for all sheets.
5
21
Save and close the workbook. Submit the workbook as directed.
0
Total Score
100
[Solved] EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Sales 1.3
- This Solution has been Purchased 3 time
- Submitted On 05 Aug, 2018 11:36:32
- Academia
- Rating : 60
- Grade : A+
- Questions : 0
- Solutions : 4595
- Blog : 1
- Earned : $25563.50