setting path and wd

getwd()

libraries

library(tidyverse)
library(readxl)
library(kableExtra)
library(scales)

Importing data

dataset <- read_xlsx("xutum_data.xlsx")

dataset[,c(4:11)] <- sapply(dataset[,c(4:11)], as.numeric)
dataset$Dates <- as.character(dataset$Dates)

Dividing by sectors and years

dataset %>%
    group_by(Dates)%>%
    group_split(Dates)%>%
    set_names((nm = unique(dataset$Dates))) -> yearly

dataset %>%
    group_by(Sector)%>%
    group_split(Sector)%>%
    set_names((nm = sort(unique(dataset$Sector)))) -> sectorly

Summary Table Codes

We set up our summary tables using group by, summarise and mutate functions from dplyr package. Had to create new functions for aggregate to remove NA values and count unique values in tables.

yearly_summary <- dataset%>%
    group_by(Dates)%>%
    summarise(avg_emp = round(mean(`Number of Employees`, na.rm = T),2),
              sd_emp = round(sd(`Number of Employees`,na.rm=T),2),
              avg_exp_per = mean(`Personnel Expense Per Employee`, na.rm = T),
              avg_profit = mean(`Profit Margin`, na.rm = T),
              avg_sales = mean(Sales, na.rm = T))%>%
    mutate(emp_index = avg_emp/first(avg_emp),
           exp_index = avg_exp_per/first(avg_exp_per),
           profit_index = avg_profit/first(avg_profit),
           sales_index = avg_sales/first(avg_sales))

sectorly_summary <- dataset%>%
    group_by(Sector)%>%
    summarise(avg_emp = round(mean(`Number of Employees`, na.rm = T),2),
              sd_emp = round(sd(`Number of Employees`,na.rm=T),2),
              avg_exp_per = round(mean(`Personnel Expense Per Employee`, na.rm = T),2),
              avg_profit = round(mean(`Profit Margin`, na.rm = T),2),
              avg_sales = round(mean(Sales, na.rm = T),2))

all_summary <- dataset%>%
    group_by(Sector, Dates)%>%
    summarise(avg_emp = round(mean(`Number of Employees`, na.rm = T),2),
              avg_exp_per = mean(`Personnel Expense Per Employee`, na.rm = T),
              avg_profit = mean(`Profit Margin`, na.rm = T),
              avg_sales = mean(Sales, na.rm = T),
              avg_market_cap = mean(`Market Cap`, na.rm = T)) 

namean <- function(data){
    mean(data, na.rm = T)
}
difference <- dataset%>%
    filter(Dates %in% c("2015-12-31","2021-12-31"))%>%
    pivot_wider(id_cols = Sector,
                values_from = c("Number of Employees","Sales","Profit Margin"),
                names_from = Dates, 
                values_fn = namean)

difference <- difference %>% 
    mutate(emp_change = 100*(`Number of Employees_2021-12-31`- `Number of Employees_2015-12-31`)/`Number of Employees_2015-12-31`,
           sales_change = 100*(`Sales_2021-12-31`- `Sales_2015-12-31`)/`Sales_2015-12-31`,
           profit_change = 100*(`Profit Margin_2021-12-31`- `Profit Margin_2015-12-31`)/`Profit Margin_2015-12-31`)


unl <- function(data){
    length(unique(data))
}
sector_firms <- aggregate(dataset$Firm, by = list(dataset$Sector),FUN = unl)
dates_firms <- aggregate(dataset$Firm, by = list(dataset$Dates), FUN = unl)

no_firms <- dataset%>% 
    group_by(Sector) %>% 
    summarise(number = length(unique(Firm)))

ratio <- prop.table(no_firms$number)*100

ratio_table <- cbind(no_firms, ratio)

Summary Tables

Here it shows how many firms are in data set by years, yearly and sectorly summary of variables, difference between first observation and the last, and summary of whole data set.

dates_firms %>% 
    kbl() %>% 
     kable_styling(bootstrap_options = c("striped", "hover"))
