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: 07/10/2019
1
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.0
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: 07/10/2019
2
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.0
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: 07/10/2019
3
100
GO19_XL_CH02_GRADER_2G_HW - Inventory 1.0
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'
Pasadena Inventory Summary
As of December 31
Pasadena facility
Inventory Status of Bulbs
As of December 31
One
re
BOOK
2
Tywy
Tamil
Ara
1
die
wice
1
41
Creare
1
FO
15
Onder
.
O
TES
Com
55
TE
One
LO
Coro
CH
Order
One
SOME
DHE
Pasadena foclity: Inventory Status of Trees
Ased December 31
3.50
bord, Popis
30
30
Ore
Toleranck
Medio
1.00
ve
re
G
Order
NE
Te
O
Ceder
och
34
OG
SK
or
W ram
Purchase answer to see full
attachment