excel help

I want the following work done in excel instructions are attached

E_CH02_GOV1_H3_Instructions x
Office 2013 – myitlab:grader – Instructions GO – Excel Chapter 2: Homework Project 3
Inventory

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 Instructions Points Possible
1 Start Excel. Download and open the file named go_e02_grader_h3.xls. 0
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
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. 5
4 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. 3
5 Display the Trees worksheet, and then repeat Steps 3 and 4 on this worksheet. 8
6 Without grouping the sheets, make the following calculation 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. 9
7 Without grouping the sheets, make the following calculation 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. 2
8 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. 4
9 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
10 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. 6
11 Clear the filter from the table. Sort the table on the Item Name column from A to Z, remove the Total Row, and then convert the table to a range. On the PAGE LAYOUT tab, set Print Titles so that row 14 repeats at the top of each page. 6
12 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. 6
13 Clear the filter from the table. Sort the table on the Item Name column from A to Z, remove the Total Row, and then convert the table to a range. On the Page Layout tab, set Print Titles so that row 14 repeats at the top of each page, and then Save your workbook. 6
14 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 3 cell style. Center the worksheets Horizontally, change the Orientation to Landscape, display the Print Preview, and then change the Settings to Fit All Columns on One Page. 7
15 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 170 pixels. Move the Summary sheet so that it is the first sheet in the workbook. 4
16 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
17 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
18 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
19 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
20 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
21 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
22 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. 4
23 Insert a custom footer in the left section with the file name. 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. 0
Total Points 100

Updated: 03/07/2013 3 E_CH02_GOV1_H3_Instructions x

go_e02_grader_h3.xlsx
Sheet1

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 Item #/Category Item Name Retail Price Size

78 1239-Daffodils Exotic 10.98 18″

88 1265-Daffodils Bell Song 7.45 18″

109 1285-Dahlias Haley Jane 8.58 12″

49 1296-Daffodils Yellow Hoop 6.98 18″

175 1325-Daffodils Split Crown 4.75 18″

128 1356-Dahlias Contessa 7.89 12″

55 1365-Daffodils Jonquilla 6.89 18″

165 1368-Gladiolus Butterfly Mix 8.25 25′

136 1379-Begonias Fringed Mix 12.99 15″

49 1389-Dahlias Decorative Glory 6.98 12″

222 1425-Gladiolus Mon Amour 7.58 25″

56 1436-Dahlias Topmix Yellow 5.95 12″

165 1452-Daffodils Campernella 5.25 18″

123 1453-Tulips Apeldoom Elite 11.25 10″

89 1456-Dahlias Stars Lady 8.59 12″

126 1456-Begonias Pendula Cascade 9.89 15″

52 1456-Callas Zantedeschia Mix 10.95 20″

75 1463-Daffodils Manley 7.85 18″

25 1465-Tulips Goldstick 9.59 10″

42 1465-Dahlias Topmix Color 7.52 12″

49 1468-Callas Elliottiana White 4.99 20″

52 1469-Begonias Multiflora 9.89 15″

70 1478-Daffodils Narcissa Mixed 9.98 18″

129 1482-Gladiolus Nathalie 16.58 25″

255 1482-Gladiolus Morning Gold 7.85 25″

75 1489-Tulips Maureen 12.85 10″

135 1489-Tulips Purple Flag 14.85 10″

75 1498-Tulips Passionate 8.59 10”

Sheet2

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 Item #/Category Item Name Retail Price Size

49 1783-Nut Trees Stuart Grafted Pecans 42.00 8′

35 2133-Shade Trees Green Weeping 40.00 13′

126 2345-Shade Trees Kentucky Coffee 22.00 6′

87 2347-Nut Trees Buckeye 56.00 7′

14 2348-Nut Trees Chinese Chestnut 32.00 6′

110 2389-Evergreens Andorra Juniper 16.00 6′

32 2469-Nut Trees American Beech 65.00 12′

42 2587-Flowering Trees Flowering Cherry 38.00 7′

175 2850-Fruit Trees Nectarines 59.00 9′

108 2895-Nut Trees Pecan 44.00 6′

82 3252-Evergreens American Arborvita 9.00 3′

54 3933-Nut Trees American Filbert 42.00 8′

156 4535-Evergreens Canadian Hemlock 8.00 2′

185 4567-Nut Trees Shelle Bark Hickory 49.00 7′

210 4587-Shade Trees Black Gum 42.00 8′

385 4597-Shade Trees Red Maple 35.00 6′

108 4625-Fruit Trees Persimmon 35.00 6′

126 4652-Nut Trees Grafted Pecans 38.00 8′

285 4822-Flowering Trees Chinese Dogwood 17.00 4′

110 5463-Nut Trees English Walnut 38.00 8′

185 5522-Shade Trees Hybrid Poplar 23.00 8′

182 5635-Shade Trees Scarlet Oak 36.00 5′

95 5636-Shade Trees Lombardy Poplar 48.00 7′

153 5664-Nut Trees Pecan Seedling 29.00 7′

42 6255-Shade Trees Sawtooth Oak 23.00 5′

45 8256-Fruit Trees Pear 18.00 5′

235 8582-Evergreens Red Cedar 10.00 4′

87 9952-Flowering Trees Catalpa 22.00 5′

