Calgary Board of Education Funding

cbe_education_funding_2013_2022

# CBE Funding (2015-2023)
library(data.table)
library(httr)
library(readxl)
library(ggplot2)

cbe_funding <- data.table()

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Budget-2022-23.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2022,revenue=1371098, goa_revenue=1237391+50783)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/2021-22-Financial-Results.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2021,revenue=1359701, goa_revenue=1301209+1080)))

#https://cbe.ab.ca/about-us/budget-and-finance/Documents/Stability-in-Challenging-Times-2020-21.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2020,revenue=1331792, goa_revenue=1286865+789)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-2019-20.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2019,revenue=1323010, goa_revenue=1246391+2088)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-2018-19.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2018,revenue=1384715, goa_revenue=1288251+2698)))

#https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-for-the-Calgary-Board-of-Education-2017-18.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2017,revenue=1363147, goa_revenue=1265557+265)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-of-the-Calgary-Board-of-Education-2016-2017.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2016,revenue=1339705, goa_revenue=1230285+376)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-of-the-Calgary-Board-of-Education-for-the-Year-Ended-August-31-2016.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2015,revenue=1325699, goa_revenue=1208794+733)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-results-of-the-Calgary-Board-of-Education.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2014,revenue=1257515, goa_revenue=1144396+1108)))

# https://cbe.ab.ca/about-us/budget-and-finance/Documents/financial-results-of-the-calgary-board-of-education-for-the-year-ended-aug31-2014.pdf
cbe_funding <- rbindlist(list(cbe_funding,data.table(year=2013,revenue=1199444, goa_revenue=1101997+1386)))

ggplot(cbe_funding,aes(x=year,y=revenue))+geom_line()

# Students:

get_xlsx <- function(url){
  GET(url,write_disk(tf <- tempfile(fileext=".xlsx")))
  df <- data.table(read_excel(tf,1L),check.names = T)
  df <- df[School.Authority.Code==3030,list(students=Total)]
  return(df)
}

students <- data.table()

# 2022
students <- rbindlist(
  list(
    students,
    cbind(
      year=2022,
      get_xlsx("https://www.alberta.ca/assets/documents/educ-authority-enrolment-data-2022-2023.xlsx")
      )
    ),
  use.names = T, 
  fill=T)

# 2021
students <- rbindlist(list(students,cbind(year=2021,get_xlsx("https://www.alberta.ca/assets/documents/educ-authority-enrolment-data-2021-2022.xlsx"))),use.names = T, fill=T)

# 2020
students <- rbindlist(
  list(
    students,
    cbind(
      year=2020,
      get_xlsx("https://www.alberta.ca/assets/documents/edc-authority-enrolment-data-2020-2021.xlsx")
    )
  ),
  use.names = T, 
  fill=T)

# 2019
students <- rbindlist(
  list(
    students,
    cbind(
      year=2019,
      get_xlsx("https://www.alberta.ca/assets/documents/edc-authority-enrolment-data-2019-2020.xlsx")
    )
  ),
  use.names = T, 
  fill=T)

# 2018
students <- rbindlist(
  list(
    students,
    cbind(
      year=2018,
      get_xlsx("https://www.alberta.ca/assets/documents/edc-authority-enrolment-data-2018-2019.xlsx")
    )
  ),
  use.names = T, 
  fill=T)


# 2017
students <- rbindlist(
  list(
    students,
    cbind(
      year=2017,
      get_xlsx("https://www.alberta.ca/assets/documents/edc-authority-enrolment-data-2017-2018.xlsx")
    )
  ),
  use.names = T, 
  fill=T)


# 2016
students <- rbindlist(
  list(
    students,
    cbind(
      year=2016,
      get_xlsx("https://www.alberta.ca/assets/documents/edc-authority-enrolment-data-2016-2017.xlsx")
    )
  ),
  use.names = T, 
  fill=T)                

# 2015 and prior not published on (alberta.ca)

# Page 42/135: https://cbe.ab.ca/about-us/budget-and-finance/Documents/Financial-Results-of-the-Calgary-Board-of-Education-for-the-Year-Ended-August-31-2016.pdf
students <- rbindlist(list(students, data.table(year=2015,students=116985)))

# 2014/15, up 3737 from prior year:
students <- rbindlist(list(students, data.table(year=2014,students=110763+3737)))

# 2013/14 https://cbe.ab.ca/about-us/budget-and-finance/Documents/financial-results-of-the-calgary-board-of-education-for-the-year-ended-aug31-2014.pdf
students <- rbindlist(list(students, data.table(year=2013,students=110763)))


# Inflation ---------------------------------------------------------------

#  Table: 18-10-0005-01 (formerly CANSIM 326-0021) 
# https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1810000501&pickMembers%5B0%5D=1.23&cubeTimeFrame.startYear=2013&cubeTimeFrame.endYear=2022&referencePeriods=20130101%2C20220101
cpi_alberta <- data.table(year=seq(2013,2022,by=1),
                          cpi=c(128.9,132.2,133.7,135.2,137.3,140.6,143.1,144.7,149.3,158.9))
cpi_alberta[,inflation_factor:=cpi/128.9] #2013 $$

# Combined ----------------------------------------------------------------

combined <- merge(merge(cbe_funding,students,by=c("year")),cpi_alberta,by=c("year"))

# Revenue in real dollars:
combined[,revenue_real:=revenue/inflation_factor,]
combined[,goa_revenue_real:=goa_revenue/inflation_factor,]

# Per capital revenue, real dollars:
combined[,revenue_real_per_student:=revenue_real*1000/students,]
combined[,goa_revenue_real_per_student:=goa_revenue_real*1000/students,]


# Plot --------------------------------------------------------------------

plot_data <- melt(combined[,list(year,revenue_real_per_student,goa_revenue_real_per_student)],id.vars = "year")
plot_data[,variable:=factor(variable,
                            levels=c("revenue_real_per_student","goa_revenue_real_per_student"),
                            labels = c("Total Revenue","GoA Funding")
)]

p <- ggplot(plot_data,aes(x=year))+
  annotate("rect",xmin=-Inf,xmax=2014.5,ymin=-Inf,ymax=Inf,fill="dodgerblue",alpha=0.2)+
  annotate("rect",xmin=2014.5,xmax=2018.5,ymin=-Inf,ymax=Inf,fill="orange",alpha=0.2)+
  annotate("rect",xmin=2018.5,xmax=2023.5,ymin=-Inf,ymax=Inf,fill="dodgerblue",alpha=0.2)+
  geom_line(aes(y=value,colour=variable))+
  geom_point(aes(y=value,colour=variable))+
  scale_y_continuous(name="CBE Funding per Student", labels=scales::dollar)+
  scale_x_continuous(name="Year (Starting Sept.)",breaks = seq(2013,2023,by=1))+
  scale_colour_manual(name=NULL,values=c("Total Revenue"="black","GoA Funding"="darkgrey"))+
  theme_bw()+
  labs(title="Calgary Board of Education Funding by Year",
       subtitle="Per Student, Adjusted for Inflation",
       caption="Data Sources: CBE, Alberta Education, Statistics Canada")

ggsave(p, filename="CBE_funding_2013-2019.png",width=8,height=4,units="in",dpi=150)