Please instructions and complete attached excel worksheet. Correctly and completely.

Excel Demo Student Assignment 1. Download the spreadsheet named 'Student Excel Assignment Spring 2017.xlsx' from D2L 2. In the worksheet named 'VLOOKUP' do the following below the Sporting Goods Inventory List: a. Write an 'INDEX' formula to find the value at the intersection of the 4 th row and the 6th column in the Inventory table. b. Write a 'VLOOKUP' formula to find the Reorder Quantity for the Basketball product. HINT: Before creating the above formulas define the appropriate data arrayamed range for the Inventory table 3. In the worksheet named 'HLOOKUP' do the following: a. Inserting the appropriate 'HLOOKUP' formula for Price per Unit and figure out the sales total for the first two transactions of July. HINT: Before creating the formula, define the appropriate data arraysamed ranges for both the ITEM and COSTSANDPRICES tables. 4. In the worksheet named 'SUMIFS, etc.' do the following: a. Write a 'COUNTIFS' formula to count the total number of observations that have a customer number greater than \"101\" and are also located in the \"SW\" sales region. b. Write a 'SUMIFS' formula to summarize the total sales for customers that are in the \"SE\" sales region and that occurred in the last two quarters of the year. 5. In the worksheet named 'PIVOTTABLE' do the following: a. Using the customer data, create a pivot table where you can find the total sales by customer name based on a certain sales region. Please put this pivot table in a new worksheet in the same spreadsheet. 6. Please add your first name and last name to the file name and save the spreadsheet. For instance, I would save the spreadsheet file as 'Student Excel Assignment(MarcusCaylor).xlsx' 7. Upload this file to the Excel Demo Assignment folder in D2L in order to receive credit for the assignment. Sporting Goods Inventory List Item Name Tennis racket Golf club (driver) Can of tennis balls Basketball Football Baseball bat Baseball Soccer ball Baseball glove Hockey stick Total Inventory Cost Preferred Supplier Quantity on Hand Reorder Point Sports Supplies, Inc. 200 100 GA Sports Distribution 250 150 GA Sports Distribution 400 250 Sports Supplies, Inc. 150 100 GA Sports Distribution 600 400 GA Sports Distribution 300 200 We Sell Sports 475 300 We Sell Sports 100 80 We Sell Sports 1000 600 Sports Supplies, Inc. 500 275 Reorder Quantity Cost per Unit Sales Price per Unit Total Item Cost 150 $60.00 $150.00 $12,000.00 150 $80.00 $200.00 $20,000.00 250 $2.00 $4.00 $800.00 200 $11.25 $25.00 $1,687.50 300 $12.00 $30.00 $7,200.00 200 $35.00 $100.00 $10,500.00 300 $6.00 $15.00 $2,850.00 120 $15.00 $30.00 $1,500.00 600 $20.00 $50.00 $20,000.00 275 $40.00 $90.00 $20,000.00 $96,537.50 Sales for July Transaction No. Date of Sale 1 7/1/2016 2 7/2/2016 Item Name Tennis Racket Baseball Dynamic Price List Can add to rows below to automatically calculate price Units Sold 55 65 Price per Unit Sales Total $0 $0 Tennis racket Name Cost per Unit Price per Unit Baseball 60 150 6 15 Baseball bat Golf club (driver) 35 100 Hockey stick 80 190 Football 45 100 14 30 Customer Number Customer name 101 101 101 101 201 201 201 201 301 301 301 301 401 401 401 401 501 501 501 501 Fighting Owl Co. Fighting Owl Co. Fighting Owl Co. Fighting Owl Co. Lexington Industries Lexington Industries Lexington Industries Lexington Industries Kennesaw Supply Kennesaw Supply Kennesaw Supply Kennesaw Supply Chastain Corporation Chastain Corporation Chastain Corporation Chastain Corporation Jonquil Company Jonquil Company Jonquil Company Jonquil Company Location Quarter SE SE SE SE SW SW SW SW NE NE NE NE SE SE SE SE SW SW SW SW 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 Sales $5,200 $6,450 $8,800 $13,540 $3,200 $5,000 $6,150 $10,500 $2,130 $4,500 $5,580 $9,350 $3,250 $4,800 $6,450 $9,940 $4,250 $7,520 $10,150 $12,120 Customer Number Customer Name Gender Sales Region Quarter Sales 100 100 100 100 200 200 200 200 300 300 300 300 400 400 400 400 500 500 500 500 Benjamin Van Zant Benjamin Van Zant Benjamin Van Zant Benjamin Van Zant Jack Daltrey Jack Daltrey Jack Daltrey Jack Daltrey Emily Nicks Emily Nicks Emily Nicks Emily Nicks Molly Hendrix Molly Hendrix Molly Hendrix Molly Hendrix Evan Plant Evan Plant Evan Plant Evan Plant M M M M M M M M F F F F F F F F M M M M SE SE SE SE NE NE NE NE SW SW SW SW SW SW SW SW SE SE SE SE 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 $5,200 $6,450 $8,800 $13,540 $3,200 $5,000 $6,150 $10,500 $2,130 $4,500 $5,580 $9,350 $3,250 $4,800 $6,450 $9,940 $4,250 $7,520 $10,150 $12,120