E_CH03_GOV1_H3_Instructions x
Office 2013 – myitlab:grader – Instructions GO! – Excel Chapter 3: Homework Project 3
Operations

Project Description:
In the following project, you will edit a worksheet that will be used to summarize the operations costs for the Public Works Department.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download and open the file named go_e03_grader_h3.xlsx. 0
2 In the Expenses worksheet, calculate row totals for each Expense item in the range F5:F9. Calculate column totals for each quarter and for the Annual Total in the range B10:F10. 4
3 In cell G5, construct a formula to calculate the Percent of Total by dividing the Annual Total for Salaries and Benefits by the Annual Total for Totals by Quarter. Use absolute cell references as necessary, format the result in Percent Style, and then Center. Fill the formula down through cell G9. 4
4 Use a 3-D Pie chart to chart the Annual Total for each item. Move the chart to a new sheet and then name the sheet Annual Expenses Chart. 6
5 For the Chart Title, type Summary of Annual Expenses and format the chart title using WordArt Style Fill – Blue, Accent 1, Shadow—in the first row, the second style. Change the chart title font size to 28. 10
6 Remove the Legend from the chart and then add Data Labels formatted so that only the Category Name and Percentage display positioned in the Center. Change the data labels Font Size to 12, and apply Bold and Italic. 6
7 Format the Data Series using a 3-D Format effect. Change the Top bevel and Bottom bevel to Circle. Set the Top bevel Width and Height to 50 pt and then set the Bottom bevel Width and Height to 256 pt. Change the Material to the fourth Standard Effect—Metal. 10
8 Display the Series Options, and then set the Angle of first slice to 125 so that the Salaries and Benefits slice is in the front of the pie. Select the Salaries and Benefits slice, and then explode the slice 10%. Change the Fill Color of the Salaries and Benefits slice to a Solid fill using Green, Accent 6, Lighter 40%—in the last column, the fourth color. 4
9 Format the Chart Area by applying a Gradient fill using the Preset gradients Light Gradient – Accent 4. Format the Border of the Chart Area by adding a Solid line border using Gold, Accent 4 and a 5 pt Width. 6
10 Display the Page Setup dialog box, and then for this chart sheet, insert a custom footer in the left section with the file name. 4
11 Display the Expenses worksheet, and then by using the Quarter names and the Totals by Quarter, insert a Line with Markers chart in the worksheet. Move the chart so that its upper left corner is positioned slightly inside the upper left corner of cell A12. As the Chart Title type City of Pacifica Bay Annual Expense Summary. 6
12 Format the Bounds of the Vertical (Value) Axis so that the Minimum is 2100000 and the Major unit is at 50000. Format the Fill of the Chart Area with a Gradient fill by applying the Preset gradient Light gradient Accent 3—in the first row, the third gradient. Format the Plot Area with a Solid fill using White, Background 1—in the first column, the first color. 6
13 Copy the Annual Total in cell F10 and then use Paste Special to paste Values & Number Formatting in cell B35. In cell C35, construct a formula to calculate the Projected Expenses after the forecasted increase of 3.5% in cell B31 is applied. Fill the formula through cell F35, and then use Format Painter to copy the formatting from cell B35 to the range C35:F35. 10
14 Change the Orientation of this worksheet to Landscape, and then use the Scale to Fit options to fit the Height to 1 page. From the Page Setup dialog box, center the worksheet Horizontally, and insert a custom footer in the left section with the file name. 10
15 Display the Projected Decrease worksheet. In cell C5, calculate the Percent of Total for the first expense, apply Percent Style, and then copy the formula down for the remaining expenses. 4
16 Copy cell B8, and then use Paste Special to paste the Values & Number Formatting to cell B13. Copy and Paste cell C8 to cell C13. With cell C13 selected, use Goal Seek to determine the goal amount of Professional Services expenses in cell B13 if the value in C13 is set to 25%. 6
17 From the Page Setup dialog box, center this worksheet Horizontally, and insert a custom footer in the left section with the file name. 4
18 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Annual Expenses Chart, Expenses, Projected Decrease. Save and close the workbook. Exit Excel. Submit the file as directed. 0
Total Points 100

Updated: 01/07/2013 1 E_CH03_GOV1_H3_Instructions x

go_e03_grader_h3.xlsx
Expenses

City of Pacifica Bay

Summary of Annual Expenses

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Annual Total Percent of Total

Salaries and Benefits $ 721,216 $ 810,451 $ 785,960 $ 788,657

Machinery and Equipment 325,723 289,430 254,638 336,354

Materials and Supplies 445,420 398,675 348,642 254,233

Professional Services 563,243 689,243 542,683 976,245

Miscellaneous 227,126 224,876 228,238 223,827

Totals by Quarter

Expenses 5-Year Forecast

Forecasted Increase 3.5%

Year 2016 2017 2018 2019 2020

Projected Expenses

Projected Decrease

City of Pacifica Bay

Projected Decrease in Expenses

Annual Total Percent of Total

Salaries and Benefits $ – 0

Machinery and Equipment – 0

Materials and Supplies – 0

Professional Services – 0

Miscellaneous – 0

Totals by Quarter $ – 0

Goal: Decrease Professional Services to 25%

Goal Amount