excel: Create Pivot Tables and Charts

profilethonolan
Home>Homework Answsers>Information Systems homework help

 i need only Create Pivot Tables and Charts part. i have done the others and i want you to check and contniue from them. maximum time 30 min.

here is the details

 
Setup data sources
1.       Create workbook named “Source-Enrollments” using the whole page at:http://en.wikipedia.org/wiki/List_of_United_States_university_campuses_by_enrollment
2.       Create workbook named “Source-Employment” using the second table at this page: http://www.bls.gov/ro4/cesatl.htm
3.       Create a workbook named “Source-Sales” using the table at: http://www.contextures.com/xlSampleData01.html
4.       Create a blank workbook named “Lookups”
5.       Create a blank workbook named “Transforms-1”
 
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”

  • 12 years ago
  • 8
Report issue
Answer(0)
Bids(1)