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