Learning Objective: This assignment is designed to gain additional proficiency in the use of MS Excel Tables, Pivot Tables and lookups. For this assignment, you will upload your Excel file to

Canvas so that it can be accessed by just clicking on the file's link. To get credit for this assignment: 1 Deliver the assignment10.xlsx file to Canvas on time. Directions: Follow the requirements listed on the next page. Create the Excel Table and the Pivot Tables as indicated. Enter your name on cell J5 and honor code of the Drinks worksheet. Requirements: Excel Tables The Orders worksheet shows orders from a MegaStore. Some research needs to be done to locate a group of information. A. Convert the Orders dataset into an Excel Table (remember you only need to select one cell to create excel table) B. Format the Sales and Profit to be currency ($) C. Format the discount to be percentage (%) D. Apply the Blue, Table Style Medium 9 to the table. E. Add a Total row using the totals feature of excel tables and use the properties of the Total Row to calculate: 1. the sum of sales (Column R). Page 1 of 2/n2. the sum of profit (column U). F. Then use filters at top of table to locate the records of the Orders dataset that comply with all the following characteristics: 1. City is Newark, Columbus, or Tampa. 2. Segment is Home Office. In column V, name the column per unit profit, create a formula for each row in that column to be profit/ quantity. In the totals row for the table, please set column V to get the maximum per unit profit. Put the product id associated with the max per unit profit into cell A313/nPivot Tables, VLOOKUP, and Pivot Charts Pivot Table 1: A. Use the Orders worksheet and create a Pivot Table with a filter by Region, and display rows of City, and sum of Quantity and Sum of Sales on a new worksheet B. Filter the information so that only East region is displayed. C. Format the Sum of Sales column to be currency ($) with no decimal places D. Rename the Pivot Table's worksheet: Pivot Table Sales. E. On Pivot Table Sales sheet put "What city has the highest total Sales $" into cell H1 and answer the question in H2 Pivot Table 2 & Pivot Chart: A. Use the Drinks worksheet and create another sheet with Pivot Table that displays the sum of Sales $ organized by: Product and Month as row labels (in that order) and State as column labels. Format all sales numbers as currency ($) with no decimal places. Click on the + next to Breakfast Power so you can see each of the months. B. Collapse the Product rows and create a Clustered Column Pivot Chart keeping on same worksheet. The clustered column chart will show each drink sales and a drill down into Breakfast Power by month. C. Apply Chart Style 14. D. Add Title to Chart labeled Total Sales. E. Rename the Pivot Table's worksheet: Pivot Table Drinks. On Drinks worksheet, create a VLOOKUP table to provide a bonus or $50 when Sales >= $1000 and $1,000 when sales are >= $10,000 Create a Bonus Column in Column E of worksheet. Use the values from column D to go against the VLOOKUP table and return the bonus. Please format the numbers in Column E to be $ with no decimal places. You