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.
Grader - Instructions
Excel 2019 Project
Excel_2G_Condiments_Inventory
Project Description:
In the following project, you will edit a worksheet that summarizes the inventory of Condiments and Toppings at the Valley
View facility.
Steps to Perform:
Step
Points
Possible
Instructions
1
Open the Excel workbook Student_Excel_2G_Condiments_Inventory.xlsx downloaded with
this project.
0
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
If the theme is not available, click Browse for Themes, navigate to the files you downloaded
with this project, and then select Ion.thmx.
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
4
Display the Toppings worksheet, and then repeat Step 3 on this worksheet.
5
5
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.
14
6
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.
6
7
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.
8
8
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, a Font Color using in the fifth column, the first color, and a
Fill color set to No Color. Apply Gradient Fill Red Data Bars to the Quantity in Stock column.
4
9
In the Condiments sheet, format the range A14:G42 as a table with headers and apply Sky
Blue, Table Style Light 20. If the table style is not available, choose a similar style. Insert a
Total Row, filter by Category for Relish, and then Sum the Quantity in Stock column. Record
the result in cell B11.
2
Created On: 02/26/2020
1
GO19_XL_CH02_GRADER_2G_AS - Condiments Inventory 1.3
Grader - Instructions
Step
Excel 2019 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
11
In the Toppings sheet, format the range A14:G42 as a table with headers and apply Light
Green, Table Style Light 19. If the table style is not available, choose a similar style. Insert a
Total Row, filter by Category for Salsa, and then Sum the Quantity in Stock column. Record
the result in cell B11.
2
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
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:G. Center the worksheets Horizontally, and
then change the Orientation to Landscape.
9
14
Save your workbook and then ungroup the sheets. Click the Toppings sheet tab, and then
insert a new worksheet. Change the sheet name to Summary and then widen columns A:D to
170 pixels. Move the Summary sheet so that it is the first sheet in the workbook.
2
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.
4
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.
2
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
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
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
20
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 first five styles in the first row in their given order. To apply the Sparkline style, on
the Design tab, in the Style group, click More, and apply the first five styles in the first row.
10
21
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.
5
22
Save and close the file, and then submit for grading.
0
Total Points
Created On: 02/26/2020
2
100
GO19_XL_CH02_GRADER_2G_AS - Condiments Inventory 1.3
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'
Grader - Instructions
Excel 2019 Project
Excel_2G_Inventory
Project Description:
In the following project, you will edit a worksheet that summarizes the inventory of bulbs and trees at the Pasadena facility.
Steps to Perform:
Step
Points
Possible
Instructions
1
Open the Excel workbook Student_Excel_2G_Inventory.xlsx downloaded with this project.
0
2
Change the Theme to Slice. Rename Sheet1 as Bulbs and Sheet2 as Trees, and then make
the Bulbs sheet the active sheet.
3
If the theme is not available, click Browse for Themes, navigate to your downloaded files, and
select Slice.thmx.
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.
5
4
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.
1
5
Display the Trees worksheet, and then repeat Steps 3 and 4 on this worksheet.
6
6
Make the following calculations in each of the two worksheets without grouping the sheets:
•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.
14
7
In each of the two worksheets, make the following calculation without grouping the sheets:
•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.
4
8
Without grouping the worksheets, complete the following in each worksheet:
8
• In cell G14, type Stock Level
• In cell G15, construct 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.
9
Without grouping the worksheets, apply conditional formatting as follows to both worksheets:
4
•Apply Conditional Formatting to the Stock Level column so that cells that contain the text
Order are formatted with Bold Italic, font color set to Automatic, and Fill color set to No Color.
•Apply Gradient Fill Blue Data Bars to the Quantity in Stock column.
Created On: 10/13/2020
1
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.1
Grader - Instructions
Excel 2019 Project
Points
Possible
Step
Instructions
10
In the Bulbs sheet, format the range A14:G42 as a table with headers and apply Light Orange,
Table Style Light 20. If the style isn’t available, choose another style. Insert a Total Row, filter
by Category for Tulips, and then Sum the Quantity in Stock column. Record the result in cell
B11.
4
11
Select the table, clear the filter, Sort the table on the Item # column from Smallest to Largest
(Ascending) 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
12
In the Trees sheet, format the range A14:G42 as a table with headers and apply Light Green,
Table Style Light 19. If the style isn’t available, choose another style. Insert a Total Row, filter
by Category for Evergreens, and then Sum the Quantity in Stock column. Record the result in
cell B11.
4
13
Select the table, clear the filter, Sort the table on the Item # column from Smallest to Largest
(Ascending), 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
14
Group the two worksheets. Merge & Center the title in cell A1 across the range A1:G1 and
apply the Title cell style. Merge & Center the subtitle in cell A2 across the range A2:G2 and
apply the Heading 1 cell style. AutoFit Column A.
5
15
With the worksheets still grouped, center the worksheets Horizontally, change the Orientation
to Landscape, and insert a footer in the left section with the file name. Display the Print
Preview, and then change the Settings to Fit All Columns on One Page.
5
(NOTE: On a Mac, on the Page Layout tab, change the Width to 1 page.)
16
Save your workbook and then ungroup the sheets. Make the Trees sheet the active sheet, and
then insert a new worksheet. Change the new sheet name to Summary and then widen
columns A:D to 170 pixels. Move the Summary sheet so that it is the first sheet in the
workbook.
2
17
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.
4
18
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.
2
19
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
20
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
21
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
22
In cells D5, D6, D7, D8, and D9, insert Column sparklines using the values in the Bulbs and
Trees columns. Format each sparkline using the first five Sparkline styles in the first row.
5
Created On: 10/13/2020
2
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.1
Grader - Instructions
Step
Excel 2019 Project
Points
Possible
Instructions
23
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 footer in the left section with the File Name.
5
24
Save and close the file, and then submit for grading.
0
Total Points
Created On: 10/13/2020
3
100
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.1
Purchase answer to see full
attachment