Assignment 4 Exam Practice

#| label: Setup
#| include: FALSE # "Run the code, but do not show any code or output thereof"

library(tidyverse) # For all the tidy things!
library(lubridate) # Convenient transforming of date values.
library(knitr)     # Useful tools when 'knitting' (rendering) Quarto documents.

Introduction to the Data

The data that will be used or this assignment will be 22 years of financial data from Xavier University and its peers. This data comes from Joel’s personal server and can be accessed via this link https://asayanalytics.com/xavier_peers-csv

The data is organized so that each row is yearly data of each college dating back to 2015. Each variable provides a detail of the a characteristic of the school or a financial statistic for that year. A summarized data dictionary, an example of the data, and some summary statistics calculated by city are provided in the following tabset:

#| label: Loading in the xavier peers dataset
#| include: FALSE
xavier_peers <- 
  read.csv("https://asayanalytics.com/xavier_peers-csv")

#| label: Initial data wrangling
#| 

xavier_peers <- xavier_peers %>% 
  mutate(filing_updated = ymd(filing_updated),
         total_exec_comp = as.numeric(total_exec_comp),
         total_other_comp = as.numeric(total_other_comp))


xavier_peers <- xavier_peers %>%
  mutate(
    oer = total_fun_expenses / (total_tuition_revenue + total_gifts)
  ) %>%
  group_by(ein) %>%
  mutate(
    avg_oer = mean(oer, na.rm = TRUE),
    better_than_avg = oer < avg_oer
  ) 

Visualizations

Visualization 1:

This shows the relationship between total compensation vs. executive compensation. The graph shows an upward trend for both AJCU and Non-AJCU colleges. However, AJCU universities tend to have more compensation than Non-AJCU school.

Visualization 2:

This visualization shows the ratio of executive total compensation between AJCU colleges in the Big East. It shows that all school with this criteria have a ration above 1% and Xavier being the highest at 3.5%.

Visualization 3:

This graph shows the variance of total revenue over the past 9 years. It shows little variance, but a slight up trend is present.

#| label: Visualizations
#| echo: FALSE

#3.1
xavier_peers %>% 
  ggplot(aes(x = total_exec_comp,
             y = total_other_comp))+
  geom_point()+
  facet_wrap(~ ajcu, nrow = 2)

#3.2
xavier_peers3.2 <- xavier_peers %>% 
  filter(ajcu == TRUE,
         big_east == TRUE) %>% 
  group_by(name) %>% 
  summarise(total_exec_comp = sum(total_exec_comp, na.rm = TRUE),
            total_other_comp = sum(total_other_comp, na.rm = TRUE)) %>% 
  mutate(ratio = total_exec_comp/total_other_comp)
  
xavier_peers3.2 %>% 
  ggplot(aes(x = name, y = ratio)) +
  geom_col() +
  labs(title = "Ratio for executive comp to Other comp")

#3.3
xavier_peers %>% 
  ggplot(aes(x = as.factor(tax_file_yr), y = total_revenue))+
  geom_boxplot()+
  labs(main = "variance of total revenue by year",
       x = "Year"
       )

#| label: Part 4
#| include: false

revenue_trends <- xavier_peers %>%
  group_by(tax_file_yr) %>%
  summarise(
    avg_tuition = mean(total_tuition_revenue, na.rm = TRUE),
    avg_gifts = mean(total_gifts, na.rm = TRUE),
    avg_expenses = mean(total_fun_expenses, na.rm = TRUE)
  )

revenue_trends %>% 
ggplot(aes(x = tax_file_yr)) +
  geom_point(aes(y = avg_tuition), color = "blue")+
  geom_point(aes(y = avg_gifts), color = "red")+
  geom_point(aes(y = avg_expenses), color = "green")+
  labs(
    title = "Average Tuition, Gifts, and Expenses by Year",
    x = "Year",
    y = "Average Amount"
  ) 

func_expense <- xavier_peers %>% 
  group_by(tax_file_yr) %>% 
  summarise(total_fun_expenses = sum(total_fun_expenses))

func_expense %>% 
  ggplot(aes(x = tax_file_yr, y = total_fun_expenses))+
  geom_point()+
  labs(
    title = "Total Functional Expenses Per Year",
    x = "Year",
    y = "Total Function Expenses"
  ) 

Response to Graphs for 4.2

For graph 1, AVG tuition = BLUE, AVG gifts = RED, AVG expense = GREENBased off of these graphs it looks like avg gifts stays pretty steady, but it seems that there is a steady increase in both the averages and functional expenses. There is also a tiny dip in all of the lines nearing 2020 mirroring the effects of the pandemic.