- Go to the UC Office of the President Website and navigate to Organization -> Financial Accounting -> Financial Reports -> Campus Financial Schedules. Download the PDF file for Berkeley for the past five fiscal years: 2019-2020, 2018-2019, 2017-2018, 2016-2017, and 2015-2016.
- Use an optical character recognition (OCR) program such as Tabula to convert the PDF to Excel.
- Extract the tables for Instruction funding for the major UC Berkeley colleges, including the College of Engineering, School of Business Administration, College of Chemistry, School of Education, Graduate School of Journalism, School of Law, College of Letters and Science, School of Information Management, College of Natural Resources, School of Optometry, School of Public Health, Graduate School of Public Policy, and School of Social Welfare. Include department breakdowns when available.
- Clean the data by standardizing department names with title case in Open Refine, formatting values as dollar amounts, and consolidating the data into one worksheet.
- Use the VLOOKUP formula to match up the department names with their funding in one spreadsheet.
- Find errors and manually check and fix the data entries. Some department names may not be adequately parsed by OCR, causing inaccuracies.
- Make the data easier to read by putting college names in bold and freezing the first column and rows up to the year.
Partial screenshot of cleaned data:
Before manipulating the data, duplicate the cleaned data set to refer to later in case of mishaps. Conduct analysis on a copy of the cleaned data.
- Let's answer this question using percent change to give a fair comparison of changes over time between departments of different sizes.
- Add a new column called "Percent Change Between 15-16 and 19-20," and type "=" to enter the function for percent change: (New - Old)/(Old).
3. Repeat the formula for all rows with funding data by dragging the bottom right corner of the cell down the column.
4. Click the percent button to format the column as a percent. Use the buttons to the right to decrease or increase decimal places.
5. Turn on filters. In the college/department column, click "Clear," then select the schools/colleges we're interested in.
6. Using filters, sort the percent change column Z-A to see the greatest percent changes on top.
The School of Information Management saw the greatest percent increase in instructional funding at +241%. The only college with less funding in 2019-2020 than in 2015-2016 was the School of Public Health.
- Find the numbers for graduate student enrollment on the Berkeley Graduate Division website
- Separate out the graduate school rows and hide all other rows. Manually add (and double check!) the Fall 2020 enrollment data into a new column.
3. Calculate the instructional funding dollars per student for 2019-2020 by dividing the funding cell by the enrollment figure.
4. Apply a filter and sort the column from Z-A to find the highest funding dollars per student.
Although the School of Business Administration has the greatest number of students and total instructional funding, the School of Social Welfare had the greatest funding per student at $194 instructional dollars for each of its 224 students in 2019-2020.
Question 3: Which major in the College of Engineering had the most instructional funding per undergraduate student in 2019-2020?
- Find the breakdown of undergraduate enrollment using the Wayback Machine for the College of Engineering Facts and Figures page. Enter the data into a new column in the datasheet.
2. In a new column, calculate instructional dollars per student using a formula.
Industrial Engineering and Operations Research had the most instructional funding per undergrad at $44.
Question 4: Which three departments within the College of Letters and Science had the most funding for each year?
- Right click on selected rows to hide all rows except for the College of Letters and Science departments.
2. Use filters on each school year to sort Z-A and view the greatest value at the top of the column. Be sure to select the entire spreadsheet when turning on filters.
2019-2020: Molecular and Cell Biology
2018-2019: Economics
2017-2018: Molecular and Cell Biology
2016-2017: Molecular and Cell Biology
2015-2016: Molecular and Cell Biology
Question 5: Of the departments in the College of Letters and Science with more than $10 thousand in instructional funding for 2019-2020, which had the greatest decrease in funding since 2015-2016?
- Once again, we will use percent change to answer this question! First, let's hide all rows except for the departments in the College of Letters and Science by highlighting and right-clicking them.
- Now filter for all departments with more than $10 thousand in funding. Turn on filters for the sheet, and then filter by condition under for the 2019-2020 column. Select "Greater than," enter 10, then click "OK."
3. Add a new column and enter a formula to calculate percent change between 2015-2016 and 2019-2020. Fill in the same formula for the rest of the column and format as a percent.
4. Sort the percent change column from A-Z to display the most negative percent change at the top.
Of the departments in the College of Letters and Science with over $10 thousand in instructional funding, the department of Ancient History And Mediterranean Archaeology experienced the most dramatic decrease in funding at a percent change of -63.73%.
In Question 3, I found that Industrial Engineering and Operations Research (IEOR) had the most funding per undergraduate student out of the College of Engineering majors. In this story, I’d investigate how this translates to students’ experience: how does the number of classes and units offered per semester compare between each of the majors? What are the student-to-instructor and student-to-GSI ratios? What determines how much money the departments have for instructional funding?
- Dat Le, Assistant Dean, Finance & Administration for the College of Engineering ([email protected])
Mr. Le is in charge of developing CoE’s budget process and advises the College’s leadership on how to allocate funds. He could provide insights on how the funds are allocated between instruction and other bins and contextualize where the funds come from. Stacey Shulman, Assistant Dean, Academic Affairs for the College of Engineering ([email protected] and 510 642-5935) As the Assistant Dean of Academic Affairs, Ms. Shulman could provide context on the decision-making process of hiring part-time lecturers and GSIs. She may be able to provide information on the Temporary Academic Support (TAS) budget, which funds temporary faculty, graduate student instructors (GSIs), readers, and tutors who assist permanent faculty. Access to the full TAS budget and help interpreting the funding allocations could help determine how students’ academic experience is impacted from the top down. - Rita d’Escoto, Director, Budget and Financial Operations for Berkeley’s Office of the Vice Chancellor of Finance ([email protected] and 510 642-0337)
Ms. d’Escoto could provide the campus Chief Financial Office’s perspective on budget allocation and contextualize how the budget is allocated to the different schools and colleges.
After talking to these administrators to help interpret the financial budgets, it would be useful to talk to department chairs (eg. IEOR Chair Alper Atamturk - [email protected]), teaching faculty, and students for their perspectives as direct beneficiaries.
- College of Engineering Master Plan
The Master Plan lays out the College of Engineering’s visions and proposed projects. The report could provide context on the College’s goals for empowering students. - College of Engineering Equity and Inclusion Strategic Plan
The EI Strategic Plan includes a section on educational equity for undergraduate and graduate students, which can provide insights on what the College’s priorities are for students’ academic experience. It would be interesting to ask financial administrators where the budget for these goals comes from.
The following is a scatterplot generated with Data Wrapper showing student enrollment on the x-axis and instructional funding on the y-axis. The dots are scaled by $ per student, which you can reveal with the tooltip by hovering over a data point.