Group.1 x
2015-12-31 406
2016-12-31 408
2017-12-31 408
2018-12-31 404
2019-12-31 397
2020-12-31 393
2021-12-31 369 
yearly_summary%>%
    kbl()%>%
    kable_styling(bootstrap_options = c("striped", "hover"))
Dates avg_emp sd_emp avg_exp_per avg_profit avg_sales emp_index exp_index profit_index sales_index
2015-12-31 2716.92 8141.46 140470.7 -35.90926 2321.607 1.0000000 1.000000 1.0000000 1.000000
2016-12-31 2686.43 8157.28 144148.6 -563.24627 2494.706 0.9887777 1.026183 15.6852647 1.074560
2017-12-31 2623.63 7995.05 143427.6 1489.53624 3038.747 0.9656633 1.021050 -41.4805588 1.308898
2018-12-31 2442.46 7803.98 166893.7 19.46442 3815.813 0.8989812 1.188103 -0.5420444 1.643609
2019-12-31 2731.63 8504.67 179087.8 -87.74947 4796.409 1.0054142 1.274912 2.4436444 2.065987
2020-12-31 2837.01 9127.57 216933.8 98.68740 4968.918 1.0442008 1.544335 -2.7482435 2.140293
2021-12-31 3097.61 9837.13 268444.2 201.41748 8248.793 1.1401182 1.911034 -5.6090676 3.553053 
sectorly_summary%>%
    kbl()%>%
    kable_styling(bootstrap_options = c("striped", "hover"))
Sector avg_emp sd_emp avg_exp_per avg_profit avg_sales
ACCOMMODATION AND FOOD SERVICE ACTIVITIES 1677.33 3127.80 93013.90 36.38 622.86
ADMINISTRATIVE AND SUPPORT SERVICE ACTIVITIES 1070.46 3016.71 63811.36 38.71 260.37
AGRICULTURE, FORESTRY AND FISHING 97.00 52.64 34930.53 14.32 82.72
ARTS, ENTERTAINMENT AND RECREATION 347.57 196.51 902135.75 -42.81 483.29
CONSTRUCTION 3857.28 6927.34 66777.53 -16.31 2072.65
ELECTRICITY, GAS, STEAM AND AIR CONDITIONING SUPPLY 1273.78 2707.24 108117.21 4531.89 3232.46
FINANCIAL AND INSURANCE ACTIVITIES 3381.09 9927.29 488609.10 -183.11 9801.18
HUMAN HEALTH AND SOCIAL WORK ACTIVITIES 5654.12 5255.45 75939.43 4.99 1424.09
INFORMATION AND COMMUNICATION 3445.77 8700.42 82711.22 14.69 2939.29
MANUFACTURING 2236.90 8238.15 118916.52 3.26 3437.30
MINING AND QUARRYING 1341.76 964.06 70116.21 19.15 1316.54
PROFESSIONAL, SCIENTIFIC AND TECHNICAL ACTIVITIES 218.16 307.20 110467.66 16.48 354.44
REAL ESTATE ACTIVITIES 85.58 152.29 192809.23 297.96 339.15
TRANSPORTATION AND STORAGE 8153.67 10698.19 106451.65 30.83 8561.95
WATER SUPPLY; SEWERAGE, WASTE MANAGEMENT AND REMEDIATION ACTIVITIES 243.50 134.87 76247.41 6.30 203.57
WHOLESALE AND RETAIL TRADE; REPAIR OF MOTOR VEHICLES AND MOTORCYCLES 6971.22 14153.02 103512.40 338.67 6776.07 
difference%>%
    kbl()%>%
    kable_styling(bootstrap_options = c("striped", "hover"))
