labels <-c(UnrestrictedFund ="Unrestricted Fund ($ in millions)",UnrestrictedFundPerExp ="Unrestricted Fund (% of Total Expenditures)",NetRevenue ="Net Revenue ($ in millions)",NetRevenuePerExp ="Net Revenue (% of Total Expenditures)",Revenue ="Total Revenue (in millions of $)",UnrestrictedRevenue ="Unrestricted Revenue (in millions of $)",LCFFRevenue ="LCFF Revenue (in millions of $)",ClassifiedTotalCompPerRev ="Classified Total Compensation (% of Revenue)",ClassifiedSalariesPerRev ="Classified Total Salaries (% of Revenue)", ClassifiedBenefitsPerRev ="Classified Total Benefits (% of Revenue)")
Show the code
DistrictName <-"Sacramento City Unified"DistrictAcroynm <-"SUSD"data_hline <-data.frame(hline =c(0, 3))data_hline_max <-data.frame(hline =c(0, 10))data_hline_0 <-data.frame(hline =c(0, 0)) data_text_min <-data.frame(label =c("3% Fund Minimum (CDE Law)"),x =c(2004),y =c(3.5))data_text_max <-data.frame(label =c("10% Fund Cap (EC 42127.01) for 2022-23 (beyond?)"),x =c(2004),y =c(10.5))data_text_future <-data.frame(label =c("Figures after 2024 are \nDistrict projections \n(likely shorting future \nfinances)"),x =c(2024.1),y =c(13))data_text_future2 <-data.frame(label =c("Figures after 2024 are \nDistrict projections \n(likely shorting future \nfinances)"),x =c(2024.1),y =c(60))
Show the code
Sacramento_filtered_all <- ca_sum_data_all |>filter(District=="Sacramento City Unified")Sacramento_filtered_all_with_future <- Sacramento_filtered_all |>rbind(Sacramento_projections_future)
Show the code
Sacramento_USD_filtered_net_rev_and_urf_exp <- Sacramento_filtered_all_with_future |>filter( Type=="NetRevenuePerExp"| Type=="UnrestrictedFundPerExp") |>mutate(Value = Value*100)Sacramento_USD_filtered_reserve_fundexp <- Sacramento_filtered_all_with_future |>filter( Type=="ReserveFundPerExp") |>mutate(Value = Value*100)Sacramento_USD_filtered_net_rev_and_urf_tot <- Sacramento_filtered_all_with_future |>filter( Type=="NetRevenue"| Type=="UnrestrictedFund")Sacramento_USD_filtered_ada_tot <- Sacramento_filtered_all_with_future |>filter( Type=="K12ADA") |>mutate(Value =round(Value, digits=0))ca_yearly_sum_data_analysis_filtered <- ca_yearly_sum_data_analysis |>filter( Type=="NetRevenuePerExp"| Type=="UnrestrictedFundPerExp") |>mutate(Summary="CA Mean (Districts with at least 1000 students)") |>mutate(Value = Value *100) |>rename(District="Summary",Value="Value") |>relocate(District, .before ="YearEnd")Sacramento_USD_filtered_revenue <- Sacramento_filtered_all_with_future |>filter( Type=="Revenue"| Type=="UnrestrictedRevenue"| Type=="LCFFRevenue") |># mutate(Value = round(Value, digits=0)) |> arrange(match(Type, c("Revenue", "UnrestrictedRevenue", "LCFFRevenue"))) |>arrange(YearEnd)Sacramento_USD_filtered_revenue_and_URF <- Sacramento_filtered_all_with_future |>filter( Type=="Revenue"| Type=="UnrestrictedFund") |>arrange(match(Type, c("Revenue", "UnrestrictedFund"))) |>arrange(YearEnd)Sacramento_USD_filtered_classified_exp <- Sacramento_filtered_all_with_future |>filter( Type=="ClassifiedTotalCompPerRev"| Type=="ClassifiedSalariesPerRev"| Type=="ClassifiedBenefitsPerRev") |>arrange(match(Type, c("ClassifiedTotalCompPerRev", "ClassifiedSalariesPerRev", "ClassifiedBenefitsPerRev"))) |>mutate(Value = Value *100) |>arrange(YearEnd)ca_yearly_sum_data_analysis_filtered_classified_exp <- ca_yearly_sum_data_analysis |>filter( Type=="ClassifiedTotalCompPerRev"| Type=="ClassifiedSalariesPerRev"| Type=="ClassifiedBenefitsPerRev") |>mutate(Summary="CA Mean (Districts with at least 1000 students)") |>mutate(Value = Value *100) |>rename(District="Summary",Value="Value") |>relocate(District, .before ="YearEnd")ca_yearly_sum_data_analysis_filtered_ReserveFundPerExp <- ca_yearly_sum_data_analysis |>filter(Type=="ReserveFundPerExp") |>mutate(Summary="CA Mean (Districts with at least 1000 students)") |>mutate(Value = Value *100) |>rename(District="Summary",Value="Value") |>relocate(District, .before ="YearEnd")ca_yearly_sum_data_analysis_filtered_ada <- ca_yearly_sum_data_analysis |>filter(Type=="K12ADA") |>mutate(Summary="CA Total") |>rename(District="Summary",Value="Value") |>relocate(District, .before ="YearEnd")Sacramento_USD_filtered_with_state <-bind_rows( Sacramento_USD_filtered_net_rev_and_urf_exp, ca_yearly_sum_data_analysis_filtered)Sacramento_USD_filtered_classified_with_state <-bind_rows( Sacramento_USD_filtered_classified_exp, ca_yearly_sum_data_analysis_filtered_classified_exp)Sacramento_USD_filtered_ada_with_state <-bind_rows( Sacramento_USD_filtered_ada_tot, ca_yearly_sum_data_analysis_filtered_ada)Sacramento_USD_filtered_reserve_with_state <-bind_rows( Sacramento_USD_filtered_reserve_fundexp, ca_yearly_sum_data_analysis_filtered_ReserveFundPerExp)
1 Updates
2024-10-31
Sections 4.4, 4.5, and 4.6 have all been updated.
4.4 Includes summary tables for:
Annual Costs (all proposals)
Two-Year Costs (all proposals)
Two-Year Costs (all proposals) with additional costing assumptions
They’ve also been updated to show the costs for both Alternative 2 (2% ranges, 4.25 steps) and 2.1 (2%/4%).
4.5. Same as before, just with both Alt 2 and Alt 2.1
4.6 New section, includes all our proposed salary schedule fixes after 2% raise.
2024-10-17
Added 4 new salary schedule idea.
Created 2-year costing summary table and plots of all economic proposals
Added section comparing distributional analysis of all 6 salary schedule fixes.
2024-10-15
New costing analysis for SEIU 1021’s 3 new salary schedule ideas.
Updated financial analysis to include 2023-24 Unaudited Actuals. Previous analysis was based on Estimated Actuals from 2024-07-01 Adopted Budget. The financial situation radically flipped from record deficit and rapidly declining reserves to near-record surplus and record reserves.
Added two new parts to our staff breakdown:
Count of how many employees are in each job title, sorted by salary range.
Count of how many employees are in each salary range, sorted by job title.
2024-09-12
Spoke with management and clarified base pay data. They are unable to produce fully clean data, so both sides agree to proceed by having me match the roster to the main salary schedule itself (for those on it), and use existing hourly base pay for everyone else. Then use that as the base to cost out everything. I will proceed with this method.
2024-09-06
Ran into another issue. Even base pay data is inconsistent. Some members within same salary schedule, with same range and step, have different base pay. Some have different rates from salary schedule. Found 630 Emp ID that have differing base pays from what’s listed on salary schedule. I emailed management to get clarification.
Regardless, I costed out everything as of now. Once I get clarification and updated data, will swap out the data and re-cost. Should have some but not a huge impact.
However, can’t cost $20 min wage proposal, as several of the disparities involve exactly $20 pay. I will need to wait for clarification.
2024-09-04
Salary Schedule data provided. I can now proceed.
2024-08-12
Management emails me with corrected wage data. The previous wage data included wage differentials, of which not all are affected by new salary schedule. Accordingly, they provided base wage data for all employees. I will use this data to cost the new salary schedule and the across the board raises.
Unfortunately, they did not answer questions about salary schedule data so I’m unable to proceed.
2024-08-08
Costed management’s squared salary schedule.
After presenting to Casey and Hari, I realized I costed the squared salary schedule incorrectly. I applied the new proposed schedules to all Emp ID on the roster, but it only applied to a subset, because there are 9 different salary schedules. I emailed management to get a list of which schedules are affected by the new proposal.
I also realized other oddities in the wage data provided. The hourly wage data was inconsistent by Range and Step, both among alike members and between members and the salary schedule.
2024-08-07
Costed our proposal.
2024-08-06
Updated the financial analysis to include the 2023-24 Budget.
Deleted old costing analysis from Fall and Winter 2023
Started new costing analysis
2 Financial Analysis
2.1 Sacramento City Unified finances declined, but are still among healthiest levels ever.
The two primary measurements of a District’s financial health are its net-revenues (revenue minus expenditures) and its ending unrestricted reserve fund balance. Both measurements can be expressed as a percentage of total expenditures.
The budget situation dramatically changed in just two months since the adopted budget (July 1). The adopted budget includes their “Estimated Actuals” for 2023, but the “Unaudited Actuals” contain much more final numbers (only “audited actuals” are more accurate, which don’t happen every year and come out well later). The more accurate numbers contained much better news:
$59m total deficit turns into $40 surplus.
Unrestricted Ending Balance grows from $78m to $172.
Sacramento_USD_filtered_net_rev_and_urf_exp |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(linetype=1,data=actualexp,size=.5,color="grey",show.legend =FALSE) +geom_line(linetype=2,data=projectedexp,size=.5,color="grey",show.legend =FALSE) +geom_vline(xintercept=2024) +geom_text(data = data_text_future,mapping =aes(x = x, y = y, label = label, hjust="left"),family ="Avenir Next Condensed") +geom_label(aes(label=paste(round(Value)), color=Value<0),size =4,family ="Avenir Next Condensed",fontface="bold",show.legend=FALSE) +facet_wrap(vars(Type),ncol=1,scales ="free",labeller =labeller(Type = labels)) +scale_x_continuous(limits=c(2004,2027),breaks=c(2004,2008,2012,2016,2020,2024)) +xlab("Year End") +scale_colour_manual(values=c("black","red")) +labs(title ="Sacramento USD: Financial History and Projected Future",subtitle ="Sacramento City Unified Ended 2024 With Healthy Finances Yet Again",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2024-08-08.\nData comes from CDE SACS files, SCUSD's unaudited actuals") +My_Theme_NoY()
Below is the same graph as above, but presented with raw dollar totals instead of percentage of expenditures.
Show the code
Sacramento_USD_filtered_net_rev_and_urf_tot_divided_by_mil |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(linetype=1,data=actualtot,size=.5,color="grey",show.legend =FALSE) +geom_line(linetype=2,data=projectedtot,size=.5,color="grey",show.legend =FALSE) +geom_vline(xintercept=2024) +geom_text(data = data_text_future2,mapping =aes(x = x, y = y, label = label, hjust="left"),family ="Avenir Next Condensed") +geom_label(aes(label=sprintf("%.0f", Total), color=Value<0),size =4,family ="Avenir Next Condensed",fontface="bold",show.legend=FALSE) +facet_wrap(vars(Type),ncol=1,scales ="free",labeller =labeller(Type = labels)) +scale_x_continuous(limits=c(2004,2027),breaks=c(2004,2008,2012,2016,2020,2024)) +scale_colour_manual(values=c("black","red")) +xlab("Year End") +labs(title ="Sacramento USD: Financial History and Projected Future",subtitle ="Sacramento City Unified Ended 2024 With Healthy Finances Yet Again",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2024-08-08.\nData comes from CDE SACS files, SCUSD's unaudited actuals") +My_Theme_NoY()
2.2 Attendance History: An Unjustified Source of District Fearmongering
School Districts like to point to declining attendance and then scare school workers and the public about impending financial doom. The problem with this narrative is: 1) declining attendance is a State-wide problem, not a District-wide problem, 2) because of 1, the State has been drastically increasing the amount of funding per student, resulting in overall rising revenue despite falling attendance.
Show the code
Sacramento_USD_filtered_ada_tot |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(size=.5) +geom_point() +geom_label(aes(label=paste(Value)),size =4,family ="Avenir Next Condensed",fontface="bold",show.legend=FALSE) +scale_x_continuous(limits=c(2004,2024),breaks=c(2004,2006,2008,2010,2012,2014,2016,2018,2020,2022)) +xlab("Year End") +scale_colour_manual(values=c("grey","purple")) +labs(title ="Sacramento USD: K-12 ADA History",subtitle ="Note: Does not include Charter school ADA",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2022-10-04.\nData comes from CDE SACS files") +My_Theme_NoY() +# show legend on toptheme(legend.position ="top")
Show the code
Sacramento_USD_filtered_ada_YoY_with_state <- Sacramento_USD_filtered_ada_with_state |>group_by(District) |>mutate(Value = ((Value/lag(Value) -1) *100))Sacramento_USD_filtered_ada_YoY_with_state |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(aes(color=District),size=.5) +geom_point() +geom_hline(data = data_hline_0,aes(yintercept = hline),linetype=1,color ="grey",show.legend =FALSE) +scale_x_continuous(limits=c(2004,2022),breaks=c(2004,2006,2008,2010,2012,2014,2016,2018,2020,2024)) +xlab("Year End") +scale_colour_manual(values=c("grey","purple")) +labs(title ="Sacramento USD: K-12 ADA (Annual % Change) History",subtitle ="Note: Does not include Charter school ADA",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2023-08-29.\nData comes from CDE SACS files") +My_Theme_WithY() +# show legend on toptheme(legend.position ="top")
This suggests that future District finances are much more tied to State TK-14 finances than they are to local ADA trends.
To see more clearly that K-12 finances are improved despite declining ADA, look at the following graph. The graph below compares Sacramento’s net-revenue and unrestricted fund balance to the CA statewide average. Both SCUSD and the State have seen flat or declining ADA since 2008, but both have also seen improving finances since 2008.
Show the code
Sacramento_USD_filtered_with_state |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(aes(color=District),size=.5) +geom_point(aes(color=District)) +geom_hline(yintercept =0, color ="grey") +facet_wrap(vars(Type),ncol=1,scales ="free",labeller =labeller(Type = labels)) +scale_y_continuous(limits=c(-10,30)) +scale_x_continuous(limits=c(2004,2023),breaks=c(2004,2006,2008,2010,2012,2014,2016,2018,2020,2022)) +xlab("Year End") +ylab("% of Total Expenditures") +scale_colour_manual(values=c("grey","purple","black")) +labs(title ="Sacramento USD: Financial History",subtitle ="Despite declining ADA, both the State and SCUSD have improving finances since 2008",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2023-08-29.\nData comes from CDE SACS files") +My_Theme_WithY() +# show legend on toptheme(legend.position ="top")
2.3 Revenue Trends
Two main thing to notice here:
despite a big decline in ADA, over time, revenue tends to go up. That’s because ADA declines are a statewide trend, not a District-specific one, and the CA budget accounts for declining ADA by increasing revenue per student.
There are legitimate reasons to be worried about near-term revenue growth, given the state budget and economic situation of tight budgets and slow growth. Given how tied TK-12 finances are to the state budget and economy, this justifies flat projections.
Show the code
Sacramento_USD_filtered_revenue$Type =factor( Sacramento_USD_filtered_revenue$Type, levels=c('Revenue','UnrestrictedRevenue','LCFFRevenue'))Sacramento_USD_filtered_revenue |>mutate(Value =round(Value/1000000),digits=0) |>ggplot(aes(x=YearEnd,y=Value)) +geom_line(size=.5) +geom_point() +facet_wrap(vars(Type),ncol=1,scales ="free",labeller =labeller(Type = labels)) +geom_label(aes(label=paste(Value)),size =4,family ="Avenir Next Condensed",fontface="bold",show.legend=FALSE) +scale_x_continuous(limits=c(2004,2026),breaks=c(2004,2006,2008,2010,2012,2014,2016,2018,2020,2022,2024,2026)) +scale_y_continuous(limits=c(0,900)) +xlab("Year End") +scale_colour_manual(values=c("grey","purple")) +labs(title ="Sacramento USD: Financial History",subtitle ="Total revenue projected to fall after post-COVID spike, but long-term trend is healthy growing revenues.",caption ="Created by Nick Warino at SEIU 1021 Research Department on 2023-08-29.\nData comes from CDE SACS files") +My_Theme_NoY() +# show legend on toptheme(legend.position ="top")
# Load TPFtpf <- readxl::read_xlsx(here("Districts/Sacramento/raw_data/2024-08-12 - RFI - Total Payroll Factor.xlsx"))# Define the file pathfile_path <-here("Districts/Sacramento/raw_data/2024-08-06 - Salary Schedule.xlsx")# Read each tab into a separate data framesalary_schedule_mgmt_2022_23_current <-read_xlsx(file_path, sheet =1)salary_schedule_mgmt_2023_24_increase_4_percent <-read_xlsx(file_path, sheet =2)salary_schedule_mgmt_2024_25_correction <-read_xlsx(file_path, sheet =3)salary_schedule_mgmt_2024_25_increase_2_percent <-read_xlsx(file_path, sheet =4)# Rename all Range columns to Rangesalary_schedule_mgmt_2022_23_current <- salary_schedule_mgmt_2022_23_current %>%rename(Range = RANGE)salary_schedule_mgmt_2023_24_increase_4_percent <- salary_schedule_mgmt_2023_24_increase_4_percent %>%rename(Range = RANGE)salary_schedule_mgmt_2024_25_correction <- salary_schedule_mgmt_2024_25_correction %>%rename(Range = RANGE)salary_schedule_mgmt_2024_25_increase_2_percent <- salary_schedule_mgmt_2024_25_increase_2_percent %>%rename(Range = RANGE)# salary_schedules <-
Show the code
tpf_summary <- tpf %>%summarise(Taxes =median(as.numeric(`Percentage allocated to Taxes`), na.rm =TRUE),Pension =median(as.numeric(`Percentage allocated Retirement`), na.rm =TRUE),Other =median(as.numeric(`Percentage allocated to Additonal deductions`), na.rm =TRUE)) %>%mutate(Combined = Taxes + Pension + Other)# Pull Combined value and save as `tpf_median`tpf_median <- tpf_summary$Combined +1tpf_summary_2 <- tpf %>%summarise(Taxes =mean(as.numeric(`Percentage allocated to Taxes`), na.rm =TRUE),Pension =mean(as.numeric(`Percentage allocated Retirement`), na.rm =TRUE),Other =mean(as.numeric(`Percentage allocated to Additonal deductions`), na.rm =TRUE)) %>%mutate(Combined = Taxes + Pension + Other)# Pull Combined value and save as `tpf_median`tpf_mean <- tpf_summary_2$Combined +1
Show the code
# Load rosterroster <- readxl::read_xlsx(here("Districts/Sacramento/raw_data/2024-08-12 - Roster.xlsx"))roster_temp <- readxl::read_xlsx(here("Districts/Sacramento/raw_data/2024-09-04 - Roster_Temp20.xlsx"))roster$`Seniority Date`<-as.Date(as.numeric(roster[[17]]), origin ="1899-12-30")colnames(roster)[c(1,2,3,6,7,9,15)] <-c("Emp ID", "Dept", "Job Class Title", "SalarySchedule", "Range", "Hourly Wage Base", "Annual Hours Paid")# Selectroster <- roster %>%select(`Emp ID`, Dept, `Job Class Title`, `SalarySchedule`, Range, `Hourly Wage Base`, `Annual Hours Paid`, FTE) %>%separate(Range, into =c("Range", "Step"), sep ="/", remove =FALSE) %>%mutate(Range =as.numeric(Range),Step =as.numeric(Step) )# Tidy roster_temproster_temp <- roster_temp %>%select(`Emp Id`, Range, Step) |># rename Emp Id to Emp IDrename(`Emp ID`=`Emp Id`) |># filter out rows with Range = 1filter(Range !=1)# In `roster`, for all rows where `SalarySchedule` is TEMP20H or TEMP20M, replace `Range` and `Step` with values from `roster_temp`roster <- roster %>%left_join(roster_temp, by ="Emp ID") %>%mutate(Range =case_when( SalarySchedule %in%c("TEMP20H", "TEMP20M") ~ Range.y,TRUE~ Range.x ),Step =case_when( SalarySchedule %in%c("TEMP20H", "TEMP20M") ~ Step.y,TRUE~ Step.x ) ) %>%select(-ends_with(".x"), -ends_with(".y"))
Show the code
# Look for rows that have matching Range and Step, but different Hourly Wage Basesame_range_and_step_diff_pay <- roster %>%# round to 2 decimal placesmutate(`Hourly Wage Base`=round(`Hourly Wage Base`, 2)) %>%group_by(Range, Step) %>%filter(n_distinct(`Hourly Wage Base`) >1) %>%arrange(Range, Step, `Hourly Wage Base`)
Show the code
# Match all employees Emp ID to current salary schedule by RANGE and STEP. Pull in wage was "SS Base Wage"salary_schedule_long_mgmt_2022_23_current <- salary_schedule_mgmt_2022_23_current %>%pivot_longer(cols =starts_with("Step"),names_to ="Step",values_to ="SS Base Wage" ) %>%mutate(Step =as.numeric(gsub("Step", "", Step)))# Split roster into "main" salary schedule ("C-H", "C-M", "C-M2", "IAC-H", "IAC-M", "TEMP20H", "TEMP20M", "SPECIAL3") and "other" salary schedules. Main being "roster_analyzed_1" and "other" being "roster_analyzed_2". Then after joining SS Base Wage to "roster_analyzed_1", create roster_with_SS_pay, which combines "roster_analyzed_1" and "roster_analyzed_2" into one roster.roster_analyzed_1 <- roster %>%filter(`SalarySchedule`%in%c("C-H", "C-M", "C-M2", "IAC-H", "IAC-M", "TEMP20H", "TEMP20M", "SPECIAL3")) %>%left_join(salary_schedule_long_mgmt_2022_23_current, by =c("Range", "Step")) %>%mutate(`SS Diff`=round(`SS Base Wage`-`Hourly Wage Base`, digits =2)) |># round Hourly Wage Base to 0 decimal placesmutate(`Hourly Wage Base`=round(`Hourly Wage Base`, 2)) |># sourt by `SS Diff` Descendingarrange(desc(`SS Diff`))roster_analyzed_2 <- roster %>%filter(!`SalarySchedule`%in%c("C-H", "C-M", "C-M2", "IAC-H", "IAC-M", "TEMP20H", "TEMP20M", "SPECIAL3"))roster_with_SS_pay <-bind_rows(roster_analyzed_1, roster_analyzed_2)# In roster_with_SS_pay$`SS Base Wage`, replace NA with value from roster_with_SS_pay$`Hourly Wage Base`roster_with_SS_pay$`SS Base Wage`<-ifelse(is.na(roster_with_SS_pay$`SS Base Wage`), roster_with_SS_pay$`Hourly Wage Base`, roster_with_SS_pay$`SS Base Wage`)# filter out rows where `SS Diff` is not between -.01 and .01roster_SS_diff <- roster_analyzed_1 %>%filter(`SS Diff`<-.01|`SS Diff`> .01)
Show the code
# Calculate annual salary for each employee (row) by multiplying hourly wage by annual hours and FTEroster_with_SS_pay <- roster_with_SS_pay %>%mutate(Annual_Salary =`SS Base Wage`*`Annual Hours Paid`* FTE) |># Sort Descending by Annual Salaryarrange(desc(Annual_Salary))# # # Calculate average for Hourly Wage, Annual Hours Paid, FTE, and Annual Salary, ignore NA, save as tibble, display as kable table# roster_with_SS_pay %>%# summarize(across(`SS Base Wage`:`Annual_Salary`, mean, na.rm = TRUE)) %>%# as_tibble() %>%# kable()
Show the code
# Visualize the distribution of annual salariesroster_with_SS_pay %>%ggplot(aes(x = Annual_Salary)) +geom_histogram(bins =30, fill ="purple", color ="black") +labs(title ="Distribution of Annual Salaries for \nSCUSD SEIU Bargaining Unit (2024-07-26)",x ="Annual Salary ($)",y ="Number of Employees") +My_Theme_WithY()
Show the code
# Create lolipop graph ranking all average Annual Salaries by Job Class Titleroster_with_SS_pay %>%group_by(`Job Class Title`) %>%summarize(Avg_Annual_Salary =mean(Annual_Salary)) %>%ggplot(aes(x =reorder(`Job Class Title`, Avg_Annual_Salary), y = Avg_Annual_Salary)) +geom_point(color ="purple", size =3) +geom_segment(aes(xend =`Job Class Title`, yend =0), color ="purple") +coord_flip() +labs(title ="Average Annual Salary by Job Class Title (2024-07-26)",x ="Job Class Title",y ="Average Annual Salary ($)") +My_Theme_WithY() +# Change Y Axis Text to font size 8theme(axis.text.y =element_text(size =10))
3.0.1 Job Class Titles by Range (NEW)
To get a first glance at how many members might be impacted by any discontinues in the salary schedule, I counted up all unique Job Class titles by Range.
Highlights:
When a Job Class moves up a level (e.g. Applications Spec I > II > III), they don’t necessary up to the next Range. Usually they go up several ranges (57 > 65 > 72 in the case of App. Spec.). This pattern isn’t always true though. Office Tech has 4 levels, going from Ranges 39 > 41 > 42 > 43.
Show the code
# I want a count of all roster_analyzed_1$`Job Class Titles` by Range. I want it displayed as a kable.roster_analyzed_1 %>%count(`Job Class Title`, Range) %>%# add scroll boxkable(format ="html", escape =FALSE, align =c("l", "r", "r"), caption ="Count of Job Class Titles by Range") %>%scroll_box(width ="100%", height ="500px")
Count of Job Class Titles by Range
Job Class Title
Range
n
Accounting Specialist
49
3
Accts Payable Lead Technician
51
1
Adm & Family Svcs Tech
42
5
Adult Ed Customer Rel Clk
35
1
Adult Ed Specialist
50
1
Adult Edctn Testing Proctor
41
3
Applications Spec I
57
2
Applications Spec II
65
2
Applications Spec III
72
5
Assessment Technician
46
2
Assessor-Translator MOC
48
10
Attendance Tech I
33
1
Attendance Tech I
38
2
Attendance Tech II
41
15
Automotive Service Attendant
37
2
Bus Attendant
29
11
Bus Vehicle Mechanic
53
6
Buyer I
41
1
Buyer II
47
1
Buyer III
65
1
Campus Monitor
34
53
Campus Supervisor I
37
1
Career Information Technician
39
6
Certified Occup Therapy Asst
55
3
Child Care Attendant, Chld Dev
29
32
Child Dev Data Info Tech
50
1
Child Dev Spec I
42
9
Child Develop Prgms Tech
48
1
Child Welfare & Att Spec
46
2
Child Welfare & Attnd Liaison
46
4
Clerk I
29
10
Clerk II
33
58
Clerk III
38
6
Clerk III
42
1
Construction/Bond Accntng Spec
69
1
Controller-Bookkeeper HS
48
6
Controller/Bkkpr Adult Ed.
49
1
Custodian
34
100
Custodian
NA
2
Customer Service Specialist
38
4
Education Entrepreneur
66
4
Educational Assistant
39
2
Educational Assistant
43
2
Employee Benefit Tech
45
2
Employment Coach SpEd
45
4
FACE Technician
48
1
Facilities Project Tech
68
1
Family Partnership Facilitator
51
2
Fiscal Services Tech I
44
5
Food Prod Asst, Cent Kit
36
18
Food Prod Lead Cent Kitchen
40
4
Food Service Assistant
35
235
Food Service Assistant
NA
1
Food Service Lead, Comp HS
39
5
Food Service Lead, School Site
37
74
Foster Youth Srvs Prog Assct
51
4
Fund Spec
52
6
Gang Violence Prev/Intrvntn Sp
70
1
Gang Violence Prev/Intrvntn Sp
100
1
HAZMAT Risk Comp Lead Worker
78
1
Health Aide
35
6
Health Aide
41
1
Health Aide
47
1
Health Aide, Spec Ed
35
1
Health Aide, Spec Ed
41
1
Health Aide, Spec Ed
47
3
Health Services Clerk
38
4
Health Services Technician
42
3
IEP Desig Inst Para-Sp Ed
29
4
IEP Desig Inst Para-Sp Ed
33
3
IEP Desig Inst Para-Sp Ed
37
3
IEP Desig Inst Para-Sp Ed
41
7
Inst Aid, Comp Lab
39
3
Inst Aid, Spec Ed
29
86
Inst Aid, Spec Ed
33
70
Inst Aid, Spec Ed
34
1
Inst Aid, Spec Ed
37
30
Inst Aid, Spec Ed
41
65
Inst Aid, Spec Ed
NA
2
Inst Aide Child Dev
29
2
Inst Aide Child Dev
33
5
Inst Aide Child Dev
37
7
Inst Aide Child Dev
41
15
Inst Aide Child Dev
NA
1
Inst Aide/Computer Lab Asst
35
1
Instructional Aide
29
17
Instructional Aide
32
5
Instructional Aide
36
2
Instructional Aide
40
12
Instructional Aide (TK)
29
8
Instructional Aide (TK)
32
18
Instructional Aide (TK)
36
3
Instructional Aide (TK)
40
2
Interp for the Deaf
56
5
Job Developer, Employment Svcs
46
2
Lead Campus Supervisor
45
20
Lead Payroll Tech
51
2
Lead Print Shop Technician
58
1
Library Media Tech Asst
41
5
Library Service App Specialist
55
3
Linked Learning/CTP Pgm Tech
48
1
Materials Lab and Mail Tech
52
1
Matric & Orient Cntr Spec
50
2
Medi-Cal Rmbrsmnt Prog Spclst
69
1
Mgmt Information Tech
47
2
Morning Duty
29
12
NUTRI SERV PROGRAM SPECIALIST
56
2
Network Spec III
74
1
Network Specialist I
60
1
Noon Duty
29
177
Noon Duty
30
1
Noon Duty
33
1
Noon Duty
NA
3
Nut Svc Inv Con Fac
45
2
Nutr Serv Purch and Wrhse Spec
54
2
Nutri Services Program Tech II
50
1
Occupational Therapist
88
14
Office Tchncn I
39
1
Office Tchncn II
41
18
Office Tchncn III
42
33
Office Technician IV
43
8
Ombudsperson
51
2
Parent Advisor
46
11
Pers Tech II
49
6
Personnel Tech I
42
3
Pest Control Specialist
59
2
Pgm Records Tech, CAMS
45
4
Pgm Records Tech-P&C
45
2
Print Shop Tech II
45
1
Professional Development Spec
73
1
Program Rec Tech SpEd
45
1
Prsnl Tech, Substitute Srvs
51
2
Pupil Personnel Records Tech
34
1
Registered Behavior Technician
47
7
Registrar
41
1
Registrar
53
12
Risk Management Specialist
51
1
School Community Liaison
34
2
School Community Liaison
38
1
School Intervention Staff Asst
42
1
School Office Manager I
44
48
School Office Manager II
44
11
School Office Manager III
45
10
Security Officer
46
5
Site Cmptr Suprt Tech I
45
2
Site Cmptr Suprt Tech II
53
3
Spec Progr & Advanced Learning
51
1
Special Ed Applications Spclst
55
4
Speech-Lang Pathology Asst
55
3
Student and Family Support
65
2
Swimming Pool Custodian
35
4
Teacher Assistant, Bilingual
37
25
Teacher Assistant, Bilingual
39
9
Teacher Asst Bil I - Spanish
39
1
Teacher Asst Bil II - Spanish
37
1
Tech Support Spec I
53
1
Tech Support Spec II
59
7
Tech Support Spec III
67
1
Trans Over-Enrollment Tech
45
1
Trans Parts-Inv Cntrl Wrkr
45
1
Transition Asst SpEd
45
2
Transportation Lead Mechanic
55
2
Transportation Scheduler/Disp
46
4
Transportation Svcs Tech
42
1
Transportation Trip Scheduler
42
1
Van Driver
36
5
Walking Attendant
29
4
Warehouse Records Clerk
41
1
Warehouse Worker
48
14
Webmaster/Comm Spec
72
1
Youth Services Pgm Associate
51
5
Youth Services Specialist
65
3
Youth/Family Mntl Hlth Adv
42
6
Show the code
# # roster_analyzed_1 %>%# count(`Job Class Title`, Range, SalarySchedule) %>%# kable(format = "html", escape = FALSE, align = c("l", "r", "r"), caption = "Count of Job Class Titles by Range")
3.0.2 Ranges by Job Class Title (NEW)
Show the code
# I want a list of all roster_analyzed_1$`Job Class Titles` by Range. I want it displayed as a kable.roster_analyzed_1 %>%count(Range, `Job Class Title`) %>%kable(format ="html", escape =FALSE, align =c("r", "l", "r"), caption ="Count of Ranges by Job Class Titles") %>%scroll_box(width ="100%", height ="500px")
Count of Ranges by Job Class Titles
Range
Job Class Title
n
29
Bus Attendant
11
29
Child Care Attendant, Chld Dev
32
29
Clerk I
10
29
IEP Desig Inst Para-Sp Ed
4
29
Inst Aid, Spec Ed
86
29
Inst Aide Child Dev
2
29
Instructional Aide
17
29
Instructional Aide (TK)
8
29
Morning Duty
12
29
Noon Duty
177
29
Walking Attendant
4
30
Noon Duty
1
32
Instructional Aide
5
32
Instructional Aide (TK)
18
33
Attendance Tech I
1
33
Clerk II
58
33
IEP Desig Inst Para-Sp Ed
3
33
Inst Aid, Spec Ed
70
33
Inst Aide Child Dev
5
33
Noon Duty
1
34
Campus Monitor
53
34
Custodian
100
34
Inst Aid, Spec Ed
1
34
Pupil Personnel Records Tech
1
34
School Community Liaison
2
35
Adult Ed Customer Rel Clk
1
35
Food Service Assistant
235
35
Health Aide
6
35
Health Aide, Spec Ed
1
35
Inst Aide/Computer Lab Asst
1
35
Swimming Pool Custodian
4
36
Food Prod Asst, Cent Kit
18
36
Instructional Aide
2
36
Instructional Aide (TK)
3
36
Van Driver
5
37
Automotive Service Attendant
2
37
Campus Supervisor I
1
37
Food Service Lead, School Site
74
37
IEP Desig Inst Para-Sp Ed
3
37
Inst Aid, Spec Ed
30
37
Inst Aide Child Dev
7
37
Teacher Assistant, Bilingual
25
37
Teacher Asst Bil II - Spanish
1
38
Attendance Tech I
2
38
Clerk III
6
38
Customer Service Specialist
4
38
Health Services Clerk
4
38
School Community Liaison
1
39
Career Information Technician
6
39
Educational Assistant
2
39
Food Service Lead, Comp HS
5
39
Inst Aid, Comp Lab
3
39
Office Tchncn I
1
39
Teacher Assistant, Bilingual
9
39
Teacher Asst Bil I - Spanish
1
40
Food Prod Lead Cent Kitchen
4
40
Instructional Aide
12
40
Instructional Aide (TK)
2
41
Adult Edctn Testing Proctor
3
41
Attendance Tech II
15
41
Buyer I
1
41
Health Aide
1
41
Health Aide, Spec Ed
1
41
IEP Desig Inst Para-Sp Ed
7
41
Inst Aid, Spec Ed
65
41
Inst Aide Child Dev
15
41
Library Media Tech Asst
5
41
Office Tchncn II
18
41
Registrar
1
41
Warehouse Records Clerk
1
42
Adm & Family Svcs Tech
5
42
Child Dev Spec I
9
42
Clerk III
1
42
Health Services Technician
3
42
Office Tchncn III
33
42
Personnel Tech I
3
42
School Intervention Staff Asst
1
42
Transportation Svcs Tech
1
42
Transportation Trip Scheduler
1
42
Youth/Family Mntl Hlth Adv
6
43
Educational Assistant
2
43
Office Technician IV
8
44
Fiscal Services Tech I
5
44
School Office Manager I
48
44
School Office Manager II
11
45
Employee Benefit Tech
2
45
Employment Coach SpEd
4
45
Lead Campus Supervisor
20
45
Nut Svc Inv Con Fac
2
45
Pgm Records Tech, CAMS
4
45
Pgm Records Tech-P&C
2
45
Print Shop Tech II
1
45
Program Rec Tech SpEd
1
45
School Office Manager III
10
45
Site Cmptr Suprt Tech I
2
45
Trans Over-Enrollment Tech
1
45
Trans Parts-Inv Cntrl Wrkr
1
45
Transition Asst SpEd
2
46
Assessment Technician
2
46
Child Welfare & Att Spec
2
46
Child Welfare & Attnd Liaison
4
46
Job Developer, Employment Svcs
2
46
Parent Advisor
11
46
Security Officer
5
46
Transportation Scheduler/Disp
4
47
Buyer II
1
47
Health Aide
1
47
Health Aide, Spec Ed
3
47
Mgmt Information Tech
2
47
Registered Behavior Technician
7
48
Assessor-Translator MOC
10
48
Child Develop Prgms Tech
1
48
Controller-Bookkeeper HS
6
48
FACE Technician
1
48
Linked Learning/CTP Pgm Tech
1
48
Warehouse Worker
14
49
Accounting Specialist
3
49
Controller/Bkkpr Adult Ed.
1
49
Pers Tech II
6
50
Adult Ed Specialist
1
50
Child Dev Data Info Tech
1
50
Matric & Orient Cntr Spec
2
50
Nutri Services Program Tech II
1
51
Accts Payable Lead Technician
1
51
Family Partnership Facilitator
2
51
Foster Youth Srvs Prog Assct
4
51
Lead Payroll Tech
2
51
Ombudsperson
2
51
Prsnl Tech, Substitute Srvs
2
51
Risk Management Specialist
1
51
Spec Progr & Advanced Learning
1
51
Youth Services Pgm Associate
5
52
Fund Spec
6
52
Materials Lab and Mail Tech
1
53
Bus Vehicle Mechanic
6
53
Registrar
12
53
Site Cmptr Suprt Tech II
3
53
Tech Support Spec I
1
54
Nutr Serv Purch and Wrhse Spec
2
55
Certified Occup Therapy Asst
3
55
Library Service App Specialist
3
55
Special Ed Applications Spclst
4
55
Speech-Lang Pathology Asst
3
55
Transportation Lead Mechanic
2
56
Interp for the Deaf
5
56
NUTRI SERV PROGRAM SPECIALIST
2
57
Applications Spec I
2
58
Lead Print Shop Technician
1
59
Pest Control Specialist
2
59
Tech Support Spec II
7
60
Network Specialist I
1
65
Applications Spec II
2
65
Buyer III
1
65
Student and Family Support
2
65
Youth Services Specialist
3
66
Education Entrepreneur
4
67
Tech Support Spec III
1
68
Facilities Project Tech
1
69
Construction/Bond Accntng Spec
1
69
Medi-Cal Rmbrsmnt Prog Spclst
1
70
Gang Violence Prev/Intrvntn Sp
1
72
Applications Spec III
5
72
Webmaster/Comm Spec
1
73
Professional Development Spec
1
74
Network Spec III
1
78
HAZMAT Risk Comp Lead Worker
1
88
Occupational Therapist
14
100
Gang Violence Prev/Intrvntn Sp
1
NA
Custodian
2
NA
Food Service Assistant
1
NA
Inst Aid, Spec Ed
2
NA
Inst Aide Child Dev
1
NA
Noon Duty
3
4 Costing Analysis
Below is a costing analysis of:
SEIU 1021’s Proposal of 2024-06-13
SCUSD’s Supposal Tables of 2024-07-25
4.1 SEIU 1021’s Proposal of 2024-06-13
Proposal from SEIU Local 1021 To Sacramento City Unified School District
ARTICLE 6 Salaries
6.1 The District shall square the salary schedule for the 2023-24 school year to reflect an increase of no less than 2.5% between salary ranges and no less than 5% between salary steps within each range, with the lowest hourly rate on the salary schedule at $18.19 per hour. The District shall maintain the same increments for the 2024-25 salary schedule, with the lowest hourly rate on the salary schedule at $20 per hour.
The parties agree to a four percent (4%) across-the-board salary increase retroactive to July 1, 2023, for all represented SEIU 1021 members.
The parties agree to a two percent (2%) across-the-board salary increase effective July 1, 2024, for all represented SEIU 1021 members.
4.1.1 Costing Methodology
Using SCUSD roster data for the 1021 bargaining unit, as of 2024-07-26
Calculating the cost for every employee, using their current hourly wage, annual hours, FTE, and the proposed wage increases.
Costing each sub-proposal separate, in the following order:
4% across-the-board salary increase retroactive to July 1, 2023 (applied to base pay rate)
“Square the Salary Schedule” after 4% raises. (more below)
2% across-the-board salary increase effective July 1, 2024 (applied to base pay rate after above raises)
Summing the total cost for all sub-proposals for all employees, for each year.
Presenting data in raw and with Total Payroll Factor (TPF) of 1.27 (benefits, taxes, etc.). Figure comes from SCUSD management as of 2024-08-07. I calculated the TPF by taking the median percent paid in taxes, retirement, and “additional benefits” and combining. I did not include health benefits, as those are flat rate (i.e. don’t go up as wages go up). I also didn’t include union dues since those come out of the employee’s paycheck, not employer’s.
4.1.1.1 The Base Pay Rate
The District was unable to provide 100% clean base pay rate data, i.e. the listed base pay for every EMP ID did not 100% match the salary schedule. Instead of trying to do so, we agree for me to just match the roster to the main salary schedule (based on Salary Schedule ID provided by management) to get the actual base rate, and for every EMP ID not on the main salary schedule, just use the base rate from the roster. This is a minor difference, but it’s worth noting.
4.1.1.2 Squaring the Salary Schedule Methodology
For 1021’s proposed salary schedule, I simply started at Range 29, Step 1, and multiplied every subsequent range by 1.025. Then I multiplied every subsequent step by 1.05.
For SCUSD’s proposed salary schedule, I simply loaded their proposed salary schedules.
Then I match the roster data to the salary schedule data. To match:
Filter out employee IDs with appropriate Salary Schedule IDs (as provided by management)
For TEMP employees, match their RANGE and STEP data to a separate TEMP roster (provided by management) that includes their “actual” RANGE and STEP. The main roster shows all TEMP employees as RANGE 1, STEP 1, but the TEMP roster shows their actual RANGE and STEP.
Based on Range and Step, match the employee’s new proposed base wage to the roster.
Calculate the difference between the new proposal base wage and after the 4 percent raise (as agreed to by both sides).
Sum the differences.
4.1.2 Squaring the Salary Schedule - 1021’s Proposed Salary Schedules
Below is 1021’s proposed salary schedule for 2023-24, which maintains a 5% differential between steps and a 2.5% differential between ranges.
# Total Salary Status Quoteroster_analysis <- roster_with_SS_pay %>%summarize(Total_Employees =n(),Total_Annual_Salary =sum(Initial_Annual_Salary),Avg_Annual_Salary =mean(Initial_Annual_Salary),Median_Annual_Salary =median(Initial_Annual_Salary),Min_Annual_Salary =min(Initial_Annual_Salary),Max_Annual_Salary =max(Initial_Annual_Salary) )
Show the code
salary_schedule_1021_analyzed_2023_24_display
Range
Step1
Step2
Step3
Step4
Step5
S2%
S3%
S4%
S5%
R1%
R2%
R3%
R4%
R5%
29
18.92
19.87
20.86
21.90
23.00
5
5
5
5
NA
NA
NA
NA
NA
30
19.39
20.36
21.38
22.45
23.57
5
5
5
5
2.5
2.5
2.5
2.5
2.5
31
19.88
20.87
21.92
23.01
24.16
5
5
5
5
2.5
2.5
2.5
2.5
2.5
32
20.37
21.39
22.46
23.59
24.77
5
5
5
5
2.5
2.5
2.5
2.5
2.5
33
20.88
21.93
23.02
24.18
25.38
5
5
5
5
2.5
2.5
2.5
2.5
2.5
34
21.41
22.48
23.60
24.78
26.02
5
5
5
5
2.5
2.5
2.5
2.5
2.5
35
21.94
23.04
24.19
25.40
26.67
5
5
5
5
2.5
2.5
2.5
2.5
2.5
36
22.49
23.61
24.80
26.03
27.34
5
5
5
5
2.5
2.5
2.5
2.5
2.5
37
23.05
24.20
25.42
26.69
28.02
5
5
5
5
2.5
2.5
2.5
2.5
2.5
38
23.63
24.81
26.05
27.35
28.72
5
5
5
5
2.5
2.5
2.5
2.5
2.5
39
24.22
25.43
26.70
28.04
29.44
5
5
5
5
2.5
2.5
2.5
2.5
2.5
40
24.82
26.07
27.37
28.74
30.17
5
5
5
5
2.5
2.5
2.5
2.5
2.5
41
25.45
26.72
28.05
29.46
30.93
5
5
5
5
2.5
2.5
2.5
2.5
2.5
42
26.08
27.39
28.75
30.19
31.70
5
5
5
5
2.5
2.5
2.5
2.5
2.5
43
26.73
28.07
29.47
30.95
32.49
5
5
5
5
2.5
2.5
2.5
2.5
2.5
44
27.40
28.77
30.21
31.72
33.31
5
5
5
5
2.5
2.5
2.5
2.5
2.5
45
28.09
29.49
30.97
32.51
34.14
5
5
5
5
2.5
2.5
2.5
2.5
2.5
46
28.79
30.23
31.74
33.33
34.99
5
5
5
5
2.5
2.5
2.5
2.5
2.5
47
29.51
30.98
32.53
34.16
35.87
5
5
5
5
2.5
2.5
2.5
2.5
2.5
48
30.25
31.76
33.35
35.01
36.76
5
5
5
5
2.5
2.5
2.5
2.5
2.5
49
31.00
32.55
34.18
35.89
37.68
5
5
5
5
2.5
2.5
2.5
2.5
2.5
50
31.78
33.37
35.03
36.79
38.63
5
5
5
5
2.5
2.5
2.5
2.5
2.5
51
32.57
34.20
35.91
37.71
39.59
5
5
5
5
2.5
2.5
2.5
2.5
2.5
52
33.39
35.06
36.81
38.65
40.58
5
5
5
5
2.5
2.5
2.5
2.5
2.5
53
34.22
35.93
37.73
39.62
41.60
5
5
5
5
2.5
2.5
2.5
2.5
2.5
54
35.08
36.83
38.67
40.61
42.64
5
5
5
5
2.5
2.5
2.5
2.5
2.5
55
35.95
37.75
39.64
41.62
43.70
5
5
5
5
2.5
2.5
2.5
2.5
2.5
56
36.85
38.69
40.63
42.66
44.79
5
5
5
5
2.5
2.5
2.5
2.5
2.5
57
37.77
39.66
41.65
43.73
45.91
5
5
5
5
2.5
2.5
2.5
2.5
2.5
58
38.72
40.65
42.69
44.82
47.06
5
5
5
5
2.5
2.5
2.5
2.5
2.5
59
39.69
41.67
43.75
45.94
48.24
5
5
5
5
2.5
2.5
2.5
2.5
2.5
60
40.68
42.71
44.85
47.09
49.44
5
5
5
5
2.5
2.5
2.5
2.5
2.5
61
41.70
43.78
45.97
48.27
50.68
5
5
5
5
2.5
2.5
2.5
2.5
2.5
62
42.74
44.87
47.12
49.47
51.95
5
5
5
5
2.5
2.5
2.5
2.5
2.5
63
43.81
46.00
48.30
50.71
53.25
5
5
5
5
2.5
2.5
2.5
2.5
2.5
64
44.90
47.15
49.50
51.98
54.58
5
5
5
5
2.5
2.5
2.5
2.5
2.5
65
46.02
48.32
50.74
53.28
55.94
5
5
5
5
2.5
2.5
2.5
2.5
2.5
66
47.17
49.53
52.01
54.61
57.34
5
5
5
5
2.5
2.5
2.5
2.5
2.5
67
48.35
50.77
53.31
55.98
58.77
5
5
5
5
2.5
2.5
2.5
2.5
2.5
68
49.56
52.04
54.64
57.37
60.24
5
5
5
5
2.5
2.5
2.5
2.5
2.5
69
50.80
53.34
56.01
58.81
61.75
5
5
5
5
2.5
2.5
2.5
2.5
2.5
70
52.07
54.68
57.41
60.28
63.29
5
5
5
5
2.5
2.5
2.5
2.5
2.5
71
53.37
56.04
58.84
61.79
64.88
5
5
5
5
2.5
2.5
2.5
2.5
2.5
72
54.71
57.44
60.32
63.33
66.50
5
5
5
5
2.5
2.5
2.5
2.5
2.5
73
56.08
58.88
61.82
64.91
68.16
5
5
5
5
2.5
2.5
2.5
2.5
2.5
74
57.48
60.35
63.37
66.54
69.86
5
5
5
5
2.5
2.5
2.5
2.5
2.5
75
58.91
61.86
64.95
68.20
71.61
5
5
5
5
2.5
2.5
2.5
2.5
2.5
76
60.39
63.41
66.58
69.91
73.40
5
5
5
5
2.5
2.5
2.5
2.5
2.5
77
61.90
64.99
68.24
71.65
75.24
5
5
5
5
2.5
2.5
2.5
2.5
2.5
78
63.44
66.62
69.95
73.44
77.12
5
5
5
5
2.5
2.5
2.5
2.5
2.5
79
65.03
68.28
71.70
75.28
79.04
5
5
5
5
2.5
2.5
2.5
2.5
2.5
80
66.66
69.99
73.49
77.16
81.02
5
5
5
5
2.5
2.5
2.5
2.5
2.5
81
68.32
71.74
75.33
79.09
83.05
5
5
5
5
2.5
2.5
2.5
2.5
2.5
82
70.03
73.53
77.21
81.07
85.12
5
5
5
5
2.5
2.5
2.5
2.5
2.5
83
71.78
75.37
79.14
83.10
87.25
5
5
5
5
2.5
2.5
2.5
2.5
2.5
84
73.58
77.25
81.12
85.17
89.43
5
5
5
5
2.5
2.5
2.5
2.5
2.5
85
75.41
79.19
83.15
87.30
91.67
5
5
5
5
2.5
2.5
2.5
2.5
2.5
86
77.30
81.17
85.22
89.48
93.96
5
5
5
5
2.5
2.5
2.5
2.5
2.5
87
79.23
83.19
87.35
91.72
96.31
5
5
5
5
2.5
2.5
2.5
2.5
2.5
88
81.21
85.27
89.54
94.01
98.72
5
5
5
5
2.5
2.5
2.5
2.5
2.5
4.1.3 Distribution of Raises from Fixing the Salary Schedule (1021 Proposal)
Below looks at the spread of raises for all employees in the roster after fixing the salary schedule. Reminder, not all members are in the salary schedule that is getting fixed, so that explains why 176 members are getting no raise from fixing the salary schedule.
Show the code
mean_median_mode_RaiseSSFixperc <- roster_with_SS_pay %>%summarise(mean_RaiseSSFixperc =round(mean(RaiseSSFixperc, na.rm =TRUE), 1),median_RaiseSSFixperc =round(median(RaiseSSFixperc, na.rm =TRUE), 1),mode_RaiseSSFixperc =round(as.numeric(names(sort(table(RaiseSSFixperc), decreasing =TRUE)[1])), 1),lowest_RaiseSSFixperc =round(min(RaiseSSFixperc, na.rm =TRUE), 1),highest_RaiseSSFixperc =round(max(RaiseSSFixperc, na.rm =TRUE), 1) ) %>%pivot_longer(cols =everything(),names_to ="Statistic",values_to ="Value" )# Create histogram of RaiseSSFixpercRaiseSSFixperc_hist <- roster_with_SS_pay %>%ggplot(aes(x = RaiseSSFixperc)) +geom_histogram(binwidth =1, fill ="purple", color ="black") +labs(title ="Distribution of Raises from Fixing the Salary Schedule (1021 Proposal)",x ="Raise (%)",y ="Number of Employees" ) +My_Theme_WithY()RaiseSSFixperc_hist
Show the code
# Display the mean, median, mode, lowest, and highest raise as a kable tablemean_median_mode_RaiseSSFixperc %>%kable(format ="html", escape =FALSE, align =c("l", "r")) %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed", "responsive"))
Statistic
Value
mean_RaiseSSFixperc
20.0
median_RaiseSSFixperc
19.9
mode_RaiseSSFixperc
19.0
lowest_RaiseSSFixperc
0.0
highest_RaiseSSFixperc
39.1
Show the code
# Create new column (Min_Wage) in roster_with_SS_pay for any row where New_Hourly_Wage_2p is less than $20, add $20 to new column, if $20 or above, keep the same value as in New_Hourly_Wage_2proster_with_SS_pay <- roster_with_SS_pay %>%mutate(Min_Wage =ifelse(New_Hourly_Wage_2p <20, 20, New_Hourly_Wage_2p),Annual_Salary_Min_Wage = Min_Wage *`Annual Hours Paid`* FTE,Raise_Min_Wage =round(Annual_Salary_Min_Wage - New_Annual_Salary_2p, digits=2))# Calculate the total cost of the second raise (2%)cost_raise_min_wage_all_2024 <- roster_with_SS_pay %>%summarise(total_cost =sum(Raise_Min_Wage, na.rm =TRUE)) %>%pull(total_cost)
4.1.4 Summary Table
Show the code
library(dplyr)library(knitr)library(scales)# Create a data frame for the table# Create a data frame for the tablecost_table <-data.frame(Description =c("4% Raise for All Employees, Retro 2023-07-01", "2% Raise for All Employees, Retro 2024-07-01","Squaring the Salary Schedule","Minimum Wage Increase to $20/hr, Retro 2024-07-01","Total Annual Costs, No Cuts"),Cost_Raw =c(cost_raise_4perc_all_2023, cost_raise_2perc_all_2024, total_annual_salary_increase, cost_raise_min_wage_all_2024, cost_raise_4perc_all_2023 + cost_raise_2perc_all_2024 + total_annual_salary_increase),Cost_With_Factor =c(cost_raise_4perc_all_2023 * tpf_median, cost_raise_2perc_all_2024 * tpf_median, total_annual_salary_increase * tpf_median, cost_raise_min_wage_all_2024 * tpf_median, (cost_raise_4perc_all_2023 + cost_raise_2perc_all_2024 + total_annual_salary_increase) * tpf_median),Notes =c("Annual cost. Management's proposal, agreed to by SEIU","Annual cost. Management's proposal, agreed to by SEIU. Applied 1021's proposed salary schedule fix.","Annual cost. 1021's proposal","Annual cost. 1021's proposal. Actually a few thousand, only affects 11 emp id.","Annual cost. Total of all 1021 proposals, no cuts."))# Format the costs as currency in millions with commas, rounded to 1 decimal placecost_table$Cost_Raw <-dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(cost_table$Cost_Raw)cost_table$Cost_With_Factor <-dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(cost_table$Cost_With_Factor)# Create the kable tablekable(cost_table, col.names =c("Proposal", "Cost (Raw)", "Cost with TPF of 1.27", "Notes"),align =c("l", "r", "r", "l"),caption ="Salary Raise Proposals and Costs (in Millions of Dollars)") %>%row_spec(5, bold =TRUE)
Salary Raise Proposals and Costs (in Millions of Dollars)
Proposal
Cost (Raw)
Cost with TPF of 1.27
Notes
4% Raise for All Employees, Retro 2023-07-01
$1.3M
$1.6M
Annual cost. Management's proposal, agreed to by SEIU
2% Raise for All Employees, Retro 2024-07-01
$3.1M
$3.9M
Annual cost. Management's proposal, agreed to by SEIU. Applied 1021's proposed salary schedule fix.
Squaring the Salary Schedule
$13.1M
$16.7M
Annual cost. 1021's proposal
Minimum Wage Increase to $20/hr, Retro 2024-07-01
$0.0M
$0.0M
Annual cost. 1021's proposal. Actually a few thousand, only affects 11 emp id.
Total Annual Costs, No Cuts
$17.5M
$22.2M
Annual cost. Total of all 1021 proposals, no cuts.
4.2 SCUSD’s Salary Schedule Supposals
4.2.1 Salary Schedule 2022-23 Current
Show the code
# Loaded the salary schedules in chunks above abovesalary_schedule_mgmt_2022_23_current_display
# First, let's reshape the salary_schedule to long formatsalary_schedule_long <- salary_schedule_mgmt_2024_25_correction %>%pivot_longer(cols =starts_with("Step"),names_to ="Step",values_to ="New_Hourly_Wage_SSFix_SCUSD" ) %>%mutate(Step =as.numeric(gsub("Step", "", Step)))# Join and calculate new salariesroster_with_SS_pay <- roster_with_SS_pay %>%left_join(salary_schedule_long, by =c("Range", "Step")) %>%mutate(New_Hourly_Wage_SSFix_SCUSD =case_when( SalarySchedule %in% appropriate_schedules ~ New_Hourly_Wage_SSFix_SCUSD,TRUE~ New_Hourly_Wage_4p ),New_Annual_Salary_SSFix_SCUSD = New_Hourly_Wage_SSFix_SCUSD *`Annual Hours Paid`* FTE,WageSSFix_SCUSD_Increase = New_Hourly_Wage_SSFix_SCUSD - New_Hourly_Wage_4p,RaiseSSFix_SCUSD = New_Annual_Salary_SSFix_SCUSD - New_Annual_Salary_4p,RaiseSSFix_SCUSDperc =round(((New_Annual_Salary_SSFix_SCUSD / New_Annual_Salary_4p) -1) *100, 1) ) |># cumulative $ and % increase from Annual_Salary_Min_Wage from Initial_Annual_Salarymutate(Cumulative_Raise_1021 = (Annual_Salary_Min_Wage - Initial_Annual_Salary),Cumulative_Raise_1021_perc =round(((Annual_Salary_Min_Wage / Initial_Annual_Salary) -1) *100, 1) )# Save as csvwrite_csv(roster_with_SS_pay, here("Districts/Sacramento/output_data/roster_with_SS_pay.csv"))# Summarize and pull value for RaiseSSFixtotal_annual_salary_increase_scusd <- roster_with_SS_pay %>%summarise(total_cost =sum(RaiseSSFix_SCUSD, na.rm =TRUE)) %>%pull(total_cost)# Calculate the total cost of annual salary difference between 3 and 2, but only for those with an increase. I.e. ignore any row with a decrease in salary.total_annual_salary_increase_scusd_raises_only <- roster_with_SS_pay %>%filter(RaiseSSFix_SCUSD >0) %>%summarise(total_cost =sum(RaiseSSFix_SCUSD, na.rm =TRUE)) %>%pull(total_cost)
4.2.5 Distribution of Raises from Fixing the Salary Schedule (SCUSD Supposal)
Below looks at the spread of raises for all employees in the roster after fixing the salary schedule. Reminder, this is not all members, just those with the applicable salary schedule.
Show the code
mean_median_mode_RaiseSSFix_SCUSDperc <- roster_with_SS_pay %>%summarise(mean_RaiseSSFix_SCUSDperc =round(mean(RaiseSSFix_SCUSDperc, na.rm =TRUE), 1),median_RaiseSSFix_SCUSDperc =round(median(RaiseSSFix_SCUSDperc, na.rm =TRUE), 1),mode_RaiseSSFix_SCUSDperc =round(as.numeric(names(sort(table(RaiseSSFix_SCUSDperc), decreasing =TRUE)[1])), 1),lowest_RaiseSSFix_SCUSDperc =round(min(RaiseSSFix_SCUSDperc, na.rm =TRUE), 1),highest_RaiseSSFix_SCUSDperc =round(max(RaiseSSFix_SCUSDperc, na.rm =TRUE), 1) ) %>%pivot_longer(cols =everything(),names_to ="Statistic",values_to ="Value" )# Create histogram of RaiseSSFix_SCUSDpercRaiseSSFix_SCUSDperc_hist <- roster_with_SS_pay %>%ggplot(aes(x = RaiseSSFix_SCUSDperc)) +geom_histogram(binwidth =1, fill ="purple", color ="black") +labs(title ="Distribution of Raises from Fixing the Salary Schedule (SCUSD Supposal)",x ="Raise (%)",y ="Number of Employees" ) +My_Theme_WithY()RaiseSSFix_SCUSDperc_hist
Show the code
# Display the mean, median, mode, lowest, and highest raise as a kable tablemean_median_mode_RaiseSSFix_SCUSDperc %>%kable(format ="html", escape =FALSE, align =c("l", "r")) %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed", "responsive"))
Statistic
Value
mean_RaiseSSFix_SCUSDperc
12.6
median_RaiseSSFix_SCUSDperc
14.7
mode_RaiseSSFix_SCUSDperc
16.7
lowest_RaiseSSFix_SCUSDperc
0.0
highest_RaiseSSFix_SCUSDperc
19.7
4.2.6 Summary Table with SCUSD’s Proposed Squaring of the Salary Schedule
Below compares 1021’s and SCUSD’s proposals for squaring the salary schedule.
Show the code
library(dplyr)library(knitr)library(scales)# Create a data frame for the tablecost_table <-data.frame(Description =c("Squaring the Salary Schedule (1021)","Squaring the Salary Schedule (SCUSD)","Difference"),Cost_Raw =c(total_annual_salary_increase, total_annual_salary_increase_scusd, total_annual_salary_increase_scusd - total_annual_salary_increase),Notes =c("Annual cost. 1021's proposal","Annual cost. SCUSD's proposal","Difference in annual cost"))# Calculate Cost_With_Factor using vectorized operationcost_table$Cost_With_Factor <- cost_table$Cost_Raw * tpf_median# Format the costs as currency in millions with commas, rounded to 1 decimal placecost_table$Cost_Raw <-dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(cost_table$Cost_Raw)cost_table$Cost_With_Factor <-dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(cost_table$Cost_With_Factor)# Create the kable tablekable(cost_table, col.names =c("Proposal", "Cost (Raw)", "Cost with TPF of 1.27", "Notes"),align =c("l", "r", "r", "l"),caption ="Salary Raise Proposals and Costs (in Millions of Dollars)")
Salary Raise Proposals and Costs (in Millions of Dollars)
Proposal
Cost (Raw)
Cost with TPF of 1.27
Notes
Squaring the Salary Schedule (1021)
$13.1M
Annual cost. 1021’s proposal
$16.7M
Squaring the Salary Schedule (SCUSD)
$6.6M
Annual cost. SCUSD’s proposal
$8.4M
Difference
-$6.5M
Difference in annual cost
-$8.3M
4.3 2024-10-15 Update: Four New Alternative Salary Schedules
Squaring the entire salary schedule at 4.5% between steps and 2% between ranges
Squaring the entire salary schedule at 4.25% between steps and 2% between ranges
Tapering the squaring from 5%/2.5% up to range 47, 5%/2% from range 48 up
Tapering the squaring from 5%/2.5% up to range 47, 5%/2% from range 48 to 79, 5%/1.5% from range 80 up
Show the code
# 1. Squaring the entire salary schedule at 4.5% between steps and 2% between rangessalary_schedule_1021_alt1 <- salary_schedule_mgmt_2023_24_increase_4_percent %>%mutate(min_range =min(Range),min_salary =min(c(Step1, Step2, Step3, Step4, Step5)),Step1 = min_salary * (1.02^ (Range - min_range)),Step2 = Step1 *1.045,Step3 = Step2 *1.045,Step4 = Step3 *1.045,Step5 = Step4 *1.045 ) %>%select(-min_range, -min_salary)# 2. Squaring the entire salary schedule at 4.25% between steps and 2% between rangessalary_schedule_1021_alt2 <- salary_schedule_mgmt_2023_24_increase_4_percent %>%mutate(min_range =min(Range),min_salary =min(c(Step1, Step2, Step3, Step4, Step5)),Step1 = min_salary * (1.02^ (Range - min_range)),Step2 = Step1 *1.0425,Step3 = Step2 *1.0425,Step4 = Step3 *1.0425,Step5 = Step4 *1.0425 ) %>%select(-min_range, -min_salary)# 2.1. Squaring the entire salary schedule at 4% between steps and 2% between rangessalary_schedule_1021_alt2p1 <- salary_schedule_mgmt_2023_24_increase_4_percent %>%mutate(min_range =min(Range),min_salary =min(c(Step1, Step2, Step3, Step4, Step5)),Step1 = min_salary * (1.02^ (Range - min_range)),Step2 = Step1 *1.04,Step3 = Step2 *1.04,Step4 = Step3 *1.04,Step5 = Step4 *1.04 ) %>%select(-min_range, -min_salary)# 3. Tapering the squaring from 5%/2.5% up to range 47, then 5%/2% from range 48 upsalary_schedule_1021_alt3 <- salary_schedule_mgmt_2023_24_increase_4_percent %>%mutate(min_range =min(Range),min_salary =min(c(Step1, Step2, Step3, Step4, Step5)),Step1 =case_when( Range <=47~ min_salary * (1.025^ (Range - min_range)), Range >47~ (min_salary * (1.025^ (47- min_range))) * (1.02^ (Range -47)) ),Step2 = Step1 *1.05,Step3 = Step2 *1.05,Step4 = Step3 *1.05,Step5 = Step4 *1.05 ) %>%select(-min_range, -min_salary)# 4. Tapering the squaring from 5%/2.5% up to range 47, 5%/2% from range 48 to 79, 5%/1.5% from range 80 upsalary_schedule_1021_alt4 <- salary_schedule_mgmt_2023_24_increase_4_percent %>%mutate(min_range =min(Range),min_salary =min(c(Step1, Step2, Step3, Step4, Step5)),Step1 =case_when( Range <=47~ min_salary * (1.025^ (Range - min_range)), Range >47& Range <=79~ (min_salary * (1.025^ (47- min_range))) * (1.02^ (Range -47)), Range >79~ (min_salary * (1.025^ (47- min_range))) * (1.02^32) * (1.015^ (Range -79)) ),Step2 = Step1 *1.05,Step3 = Step2 *1.05,Step4 = Step3 *1.05,Step5 = Step4 *1.05 ) %>%select(-min_range, -min_salary)# Round to 2 decimal places for cols 2:6 in all schedulessalary_schedule_1021_alt1[, 2:6] <-round(salary_schedule_1021_alt1[, 2:6], 2)salary_schedule_1021_alt2[, 2:6] <-round(salary_schedule_1021_alt2[, 2:6], 2)salary_schedule_1021_alt3[, 2:6] <-round(salary_schedule_1021_alt3[, 2:6], 2)salary_schedule_1021_alt4[, 2:6] <-round(salary_schedule_1021_alt4[, 2:6], 2)
Show the code
# Analyze salary schedules# Apply to new alternative schedulessalary_schedule_1021_alt1_analyzed <-analyze_salary_schedule(salary_schedule_1021_alt1)salary_schedule_1021_alt2_analyzed <-analyze_salary_schedule(salary_schedule_1021_alt2)salary_schedule_1021_alt2p1_analyzed <-analyze_salary_schedule(salary_schedule_1021_alt2p1)salary_schedule_1021_alt3_analyzed <-analyze_salary_schedule(salary_schedule_1021_alt3)salary_schedule_1021_alt4_analyzed <-analyze_salary_schedule(salary_schedule_1021_alt4)
4.3.1 Alt #1: Uniform Squaring of Salary Schedule at Slightly Smaller Rates
# Display salary schedules# Create a data frame for the tablecost_table <-data.frame(Description =c("Squaring the Salary Schedule (1021)","Squaring the Salary Schedule (SCUSD)","Alternative 1","Alternative 2","Alternative 2.1","Alternative 3","Alternative 4"),Cost_Raw =c(total_annual_salary_increase, total_annual_salary_increase_scusd, total_annual_salary_increase_al1, total_annual_salary_increase_al2, total_annual_salary_increase_al2p1, total_annual_salary_increase_al3, total_annual_salary_increase_al4))# Calculate Cost_With_Factor using vectorized operationcost_table$Cost_With_Factor <- cost_table$Cost_Raw * tpf_median# Get 2024 Unrestricted Ending Balance from Sacramento_USD_filtered_net_rev_and_urf_tot_divided_by_mil$Type. Filtered to "UnrestrictedFund." $YearEnd == 2024unrestricted_ending_balance_2024 <- Sacramento_USD_filtered_net_rev_and_urf_tot %>%filter(Type =="UnrestrictedFund", YearEnd ==2024) %>%pull(Value)# Add the unrestricted ending balance to the cost_tablecost_table <- cost_table %>%add_row(Description ="Unrestricted Ending Balance 2024",Cost_Raw = unrestricted_ending_balance_2024,Cost_With_Factor = unrestricted_ending_balance_2024)# Add column that is Cost_With_Factor as a percentage of unrestricted ending balancecost_table <- cost_table %>%mutate(Cost_Percent_Unrestricted =round(Cost_With_Factor / unrestricted_ending_balance_2024 *100, 1))# Sort in descending ordercost_table_plot <- cost_table %>%arrange(desc(Cost_Raw)) |># divide by million and round to 1 decimalmutate(Cost_Raw =round(Cost_Raw /1e6, 1),Cost_With_Factor =round(Cost_With_Factor /1e6, 1))# Format the costs as currency in millions with commas, rounded to 1 decimal place, save as new dfcost_table_formatted <- cost_table %>%mutate(Cost_Raw =dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(Cost_Raw),Cost_With_Factor =dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(Cost_With_Factor))# Create the kable tablekable(cost_table_formatted, col.names =c("Proposal", "Cost (Raw)", "Cost (w/TPF of 1.27)", "Cost (w/TPF) as % of UR Fund"),align =c("l", "r", "r", "r"),caption ="Salary Raise Proposals and Costs (in Millions of Dollars)")
Salary Raise Proposals and Costs (in Millions of Dollars)
Proposal
Cost (Raw)
Cost (w/TPF of 1.27)
Cost (w/TPF) as % of UR Fund
Squaring the Salary Schedule (1021)
$13.1M
$16.7M
9.7
Squaring the Salary Schedule (SCUSD)
$6.6M
$8.4M
4.9
Alternative 1
$7.8M
$10.0M
5.8
Alternative 2
$7.4M
$9.4M
5.4
Alternative 2.1
$6.9M
$8.8M
5.1
Alternative 3
$12.1M
$15.3M
8.9
Alternative 4
$12.0M
$15.2M
8.9
Unrestricted Ending Balance 2024
$172.0M
$172.0M
100.0
Show the code
# Create the lollipop plot of cost_table_plotcost_table_plot %>%ggplot(aes(x =reorder(Description, Cost_With_Factor), y = Cost_With_Factor)) +geom_segment(aes(xend = Description, yend =0), color ="grey") +geom_point(size =12, color ="purple") +coord_flip() +geom_text(aes(label =`Cost_With_Factor`), vjust = .5, size =4, color="white", fontface="bold", family="Avenir Next Condensed") +labs(title ="Total Annual Costs of Squaring the Salary Schedule",x ="Proposal",y ="Total Annual Cost (in Millions of Dollars)") +My_Theme_WithY() +theme(axis.text.x =element_text(angle =45, hjust =1))
4.4 Summary Tables of All Proposals
Show the code
# Calculate 2% raise cost being applied to different salary schedules# In roster_with_SS_pay, create new columns which are the following salary schedules * 1.02# New_Annual_Salary_SSFix_SCUSD# New_Annual_Salary_Alt1# New_Annual_Salary_Alt2# New_Annual_Salary_Alt3roster_with_SS_pay <- roster_with_SS_pay %>%mutate(New_Annual_Salary_SSFix_SCUSD_2p = New_Annual_Salary_SSFix_SCUSD *1.04,New_Annual_Salary_Alt1_2p = New_Annual_Salary_Alt1 *1.04,New_Annual_Salary_Alt2_2p = New_Annual_Salary_Alt2 *1.04,`New_Annual_Salary_Alt2.1_2p`=`New_Annual_Salary_Alt2.1`*1.04,New_Annual_Salary_Alt3_2p = New_Annual_Salary_Alt3 *1.04,New_Annual_Salary_Alt4_2p = New_Annual_Salary_Alt4 *1.04 ) |># Create 4 new columns that are the difference between the new salary schedule and the original salary schedulemutate(WageSSFix_SCUSD_Increase_2p = New_Annual_Salary_SSFix_SCUSD_2p - New_Annual_Salary_SSFix_SCUSD,Wage_Alt1_Increase_2p = New_Annual_Salary_Alt1_2p - New_Annual_Salary_Alt1,Wage_Alt2_Increase_2p = New_Annual_Salary_Alt2_2p - New_Annual_Salary_Alt2,Wage_Alt2p1_Increase_2p =`New_Annual_Salary_Alt2.1_2p`-`New_Annual_Salary_Alt2.1`,Wage_Alt3_Increase_2p = New_Annual_Salary_Alt3_2p - New_Annual_Salary_Alt3,Wage_Alt4_Increase_2p = New_Annual_Salary_Alt4_2p - New_Annual_Salary_Alt4 )# Create new df that is the summary (total cost) of the 2% raise for each salary schedule. It should be 5 rows for each salary schedule. `Raise2p` is column for the first salary scheudle, `Raise2p_Alt1` is the column for the second salary schedule, etc.cost_raise_2perc_all_2024 <- roster_with_SS_pay %>%summarise(Raise2p =sum(Raise2p, na.rm =TRUE),Raise2p_SCUSD =sum(WageSSFix_SCUSD_Increase_2p, na.rm =TRUE),Raise2p_Alt1 =sum(Wage_Alt1_Increase_2p, na.rm =TRUE),Raise2p_Alt2 =sum(Wage_Alt2_Increase_2p, na.rm =TRUE),Raise2p_Alt2p1 =sum(Wage_Alt2p1_Increase_2p, na.rm =TRUE),Raise2p_Alt3 =sum(Wage_Alt3_Increase_2p, na.rm =TRUE),Raise2p_Alt4 =sum(Wage_Alt4_Increase_2p, na.rm =TRUE) ) |># make longpivot_longer(cols =everything(),names_to ="Salary_Schedule",values_to ="Total_Cost" )# Rename columns so they match this# Squaring the Salary Schedule (1021)# Squaring the Salary Schedule (SCUSD)# Alternative 1# Alternative 2# Alternative 3cost_raise_2perc_all_2024 <- cost_raise_2perc_all_2024 %>%mutate(Salary_Schedule =case_when( Salary_Schedule =="Raise2p"~"Squaring the Salary Schedule (1021)", Salary_Schedule =="Raise2p_SCUSD"~"Squaring the Salary Schedule (SCUSD)", Salary_Schedule =="Raise2p_Alt1"~"Alternative 1", Salary_Schedule =="Raise2p_Alt2"~"Alternative 2", Salary_Schedule =="Raise2p_Alt2p1"~"Alternative 2.1", Salary_Schedule =="Raise2p_Alt3"~"Alternative 3", Salary_Schedule =="Raise2p_Alt4"~"Alternative 4" ) )
4.4.1 Annual Costs
Below is the ongoing cost of all proposals. Specifically:
Add:
2% raise
2024-2025 Salary Schedule Fix
2024-2025 4% Raise (reminder: 4% is applied to the new salary schedule, not the original salary schedule)
Equals:
Ongoing cost
Show the code
# Take cost_table, turn into new DF, remove final rowcost_table_1yr <- cost_table[-nrow(cost_table), ]cost_table_1yr <- cost_table_1yr %>%select(-Cost_With_Factor, -Cost_Percent_Unrestricted) %>%rename(`SS Cost (Raw)`= Cost_Raw)# Add new columns and calculate totalscost_table_1yr <- cost_table_1yr %>%add_column(`4% Raise (2y)`= cost_raise_4perc_all_2023, .before =1)# Join cost_raise_2perc_all_2024 to cost_table_1yr and rename new column "2% Raise 2024-25"cost_table_1yr <- cost_table_1yr %>%left_join(cost_raise_2perc_all_2024, by =c("Description"="Salary_Schedule")) |>rename(`2% Raise 2024-25`= Total_Cost) |>mutate(`Total 1-Year Cost (Raw)`=`4% Raise (2y)`+`SS Cost (Raw)`+`2% Raise 2024-25`,`Total 1-Year Cost (TPF)`=`Total 1-Year Cost (Raw)`* tpf_median,`Total 1-Year Cost (TPF) as % of UR Fund`=round(`Total 1-Year Cost (TPF)`/ unrestricted_ending_balance_2024 *100, 1) ) %>%arrange(desc(`Total 1-Year Cost (Raw)`))# Turn into cost_table_1yr_formatted with the costs as currency in millions with commas, rounded to 1 decimal placecost_table_1yr_formatted <- cost_table_1yr %>%mutate(`4% Raise (2y)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`4% Raise (2y)`),`SS Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`SS Cost (Raw)`),`2% Raise 2024-25`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`2% Raise 2024-25`),`Total 1-Year Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 1-Year Cost (Raw)`),`Total 1-Year Cost (TPF)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 1-Year Cost (TPF)`) )# # Kablekable(cost_table_1yr_formatted,col.names =c("2% Raise", "SS Description", "SS Cost (Raw)", "4% Raise", "Total 1-Year Cost (Raw)", "Total 1-Year Cost (TPF of 1.27)", "Total 1-Year Cost (TPF) as % of UR Fund"),align =c("r", "l", "r", "r", "r", "r", "r"),caption ="One-Year Costs of Salary Raise Proposals (in Millions of Dollars)")
One-Year Costs of Salary Raise Proposals (in Millions of Dollars)
2% Raise
SS Description
SS Cost (Raw)
4% Raise
Total 1-Year Cost (Raw)
Total 1-Year Cost (TPF of 1.27)
Total 1-Year Cost (TPF) as % of UR Fund
$1.3M
Squaring the Salary Schedule (1021)
$13.1M
$3.1M
$17.5M
$22.2M
12.9
$1.3M
Alternative 3
$12.1M
$3.1M
$16.4M
$20.8M
12.1
$1.3M
Alternative 4
$12.0M
$3.1M
$16.3M
$20.7M
12.0
$1.3M
Alternative 1
$7.8M
$2.9M
$12.0M
$15.2M
8.9
$1.3M
Alternative 2
$7.4M
$2.9M
$11.5M
$14.6M
8.5
$1.3M
Alternative 2.1
$6.9M
$2.9M
$11.0M
$14.0M
8.2
$1.3M
Squaring the Salary Schedule (SCUSD)
$6.6M
$2.8M
$10.7M
$13.6M
7.9
4.4.2 Two Year Costs (2023-2025)
Exact same as above, except the 4% raise gets counted twice, since it’s being applied retroactively for a year.
Show the code
# Take cost_table, turn into new DF, remove final rowcost_table_2yr <- cost_table[-nrow(cost_table), ]cost_table_2yr <- cost_table_2yr %>%select(-Cost_With_Factor, -Cost_Percent_Unrestricted) %>%rename(`SS Cost (Raw)`= Cost_Raw)# Add new columns and calculate totalscost_table_2yr <- cost_table_2yr %>%add_column(`4% Raise (2y)`= cost_raise_4perc_all_2023 *2, .before =1)# Join cost_raise_2perc_all_2024 to cost_table_2yr and rename new column "2% Raise 2024-25"cost_table_2yr <- cost_table_2yr %>%left_join(cost_raise_2perc_all_2024, by =c("Description"="Salary_Schedule")) |>rename(`2% Raise 2024-25`= Total_Cost) |>mutate(`Total 2-Year Cost (Raw)`=`4% Raise (2y)`+`SS Cost (Raw)`+`2% Raise 2024-25`,`Total 2-Year Cost (TPF)`=`Total 2-Year Cost (Raw)`* tpf_median,`Total 2-Year Cost (TPF) as % of UR Fund`=round(`Total 2-Year Cost (TPF)`/ unrestricted_ending_balance_2024 *100, 1) ) %>%arrange(desc(`Total 2-Year Cost (Raw)`))# Turn into cost_table_2yr_formatted with the costs as currency in millions with commas, rounded to 1 decimal placecost_table_2yr_formatted <- cost_table_2yr %>%mutate(`4% Raise (2y)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`4% Raise (2y)`),`SS Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`SS Cost (Raw)`),`2% Raise 2024-25`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`2% Raise 2024-25`),`Total 2-Year Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 2-Year Cost (Raw)`),`Total 2-Year Cost (TPF)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 2-Year Cost (TPF)`) )# # Kablekable(cost_table_2yr_formatted,col.names =c("2% Raise (2y)", "SS Description", "SS Cost (Raw)", "4% Raise 2024-25", "Total 2-Year Cost (Raw)", "Total 2-Year Cost (TPF of 1.27)", "Total 2-Year Cost (TPF) as % of UR Fund"),align =c("r", "l", "r", "r", "r", "r", "r"),caption ="Two-Year Costs of Salary Raise Proposals (in Millions of Dollars)")
Two-Year Costs of Salary Raise Proposals (in Millions of Dollars)
2% Raise (2y)
SS Description
SS Cost (Raw)
4% Raise 2024-25
Total 2-Year Cost (Raw)
Total 2-Year Cost (TPF of 1.27)
Total 2-Year Cost (TPF) as % of UR Fund
$2.5M
Squaring the Salary Schedule (1021)
$13.1M
$3.1M
$18.8M
$23.8M
13.9
$2.5M
Alternative 3
$12.1M
$3.1M
$17.7M
$22.5M
13.1
$2.5M
Alternative 4
$12.0M
$3.1M
$17.6M
$22.3M
13.0
$2.5M
Alternative 1
$7.8M
$2.9M
$13.3M
$16.9M
9.8
$2.5M
Alternative 2
$7.4M
$2.9M
$12.8M
$16.2M
9.4
$2.5M
Alternative 2.1
$6.9M
$2.9M
$12.3M
$15.6M
9.1
$2.5M
Squaring the Salary Schedule (SCUSD)
$6.6M
$2.8M
$12.0M
$15.2M
8.9
Show the code
# Take cost_table_2yrkeep only column 2 and 6 and add unrestricted ending balancecost_table_plot_2yr_plot <- cost_table_2yr |>select(Description, `Total 2-Year Cost (TPF)`) |>add_row(Description ="Unrestricted Ending Balance 2024",`Total 2-Year Cost (TPF)`= unrestricted_ending_balance_2024) |># sort in descending orderarrange(desc(`Total 2-Year Cost (TPF)`)) |># divide by million and round to 1 decimalmutate(`Total 2-Year Cost (TPF)`=round(`Total 2-Year Cost (TPF)`/1e6, 1))
Show the code
# Create the lollipop plot of cost_table_plotcost_table_plot_2yr_plot %>%ggplot(aes(x =reorder(Description, `Total 2-Year Cost (TPF)`), y =`Total 2-Year Cost (TPF)`)) +geom_segment(aes(xend = Description, yend =0), color ="grey") +geom_point(size =12, color ="purple") +coord_flip() +geom_text(aes(label =`Total 2-Year Cost (TPF)`), vjust = .5, size =4, color="white", fontface="bold", family="Avenir Next Condensed") +labs(title ="Total Two-Year Costs of all Proposals (2023-2025)",x ="Proposal",y ="Total Two-Year Cost (in Millions of Dollars)") +My_Theme_WithY() +theme(axis.text.x =element_text(angle =45, hjust =1))
4.4.3 Summary Table With Differrent Costing Assumptions
Below I estimated the two-year cost including two different assumptions:
A Total Payroll Factor (TPF) of 1.4 instead of 1.27.
A “Vacancy Charge” of 23% on top of the total cost. Employers typically budget and cost out a fully-staffed bargaining unit, which never happens in reality. The “Vacancy Charge” is my estimate to account for this budgeting assumption.
Show the code
tpf_high <-1.4# Take cost_table, turn into new DF, remove final rowcost_table_2yr_new_ass <- cost_table[-nrow(cost_table), ]cost_table_2yr_new_ass <- cost_table_2yr_new_ass %>%select(-Cost_With_Factor, -Cost_Percent_Unrestricted) %>%rename(`SS Cost (Raw)`= Cost_Raw)# Add new columns and calculate totalscost_table_2yr_new_ass <- cost_table_2yr_new_ass %>%add_column(`4% Raise (2y)`= cost_raise_4perc_all_2023 *2, .before =1)# Join cost_raise_2perc_all_2024 to cost_table_2yr_new_ass and rename new column "2% Raise 2024-25"cost_table_2yr_new_ass <- cost_table_2yr_new_ass %>%left_join(cost_raise_2perc_all_2024, by =c("Description"="Salary_Schedule")) |>rename(`2% Raise 2024-25`= Total_Cost) |>mutate(`Total 2-Year Cost (Raw)`=`4% Raise (2y)`+`SS Cost (Raw)`+`2% Raise 2024-25`,`Total 2-Year Cost (TPF)`=`Total 2-Year Cost (Raw)`* tpf_high,`Total w/ Vacancy Charge (1.23)`=`Total 2-Year Cost (TPF)`*1.23,`Total 2-Year Cost (TPF) as % of UR Fund`=round(`Total w/ Vacancy Charge (1.23)`/ unrestricted_ending_balance_2024 *100, 1) ) %>%arrange(desc(`Total 2-Year Cost (Raw)`))# Turn into cost_table_2yr_new_ass_formatted with the costs as currency in millions with commas, rounded to 1 decimal placecost_table_2yr_new_ass_formatted <- cost_table_2yr_new_ass %>%mutate(`4% Raise (2y)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`4% Raise (2y)`),`SS Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`SS Cost (Raw)`),`2% Raise 2024-25`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`2% Raise 2024-25`),`Total 2-Year Cost (Raw)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 2-Year Cost (Raw)`),`Total w/ Vacancy Charge (1.23)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total w/ Vacancy Charge (1.23)`),`Total 2-Year Cost (TPF)`=dollar_format(scale =1e-6, prefix ="$", suffix ="M", accuracy =0.1)(`Total 2-Year Cost (TPF)`) )# Kablekable(cost_table_2yr_new_ass_formatted,col.names =c("4% Raise (2y)", "SS Description", "SS Cost (Raw)", "2% Raise 2024-25", "Total 2-Year Cost (Raw)", "Total 2-Year Cost (TPF of 1.39)", "Total w/ Vacancy Charge (1.23)", "Total (all assumptions) % of UR Fund"),align =c("r", "l", "r", "r", "r", "r", "r", "r"),caption ="Two-Year Costs of Salary Raise Proposals (in Millions of Dollars)")
Two-Year Costs of Salary Raise Proposals (in Millions of Dollars)
4% Raise (2y)
SS Description
SS Cost (Raw)
2% Raise 2024-25
Total 2-Year Cost (Raw)
Total 2-Year Cost (TPF of 1.39)
Total w/ Vacancy Charge (1.23)
Total (all assumptions) % of UR Fund
$2.5M
Squaring the Salary Schedule (1021)
$13.1M
$3.1M
$18.8M
$26.3M
$32.4M
18.8
$2.5M
Alternative 3
$12.1M
$3.1M
$17.7M
$24.8M
$30.5M
17.7
$2.5M
Alternative 4
$12.0M
$3.1M
$17.6M
$24.6M
$30.3M
17.6
$2.5M
Alternative 1
$7.8M
$2.9M
$13.3M
$18.6M
$22.9M
13.3
$2.5M
Alternative 2
$7.4M
$2.9M
$12.8M
$17.9M
$22.0M
12.8
$2.5M
Alternative 2.1
$6.9M
$2.9M
$12.3M
$17.2M
$21.2M
12.3
$2.5M
Squaring the Salary Schedule (SCUSD)
$6.6M
$2.8M
$12.0M
$16.8M
$20.7M
12.0
Show the code
cost_table_plot_2yr_plot_new_ass <- cost_table_2yr_new_ass |>select(Description, `Total w/ Vacancy Charge (1.23)`) |>add_row(Description ="Unrestricted Ending Balance 2024",`Total w/ Vacancy Charge (1.23)`= unrestricted_ending_balance_2024) |># sort in descending orderarrange(desc(`Total w/ Vacancy Charge (1.23)`)) |># divide by million and round to 1 decimalmutate(`Total w/ Vacancy Charge (1.23)`=round(`Total w/ Vacancy Charge (1.23)`/1e6, 1))# Create the lollipop plot of cost_table_plotcost_table_plot_2yr_plot_new_ass %>%ggplot(aes(x =reorder(Description, `Total w/ Vacancy Charge (1.23)`), y =`Total w/ Vacancy Charge (1.23)`)) +geom_segment(aes(xend = Description, yend =0), color ="grey") +geom_point(size =12, color ="purple") +coord_flip() +geom_text(aes(label =`Total w/ Vacancy Charge (1.23)`), vjust = .5, size =4, color="white", fontface="bold", family="Avenir Next Condensed") +labs(title ="Total Two-Year Costs of all Proposals (2023-2025)",x ="Proposal",y ="Total Two-Year Cost (in Millions of Dollars)") +My_Theme_WithY() +theme(axis.text.x =element_text(angle =45, hjust =1))
4.5 Distributional Analysis of Proposals
The graph below shows the exact percentage raise for each 1,878 Employee IDs in the bargaining unit. On the X-axis (left to right), they are ordered by initial annual salary. By sorting by initial annual salary, and then showing the percentage raise, we can see how the proposed raises are being distributed–whether it’s “progressive” (more to lower-paid members) or “regressive” (more to higher-paid). This scatterplot is then displayed for all 6 different proposals, so we can easily compare across proposals.
4.5.1 Cases of Negative Raises from Salary Schedule Fixes
Depending on your costing assumptions, Alternative Proposal #2 (2% ranges, 4% steps) is only $300K to $500K more costly than the District’s initial proposal, despite fully fixing the salary schedule with no cliffs and a smooth progression of range and step increases. Unfortunately, at the top of the salary schedule, starting at Range 78, Step 5, some cells end up below the status quo (that is after the 4% retro). This is because the combination of the 2% and 4% differentials, which are lower in many cases than the current 2.5% and 5% differentials, results in a lower salary schedule for a handful of employees at the top. The table below shows the Employee IDs and their negative raises for each proposal.
A fix is relatively simple: just include language that “for any salary schedule cell that ends up lower than the status quo, the employee will receive the status quo salary.” Such a fix would solve the issue and cost negligible amounts of money.
Show the code
library(tidyverse)library(kableExtra)# Function to identify negative raise percentages across specified columnsfind_negative_raises <-function(df) {# Vector of columns to check with their display names raise_cols <-c("RaiseSSFixperc","RaiseSSFix_SCUSDperc","Raise_Alt1_perc","Raise_Alt2_perc","Raise_Alt2.1_perc","Raise_Alt3_perc","Raise_Alt4_perc" )# Create named vector for renaming proposal_names <-c("RaiseSSFixperc"="Initial 1021 Fix Proposal (2.5 & 5%)","RaiseSSFix_SCUSDperc"="Initial SCUSD Fix Proposal (2 & 4.5% to Range 48)","Raise_Alt1_perc"="Alternative 1 (2 & 4.5%)","Raise_Alt2_perc"="Alternative 2 (2 & 4.25%)","Raise_Alt2.1_perc"="Alternative 2.1 (2 & 4.0%)","Raise_Alt3_perc"="Alternative 3 (Two-Tiered)","Raise_Alt4_perc"="Alternative 4 (Three-Tiered)" )# Process data and create table result <- df %>%# Select only necessary columnsselect(`Emp ID`, Range, Step,all_of(raise_cols) ) %>%# Convert wide to long formatpivot_longer(cols =all_of(raise_cols),names_to ="Proposal",values_to ="Value" ) %>%# Filter for negative valuesfilter(Value <0) %>%# Replace proposal names with descriptive versionsmutate(Proposal = proposal_names[Proposal]) %>%# Arrange by Emp ID and Proposalarrange(`Emp ID`, Proposal) %>%# Sort by Value first (this will sort the numeric values before formatting)arrange(Value, `Emp ID`)# Format the Value column as percentage with one decimal place result$Value <-sprintf("%.1f%%", result$Value)# Create formatted kable output kable_output <- result %>%kable(col.names =c("Employee ID", "Range", "Step", "Proposal", "Value"),caption ="Employees with Negative Raise Percentages",align =c('r', 'r', 'r', 'l', 'r') ) %>%kable_styling(bootstrap_options =c("striped", "hover"),full_width =FALSE,position ="left" )return(kable_output)}# Example usage:negative_raises_table <-find_negative_raises(roster_with_SS_pay)negative_raises_table
Employees with Negative Raise Percentages
Employee ID
Range
Step
Proposal
Value
NA
NA
NA
NA
NA
-----------:
-----:
----:
:--------
-----:
Show the code
# Save roster_with_SS_pay to a csv file to here() output_datawrite_csv(roster_with_SS_pay, here("Districts/Sacramento/output_data/", "roster_with_SS_pay.csv"))write_csv(roster_SS_diff, here("Districts/Sacramento/output_data/", "roster_SS_diff.csv"))
4.6 Final Salary Schedule Proposals (after 4% raise is applied to SS fix)
Show the code
library(tidyverse)# Function to adjust salaries with roundingadjust_salaries <-function(df) { df %>%mutate(across(2:6, ~round(.x *1.04, 2)))}# Apply adjustment to each dataframesalary_schedule_1021_2023_24_adj <-adjust_salaries(salary_schedule_1021_analyzed_2023_24)salary_schedule_1021_alt1_adj <-adjust_salaries(salary_schedule_1021_alt1_analyzed)salary_schedule_1021_alt2_adj <-adjust_salaries(salary_schedule_1021_alt2_analyzed)salary_schedule_1021_alt2p1_adj <-adjust_salaries(salary_schedule_1021_alt2p1_analyzed)salary_schedule_1021_alt3_adj <-adjust_salaries(salary_schedule_1021_alt3_analyzed)salary_schedule_1021_alt4_adj <-adjust_salaries(salary_schedule_1021_alt4_analyzed)# Display all adjusted dataframes with headerscat("\n=== Initial 1021 Proposal (w/4% Increase) ===\n")
