Advice-Only/Fee-Only Best Advisors

library(haven)       
library(tidyverse)   
library(knitr)       
library(kableExtra)  

file_path <- "C:\\Users\\JH\\Kitces.com\\DV - Research\\Anonymized Data and Codebooks - All Projects\\Productivity Studies\\2024\\fp24.dta"

df <- read_dta(file_path)

# define advice only 
advice_only <- df %>% 
  filter(grpadvice == 1)

med_rev <- median(advice_only$rvxmbr, na.rm = TRUE)

processed_data <- advice_only %>%
  filter(!is.na(rvxmbr)) %>%
  mutate(
    adv_seg = if_else(rvxmbr <= med_rev, "Below Median Revenue", "Above Median Revenue"),
    
    total_weekly_hrs = rowSums(across(c(
      hrsprospect, hrsmarket, hrsprep, hrscurrentcl, hrsfinplan, 
      hrsinvestres, hrsinvestmgt, hrsclserv, hrsadmin, hrsmgmt, 
      hrscompliance, hrsproffdev, hrsoth
    )), na.rm = TRUE),
    
    total_weekly_hrs = if_else(is.na(hrscurrentcl) & is.na(hrsfinplan), NA_real_, total_weekly_hrs),
    
    pct_current_cl = (hrscurrentcl / total_weekly_hrs) * 100,
    pct_current_cl = if_else(is.infinite(pct_current_cl) | is.nan(pct_current_cl), NA_real_, pct_current_cl),
    
    pct_fin_plan = (hrsfinplan / total_weekly_hrs) * 100,
    pct_fin_plan = if_else(is.infinite(pct_fin_plan) | is.nan(pct_fin_plan), NA_real_, pct_fin_plan)
  ) %>%
  mutate(
    missing_components = rowSums(across(c(
      pcvrretire, pcvrinvest, pcvrtxplan, pcvrlifeins, pcvrsave, pcvrcash, 
      pcvrestate, pcvrcollege, pcvr401k, pcvrltc, pcvrdisability, pcvroptions, 
      pcvrbenefits, pcvrcredit, pcvrproperty, pcvrhealth, pcvrstudent, pcvrbiz, 
      pcvrtxprep, pcvrcareer, pcvrestatedoc, pcvrheld401k, pcvrcharity, pcvrss, 
      pcvrmedicare, pcvrlifepl, pcvrrealestate, pcvroth
    ), is.na)),
    
    total_components = rowSums(across(c(
      pcvrretire, pcvrinvest, pcvrtxplan, pcvrlifeins, pcvrsave, pcvrcash, 
      pcvrestate, pcvrcollege, pcvr401k, pcvrltc, pcvrdisability, pcvroptions, 
      pcvrbenefits, pcvrcredit, pcvrproperty, pcvrhealth, pcvrstudent, pcvrbiz, 
      pcvrtxprep, pcvrcareer, pcvrestatedoc, pcvrheld401k, pcvrcharity, pcvrss, 
      pcvrmedicare, pcvrlifepl, pcvrrealestate, pcvroth
    )), na.rm = TRUE),
    
    total_components = if_else(missing_components == 28, NA_real_, total_components)
  )

 
#  SUMMARY STATISTICS  
 summary_metrics <- processed_data %>%
  group_by(adv_seg) %>%
  summarise(
    meet_pct     = median(pct_current_cl, na.rm = TRUE),
    plan_pct     = median(pct_fin_plan, na.rm = TRUE),
    comp_med     = median(total_components, na.rm = TRUE),
    team_tot     = mean(ftetot, na.rm = TRUE),
    role_senior  = mean(haslead == 1, na.rm = TRUE) * 100,
    role_service = mean(hassrv == 1, na.rm = TRUE) * 100,
    role_assoc   = mean(hasass == 1, na.rm = TRUE) * 100,
    role_para    = mean(haspara == 1, na.rm = TRUE) * 100,
    role_admin   = mean(hascsa == 1, na.rm = TRUE) * 100
  )

 #  MATRIX RESHAPING & LABELS