Sector Number of Employees_2015-12-31 Number of Employees_2021-12-31 Sales_2015-12-31 Sales_2021-12-31 Profit Margin_2015-12-31 Profit Margin_2021-12-31 emp_change sales_change profit_change
MANUFACTURING 2334.08163 2445.61905 1715.5819 7307.73299 -22.1083567 12.635696 4.778642 325.96235 -157.15349
TRANSPORTATION AND STORAGE 8606.12500 7544.50000 5292.4500 13528.52310 10.9452714 82.132790 -12.335691 155.61929 650.39519
CONSTRUCTION 4910.11111 3178.60000 1633.8650 3407.79404 -0.5479364 51.594880 -35.264194 108.57256 -9516.21754
FINANCIAL AND INSURANCE ACTIVITIES 3308.76190 3916.77049 5679.7234 17741.53824 -165.0489377 37.848705 18.375713 212.36624 -122.93181
WHOLESALE AND RETAIL TRADE; REPAIR OF MOTOR VEHICLES AND MOTORCYCLES 4315.16000 10265.37500 2943.5719 14215.83481 4.4560370 2258.544483 137.890947 382.94505 50585.04738
INFORMATION AND COMMUNICATION 5391.50000 3159.15789 2585.6388 4196.05575 10.3985909 27.263210 -41.404843 62.28314 162.18178
ELECTRICITY, GAS, STEAM AND AIR CONDITIONING SUPPLY 343.00000 1397.15385 1656.6895 5502.32319 -532.5148444 1.134815 307.333483 232.12761 -100.21310
MINING AND QUARRYING 470.00000 1731.83333 611.2890 2478.44912 7.0582600 33.787950 268.475177 305.44638 378.70084
PROFESSIONAL, SCIENTIFIC AND TECHNICAL ACTIVITIES 299.50000 116.00000 189.7816 424.92770 2.9051500 40.632667 -61.268781 123.90359 1298.64264
REAL ESTATE ACTIVITIES 36.08696 95.07143 134.5997 618.05117 171.9876074 415.982448 163.450947 359.17726 141.86769
ACCOMMODATION AND FOOD SERVICE ACTIVITIES 1743.87500 2041.71429 386.6231 1416.87349 5.4703333 159.891514 17.079165 266.47410 2822.88430
HUMAN HEALTH AND SOCIAL WORK ACTIVITIES 9224.00000 5065.66667 992.4818 2139.72520 0.1236000 10.256400 -45.081671 115.59339 8198.05825
ARTS, ENTERTAINMENT AND RECREATION 328.00000 368.80000 316.4248 835.58218 -31.5968000 -81.926700 12.439024 164.06978 159.28797
WATER SUPPLY; SEWERAGE, WASTE MANAGEMENT AND REMEDIATION ACTIVITIES NaN 428.00000 NaN 427.70380 NaN 0.885300 NaN NaN NaN
ADMINISTRATIVE AND SUPPORT SERVICE ACTIVITIES 2152.16667 41.75000 301.2063 74.46788 16.2465667 255.979000 -98.060095 -75.27678 1475.58828
AGRICULTURE, FORESTRY AND FISHING 50.00000 111.50000 13.3176 171.18120 13.7284000 17.830750 123.000000 1185.37574 29.88221 
summary(dataset[,4:11])%>%
    kbl()%>%
    kable_styling(bootstrap_options = c("striped", "hover"))
Number of Employees EBITDA Profit Margin Sales Market Cap Personnel Expense (Millions) Personnel Expense Per Employee Price/Earnings
Min. : 1.0 Min. : -426.89 Min. :-181191.9 Min. : -10.8 Min. : 3.3 Min. : 0.000 Min. : 0 Min. : 0.467
1st Qu.: 66.5 1st Qu.: 7.48 1st Qu.: 0.5 1st Qu.: 71.9 1st Qu.: 124.9 1st Qu.: 4.228 1st Qu.: 45166 1st Qu.: 6.855
Median : 319.0 Median : 43.81 Median : 6.4 Median : 321.9 Median : 456.4 Median : 22.405 Median : 83816 Median : 11.942
Mean : 2735.7 Mean : 486.41 Mean : 159.0 Mean : 4278.5 Mean : 3011.6 Mean : 306.784 Mean : 182747 Mean : 48.585
3rd Qu.: 1179.0 3rd Qu.: 195.03 3rd Qu.: 18.8 3rd Qu.: 1445.6 3rd Qu.: 1862.2 3rd Qu.: 107.062 3rd Qu.: 145286 3rd Qu.: 28.651
Max. :105908.0 Max. :43946.57 Max. : 545549.8 Max. :346688.7 Max. :98560.0 Max. :17379.680 Max. :18201608 Max. :11194.969
NA’s :290 NA’s :308 NA’s :190 NA’s :172 NA’s :426 NA’s :261 NA’s :368 NA’s :669 
ratio_table%>%
    kbl()%>%
    kable_styling(bootstrap_options = c("striped", "hover"))
