Office 2013 – myitlab:grader – Instructions
Skills for Success - Excel Chapter 4: Assessment Project 1
Tom’s Prices and Inventory
Project Description:
In this project, you will create an Excel table to sort and filter data at a hardware store. You will use an IF
function to determine if an inventory item should continue to be carried in the store and use the Find and Replace
function to correct an error.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel 2013, and then open the downloaded file skills_exl04_grader_a1.
0
2
On the Price worksheet, in the merged cell A2, insert the NOW function.
5
3
On the Price worksheet, in cell F3, use the COUNT function and select the argument range
F5:F50.
10
4
On the Price worksheet, apply the Thick Bottom Border style to the range A5:G5.
4
5
On the Price worksheet, in cell G6, enter an IF function that will display the text Remove if
the value in cell F6 is greater than 500. Otherwise, the function will return the text Keep.
Center the text and then AutoFill the function down through cell G50.
10
6
On the Price worksheet, use Find and Replace to find all occurrences of the words West Side
in the worksheet, and replace them with the word Westside.
10
7
On the Price worksheet, apply the solid Blue Data Bar conditional formatting to the range
F6:F50.
10
8
On the Price worksheet, freeze the panes of the worksheet so that when you scroll down, the
headings in the rows 1:5 remain visible.
5
9
Create a copy of the Price worksheet and move the copied worksheet to the end of the
workbook. Rename the copied worksheet tab as Appliances. Unfreeze the panes in the
Appliances worksheet.
5
On the Appliances worksheet, format the data in the range A5:G50 as a table with headers
using Table Style Light 1.
7
10
Updated: 01/07/2013
1
E_CH04_SKLV1_A1_Instructions.docx
Office 2013 – myitlab:grader – Instructions
Step
Skills for Success - Excel Chapter 4: Assessment Project 1
Points
Possible
Instructions
11
On the Appliances worksheet, filter the data in the Department column so that only records
containing Appliances are displayed.
5
12
On the Appliances worksheet, sort the Price column from the smallest to the largest value.
5
13
On the Appliances worksheet, add a Total row to the Excel table. Display the sum of column F
in the Total row.
5
14
On the Inventory worksheet, in the range G4:G10, insert a Line Sparkline for each row. Show
the High Point on each Sparkline.
8
15
Group the worksheets. Adjust the Scale option to change the Width to 1 page. Change the
Orientation to Landscape. Ungroup the worksheets.
6
16
On the Price worksheet, prepare the worksheet for printing by repeating row 5 on each page.
5
17
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Price, Inventory, Appliances. Save the workbook, close the workbook window, and
then submit the workbook as directed.
0
Total Points
Updated: 01/07/2013
2
100
E_CH04_SKLV1_A1_Instructions.docx
Tom's Place
Number of Inventory Items
Inventory
Number
Item
Category
Department
Store
Location
10-3015
10-3034
10-3053
10-2996
10-2977
10-3072
10-2958
10-3091
10-3110
67-1085
67-2111
67-3137
67-4163
67-5189
67-8267
67-6215
67-7241
55-9394
55-6268
55-2100
55-4184
55-3142
55-8352
55-5226
55-1058
55-7310
29-5825
29-5830
29-5835
29-5840
29-5845
29-5850
29-5855
29-5860
29-5865
29-5870
29-5875
98-9886
98-9894
Charcoal
Charcoal/Gas Combo
Electric
Kettle Charcoal
Natural Gas
Portable
Propane
Smoker
Turkey Fryer
Cooktop
Dishwasher
Freezer
Garbage Disposal
Microwave
Oven
Refrigerator
Trash Compactor
Aerator
Bagger
Dump Cart
Electric Push Mower
Gas Push Mower
Lawn Sweeper
Reel Mower
Riding Mower
Spreader
Adirondack Chair
Canopies
Deck Boxes
Folding Chairs
Hammocks
Patio Bench
Patio Chaise
Patio Table
Patio Umbrella
Picnic Table
Porch Swing
Air Purifier
Dehumidifier
Grills
Grills
Grills
Grills
Grills
Grills
Grills
Grills
Grills
Kitchen
Kitchen
Kitchen
Kitchen
Kitchen
Kitchen
Kitchen
Kitchen
Mower
Mower
Mower
Mower
Mower
Mower
Mower
Mower
Mower
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Patio Furniture
Small Appliance
Small Appliance
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Appliances
Appliances
Appliances
Appliances
Appliances
Appliances
Appliances
Appliances
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Outdoor Living
Appliances
Appliances
Central
Central
Downtown
East Valley
East Valley
Downtown
West Side
West Side
East Valley
Downtown
West Side
Central
Downtown
Central
East Valley
Central
East Valley
East Valley
West Side
West Side
Central
East Valley
East Valley
Central
West Side
West Side
Downtown
West Side
West Side
East Valley
East Valley
Central
Central
Central
Central
West Side
West Side
Downtown
Central
98-9890
98-9898
77-8659
77-8690
77-8721
77-8752
Humidifier
Vacuum
Cordless String Trimmer
Electric String Trimmer
Gas String Trimmer
Power Lawn Edger
Small Appliance
Small Appliance
Trimmers and Edgers
Trimmers and Edgers
Trimmers and Edgers
Trimmers and Edgers
Appliances
Appliances
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
Outdoor Equipment
West Side
Downtown
Downtown
Downtown
Central
West Side
Price
$199,00
$299,00
$599,00
$189,00
$359,00
$79,00
$399,00
$189,00
$259,00
$249,00
$550,00
$459,00
$95,00
$249,00
$469,00
$876,00
$129,00
$239,00
$59,00
$79,00
$109,00
$129,00
$149,00
$69,00
$749,00
$189,00
$109,00
$129,00
$59,00
$49,00
$149,00
$259,00
$129,00
$549,00
$109,00
$269,00
$229,00
$79,00
$359,00
Remove from
Inventory
$359,00
$259,00
$99,00
$109,00
$179,00
$209,00
Tom's Place
Category
Grills
Kitchen
Mower
Patio Furniture
Small Appliance
Trimmers
Edgers
$
$
$
$
$
$
$
FY 2010
10.576
30.862
17.856
15.066
12.862
5.298
6.825
$
$
$
$
$
$
$
FY 2011
10.682
29.504
18.392
14.463
11.115
5.351
7.962
$
$
$
$
$
$
$
FY 2012
11.789
34.025
18.943
13.885
12.782
5.404
7.101
$
$
$
$
$
$
$
FY 2013
10.896
35.727
19.512
15.786
14.699
5.459
7.243
$
$
$
$
$
$
$
FY 2014
11.005
37.513
20.097
12.796
16.904
6.513
7.388
Trend
Purchase answer to see full
attachment