Select Page

Please follow every instruction carefully! File regarding instructions and steps is attached. Materials required are attached as well.

Don't use plagiarized sources. Get Your Custom Essay on
Need help on myitlab excel project
Just from \$10/Page

Unformatted Attachment Preview

Office 2016 – myitlab:grader – Instructions
Excel Project
Project Description:
In the following project, you will edit a worksheet that summarizes the inventory of bulbs and trees at the
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
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.
4.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.
4.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.
8.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: 07/24/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.
8.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.
4.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.
3.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.
3.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%
5.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: 07/24/2017
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
22
Excel Project
Points
Possible
Instructions
Add a new worksheet after the Trees sheet and rename the sheet Tree Summary.
1.000
In cell A1 type Inventory Summary and then Merge & Center across A1:B1. In A2 type By
Tree Type and then Merge & Center across A2:B2. Change the Font Size for A1:A2 to 16 and
apply Bold. In the range A4:B9, type the following data:
23
A4:
A5:
A6:
A7:
A8:
A9:
Tree Category
Evergreens
Flowering Trees
Fruit Trees
Nut Trees
B4:
B5:
B6:
B7:
B8:
B9:
Amount in Inventory
583
414
328
918
1260
4.000
24
AutoFit columns A:B and apply Comma Style with zero decimal places to the numbers in
Column B. Apply the Heading 3 style to A4:B4.
3.000
25
Create a 3-D Pie chart from the data in the range A5:B9 and move the chart to a New Sheet
named Tree Summary Chart.
3.000
26
Apply Chart Style 3. Remove the Legend and display the Data Labels with the Category Name
and Percentage displayed in the Center. Change the font size of the data labels to 12. Change
the Chart Title to Tree Inventory Summary and then insert the File Name in the left section
of the footer. If necessary, move the Tree Summary Chart sheet so that it is the last sheet in
the workbook.
4.000
27
Select the Tree Summary sheet and then center the worksheet Horizontally on the page.
Group the worksheets and insert a footer in the left section with the File Name.
2.000
28
As the Tags/Keywords, types Pasadena Inventory and as the Subject, type your course
name and section number.
2.000
29
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Summary, Bulbs, Trees, Tree Summary, Tree Summary Chart. Save and close the
worksheet. Exit Excel. Submit the file as directed.
0.000
Total Points
Updated: 07/24/2017
3
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
1379-Begonias
1389-Dahlias
1436-Dahlias
1452-Daffodils
1453-Tulips
1456-Dahlias
1456-Begonias
1456-Callas
1463-Daffodils
1465-Tulips
1465-Dahlias
1468-Callas
1469-Begonias
1478-Daffodils
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
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
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
4625-Fruit Trees
4652-Nut Trees
4822-Flowering Trees
5463-Nut Trees
5664-Nut 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
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′