Sector number ratio
ACCOMMODATION AND FOOD SERVICE ACTIVITIES 9 2.2058824
ADMINISTRATIVE AND SUPPORT SERVICE ACTIVITIES 8 1.9607843
AGRICULTURE, FORESTRY AND FISHING 2 0.4901961
ARTS, ENTERTAINMENT AND RECREATION 5 1.2254902
CONSTRUCTION 15 3.6764706
ELECTRICITY, GAS, STEAM AND AIR CONDITIONING SUPPLY 14 3.4313725
FINANCIAL AND INSURANCE ACTIVITIES 64 15.6862745
HUMAN HEALTH AND SOCIAL WORK ACTIVITIES 3 0.7352941
INFORMATION AND COMMUNICATION 20 4.9019608
MANUFACTURING 186 45.5882353
MINING AND QUARRYING 6 1.4705882
PROFESSIONAL, SCIENTIFIC AND TECHNICAL ACTIVITIES 4 0.9803922
REAL ESTATE ACTIVITIES 32 7.8431373
TRANSPORTATION AND STORAGE 10 2.4509804
WATER SUPPLY; SEWERAGE, WASTE MANAGEMENT AND REMEDIATION ACTIVITIES 1 0.2450980
WHOLESALE AND RETAIL TRADE; REPAIR OF MOTOR VEHICLES AND MOTORCYCLES 29 7.1078431

Comparasion of personnel expenses and employment

Codes for comparing throughout the years. We use ggplot package for its customization. You can use “esquisse” package for easier approach too. Be aware of which summary table that we used.

ylim.emp <- c(1000, 4000)  
ylim.expens <- c(100000,300000)   

secaxexps <- diff(ylim.emp)/diff(ylim.expens)
secax_exps <- ylim.emp[1] - secaxexps*ylim.expens[1]

ylim.prof <- c(-600,1800)
secaxprof <- diff(ylim.emp)/diff(ylim.prof)
secax_prof <- ylim.emp[1] - secaxprof*ylim.prof[1]


exp_vs_emp <- ggplot(yearly_summary, aes(x = Dates, group = 1))+
    geom_line(aes(y = avg_emp))+
    geom_line(aes(y = secax_exps+avg_exp_per*secaxexps), color = "red")+
    geom_point(aes(y = avg_emp),color = "navy")+
    geom_point(aes(y = secax_exps+avg_exp_per*secaxexps), color = "navy")+
    scale_y_continuous("Average Employee", sec.axis = sec_axis(~ (. - secax_exps)/secaxexps, name = "Average Expense per Employee"))+
    theme(axis.line.y.right = element_line(color = "red"), 
        axis.ticks.y.right = element_line(color = "red"),
        axis.text.y.right = element_text(color = "red"))+
    labs(x = "Years")


profit_emp <- ggplot(yearly_summary, aes(x = Dates, group = 1))+
    geom_line(aes(y = avg_emp))+
    geom_line(aes(y = secax_prof+avg_profit*secaxprof), color = "red")+
    geom_point(aes(y = avg_emp),color = "navy")+
    geom_point(aes(y = secax_prof+avg_profit*secaxprof), color = "navy")+
    scale_y_continuous("Average Employee", sec.axis = sec_axis(~ (. - secax_prof)/secaxprof, name = "Average Profit"))+
    theme(axis.line.y.right = element_line(color = "red"), 
        axis.ticks.y.right = element_line(color = "red"),
        axis.text.y.right = element_text(color = "red"))+
    labs(x = "Years",y="Average Profit Margin",title = "Change in Average Profit Margins of BIST TUM Firms",caption = "Source: Bloomberg")

