Advice-Only/Fee-Only Best Advisors Using The Productivity Study

library(haven)
library(tidyverse)
library(knitr)
library(kableExtra)
library(scales) # Needed for clean currency/dollar formatting

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)

# FIXED: Switched the median calculation split to rvxadv (Revenue Per Advisor)
med_rev <- median(advice_only$rvxadv, na.rm = TRUE)
med_rev_formatted <- dollar(med_rev, accuracy = 1) # Store formatted string for text below

processed_data <- advice_only %>% 
  # FIXED: Protect calculations from observations missing rvxadv data
  filter(!is.na(rvxadv)) %>% 
  mutate(
    # FIXED: Split performance segments using Revenue Per Advisor
    adv_seg = if_else(rvxadv <= med_rev, "Below Median Revenue Per Advisor", "Above Median Revenue Per Advisor"),
    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),
    rev_client   = median(rvxclient, 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 == "rev_client"   ~ 5, 
      Metric == "role_senior"  ~ 7, 
      Metric == "role_service" ~ 8,
      Metric == "role_assoc"   ~ 9,
      Metric == "role_para"    ~ 10,
      Metric == "role_admin"   ~ 11
    )
  ) %>% 
  mutate(
    `Below Median Revenue Per Advisor` = 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 Per Advisor`)),
      Metric %in% c("comp_med", "team_tot") 
        ~ sprintf("%.0f", round(`Below Median Revenue Per Advisor`)),
      Metric == "rev_client"
        ~ dollar(`Below Median Revenue Per Advisor`, accuracy = 1),
      TRUE ~ as.character(`Below Median Revenue Per Advisor`)
    ),
    `Above Median Revenue Per Advisor` = 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 Per Advisor`)),
      Metric %in% c("comp_med", "team_tot") 
        ~ sprintf("%.0f", round(`Above Median Revenue Per Advisor`)),
      Metric == "rev_client"
        ~ dollar(`Above Median Revenue Per Advisor`, accuracy = 1),
      TRUE ~ as.character(`Above Median Revenue Per Advisor`)
    ),
    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 == "rev_client"   ~ "Median Revenue Per Client",
      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 Per Advisor` = "", 
    `Above Median Revenue Per Advisor` = "", 
    Row_Order = 6 
  ) %>% 
  arrange(Row_Order) %>% 
  select(Metric, `Below Median Revenue Per Advisor`, `Above Median Revenue Per Advisor`)

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

Advice-Only Practices By Median Revenue Per Advisor

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

# Dynamically prints the precise value of the median split dollar amount
cat(paste0("Note: The median Revenue Per Advisor is ", med_rev_formatted, ".\n\n"))

Note: The median Revenue Per Advisor is $172,500.

# Target interpretation text required by your teammate
cat("What makes advice-only advisors productive is the same thing that makes all advisors productive: having a team to leverage and support you, and using that added leverage to offer the more complex planning work that higher-value clients want/have the financial wherewithal to pay for.\n")

What makes advice-only advisors productive is the same thing that makes all advisors productive: having a team to leverage and support you, and using that added leverage to offer the more complex planning work that higher-value clients want/have the financial wherewithal to pay for.