ADDUCE MODUUL Emphasis Heading 1 Heading 2 Normal Excel 2016 Project Grader Instructions G016_XL_CH02_GRADER_2E_AS - Dessert Inventory 1.2 Project Description: In the following project, you will edit a worksheet dealing the current inventory of Marisol Desserts and Spices Austin facility Steps to Perform: Step Instructions Points Possible Start Excel Download and open the file named 9016 wcho2 grader 20 as.xlsx To the right of column B, insert Iwo new columns to create new blank columns C and D. By using Flash F in the two new columns, split the data in column B into a column for item in column C and terrylame in column D. Be sure that Item #and Item Name display as the column headings, fid then delete column B. Note, Mac users, select the range B14 B42, and start the Text to Columns wizard. Select the text de limiter as other and type a dash (-). Set the destination collas C14 and finish the wizard to separate the tem numbers and the item names By using the cut and Paste commands out column Category and paste to column and then delete the empty column D. Apply AutoF to columns AG In cell 34, insert a function to calculate the Total Items in Stock by summing the Quantity in Stock data, and then apply Comma Style with zero decimal places to the result In the appropriate cells in the range 35.BB, Insert functions to calculate the Average, Median, Lowest, and Highest retail prices, and then apply the Accounting Number Format to each result Move the range A4 B8 to the range D4EB, apply the 20%. Accent col style to the range and then select columns D.E and AutoFit In cel C, type Statistics and then select the range CA C8. From the Format Cells dialog box merge the selected cells, and change the text Orientation to 25 Degrees Format cel C with Bold, a Font Sure of 14 t. and then change the Font Color to Green Accent 6. Apply Middle Align and Align Right In the Category column, Replace All occurrences of Pastries with Danish in cel 10, use the COUNTF function to count the number of Cake dessert types in the Category column incel H13, type Stock Lavel in cell H14, enter an Function to determine the theme that must be ordered the Quantity in Stock is less than 50 the Value s Order. Otherwise the VALUE OKFel the formula down through col 42 Apply Conditional Formatting to the Stock Level column so that cels that contain the text Order are formatted with Bold Italie and a font color of Color of Gold, Accent 4 Note Mac sens, ensure that the background color of the cells set to No Color Apply conditional formatting to the Quantity in Stock column by Applying a Gradient Dwa Bar Blue G016 XL CHOR_GRADER ELAS.Dessertventory 12 quraishi go16_xl_ch02 grader_ View OS 5- Home Insert Xout Copy Peste A1 x Wrap Text Draw Page Layout Formulas Data Review Calibri Light... - 11 A A- B I U . . S. A . E33 2 Sx Austin Facility: Inventory Status of Desserts General $. % Merge & Center. ) i Austin Facility Inventory asbesserts Totalems in Stock Media Price Packaging 19 95 1002 24.55 100 28.95 28 01 26.95 11 45.95 400 1695 ISOL Box Cellophane Cellophane Box Item Item Name 72 852-Tamu Mocha 38 502-Butter Fudge 122 803 Fudge Duo 49 402-Apricot Putt 105 356-scotti 150 266-Celebration Pack 81 862-four FRS 62 223-Amandine 38 834 scotti Cheese 55 843-opped ricots 72 832 Morello Cherries 30 311-Oart Orangettes 83 848 Dresden Stellen 61 82 Royal Preserve Duo 36 1267-Cassonade Waffles 151 439 Hoiday Assortiment 55 859 Coconut Macaroon 39 580 Holdayet 61 877- B an Truffles 58 864-Strawberry B CHEYENNNNNNHER 34.95 360 24.95 1001 28.95 600 37.95 16. 32.95 26 16.95 150 28.5 42 02 349 350 15 95 180 3195 260 16954 391Wement 15.95 15 or 26.95 332 44.95 1602 38.95 2802 3195 100 13.95 142 24.95 16 23.95 Go. 47 48 French Man 83 881-Candy Trio 42 58 Orange Sponge 21 27 Pecan Parties 63 228 Pets Fours 44144 Grand Party Assortment 37 918 Mai's Confection Pastries 29.95 7oz Sheet1 MacBook Pro ADDUCE MODUUL Emphasis Heading 1 Heading 2 Normal Excel 2016 Project Grader Instructions G016_XL_CH02_GRADER_2E_AS - Dessert Inventory 1.2 Project Description: In the following project, you will edit a worksheet dealing the current inventory of Marisol Desserts and Spices Austin facility Steps to Perform: Step Instructions Points Possible Start Excel Download and open the file named 9016 wcho2 grader 20 as.xlsx To the right of column B, insert Iwo new columns to create new blank columns C and D. By using Flash F in the two new columns, split the data in column B into a column for item in column C and terrylame in column D. Be sure that Item #and Item Name display as the column headings, fid then delete column B. Note, Mac users, select the range B14 B42, and start the Text to Columns wizard. Select the text de limiter as other and type a dash (-). Set the destination collas C14 and finish the wizard to separate the tem numbers and the item names By using the cut and Paste commands out column Category and paste to column and then delete the empty column D. Apply AutoF to columns AG In cell 34, insert a function to calculate the Total Items in Stock by summing the Quantity in Stock data, and then apply Comma Style with zero decimal places to the result In the appropriate cells in the range 35.BB, Insert functions to calculate the Average, Median, Lowest, and Highest retail prices, and then apply the Accounting Number Format to each result Move the range A4 B8 to the range D4EB, apply the 20%. Accent col style to the range and then select columns D.E and AutoFit In cel C, type Statistics and then select the range CA C8. From the Format Cells dialog box merge the selected cells, and change the text Orientation to 25 Degrees Format cel C with Bold, a Font Sure of 14 t. and then change the Font Color to Green Accent 6. Apply Middle Align and Align Right In the Category column, Replace All occurrences of Pastries with Danish in cel 10, use the COUNTF function to count the number of Cake dessert types in the Category column incel H13, type Stock Lavel in cell H14, enter an Function to determine the theme that must be ordered the Quantity in Stock is less than 50 the Value s Order. Otherwise the VALUE OKFel the formula down through col 42 Apply Conditional Formatting to the Stock Level column so that cels that contain the text Order are formatted with Bold Italie and a font color of Color of Gold, Accent 4 Note Mac sens, ensure that the background color of the cells set to No Color Apply conditional formatting to the Quantity in Stock column by Applying a Gradient Dwa Bar Blue G016 XL CHOR_GRADER ELAS.Dessertventory 12 quraishi go16_xl_ch02 grader_ View OS 5- Home Insert Xout Copy Peste A1 x Wrap Text Draw Page Layout Formulas Data Review Calibri Light... - 11 A A- B I U . . S. A . E33 2 Sx Austin Facility: Inventory Status of Desserts General $. % Merge & Center. ) i Austin Facility Inventory asbesserts Totalems in Stock Media Price Packaging 19 95 1002 24.55 100 28.95 28 01 26.95 11 45.95 400 1695 ISOL Box Cellophane Cellophane Box Item Item Name 72 852-Tamu Mocha 38 502-Butter Fudge 122 803 Fudge Duo 49 402-Apricot Putt 105 356-scotti 150 266-Celebration Pack 81 862-four FRS 62 223-Amandine 38 834 scotti Cheese 55 843-opped ricots 72 832 Morello Cherries 30 311-Oart Orangettes 83 848 Dresden Stellen 61 82 Royal Preserve Duo 36 1267-Cassonade Waffles 151 439 Hoiday Assortiment 55 859 Coconut Macaroon 39 580 Holdayet 61 877- B an Truffles 58 864-Strawberry B CHEYENNNNNNHER 34.95 360 24.95 1001 28.95 600 37.95 16. 32.95 26 16.95 150 28.5 42 02 349 350 15 95 180 3195 260 16954 391Wement 15.95 15 or 26.95 332 44.95 1602 38.95 2802 3195 100 13.95 142 24.95 16 23.95 Go. 47 48 French Man 83 881-Candy Trio 42 58 Orange Sponge 21 27 Pecan Parties 63 228 Pets Fours 44144 Grand Party Assortment 37 918 Mai's Confection Pastries 29.95 7oz Sheet1 MacBook Pro