emp_box <- ggplot(dataset, aes(x = Sector))+
    geom_boxplot(aes(y = `Number of Employees`, color = Dates),show.legend = T, outlier.alpha = 0.2)+
    lims(y = c(0,30000))+
    scale_x_discrete(labels = abbreviate)+
    theme(axis.text.x = element_text(size= 8,angle = 45,hjust = 1, vjust = 1))

exp_emp_index <- ggplot(yearly_summary, aes(x = Dates, group = 1))+
    geom_line(aes(y = emp_index, color = "Employee Index"))+
    geom_line(aes(y=exp_index, color = "Average Personnel Expense Index"))+
    geom_point(aes(y = emp_index))+
    geom_point(aes(y = exp_index))+
    theme(legend.position = "bottom")+
    scale_color_manual(name = "Index",
                       values = c("red","black"),
                       labels = c("Average Personnel Expense Index","Employee Index"))+
    labs(x = "Years",
         y = "Index",
         title = "Index comparasion of Employee Number and Average Personnel Expense",caption = "Source: Bloomberg")

sales_change <- ggplot(yearly_summary, aes(x = Dates, group = 1))+
    geom_line(aes(y = avg_sales))+
    geom_point(aes(y = avg_sales))+
    labs(x = "Years",y="Average Sales (thousand)",title = "Change in Average Sales of BIST TUM Firms",caption = "Source: Bloomberg")

Plots of variables

profit_emp

exp_emp_index

sales_change

Comparasion of Yearly Average Number of Employee by Sector

First we divide sectors by 2 to make our plots better looking, then we write use ggplot options for better visualization.

sectors1 <- c("ACCOMMODATION AND FOOD SERVICE ACTIVITIES","ADMINISTRATIVE AND SUPPORT SERVICE ACTIVITIES","AGRICULTURE, FORESTRY AND FISHING","ARTS, ENTERTAINMENT AND RECREATION","CONSTRUCTION","ELECTRICITY, GAS, STEAM AND AIR CONDITIONING SUPPLY","FINANCIAL AND INSURANCE ACTIVITIES","HUMAN HEALTH AND SOCIAL WORK ACTIVITIES")

sectors2 <- c("INFORMATION AND COMMUNICATION","MANUFACTURING","MINING AND QUARRYING","PROFESSIONAL, SCIENTIFIC AND TECHNICAL ACTIVITIES","REAL ESTATE ACTIVITIES","TRANSPORTATION AND STORAGE","WATER SUPPLY; SEWERAGE, WASTE MANAGEMENT AND REMEDIATION ACTIVITIES","WHOLESALE AND RETAIL TRADE; REPAIR OF MOTOR VEHICLES AND MOTORCYCLES")