final_table_df <- summary_metrics %>%
  pivot_longer(
    cols = -adv_seg,
    names_to = "Metric",
    values_to = "Value"
  ) %>%
  pivot_wider(
    names_from = adv_seg,
    values_from = Value
  ) %>%
  mutate(
    Row_Order = case_when(
      Metric == "meet_pct"     ~ 1,
      Metric == "plan_pct"     ~ 2,
      Metric == "comp_med"     ~ 3,
      Metric == "team_tot"     ~ 4,
      Metric == "role_senior"  ~ 6, 
      Metric == "role_service" ~ 7,
      Metric == "role_assoc"   ~ 8,
      Metric == "role_para"    ~ 9,
      Metric == "role_admin"   ~ 10
    )
  ) %>%
  mutate(
    `Below Median Revenue` = case_when(
      Metric %in% c("meet_pct", "plan_pct", "role_senior", "role_service", "role_assoc", "role_para", "role_admin") 
        ~ sprintf("%.0f%%", round(`Below Median Revenue`)),
      Metric %in% c("comp_med", "team_tot") 
        ~ sprintf("%.0f", round(`Below Median Revenue`)),
      TRUE ~ as.character(`Below Median Revenue`)
    ),
    `Above Median Revenue` = case_when(
      Metric %in% c("meet_pct", "plan_pct", "role_senior", "role_service", "role_assoc", "role_para", "role_admin") 
        ~ sprintf("%.0f%%", round(`Above Median Revenue`)),
      Metric %in% c("comp_med", "team_tot") 
        ~ sprintf("%.0f", round(`Above Median Revenue`)),
      TRUE ~ as.character(`Above Median Revenue`)
    ),
    Metric = case_when(
      Metric == "meet_pct"     ~ "% Of Weekly Hours Spent On Meeting With Current Clients",
      Metric == "plan_pct"     ~ "% Of Weekly Hours Spent On Financial Plan Preparation",
      Metric == "comp_med"     ~ "Median Components Included In Financial Plans",
      Metric == "team_tot"     ~ "Mean Total Service Team Members (FTE)",
      Metric == "role_senior"  ~ "Senior Advisors",
      Metric == "role_service" ~ "Service Advisors",
      Metric == "role_assoc"   ~ "Associate Advisors",
      Metric == "role_para"    ~ "Paraplanners",
      Metric == "role_admin"   ~ "Admin / CSA"
    )
  ) %>%
  add_row(
    Metric = "% Of Teams With This Role Present", 
    `Below Median Revenue` = "", 
    `Above Median Revenue` = "", 
    Row_Order = 5
  ) %>%
  arrange(Row_Order) %>%
  select(Metric, `Below Median Revenue`, `Above Median Revenue`)

  
#  TABLE  
kable(
  final_table_df, 
  align = c("l", "c", "c"),
  col.names = c("Metric", "Below Median Revenue", "Above Median Revenue")
) %>%
   kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"), 
    full_width = FALSE,
    position = "left"
  ) %>%
   row_spec(
    0, 
    bold = TRUE, 
    color = "white", 
    background = "#1B365D"
  ) %>%
 
  row_spec(
    5, 
    bold =  FALSE,
    color = "#000000", 
    background = "#E8EEF5"
  ) %>%
    add_indent(c(6, 7, 8, 9, 10))

Metric	Below Median Revenue	Above Median Revenue
% Of Weekly Hours Spent On Meeting With Current Clients	11%	20%
% Of Weekly Hours Spent On Financial Plan Preparation	18%	17%
Median Components Included In Financial Plans	14	18
Mean Total Service Team Members (FTE)	1	3
% Of Teams With This Role Present
Senior Advisors	100%	100%
Service Advisors	0%	29%
Associate Advisors	0%	14%
Paraplanners	7%	14%
Admin / CSA	7%	21%