Reference no: EM13761135

Set up Lookup Table

1. In the lookup workbook, create a table: with headers: Region, Metro, University, Employment, Enrollments

2. Build the table using direct sourcing from the appropriate tables to create this chart:

East	Atlanta-Sandy Springs-Marietta, GA	University of Central Florida [note 2]	2.1	59,770
Central	Chicago-Joliet-Naperville, IL-IN-WI	Ohio State University	0.9	57,466
West	Dallas-Fort Worth-Arlington, TX	University of Texas at Austin	3.3	51,145

3. Insert this as a formal table named LookupTable1

Set up Analysis Table

1. In the workbook Transforms-1, create headers:

OrderDate

Location

Employment

University

Enrollments

Rep

Item

Units

Cost

Total

2. Location: Use VLOOKUP to associate Source-Sales region with lookup table Metro

3. Employment: Use VLOOKUP to associate Source-Sales region with lookup table Employment

4. University: Use VLOOKUP to associate Source-Sales region with lookup table university

5. Enrollments: Use VLOOKUP to associate Source-Sales region with lookup table enrollments

6. For columns OrderDate, Rep, Item, Units, Cost: Do a direct association with these values from the Source-Sales

7. For column Total: Create a simple formula Units * Cost

Create Pivot Tables and Charts

1. Create a pivot table and associated bar chart to show total sales per rep by Metro area. Label this workbook "Report-1"

2. Create a pivot table and associated bar chart to show total sales per rep by University. Label this workbook "Report-2"

3. Create a pivot table and associated bar chart to show total sales per employment number by University. Label this workbook "Report-3"

4. Create a copy of the Transforms table in a new workbook labeled "Transforms-2" and set up filters. Hide date, rep, item, units, costs columns. Create a pivot table and chart to show total sales per Metro area. Label this workbook "Report-4"

Attachment:- dowmloads.rar