sec_emp1 <- ggplot(all_summary%>%
           filter(avg_market_cap<=1698.35))+
    geom_point(aes(x= Dates,
                   y = avg_emp,
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = avg_emp,
                   color = Sector, group = Sector),show.legend = T)+
    theme(axis.text.x = element_text(angle = 15),
    legend.text = element_text(size = 8,vjust = 1, hjust = 0.5), 
    legend.position = "right",
    legend.key.width = unit(0.1, "cm"),
    legend.key.size = unit(0.1, "cm"),
    legend.spacing = unit(0.01, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(25),
                               guide = guide_legend(nrow = 16))+
    lims(y= c(-1000,13000))+
    ylab("Average Number of Employee")+
    labs(title = "Change of yearly average Number of Employees",
         subtitle = "Average Market Cap of Sector lower than the median 1.6 billion liras")

sec_emp2 <- ggplot(all_summary%>%
           filter(1698.35<avg_market_cap))+
    geom_point(aes(x= Dates,
                   y = avg_emp,
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = avg_emp,
                   color = Sector, group = Sector),show.legend = T)+
    theme(axis.text.x = element_text(angle = 15),
    legend.text = element_text(size = 8,vjust = 1, hjust = 0.5), 
    legend.position = "right",
    legend.key.width = unit(0.1, "cm"),
    legend.key.size = unit(0.1, "cm"),
    legend.spacing = unit(0.01, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(25),
                               guide = guide_legend(nrow = 16))+
    lims(y= c(-1000,13000))+
    ylab("Average Number of Employee")+
    labs(title = "Change of yearly average Number of Employees",
         subtitle = "Average Market Cap of Sector higher than the median 1.6 billion liras")

sales_avg_s1 <- ggplot(all_summary %>% 
            filter(Sector %in% sectors1))+
    geom_point(aes(x= Dates,
                   y = log(avg_sales),
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = log(avg_sales),
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(25),
                               guide = guide_legend(nrow = 8))+
     labs(x = "Years",y = "Log of Average Sales",title = "Change of Average Sales by Sectors over Years",subtitle = "Alphabetical First 8 Sectors",caption = "Source: Bloomberg")
 
sales_avg_s2 <- ggplot(all_summary %>% 
            filter(Sector %in% sectors2))+
    geom_point(aes(x= Dates,
                   y = log(avg_sales),
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = log(avg_sales),
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(25),
                               guide = guide_legend(nrow = 8))+
     labs(x = "Years",y = "Log of Average Sales",title = "Change of Average Sales by Sectors over Years",subtitle = "Alphabetical Latter 8 Sectors",caption = "Source: Bloomberg")

prof_date_s1 <- ggplot(all_summary %>% 
           filter(!Sector %in% "ELECTRICITY, GAS, STEAM AND AIR CONDITIONING SUPPLY" & Sector %in% sectors1))+
    geom_point(aes(x= Dates,
                   y = (avg_profit),
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = (avg_profit),
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15),
    legend.text = element_text(size = 8,vjust = 0.5, hjust = 0))+
        scale_colour_viridis_d(option = "turbo",
                               labels = scales::label_wrap(12),
                               guide = guide_legend(nrow = 8))+
    labs(title = "Change of Average Profit Margins by Sectors over Years",subtitle = "Alphabetical First 8 Sectors & Outlier Electricity,Gas,Steam and AC Supply Excluded",caption = "Source: Bloomberg",x = "Years",y="Average Profit Margin")+
    lims(y = c(-100,200))

prof_date_s2 <- ggplot(all_summary %>% 
           filter( Sector %in% sectors2))+
    geom_point(aes(x= Dates,
                   y = (avg_profit),
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = (avg_profit),
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15),
    legend.text = element_text(size = 8,vjust = 0.5, hjust = 0))+
        scale_colour_viridis_d(option = "turbo",
                               labels = scales::label_wrap(20),
                               guide = guide_legend(nrow = 8))+
    lims(y=c(-50,200))+
    labs(title = "Change of Average Profit Margins by Sectors over Years",subtitle = "Alphabetical Latter 8 Sectors",caption = "Source: Bloomberg",x = "Years",y="Average Profit Margin")

sec_expense_out <- ggplot(all_summary %>% 
           filter(Sector %in% c("FINANCIAL AND INSURANCE ACTIVITIES","ARTS, ENTERTAINMENT AND RECREATION")))+
    geom_point(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15))+
    scale_colour_viridis_d(option = "turbo",
                               labels = scales::label_wrap(5),
                               guide = guide_legend(nrow = 2))+
    labs(x = "Years",y="Average Personnel Expense",title = "Average Personnel Expense by Sectors over Years",subtitle = "Outlier Sectors",caption = "Source: Bloomberg")

sec_expense_s1 <- ggplot(all_summary %>% 
           filter(!Sector %in% c("FINANCIAL AND INSURANCE ACTIVITIES","ARTS, ENTERTAINMENT AND RECREATION") & Sector %in% sectors1))+
    geom_point(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector, group = Sector),show.legend = T)+
     theme(axis.text.x = element_text(angle = 15),
           legend.text = element_text(size = 8,vjust = 0.5, hjust = 0), 
    legend.position = "right",
    legend.key.width = unit(0.3, "cm"),
    legend.key.size = unit(0.8, "cm"),
    legend.spacing = unit(2, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               labels = scales::label_wrap(12),
                               guide = guide_legend(nrow = 8))+
    labs(x = "Years",y="Average Personnel Expense",title = "Average Personnel Expense by Sectors over Years",subtitle = "Alphabetical First 8 Sector excluding Outliers",caption = "Source: Bloomberg")

sec_expense_s2 <- ggplot(all_summary %>% 
           filter(!Sector %in% c("FINANCIAL AND INSURANCE ACTIVITIES","ARTS, ENTERTAINMENT AND RECREATION") & Sector %in% sectors2))+
    geom_point(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector),show.legend = T)+
    geom_line(aes(x= Dates,
                   y = avg_exp_per,
                   color = Sector, group = Sector),show.legend = T)+
    theme(axis.text.x = element_text(angle = 15),
    legend.text = element_text(size = 8,vjust = 0.5, hjust = 0), 
    legend.position = "right",
    legend.key.width = unit(0.3, "cm"),
    legend.key.size = unit(0.8, "cm"),
    legend.spacing = unit(2, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               labels = scales::label_wrap(15),
                               guide = guide_legend(nrow = 8))+
    labs(x = "Years",y="Average Personnel Expense",title = "Average Personnel Expense by Sectors over Years",subtitle = "Alphabetical Latter 8 Sector excluding Outliers",caption = "Source: Bloomberg")
sec_emp1

sec_emp2

sales_avg_s1

sales_avg_s2

prof_date_s1

prof_date_s2

sec_expense_out

sec_expense_s1

sec_expense_s2

Function for scatter plots of sales and profit

Here I made a function for custom employee number interval plot. You can choose year and a custom interval for broader look at the data.

profit_emp_point <- function(date,emp_int){
    ggplot(dataset%>%
        filter(Dates %in% date),
        aes(x = `Number of Employees`,
            y = `Profit Margin`))+
        geom_point(aes(color = Sector))+
    theme(
    legend.text = element_text(size = 6,vjust = 0.5, hjust = 0.5, margin = margin(r = 11)), 
    legend.position = "right",
    legend.key.width = unit(0.1, "cm"),
    legend.key.size = unit(0.1, "cm"),
    legend.spacing = unit(0.01, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(20),
                               guide = guide_legend(nrow = 16))+
        lims(x = emp_int,
             y = c(-80,300))+
        labs(title = "Profit - No. of Employee",
             subtitle = date)
}

sales_emp_point <- function(date,emp_int){
    ggplot(dataset%>%
        filter(Dates %in% date),
        aes(x = `Number of Employees`,
            y = log(Sales)))+
        geom_point(aes(color = Sector))+
    theme(
    legend.text = element_text(size = 6,vjust = 0.5, hjust = 0.5, margin = margin(r = 11)), 
    legend.position = "right",
    legend.key.width = unit(0.1, "cm"),
    legend.key.size = unit(0.1, "cm"),
    legend.spacing = unit(0.01, "cm"))+
        scale_colour_viridis_d(option = "turbo",
                               end = 1,
                               labels = scales::label_wrap(20),
                               guide = guide_legend(nrow = 16))+
        lims(x = emp_int)+
        labs(title = "Sales - No. of Employee",
             subtitle = date)+
        ylab("log of Sales (thousand)")
}

Custom Interval Plots

profit_emp_point("2021-12-31",c(2500,10000))+
    geom_smooth(aes(x =`Number of Employees`,
                    y = `Profit Margin`),
                se =T, color = "navy")
## `geom_smooth()` using method = 'loess' and formula = 'y ~ x'

profit_emp_point("2020-12-31",c(10,1000))+
    geom_smooth(aes(x =`Number of Employees`,
                    y = `Profit Margin`),
                se =T, color = "navy")
## `geom_smooth()` using method = 'loess' and formula = 'y ~ x'

profit_emp_point("2020-12-31",c(10,100))+
    geom_smooth(aes(x =`Number of Employees`,
                    y = `Profit Margin`),
                se =T, color = "navy")
## `geom_smooth()` using method = 'loess' and formula = 'y ~ x'

sales_emp_point("2019-12-31",c(100,2000))+
    geom_smooth(aes(x =`Number of Employees`,
                    y = log(Sales)),
                    color = "#526788",se =T)
## `geom_smooth()` using method = 'loess' and formula = 'y ~ x'