i have attache the excel assignment. can you please complete this and help me out?

Instructions: The following assignment is a 4-part tutorial on more advanced and useful techniques in Excel for data analysis. Read the instructions carefully and work through the steps. Throughout the tutorial there are \"Application Questions\" which introduce each topic. You do not need to answer these. At the end of the tutorial is a section title \"Final application questions.\" You must include an answer to these questions in your submission. Instructions on where to include these answers are provided at the end of the tutorial. Your submission should include an Excel file (.xlsx extension) with 6 tabs/worksheets. Note that some steps require you to delete work you previously completed. This is fine. Follow the instructions exactly. 1 Overview of Excel 2013 Skills Topics Covered Chapter 1 Data Entry Name and Group Worksheets Formatting Sorting and Filtering Page Layout Forms Chapter 2 Data Validation Formulas Conditional Formatting Defining a Name Chapter 3 Pivot Tables Pivot Charts Chapter 4 VLOOKUP IF Function Create a Source Document with controls Spreadsheet Protection Open Excel 2013, double-click on the Blank workbook option found in the top-left corner of the right pane. On the Menu Bar, click File > Save As and save your file as xxx_xxx_Excel.xlsx (where xxx_xxx represents your first name and last name. For example, Mark_Adams_Excel.xlsx). Chapter 1: Data entry, name and group worksheets, formatting, sorting and filtering, page layout, and forms Application Question: How might a manager track inventory quantities on hand? We will begin by creating two worksheets, each containing one inventory list. Data Entry, Naming, and Grouping Worksheets On Sheet1, create a Candy Inventory table. Highlight the Candy Inventory table below by clicking on the upper left corner. Note: Do not highlight the title \"Candy Inventory\". Copy the information by \"right-clicking\" and selecting copy OR holding down the CTRL key and the \"C\" key simultaneously. 2 Paste the information onto Sheet1 by placing your cursor in cell A1 and \"right-clicking\" and selecting paste OR holding down the CTRL key and the \"V\" key simultaneously. Create a new sheet by clicking the icon to the right of the Sheet1 tab. On the new Sheet2, use the same process to create a Popcorn Inventory table. Name Sheet1 Candy by double-clicking on the tab labeled Sheet1 and typing Candy. Note: you may also change the tab color by right clicking on a tab and selecting tab color. Using the same process, name Sheet2 Popcorn. Candy Inventory Item Code Wholesaler 10 10 10 10 Groovy Inc Bulk Co Cheapster Bulk Co Item Number 501 502 503 504 Item Description Almond Joy Nestle Crunch Reese's Cups Kit Kat Quantity on Hand 20 60 175 40 Cost Price .35 .20 .35 .25 .75 .75 1.00 1.00 Item Number 601 602 603 604 Item Description Plain Buttered Carmel Crunch Quantity on Hand 50 80 100 25 Cost Price .50 .55 .60 .75 1.50 1.50 1.50 2.00 Popcorn Inventory Item Code Wholesaler 20 20 20 20 Bulk Co Groovy Inc Groovy Inc Groovy Inc Group the worksheets by holding the CTRL key and left-clicking on the two worksheet tabs, or right-click on one tab and click on \"select all sheets\". You will know that the sheets are grouped because the tab colors are highlighted. Note: grouping allows you to make simultaneous changes to all selected sheets to make formatting more efficient and consistent. Be aware that formatting only applies to identical ranges, if one list is longer than the other, formatting will not extend to the extra rows/columns. Formatting Format the data within the worksheets: Center text and numbers. Highlight the cells that you want to center, click on the Home tab, select the centering icon listed in the Alignment section of the Home ribbon. Widen columns. Highlight the desired area, double-click on the right side of the column header OR drag the right hand border of each column until you are satisfied. Format numbers as currency. Highlight the cost and price columns, right-click the highlighted area and select Format Cells from the pop-up menu. On the Number tab, Select the following options: Currency Category, 2 Decimal places and $ Symbol. Ungroup worksheets - right-click on either worksheet tab, and select Ungroup Sheets. 3 Format each inventory list as a table. Highlight the entire table (including the Header row), on the Home tab, in the Styles section, select Format as Table, select a table style. Note: be sure My table has headers is checked the in the Format As Table pop-up window. Note that drop-down arrows appear next to each header row. These enable you to sort and filter the data. You may want to group your worksheets and adjust your column widths to accommodate the drop-down arrows. Your workbook should appear similar (as you chose your own table style) to the screenshot below. Sorting and Filtering Click on the Candy inventory sheet. Using the drop down arrows, use the sort and filter functions to: Alphabetical Sort. Sort by Item Description, ascending. Select the arrow next to the item description column heading, click on sort, A to Z. Numerical Filter. Show only items that have a Cost between .20 and .25. Select the arrow next to the cost column heading, click on Number Filters, choose Between, enter .20 AND .25. Additional numerical filter. Display only items with a price of 1.00. Using drop down arrow, uncheck (Select All), and check the box next to $1.00. Your worksheet should be similar to the screenshot below: Display all items by turning off filtering. In the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click Clear. Note, Clear removes all filtering criteria, but leaves the drop-down arrows visible. To remove the drop-down arrows completely, in the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click the Filter icon. 4 Note: you may also use the sort and filter icon in the Editing section of the Home tab to apply sorting and filtering. Page Layout Change page layout to landscape. Group the worksheets and click the Page Layout tab. Click the Orientation and select Landscape. Note you may also make other printing decisions here, such as setting margins and hiding gridlines. Ungroup your worksheets. Forms Application Question: Is there a tool to improve data entry efficiency? Add the Form command to the main toolbar. Click the Customize Quick Access Toolbar down arrow and select More Commands (see screenshot below). Select All Commands in the Choose commands from: drop-down list. Scroll down to Form... and click on Form to highlight it, Click the button and click OK (see the screen shot on the next page) now the Form icon should be visible in the top toolbar (see screenshot below). 5 Note: The form command allows you to enter data in a form view, rather than a spreadsheet view. 6 With your cursor in the candy table, click the Form icon now located in the Quick Access Toolbar. In the form dialog box, click New and add the items from the table below (next page) using the form. Item Code Wholesaler 10 10 10 10 Cheapster Bulk Co Groovy Inc Groovy Inc Item Number 505 506 507 508 Item Description Twix Milk Duds Heath Baby Ruth Quantity on Hand 200 175 50 90 Cost Price .25 .40 .50 .50 .75 1.00 1.00 1.00 Notice that the items appear in your worksheet as soon as click the New button. Click Close when you have entered all the new items. Application Question: Can Forms be used to search spreadsheets for particular data? Say you want to locate Twix candy. Open the Form and click Criteria. Type Twix in the Item Description field. Click, Find Next. 7 The Twix record will appear in the form. Search for other items by quantity on hand, cost, and price. 8 Chapter 2: Data validation, formulas, conditional formatting, and defining names Application question: What tools can designers use to make sure that the data entered is reliable (error-free)? Consider different ways to achieve this goal. Data Validation Open the Candy inventory worksheet. Create a data validation rule to ensure that the item number has only three digits. Highlight the Item Number column. Select Data tab. In the Data Tools section, click the Data Validation icon. Enter the following information in each tab (see screenshots below): 1. Settings. Under Validation criteria: select Text length from the Allow drop-down list, select equal to from the Data drop-down list, and select 3 from the Length drop-down list. 2. Input message. Make sure the Show input message when cell is selected option is checked. In the Title textbox, input Item Number and in the Input message textbox type Item Number must be 3 digits. 3. Error alert. Make sure the Show error alert after invalid data is entered option is checked. Select Stop from the Style drop-down list. Input Item Number Error in the Title textbox. Type in Item Number must be exactly 3 digits in the Error Message textbox. Click OK. Test the above rule by trying to enter an item number with more or less than 3 digits. As you have noticed, the input message that appears when an Item Number cell is selected is annoying so remove the input message. Add the following rules. Adjust settings appropriately and provide an error alert. Note: Do not provide an input message. Quantity on hand must be between 0 and 250. The Error Alert Style is Information. Cost must be positive. The Error Alert Style is Warning. Price must be greater than .25. The Error Alert Style is Stop. Test the rules to see if they provide the desired controls. 9 Formulas and Conditional Formatting Application questions: How can you design a spreadsheet to help the manager determine which candies are the most profitable per unit? What Excel feature could you use to alert the manager when quantities drop below a reorder point? Create a new sheet, then copy and paste the tables from each worksheet to a new worksheet to form a single table. Note: Before you past the table, you might want to right-click in cell A1 in the new sheet, select Paste Special and then select Column widths from the Paste Special pop-up box. Copy the header rows from only one table. Name this new sheet Total Inventory. Create additional columns to display: Gross Profit (Price minus Cost) Gross Margin % (Gross Profit divided by Price) Total Cost (Quantity * Cost) Total Price (Quantity * Price) Note: You may want to select the new columns, right-click anywhere in the highlighted area and select Format Cells from the pop-up menu and select the Alignment tab and check the Wrap text Text control if not already checked. Adjust column-widths as needed. Enter appropriate formulas. Note: The formulas will \"copy down\" to all rows as long as the data is still in a table format. Also, the formulas will look a little different because of the table format. Format as appropriate. (Percentage with no decimal places for gross margin and currency for the other items). Add formulas to the bottom row to display \"Total quantity on hand\" by entering an AutoSum function to the entire row. Place the cursor in the first empty cell under the quantity on hand column. Select the Formulas tab, and click the AutoSum icon found on the left side of the ribbon. Experiment with formula options as follows: Total: quantity on Hand, cost, price Average: Cost, price, gross profit, gross margin % Experiment with other Formula options: Count, Min, Max. Delete the bottom row of the table (the one with the formulas) since it is not needed for the next Chapter. Select the row number, right-click, select Delete. Conditional Formatting Apply conditional formatting to alert management when items fall below minimum levels. Highlight the quantity on hand column, select Home tab, In the Styles section, select Conditional Formatting. Choose New Rule from the pop-up menu. In the New Formatting Rule window, select Format cells based on their values from the Select a Rule Type: section. In the Edit the Rule Description: section, select Icon Sets from the Format Style drop-down list. Select the icon set you want from the Icon Style drop-down list. 10 In the Display each icon according to these rules section, set the green icon to >=, Type Number, Value 75. For the yellow icon: >=, Type Number, Value 25. The red icon will change to