# import libs
library(readxl)
library(tidyverse)
library(dplyr)
library(lubridate)
library(ggplot2)
library(plotly)
library(hrbrthemes)
library(viridis)
library(fontawesome)I’m going to create (technically on here), clean, and did some EDA a dummy data for my KPI Dashboard Capstone Project. The components of the portfolio are not real numbers and are solely based on my own view and experience as a Relationship Manager.
# importing company data
port_1 <- read_xlsx("data/portfolio1.xlsx")
port_2 <- read_xlsx("data/portfolio2.xlsx")
port_3 <- read_xlsx("data/portfolio3.xlsx")
# combining data
portfolio <- bind_rows(port_1, port_2, port_3)
# check for duplicate company name
n_distinct(portfolio$CompanyName)[1] 297# drop duplicates company name and adding row numbers
portfolio <- portfolio[!duplicated(portfolio$CompanyName), ]
# add No column
portfolio$No <- seq.int(nrow(portfolio))
# arrange columns
portfolio <- portfolio %>% select(14, everything())
DT::datatable(portfolio, rownames = F)Since there are 297 companies, I’ll be dividing those 297 to 6 RMs (Relationship Manager) under 2 SH (Sales Head – 3 RMs each SH). The names of the RMs and SHs are generated by the same website.
# import names
name <- read_xlsx("data/name.xlsx")
# subset 2 SH names
sh <- name %>% head(2)
sh <- unique(sh$RMName)
# subset 6 RM names
rm <- name %>% tail(6)
rm <- unique(rm$RMName)
# portfolio distribution: randomized with sample(80,1)
## rm1
rm1 <- portfolio %>%
filter(No <=58) %>%
mutate(SHName = sh[1], RMName = rm[1]) %>%
select(1, 15, 16, everything())
## rm2
rm2 <- portfolio %>%
filter(No > 58, No <= 104) %>%
mutate(SHName = sh[1], RMName = rm[2]) %>%
select(1, 15, 16, everything())
## rm3
rm3 <- portfolio %>%
filter(No > 104, No <= 160) %>%
mutate(SHName = sh[1], RMName = rm[3]) %>%
select(1, 15, 16, everything())
## rm4
rm4 <- portfolio %>%
filter(No > 160, No <= 182) %>%
mutate(SHName = sh[2], RMName = rm[4]) %>%
select(1, 15, 16, everything())
## rm5
rm5 <- portfolio %>%
filter(No > 182, No <= 250) %>%
mutate(SHName = sh[2], RMName = rm[5]) %>%
select(1, 15, 16, everything())
## rm6
rm6 <- portfolio %>%
filter(No > 250, No <= 297) %>%
mutate(SHName = sh[2], RMName = rm[6]) %>%
select(1, 15, 16, everything())
# combine all portfolio
portfolio <- rbind(rm1, rm2, rm3, rm4, rm5,rm6)
# total portfolio of each RM
casa_2020_summary <- portfolio %>% group_by(SHName, RMName) %>% summarise(Volume2020 = sum(December)) %>% ungroup() %>% arrange(desc(Volume2020))
# total SH portfolio
#sh_port <- casa_2020_summary %>% group_by(SHName) %>% summarise(Volume2020 = sum(Volume2020))Next I’m going to import company NTB data generated by the same website above. Since for this case each RM’s target is to obtain 20 NTBs per year, the distribution of how many NTBs do each RM get will be generated with sample(20,1).
# import NTB data
ntb <- read_xlsx("data/ntb1.xlsx")
# check for duplicates within ntb dataset
n_distinct(ntb)[1] 100# check for duplicates within portfolio dataset
dup <- bind_rows(portfolio, ntb)
n_distinct(dup)[1] 397# create a no column
ntb$No <- seq.int(nrow(ntb))
# ntb distribution
set.seed(132)
sample(20,6)[1] 11 2 6 15 14 9## rm1
ntb1 <- ntb %>%
filter(No <=11) %>%
mutate(SHName = sh[1], RMName = rm[1]) %>%
select(3:5,1:2, everything())
## rm2
ntb2 <- ntb %>%
filter(No > 11, No <= 13) %>%
mutate(SHName = sh[1], RMName = rm[2]) %>%
select(3:5,1:2, everything())
## rm3
ntb3 <- ntb %>%
filter(No > 13, No <= 19) %>%
mutate(SHName = sh[1], RMName = rm[3]) %>%
select(3:5,1:2, everything())
## rm4
ntb4 <- ntb %>%
filter(No > 19, No <= 34) %>%
mutate(SHName = sh[2], RMName = rm[4]) %>%
select(3:5,1:2, everything())
## rm5
ntb5 <- ntb %>%
filter(No > 34, No <= 48) %>%
mutate(SHName = sh[2], RMName = rm[5]) %>%
select(3:5,1:2, everything())
## rm6
ntb6 <- ntb %>%
filter(No > 48, No <= 57) %>%
mutate(SHName = sh[2], RMName = rm[6]) %>%
select(3:5,1:2, everything())
# combining all rows
ntb <- bind_rows(ntb1, ntb2, ntb3, ntb4, ntb5,ntb6)
# change NTBDate type to Date and extract month's name
ntb_2020_company <- ntb %>% mutate(NTBDate = dmy(NTBDate),
Month = month(NTBDate, label = T, abbr = F)) %>%
select(-NTBDate)
# ntb portfolio 2020
ntb_2020_month <- ntb_2020_company %>%
group_by(SHName, RMName, Month) %>%
summarise(NTB2020 = length(CompanyName)) %>%
ungroup()
ntb_2020_summary <- ntb_2020_month %>%
group_by(SHName, RMName) %>%
summarise(NTB2020 = sum(NTB2020))Next step is I’ll be grading each RM from Grade 1 - Grade 3 with Grade 3 being the highest grade according to their present portfolio.
- Grade 1: Total portfolio of 0 - 1 000 Bio with 10% target incremental from previous period.
- Grade 2: Total portfolio of 1 000 Bio - 3 000 Bio with 15% target incremental from previous period.
- Grade 3: Total portfolio of > 3 000 Bio with 20% target incremental from previous period.
# rm officer grade
casa_2020_summary <-casa_2020_summary %>% mutate(Grade = case_when(
Volume2020 >= 3000000 ~ "Grade 3",
Volume2020 >= 1000000 ~ "Grade 2",
TRUE ~ "Grade 1"))For this dataset, the KPI components will be set to:
- Volume: 50%
- Revenue: 35%
- NTB: 15%
Due to the limited type of data that I made, Revenue is calculated as 3% of each month’s balance, and since there’s no average balance for 2019, the Revenue will be taken from 3% of Volume2019* 12 months.
# calculating 2020 Revenue
casa_2020_month <- portfolio %>%
pivot_longer(!c(No, SHName, RMName, CompanyName), names_to = "Month", values_to = "Balance") %>%
group_by(SHName, RMName, Month) %>%
summarise(Balance2020 = sum(Balance)) %>%
ungroup() %>%
mutate(Revenue2020 = 0.03 * Balance2020)
revenue_2020_summary <- casa_2020_month %>%
group_by(SHName, RMName) %>%
summarise(Revenue2020 = sum(Revenue2020))
casa_2020_summary <- left_join(casa_2020_summary, revenue_2020_summary) %>%
select(1,2,4,3,5)
# 2019 portfolio by random numbers generated with sample()
casa_2020_summary$Volume2019 <- c(3090248.2, 2948506.6, 2654778.7, 2545710.5, 2055439.8, 765275.5)
# 2019 revenue
overall_summary <- casa_2020_summary %>% mutate(Revenue2019 = 0.03 * Volume2019 * 12)
# 2019 NTB gained by random numbers generated with sample()
set.seed(654)
sample(20, 6)[1] 11 16 5 12 4 1overall_summary$NTB2019 <- c(11, 16, 5, 12, 4, 1)
# joining ntb column
overall_summary <- left_join(overall_summary, ntb_2020_summary) %>%
select(1:3, 9, 4, 5, 8, everything())Creating actual target for 2020 with increments according to RM’s grade.
# create actual 2020 target.
target_2020 <- overall_summary %>%
#select(1:3,7:8) %>%
mutate(TargetVolume2020 = case_when(Grade == "Grade 1" ~ 1.1 * Volume2019,
Grade == "Grade 2" ~ 1.15 * Volume2019,
TRUE ~ 1.2 * Volume2019),
TargetRevenue2020 = case_when(Grade == "Grade 1" ~ 1.1 * Revenue2019,
Grade == "Grade 2" ~ 1.15 * Revenue2019,
TRUE ~ 1.2 * Revenue2019)) %>%
select(1:3,10,11)
## NTB target
target_2020$TargetNTB2020 <- 20
# arrange columns
target_2020 <- target_2020 %>% select(1:3,6,4:5)Since NTB counts are not cumulative, I’ll create a new column with the new cumulative sum of each RM’s NTB per month.
# all rm 2020 portfolio per month
casa_ntb_2020 <- full_join(ntb_2020_month, casa_2020_month) %>%
group_by(SHName, RMName, Month) %>%
summarise(NTB2020 = sum(NTB2020),
Volume2020 = sum(Balance2020),
Revenue2020 = sum(Revenue2020))
# replace NTB NA with 0
casa_ntb_2020[is.na(casa_ntb_2020)] <- 0
# change all months column to factor
ntb_2020_month$Month = as.factor(ntb_2020_month$Month)
casa_2020_month$Month = as.factor(casa_2020_month$Month)
# arrange month's name
casa_ntb_2020 <- casa_ntb_2020 %>% arrange(match(Month, month.name))
# subset data per rm
## rm1
rm1_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[1]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
## rm2
rm2_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[2]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
## rm3
rm3_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[3]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
## rm4
rm4_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[4]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
## rm5
rm5_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[5]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
## rm6
rm6_2020 <- casa_ntb_2020 %>%
filter(RMName == rm[6]) %>%
mutate(NTB2020 = cumsum(NTB2020),
Revenue2020 = cumsum(Revenue2020))
# combine all rows
casa_ntb_2020_2 <- bind_rows(rm1_2020, rm2_2020, rm3_2020, rm4_2020, rm5_2020, rm6_2020)Transforming RM’s 2020 Portfolio data per month to KPI %.
real_target_2020 <- left_join(casa_ntb_2020_2, target_2020) %>% select(-Grade) %>%
mutate(Achieved = round(NTB2020/TargetNTB2020*15 + Volume2020/TargetVolume2020*50 + Revenue2020/TargetRevenue2020*35, 2),
Achieved = case_when(Achieved > 100.00 ~ 100.00,
TRUE ~ Achieved))
#%>%
#mutate(Achieved = case_when(Achieved > 100.00 ~ 100.00,
#TRUE ~ Achieved))
# change data types
real_target_2020$SHName = as.factor(casa_ntb_2020_2$SHName)
real_target_2020$RMName = as.factor(casa_ntb_2020_2$RMName)month.abb [1] "Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"Abbreviate month names
real_target_2020 <- real_target_2020 %>% mutate(Month = stringr::str_extract(Month, "^.{3}"))Here are the end results of the data that will be used on the Shiny KPI Dashboard.
# RMs' portfolio for the year 2020
DT::datatable(portfolio %>% select(-No), rownames = F)# RMs' targets for 2020 and their achievements per month
DT::datatable(real_target_2020, rownames = F)# overall summary for RMs' target and achievement summarized in the whole year
DT::datatable(overall_summary, rownames = F)The end result for the KPI Dashboard can be seen here, as mentioned before.