Agenda for Wednesday Sept. 5
Review Readings
Review Homework
Pivot Tables
Pivot Table Class Exercise
Univ of Arkansas, Fayetteville financials
1) Watch Video On Gathering Financials, Pivot Table and Making A Chart
2. Open Adobe Acrobat Pro.
3. Download this document and open in Adobe Acrobat Pro. University of Arkansas financial report June 2016
(Data Note: Data in this exercise represents revenues pledged for repayment of various bond issues. They do not represent the full budget. If you are curious about that, see: http://financial-affairs.uark.edu/PDFs/1415annualrept.pdf)
4. Find “UAF Various Facility Pledged Revenues” from Pg. 3.
Highlight the table.
Cntl + click to bring up Menu. Select “Export Selection As…”
Export it as an Excel table.
You can only do this in Adobe Acrobat Pro
5. Check and verify your data was imported correctly. Check the totals against the original file.
Copy your data into a new tab.
Create a tab with a data biography, write down the source of your information.
6. Format into dollars, no decimals. Delete Totals Row.
7. Insert New Column A to Categorize Revenues
Title Column “Category”
Code the “Tuition and Fees” and “Bookstore” as “School” in the new Category Column
Everything else is categorized as “Other”
8. Create Pivot table.
Select just the data you downloaded, not the whole sheet
From the Main Menu: Data | Summarize with Pivot Table
A box appears with the select Table and Range and New Worksheet filled in. Select OK
9. Design Pivot Table
Drag Category field into rows
Drag 2012 and 2016 into Values
You have created a Table with two Columns and Two Rows Summarizing the Revenues from 2012 to 2016
10. In Column D, Create a Percentage Change Calculation, 2012-2016
Cell D4 Type formula =(C4-B4)/B4 for Other
Cell D5 Type formula =(C5-B5)/B5 for School
Cell D6 Type formula =(C6-B6)/B6 for School
Format as Percentage
11. Write a short news lead describing your main conclusions from this data
Part 2: Chart
5. Copy Data and “Paste as Values” into a new Tab
6. Delete “Sum 2012” and “Sum 2016” columns. That leaves two columns, Row Labels and Pct Change
Highlight Source and % change,
On the ribbon bar in Excel, Click Insert and Select Clustered Chart.
Homework: Analyze Student Loan Data
Due Saturday, Sept. 8, 11:59 pm. POST on Blackboard
Examine The Data Dictionary and the Data
Add a new column and call it Beauty. Use this categorize Beauty / Cosemotology / Barber / Salon schools versus others.
Create a Pivot Table.
- Answer the following questions with a short answer and by posting a table in a tab on the Excel workbook.
–Number of Schools by City? Write a sentence identifying the top five cities and the number of colleges. Post a table with the total number of cities, ranked in descending order.
–Use your Beauty column as a filter in the pivot table. Write a sentence identifying the top five cities with beauty schools and the number of beauty schools. Post a table with the total number of cities, ranked in descending order.
Sort the Data:
-Sort by Schools with Largest Enrollment. Write a text answer with the top five schools by enrollment.
-Sort by Schools with Highest Median Debt for Graduates. Write a text answer with the top five schools by Median Debt for Graduates.
-Sort by Schools with Highest Median Debt for Student Who Withdrew. Write a text answer with the top five schools byHighest Median Debt for Student Who Withdrew.