Pivot Table Class Exercise: MA Students
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.