Office 2016 – myitlab:grader – Instructions
Excel Project
GO16_XL_CH02_GRADER_2G_AS - Condiments Inventory 1.2
Project Description:
In the following project, you will edit a worksheet that summarizes the inventory of Condiments and Toppings at
the Pasadena facility.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel. Download and open the file named go16_xl_ch02_grader_2g_as.xlsx.
0.000
2
Change the Theme to Ion. Rename Sheet1 as Condiments and Sheet2 as Toppings. Click
the Condiments sheet tab to make it the active sheet.
3.000
3
To the right of column B, insert two new columns to create new blank columns C and D. By
using Flash Fill in the two new columns, split the data in column B into a column for Item # in
column C and Category in column D. Type Item # as the column title in column C and
Category as the column title in column D. Delete column B. Cut column C, Category, and
paste it to column G. Delete the empty column C.
5.000
Note, Mac users, after inserting the two columns, select the range B15:B42 and, on the Data
tab, click Text to Columns. Select Other as the delimiters, and then type a hyphen (-). Set the
destination cell as C15, and then finish the wizard. Continue with the remaining instructions.
4
5
6
7
8
9
Display the Toppings worksheet, and then repeat Step 3 on this worksheet.
5.000
Without grouping the sheets, make the following calculations in both worksheets:
• In cell B4, enter a function to sum the Quantity in Stock data, and then apply Comma Style
with zero decimal places to the result.
• In cells B5:B8, enter formulas to calculate the Average, Median, Lowest, and Highest retail
prices, and then apply the Accounting Number Format.
Without grouping the sheets, make the following calculations in both worksheets:
• In cell B10, enter a COUNTIF function to determine how many different types of Relish are
in stock on the Condiments sheet and how many different types of Salsa are in stock on the
Toppings worksheet.
Without grouping the sheets, make the following calculations in both worksheets:
• In cell G14 type Stock Level. In cell G15, enter an IF function to determine the items that
must be ordered. If the Quantity in Stock is less than 75, the Value_if_true is Order.
Otherwise, the Value_if_false is OK. Fill the formula down through all the rows.
Without grouping the sheets, apply the following formatting in both worksheets:
• Apply Conditional Formatting to the Stock Level column so that Text that Contains the text
Order are formatted with Bold Italic with a Font Color of Dark Red, Accent 1. Apply Gradient
Fill Red Data Bars to the Quantity in Stock column.
In the Condiments sheet, format the range A14:G42 as a table with headers and apply Table
Style Light 20. Insert a Total Row, filter by Category for Relish, and then Sum the Quantity in
Stock column. Record the result in cell B11.
Updated: 08/04/2017
1
14.000
6.000
8.000
4.000
3.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
10
Clear the filter from the table. Sort the table on the Item # column from Smallest to Largest,
and then remove the Total Row. On the Page Layout tab, set Print Titles so that row 14
repeats at the top of each page.
3.000
11
In the Toppings sheet, format the range A14:G42 as a table with headers and apply Table
Style Light 19. Insert a Total Row, filter by Category for Salsa, and then Sum the Quantity in
Stock column. Record the result in cell B11.
3.000
12
Clear the filter from the table. Sort the table on the Item # column from Smallest to Largest,
and then remove the Total Row. On the Page Layout tab, set Print Titles so that row 14
repeats at the top of each page, and then Save your workbook.
3.000
13
Group the two worksheets. Merge and center the title in cell A1 across the range A1:G1 and
apply the Title cell style. Merge and center the subtitle in cell A2 across the range A2:G2 and
apply the Heading 1 cell style. AutoFit Columns A:F. Center the worksheets Horizontally, and
then change the Orientation to Landscape.
9.000
14
In Backstage view, on the left, click Save, and then click the Condiments sheet tab to cancel
the grouping. Click the Toppings sheet tab, and then insert a new worksheet. Change the
sheet name to Summary and then widen columns A:D to 20.63 width. Move the Summary
sheet so that it is the first sheet in the workbook.
3.000
15
In cell A1, type Valley View Inventory Summary. Merge & Center the title across the
range A1:D1, and then apply the Title cell style. In cell A2, type As of June 30, and then
Merge & Center the text across the range A2:D2. Apply the Heading 1 cell style.
3.000
16
On the Condiments sheet, copy the range A4:A8. Display the Summary sheet and Paste the
selection to cell A5. Apply the Heading 4 cell style to the selection.
1.000
17
In the Summary sheet, in cell B4, type Condiments. In cell C4 type Toppings. In cell D4
type Condiments/Toppings. Center the column titles, and then apply the Heading 3 cell
style.
3.000
18
In cell B5, enter a formula that references cell B4 in the Condiments sheet so that the
Condiments Total Items in Stock displays in B5. Create similar formulas to enter the Average
Price, Median Price, Lowest Price, and Highest Price from the Condiments sheet into the
Summary sheet in the range B6:B9.
5.000
19
Enter formulas in the range C5:C9 that reference the Total Items in stock and the Average
Price, Median Price, Lowest Price, and Highest Price cells in the Toppings worksheet.
5.000
20
21
In the range D5:D9 of the Summary sheet, insert Column sparklines using the values in the
Condiments and Toppings columns (insert in each cell individually). Format the sparklines
using the styles in the first row as follows:
D5: Sparkline Style Accent 1, Darker 50%
D6: Sparkline Style Accent 2, Darker 50%
D7: Sparkline Style Accent 3, Darker 50%
D8: Sparkline Style Accent 4, Darker 50%
D9: Sparkline Style Accent 5, Darker 50%
To the range B5:C5, apply Comma Style with zero decimal places, and confirm that the
Accounting Number Format is applied to the range B6:C9. Center the Summary worksheet
Horizontally and change the Orientation to Landscape. Insert a custom footer in the left
section with the file name.
Updated: 08/04/2017
2
10.000
4.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Step
Instructions
Points
Possible
22
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Summary, Condiments, Toppings. Save and close Excel. Submit the file as directed.
0.000
Total Points
Updated: 08/04/2017
3
100.000
Current_Instruction.docx
Valley View Facility: Inventory Status of Condiments
As of June 30, 2014
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Relish Types:
Relish Total Quantity:
Quantity in Stock
45
46
46
152
89
56
129
109
68
52
45
43
66
149
243
102
29
75
85
46
42
89
24
85
68
75
75
135
Item #/Category
1956-Olives
1899-Chutney
1898-Pesto
1897-Chutney
1456-Relish
1896-Mustard
1798-Chutney
1285-Relish
1654-Relish
1652-Olives
1645-Olives
1645-Spread
1635-Relish
1569-Olives
1568-Olives
1565-Spread
1565-Olives
1564-Olives
1564-Spread
1564-Pesto
1465-Relish
1548-Spread
1545-Mustard
1528-Chutney
1523-Mustard
1498-Spread
1490-Spread
1489-Spread
Item Name
Moroccan
New England Cranberry
Basil and Parsley
Spicy Plum
Hot and Spicy Corn
Red Raspberry Wasabi
Portuguese Membrillo
Homemade Hot dog
Orange Fennel/Caper
Stuffed Manzanilla
Organic Arbequina
Roasted Garlic
Pomegranate Molasses
Pitted Variety
Variety Pack
Vietnamese Dipping Sauce
Stuffed Greek
Garlic/Lemon Green
Pork Sausage Ragu
Pine Nut Cilantro
Roasted Onion
Tomato Sauce with Pancetta
Balsamic Vinaigrette
Strawberry
Maine Maple
Red Clam
Sicilian Bruschetta
Black Olive
Retail Price
7.59
6.99
9.99
7.95
8.59
8.95
10.99
8.58
9.35
4.95
39.95
8.59
9.65
14.85
15.85
14.55
8.99
7.95
9.95
8.98
7.52
12.89
8.85
14.85
6.55
8.59
12.85
14.85
Size
8 oz.
8 oz.
12 oz.
9 oz.
12 oz.
8 oz.
12 oz.
10 oz.
10 oz.
6 oz.
32 oz.
11 oz.
12 oz.
12 oz.
16 oz.
15 oz.
7 oz.
11 oz.
12 oz.
6 oz.
12 oz.
16 oz.
9 oz.
20 oz.
8 oz.
11 oz.
14 oz.
15 oz.
Valley View Facility: Inventory Status of Toppings
As of June 30, 2014
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Salsa Types:
Salsa Total Quantity:
Quantity in Stock
87
235
45
42
153
95
182
185
110
285
126
108
385
210
185
22
54
82
108
175
42
32
110
14
87
85
35
49
Item #/Category
9952-Salsa
8582-Syrup
8256-Jam
6255-Sauce
5664-Dressing
5636-Sauce
5635-Sauce
5522-Sauce
5463-Dressing
4822-Salsa
4652-Dressing
4625-Jam
4597-Sauce
4587-Sauce
4567-Dressing
1630-Salsa
3933-Dressing
3252-Syrup
2895-Dressing
2850-Jam
2587-Salsa
2469-Dressing
2389-Syrup
2348-Dressing
2347-Dressing
1004-Salsa
2133-Sauce
1783-Dressing
Item Name
Verde Hot
Dark Chocolate
Tangerine Marmalade
Artichoke Pesto
Olive Oil and Balsamic
Classic Alfredo
Chocolate Peanut Butter
Bittersweet Chocolate
Balsamic Fig
Black Bean
Creamy Asiago
Raspberry Peach
Roasted Garlic Basil
Maple Chipotle
Creamy Asiago
Spicy Corn Relish
Chocolate Orange
European Crock
Black Bean
Strawberry
Habanero Mango
Roasted Salmon
Cinnamon Apple
Green Goddess
Cilantro Lime
Black Bean
Habanero Mango
Roasted Garlic Oil
Retail Price
8.95
15
7.59
9.95
6.89
9.59
8.45
8.95
8.59
6.5
8.75
4.75
6.95
7.56
6.95
6.55
6.95
29.95
8.59
4.75
7.75
6.95
6.95
7.45
7.59
6.95
7.25
6.75
Size
13 oz.
17 oz.
13 oz.
8 oz.
13 oz.
14 oz.
9 oz.
12 oz.
11 oz.
8 oz.
13 oz.
4 oz.
8 oz.
11 oz.
11 oz.
11 oz.
10 oz.
16 oz.
11 oz.
4 oz.
9 oz.
10 oz.
9 oz.
15 oz.
11 oz.
8 oz.
12 oz.
9 oz.
Office 2016 – myitlab:grader – Instructions
Excel Project
GO16_XL_CH02_GRADER_2G_HW - Inventory 1.1
Project Description:
In the following project, you will edit a worksheet that summarizes the inventory of bulbs and trees at the
Pasadena facility.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel. Download and open the file named go16_xl_ch02_grader_2g_hw.xlsx.
0.000
2
Change the Theme to Slice. Rename Sheet1 as Bulbs and Sheet2 as Trees. Click the Bulbs
sheet tab to make it the active sheet.
3.000
3
To the right of column B, insert two new columns to create new blank columns C and D. By
using Flash Fill in the two new columns, split the data in column B into a column for Item # in
column C and Category in column D. As necessary, type Item # as the column title in column
C and Category as the column title in column D. Delete column B. By using the Cut and Paste
commands, cut column C—Category—and paste it to column G, and then delete the empty
column C. Apply AutoFit to columns A:F.
6.000
Note, Mac users, after inserting the two columns, select the range B15:B42 and, on the Data
tab, click Text to Columns. Select Other as the delimiters, and then type a hyphen (-). Set the
destination cell as C15, and then finish the wizard. Continue with the remaining instructions.
4
5
6
Display the Trees worksheet, and then repeat Step 3 on this worksheet.
6.000
Without grouping the sheets, make the following calculations in both worksheets:
• In cell B4, enter a function to sum the Quantity in Stock data, and then apply Comma Style
with zero decimal places to the result.
• In cells B5:B8, enter formulas to calculate the Average, Median, Lowest, and Highest retail
prices, and then apply the Accounting Number Format.
Without grouping the sheets, make the following calculations in both worksheets:
• In cell B10, enter a COUNTIF function to determine how many different types of Tulips are
in stock on the Bulbs sheet and how many different types of Evergreens are in stock on the
Trees worksheet.
14.000
4.000
7
Without grouping the sheets, make the following calculation in both worksheets:
• In cell G14, type Stock Level. In cell G15, enter an IF function to determine the items that
must be ordered. If the Quantity in Stock is less than 75 the Value_if_true is Order.
Otherwise the Value_if_false is OK. Fill the formula down through all the rows.
8.000
8
Without grouping the sheets, apply the following formatting in both worksheets:
• Apply Conditional Formatting to the Stock Level column so that cells that contain the text
Order are formatted with Bold Italic with a Font Color of Dark Blue, Text 2. Apply Gradient Fill
Blue Data Bars to the Quantity in Stock column.
4.000
9
In the Bulbs sheet, format the range A14:G42 as a table with headers and apply Table Style
Light 20. Insert a Total Row, filter by Category for Tulips, and then Sum the Quantity in Stock
column. Record the result in cell B11.
3.000
Updated: 08/07/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
10
Clear the filter from the table. Sort the table on the Item # column from Smallest to Largest,
and then remove the Total Row. On the Page Layout tab, set Print Titles so that row 14
repeats at the top of each page.
3.000
11
In the Trees sheet, format the range A14:G42 as a table with headers and apply Table Style
Light 19. Insert a Total Row, filter by Category for Evergreens, and then Sum the Quantity in
Stock column. Record the result in cell B11.
3.000
12
Clear the filter from the table. Sort the table on the Item # column from Smallest to Largest,
and then remove the Total Row. On the Page Layout tab, set Print Titles so that row 14
repeats at the top of each page, and then Save your workbook.
3.000
13
Group the two worksheets. Merge and center the title in cell A1 across the range A1:G1 and
apply the Title cell style. Merge and center the subtitle in cell A2 across the range A2:G2 and
apply the Heading 1 cell style. AutoFit Column A. Center the worksheets Horizontally, change
the Orientation to Landscape.
9.000
14
In Backstage view, on the left click Save, and then click the Bulbs sheet tab to cancel the
grouping. Click the Trees sheet tab, and then insert a new worksheet. Change the sheet name
to Summary and then widen columns A:D to 20.63 width. Move the Summary sheet so that it
is the first sheet in the workbook.
3.000
15
In cell A1, type Pasadena Inventory Summary. Merge & Center the title across the range
A1:D1, and then apply the Title cell style. In cell A2, type As of December 31 and then
Merge & Center the text across the range A2:D2. Apply the Heading 1 cell style.
3.000
16
On the Bulbs sheet, Copy the range A4:A8. Display the Summary sheet and Paste the
selection to cell A5. Apply the Heading 4 cell style to the selection.
1.000
17
In the Summary sheet, in cell B4, type Bulbs. In cell C4 type Trees. In cell D4 type
Bulbs/Trees. Center the column titles, and then apply the Heading 3 cell style.
3.000
18
In cell B5, enter a formula that references cell B4 in the Bulbs sheet so that the Bulbs Total
Items in Stock displays in B5. Create similar formulas to enter the Average Price, Median Price,
Lowest Price, and Highest Price from the Bulbs sheet into the Summary sheet in the range
B6:B9.
5.000
19
Enter formulas in the range C5:C9 that reference the Total Items in stock and the Average
Price, Median Price, Lowest Price, and Highest Price cells in the Trees worksheet.
5.000
20
In cells D5, D6, D7, D8, and D9, insert Column sparklines using the values in the Bulbs and
Trees columns. Format the sparklines using the styles in the first row as follows:
D5: Sparkline Style Accent 1, Darker 50%
D6: Sparkline Style Accent 2, Darker 50%
D7: Sparkline Style Accent 3, Darker 50%
D8: Sparkline Style Accent 4, Darker 50%
D9: Sparkline Style Accent 5, Darker 50%
10.000
21
To the range B5:C5, apply Comma Style with zero decimal places, and to the range B6:C9,
apply Accounting Number Format. Center the Summary worksheet Horizontally and change
the Orientation to Landscape. Insert a custom footer in the left section with the file name.
4.000
Updated: 08/07/2017
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
22
Excel Project
Points
Possible
Instructions
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Summary, Bulbs, Trees. Save and close the worksheet. Exit Excel. Submit the file
as directed.
Total Points
Updated: 08/07/2017
3
0.000
100.000
Current_Instruction.docx
Pasadena Facility: Inventory Status of Bulbs
As of December 31
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Tulip Types
Tulip Total Quantity
Quantity in Stock
78
88
109
49
175
128
55
165
136
49
222
56
165
123
89
126
52
75
25
42
49
52
70
129
255
75
135
75
Item #/Category
1239-Daffodils
1265-Daffodils
1285-Dahlias
1296-Daffodils
1325-Daffodils
1356-Dahlias
1365-Daffodils
1368-Gladiolus
1379-Begonias
1389-Dahlias
1425-Gladiolus
1436-Dahlias
1452-Daffodils
1453-Tulips
1456-Dahlias
1456-Begonias
1456-Callas
1463-Daffodils
1465-Tulips
1465-Dahlias
1468-Callas
1469-Begonias
1478-Daffodils
1482-Gladiolus
1482-Gladiolus
1489-Tulips
1489-Tulips
1498-Tulips
Item Name
Exotic
Bell Song
Haley Jane
Yellow Hoop
Split Crown
Contessa
Jonquilla
Butterfly Mix
Fringed Mix
Decorative Glory
Mon Amour
Topmix Yellow
Campernella
Apeldoom Elite
Stars Lady
Pendula Cascade
Zantedeschia Mix
Manley
Goldstick
Topmix Color
Elliottiana White
Multiflora
Narcissa Mixed
Nathalie
Morning Gold
Maureen
Purple Flag
Passionate
Retail Price
10.98
7.45
8.58
6.98
4.75
7.89
6.89
8.25
12.99
6.98
7.58
5.95
5.25
11.25
8.59
9.89
10.95
7.85
9.59
7.52
4.99
9.89
9.98
16.58
7.85
12.85
14.85
8.59
Size
18"
18"
12"
18"
18"
12"
18"
25'
15"
12"
25"
12"
18"
10"
12"
15"
20"
18"
10"
12"
20"
15"
18"
25"
25"
10"
10"
10''
Pasadena Facility: Inventory Status of Trees
As of December 31
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Evergreen Types
Evergreen Total Quantity:
Quantity in Stock
49
35
126
87
14
110
32
42
175
108
82
54
156
185
210
385
108
126
285
110
185
182
95
153
42
45
235
87
Item #/Category
1783-Nut Trees
2133-Shade Trees
2345-Shade Trees
2347-Nut Trees
2348-Nut Trees
2389-Evergreens
2469-Nut Trees
2587-Flowering Trees
2850-Fruit Trees
2895-Nut Trees
3252-Evergreens
3933-Nut Trees
4535-Evergreens
4567-Nut Trees
4587-Shade Trees
4597-Shade Trees
4625-Fruit Trees
4652-Nut Trees
4822-Flowering Trees
5463-Nut Trees
5522-Shade Trees
5635-Shade Trees
5636-Shade Trees
5664-Nut Trees
6255-Shade Trees
8256-Fruit Trees
8582-Evergreens
9952-Flowering Trees
Item Name
Stuart Grafted Pecans
Green Weeping
Kentucky Coffee
Buckeye
Chinese Chestnut
Andorra Juniper
American Beech
Flowering Cherry
Nectarines
Pecan
American Arborvita
American Filbert
Canadian Hemlock
Shelle Bark Hickory
Black Gum
Red Maple
Persimmon
Grafted Pecans
Chinese Dogwood
English Walnut
Hybrid Poplar
Scarlet Oak
Lombardy Poplar
Pecan Seedling
Sawtooth Oak
Pear
Red Cedar
Catalpa
Retail Price
42.00
40.00
22.00
56.00
32.00
16.00
65.00
38.00
59.00
44.00
9.00
42.00
8.00
49.00
42.00
35.00
35.00
38.00
17.00
38.00
23.00
36.00
48.00
29.00
23.00
18.00
10.00
22.00
Size
8'
13'
6'
7'
6'
6'
12'
7'
9'
6'
3'
8'
2'
7'
8'
6'
6'
8'
4'
8'
8'
5'
7'
7'
5'
5'
4'
5'
Purchase answer to see full
attachment