Profit Analysis of Public Companies Listed at Indonesia Stock Exchange in 2021 - EDA
Dwi Puji Laksono Gumilang
2022-08
1.Introduction
1.1. Objective
The objective of this analysis is to describe profit of companies listed at Indonesia Stock Exchange in 2021
1.2. Scope of Analysis
The scope of the analysis is limited only to profit based on its sectors and companies.
1.3. Soure of Data
Source of data used in this analysis is from Indonesia Stock Exchange Market Data that can be accessed on https://www.idx.co.id/en-us/market-data/statistical-reports/digital-statistic-beta/
2. Data Pre-Prosessing and Library & Setup
2.1. Library & Setup
Here is the data used in this analysis
library(knitr)
library(rmarkdown)
library(dplyr)
library(ggplot2)
library(readxl)
library(lubridate)
library(readr)
library(scales)
library(tidyverse)
library(forcats)
library(viridis)2.2. Data Inputted
Here is the data used in this analysis
FSR <-read_xlsx("FSR_IDX.xlsx")
glimpse(FSR)## Rows: 1,472
## Columns: 25
## $ No <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14~
## $ Sector <chr> "Energy", "Energy", "Energy", "Energy", "Ener~
## $ `Sub Industry Code` <chr> "A121", "A121", "A121", "A112", "A131", "A121~
## $ Sub_Industry <chr> "Coal Production", "Coal Production", "Coal P~
## $ Stock_Code <chr> "ADMR", "ADRO", "AIMS", "AKRA", "APEX", "ARII~
## $ Company_Name <chr> "Adaro Minerals Indonesia Tbk", "Adaro Energy~
## $ Sharia <chr> "S", "S", "S", "S", "S", "S", "S", "S", "S", ~
## $ `FS Date` <dttm> 2021-12-31, 2021-12-31, 2021-09-30, 2021-12-~
## $ Period <dbl> 2021, 2021, 2021, 2021, 2021, 2021, 2021, 202~
## $ `Auditor's Opinion` <chr> "WTP", "WTP", NA, "WTP", NA, NA, NA, NA, NA, ~
## $ `Assets_(B.IDR)` <dbl> 13779.60933, 108257.98978, 26.73491, 23508.58~
## $ `Liabilities_(B.IDR)` <dbl> 10848.1287, 44642.2930, 13.2587, 12209.6206, ~
## $ `Equity_(B.IDR)` <dbl> 2931.48062, 63615.69673, 13.47621, 11298.9651~
## $ `Sales_(B.IDR)` <dbl> 6566.176403, 56972.093142, 27.630556, 25707.0~
## $ `EBT_(B.IDR)` <dbl> 2.881152e+03, 2.120732e+04, 7.006294e-01, 1.4~
## $ `Profit_(B.IDR)` <dbl> 2.236119e+03, 1.467699e+04, 7.006294e-01, 1.1~
## $ `Profit attr,to owner's` <dbl> 2.213275e+03, 1.332000e+04, 7.006294e-01, 1.1~
## $ `EPS_(IDR)` <dbl> 54.14, 416.43, 2.11, 55.38, 315.44, 8.63, 127~
## $ `Book_Value_(IDR)` <dbl> 71.71, 1988.86, 61.26, 562.88, 664.76, 177.75~
## $ `P/E_Ratio` <dbl> 42.30, 6.46, 65.94, 16.43, 28.10, 34.61, 5.74~
## $ Price_to_BV <dbl> 31.94, 1.35, 4.57, 1.62, 0.86, 1.40, 1.89, 3.~
## $ `D/E_Ratio` <dbl> 3.70, 0.70, 0.98, 1.08, 1.69, 8.94, 4.87, 3.4~
## $ `ROA_(%)` <dbl> 0.16, 0.12, 0.02, 0.05, 0.18, 0.00, 1.24, 0.0~
## $ `ROE_(%)` <dbl> 0.76, 0.21, 0.03, 0.10, 0.47, 0.05, 0.00, 0.2~
## $ `NPM_(%)` <dbl> 0.34, 0.23, 0.02, 0.04, 1.48, 0.03, 318.06, 0~Column description:
- No = number of row
- Sector = category of sector where the company run the business
- Sub Industry Code = code of the category of sub industry where the company run the business
- Stock_Code = code of the company listed in the Indonesia Stock Exchange
- Company Name = the name of company listed in the Indonesia Stock Exchange
- Sharia = the category of company type whether it is sharia or not
- FS Date = the date of financial statement period calculated
- Period = year of the financial statement period
- Auditor’s Opinion = the given opinion of public accountant firm to the listed company resulted from auditing process
- Assets_(B.IDR) = assets the company has during the period
- Liabilities_(B.IDR)= liabilities of the company has during the period
- Equity_(B.IDR) = equity of the company has during the period
- Sales_(B.IDR) = sales of the company has during the period
- EBT_(B.IDR) = earning before tax of the company has during the period
- Profit_(B.IDR) = profit of the company has during the period
- Profit attr,to owner’s = profit of the owner the company has during the period
- EPS_(IDR) = earning per share of the company has during the period
- Book_Value_(IDR) = book value of the company has during the period
- P/E_Ratio = price to earning ratio of the company has during the period
- Price_to_BV = price to book value of the company has during the period
- D/E_Ratio = debt to equity ratio of the company has during the period
- ROA_(%) = return of assets of the company has during the period
- ROE_(%) = return of the equity of the company has during the period
- NPM_(%) net profit margin of the company has during the period
3. EDA
3.1. Data Wrangling
- Removing unecessary columns: No, Sub Industry Code, Sharia, Auditor’s Opinion
- Filtering data: the period that will be used as the filter in this analysis is only data that recorded FS Date = 31-12-2021 and the no missing value data (financial data recorded on 31-12-2021)
- Data type change:
- Sector: chr -> factor
- Sub Industry: chr -> factor
- Stock_Code `: chr -> factor
FSR_AN <- FSR %>%
select(-c(No, "Sub Industry Code", Sharia, "Auditor's Opinion"))%>%
mutate_at(vars(Sector, Sub_Industry, Stock_Code, Period ), as.factor) %>%
filter(complete.cases(.),`FS Date` == as.Date("2021-12-31"))
glimpse(FSR_AN)## Rows: 261
## Columns: 21
## $ Sector <fct> Energy, Energy, Energy, Energy, Energy, Energ~
## $ Sub_Industry <fct> "Coal Production", "Coal Production", "Oil & ~
## $ Stock_Code <fct> ADMR, ADRO, AKRA, BSSR, BYAN, CANI, DSSA, DWG~
## $ Company_Name <chr> "Adaro Minerals Indonesia Tbk", "Adaro Energy~
## $ `FS Date` <dttm> 2021-12-31, 2021-12-31, 2021-12-31, 2021-12-~
## $ Period <fct> 2021, 2021, 2021, 2021, 2021, 2021, 2021, 202~
## $ `Assets_(B.IDR)` <dbl> 13779.6093, 108257.9898, 23508.5857, 6211.543~
## $ `Liabilities_(B.IDR)` <dbl> 10848.12871, 44642.29305, 12209.62062, 2607.0~
## $ `Equity_(B.IDR)` <dbl> 2931.4806, 63615.6967, 11298.9651, 3604.5305,~
## $ `Sales_(B.IDR)` <dbl> 6566.17640, 56972.09314, 25707.06890, 9865.19~
## $ `EBT_(B.IDR)` <dbl> 2881.151732, 21207.315519, 1436.743040, 3769.~
## $ `Profit_(B.IDR)` <dbl> 2.236119e+03, 1.467699e+04, 1.135002e+03, 2.9~
## $ `Profit attr,to owner's` <dbl> 2.213275e+03, 1.332000e+04, 1.111614e+03, 2.9~
## $ `EPS_(IDR)` <dbl> 54.14, 416.43, 55.38, 1118.85, 3700.69, 33.91~
## $ `Book_Value_(IDR)` <dbl> 71.71, 1988.86, 562.88, 1377.62, 7974.54, 442~
## $ `P/E_Ratio` <dbl> 42.30, 6.46, 16.43, 3.35, 11.64, 2.74, 19.79,~
## $ Price_to_BV <dbl> 31.94, 1.35, 1.62, 2.72, 5.44, 0.31, 1.36, 12~
## $ `D/E_Ratio` <dbl> 3.70, 0.70, 1.08, 0.72, 0.31, 1.88, 0.72, 8.1~
## $ `ROA_(%)` <dbl> 0.16, 0.12, 0.05, 0.47, 0.36, 0.09, 0.04, 0.0~
## $ `ROE_(%)` <dbl> 0.76, 0.21, 0.10, 0.81, 0.46, 0.00, 0.07, 0.1~
## $ `NPM_(%)` <dbl> 0.34, 0.23, 0.04, 0.30, 0.30, 1.49, 0.06, 0.0~3.2. Missing Value Check
colSums(is.na(FSR_AN))## Sector Sub_Industry Stock_Code
## 0 0 0
## Company_Name FS Date Period
## 0 0 0
## Assets_(B.IDR) Liabilities_(B.IDR) Equity_(B.IDR)
## 0 0 0
## Sales_(B.IDR) EBT_(B.IDR) Profit_(B.IDR)
## 0 0 0
## Profit attr,to owner's EPS_(IDR) Book_Value_(IDR)
## 0 0 0
## P/E_Ratio Price_to_BV D/E_Ratio
## 0 0 0
## ROA_(%) ROE_(%) NPM_(%)
## 0 0 0There is no missing values
3.3. Summary of Data
summary(FSR_AN)## Sector Sub_Industry
## Financials :53 Banks : 34
## Consumer Non0Cyclicals:44 Real Estate Development & Management : 15
## Basic Materials :35 Plantations & Crops : 13
## Consumer Cyclicals :27 Coal Production : 11
## Infrastructures :26 Heavy Constructions & Civil Engineering: 10
## Energy :21 Consumer Financing : 9
## (Other) :55 (Other) :169
## Stock_Code Company_Name FS Date Period
## AALI : 1 Length:261 Min. :2021-12-31 2019: 0
## ABDA : 1 Class :character 1st Qu.:2021-12-31 2020: 0
## ACES : 1 Mode :character Median :2021-12-31 2021:261
## ACST : 1 Mean :2021-12-31
## ADCP : 1 3rd Qu.:2021-12-31
## ADHI : 1 Max. :2021-12-31
## (Other):255
## Assets_(B.IDR) Liabilities_(B.IDR) Equity_(B.IDR) Sales_(B.IDR)
## Min. : 0.1 Min. : 1.0 Min. : 25.78 Min. : 0.0
## 1st Qu.: 1396.5 1st Qu.: 524.1 1st Qu.: 854.11 1st Qu.: 619.9
## Median : 5603.8 Median : 2557.8 Median : 2465.83 Median : 2652.3
## Mean : 45734.2 Mean : 32457.2 Mean : 11811.85 Mean : 11070.3
## 3rd Qu.: 17760.2 3rd Qu.: 8382.6 3rd Qu.: 8249.46 3rd Qu.: 9865.2
## Max. :1725611.1 Max. :1386310.9 Max. :291786.80 Max. :233485.0
##
## EBT_(B.IDR) Profit_(B.IDR) Profit attr,to owner's
## Min. : 0.39 Min. : 0.088 Min. : 0.088
## 1st Qu.: 84.45 1st Qu.: 76.508 1st Qu.: 71.054
## Median : 314.92 Median : 265.176 Median : 265.176
## Mean : 2006.75 Mean : 1591.086 Mean : 1498.959
## 3rd Qu.: 1483.99 3rd Qu.: 1211.053 3rd Qu.: 1186.599
## Max. :38841.17 Max. :31440.159 Max. :31422.660
##
## EPS_(IDR) Book_Value_(IDR) P/E_Ratio Price_to_BV
## Min. : 0.06 Min. : 14.76 Min. : 0.19 Min. : 0.010
## 1st Qu.: 14.59 1st Qu.: 201.49 1st Qu.: 8.87 1st Qu.: 0.800
## Median : 61.36 Median : 509.68 Median : 15.97 Median : 1.460
## Mean : 232.88 Mean : 1635.18 Mean : 69.46 Mean : 3.691
## 3rd Qu.: 174.92 3rd Qu.: 1663.38 3rd Qu.: 31.49 3rd Qu.: 3.190
## Max. :6005.63 Max. :32410.18 Max. :2359.74 Max. :96.290
##
## D/E_Ratio ROA_(%) ROE_(%) NPM_(%)
## Min. : 0.000 Min. : 0.000 Min. :0.0000 Min. : 0.0000
## 1st Qu.: 0.420 1st Qu.: 0.020 1st Qu.:0.0500 1st Qu.: 0.0500
## Median : 0.840 Median : 0.060 Median :0.1000 Median : 0.1000
## Mean : 2.178 Mean : 2.194 Mean :0.1875 Mean : 0.2848
## 3rd Qu.: 1.850 3rd Qu.: 0.100 3rd Qu.:0.1700 3rd Qu.: 0.2300
## Max. :149.870 Max. :537.530 Max. :8.6800 Max. :14.9900
## Summary:
- Assets: the highest is 1.725.611,1 B.IDR with median of assets is 2.557 B.IDR
- Liabilities: the highest is 1.386.310,9 B.IDR with the lowest is 1 B.IDR
- Sales: the highest is 233.485 B.IDR with average sales is 11.070,3
- EBT: the highest is 38.841,17 with median is 314,92 B.IDR
- Profit: the highest is 31.440,159 B.IDR with the lowest is 0,088 B.IDR
- EPS: the highest is 6.005,63 IDR and the lowest is 0.06 IDR
- In term of financial statement ratio:
- NPM: the highest is 14.99%
- ROA: median of ROA is 0.060%
- ROE: the highest of ROE is 8.68%
- D/E: the higest of D/E is 149,87
Due to the objective of this analysis is only to get understanding the perfomance of which sector generate the most profit in Indonesia Stock Exchange in 2021, therefore, we only focus on “Profit”.
3.4. Data Distribution Check:
Here is we use boxplot tool to explore the outlier phenomenon in the used data.
#Profit data distribution
FSR_AN %>%
ggplot(aes(x = Sector, y = `Profit_(B.IDR)`, fill= Sector)) +
geom_boxplot()+
labs(title = "Profit Data Distribution based on Sector", x = "Sector", y = "B.IDR")+
scale_fill_viridis(discrete = TRUE, alpha=0.6) +
geom_jitter(color="black", size=0.4, alpha=0.9) +
scale_y_continuous(limits = c(0,35000),labels = comma)+
coord_flip()
Outlier Check Result:
According the profit data distribution above, there are outliers data in these data, therefore, here are the conclusion of the outlier check:
- Due to the outlier occurance, using mean to analyse this data is irrelevant, it is better to use median instead if it is needed to make a generalization.
- Even though the data has outliers, the data will still be used without removing the outliers due to the objective of this analysis is to describe the profitability so that it is important to understand which sector or company that has good profitability performance that it might be categorized as an outlier datum.
Therefore, we can continue the analysis.
4. Data Analysis Explanation
4.1. Question & Answer 1:
Question1:
How many companies listed at Indonesia Stock Exchange in 2021 based on their sector?
#Total of companies pass the cdata leansing process
sum(xtabs(~ Sector, data = FSR_AN))## [1] 261#Based on number of companies
qcmp.sec <- xtabs(~ Sector, data = FSR_AN) %>%
as.data.frame()
ggplot(data = qcmp.sec, mapping = aes(x = reorder(Sector , Freq), y = Freq, fill = Freq))+
geom_col()+
geom_text(aes(label = paste0(Freq), hjust = -0.1))+
labs(title = "Number of Company based on Sector", x = "Sector", y = "Number of Companies")+
scale_fill_viridis(discrete = F, alpha=0.6)+
theme_minimal()+
coord_flip()
#Based on percentage of companies (portion)
qcmp.sec$'Total_Company_by_Sector(%)' <- round(qcmp.sec$Freq / sum(qcmp.sec$Freq)*100,2) #to add new column in dataframe, to determine how many portion (%) company running in each sector
ggplot(data = qcmp.sec, mapping = aes(x = reorder(Sector , `Total_Company_by_Sector(%)`), y = `Total_Company_by_Sector(%)`, fill = `Total_Company_by_Sector(%)`))+
geom_col()+
geom_text(aes(label = paste0(qcmp.sec$`Total_Company_by_Sector(%)`,"%"), hjust = -0.1))+
labs(title = "Percentage of Company based on Sector", x = "Sector", y = "Percentage of Companies", fill = "Percentage")+
scale_y_continuous(limits = c(0,25))+
scale_fill_viridis(discrete = F, alpha=0.6)+
theme_minimal()+
coord_flip()
Answer 1:
According to the data above, the most companies are in the financial sector (53 companies or 20,31%), consumer non-cyclicals sector (44 companies or 16,86%), and basic material sector (36 companies or 13,41%). If these 3 sectors’ accumulated is equal to 50.58%. Meanwhile, properties & real estate, technology and transportation-logistic sectors are the least.
4.2. Question & Answer 2:
Question 2:
What is the most contributing sector in term of profit in 2021?
#Based on amount of profit
pft.sec <- aggregate(`Profit_(B.IDR)`~ Sector, FSR_AN, sum)
ggplot(data = pft.sec, mapping = aes(x = reorder(Sector , `Profit_(B.IDR)`), y = `Profit_(B.IDR)`, fill = `Profit_(B.IDR)`))+
geom_col()+
geom_text(aes(label = paste0(round(`Profit_(B.IDR)`,2))), hjust = -0.1)+
scale_y_continuous(limits = c(0,200000),labels = comma)+
scale_fill_continuous(labels = comma)+
labs(title = "Accumulated Amount of Profit based on Sector", x = "Sector", y = "Profit (B.IDR)", fill = "B.IDR")+
scale_fill_viridis(discrete = F, alpha=0.6, labels = comma)+
theme_minimal()+
coord_flip()
#Based on percentage of profit contribution
pft.sec$'Total_Profit_by_Sector(%)' <- round(pft.sec$`Profit_(B.IDR)` / sum(pft.sec$`Profit_(B.IDR)`)*100,2) #to add new column in dataframe, to determine how many portion (%) profit contribution of each sector
ggplot(data = pft.sec, mapping = aes(x = reorder(Sector , `Total_Profit_by_Sector(%)`), y = `Total_Profit_by_Sector(%)`, fill = `Total_Profit_by_Sector(%)`))+
geom_col()+
geom_text(aes(label = paste0(pft.sec$`Total_Profit_by_Sector(%)`,"%"), hjust = -0.1))+
labs(title = "Percentage of Accumulated Profit based on Sector", x = "Sector", y = "Percentage of Profit", fill = "Percentage")+
scale_y_continuous(limits = c(0,50))+
scale_fill_viridis(discrete = F, alpha=0.6)+
theme_minimal()+
coord_flip()
Answer 2:
According to the data above, the most profit contributing sectors in all profit accumulation from all sectors in Indonesia Stock Exchange in 2021 are financials (170,235.52 B.IDR or 40.99%), energy (72,503.43 B.IDR or 17.46%), and Consumer Non-Cyclicals (64,983.7 B.IDR or 15.65%). Besides, if the profit from these 3 sectors is accumulated equal to 74.1% from all total profit.
Meanwhile, properties & real estate (5,437.2 B.IDR or 1.31%), transportation-logistic (2,164.29 or 0.52%), and technology (1,447.72 or 0.35%) sector are the least.
4.3. Question & Answer 3:
Question 3:
What is the most profit sector based on data distribution the in 2021?
#Based on median of profit distribution
pft.sec.med <- aggregate(`Profit_(B.IDR)`~ Sector, FSR_AN, median)
ggplot(data = pft.sec.med, mapping = aes(x = reorder(Sector , `Profit_(B.IDR)`), y = `Profit_(B.IDR)`, fill = `Profit_(B.IDR)`))+
geom_col()+
geom_text(aes(label = paste0(round(`Profit_(B.IDR)`,2))), hjust = -0.1)+
labs(title = "Median of Profit based on Sector", x = "Sector", y = "B.IDR", fill = "B.IDR")+
scale_y_continuous(limits = c(0,2000), labels = comma)+
scale_fill_viridis(discrete = F, alpha=0.6)+
theme_minimal()+
coord_flip()
Answer 3:
According to the data above, the most generating profit sector by data distribution in the Indonesia Stock Exchange in 2021 are energy (1,402.45 B.IDR), healthcare (621.62 B.IDR), and Consumer Non-Cyclicals (616.03 B.IDR). Meanwhile, properties & real estate (177.65), consumer cyclicals (114.92), and transportation-logistic (13.2) sector are the least.
4.4. Question & Answer 4:
Question 4:
What is the company with the highest profit generated in 2021?
#Based on amount of company's profit
comp <- FSR_AN %>% select(c(Company_Name, Sector, "Profit_(B.IDR)"))
pft.comp <- comp[order(comp$`Profit_(B.IDR)`, decreasing = T) , ] %>% head(20)Top 20 Companies with the Highest Profit
#Top 20 profitable companies based on sector
pft.comp.sec <- xtabs(~ Sector, data = pft.comp) %>%
as.data.frame()
pft.comp.sec[order(pft.comp.sec$Freq, decreasing = T) , ]#Plot of amount of company's profit
ggplot(data = pft.comp, mapping = aes(x = reorder(Company_Name , `Profit_(B.IDR)`), y = `Profit_(B.IDR)`, fill = `Profit_(B.IDR)`))+
geom_col()+
geom_text(aes(label = paste0(round(`Profit_(B.IDR)`,2))), hjust = -0.1)+
scale_y_continuous(limits = c(0,40000),labels = comma)+
scale_fill_continuous(labels = comma)+
labs(title = "Amount of Profit based on Company", x = "Sector", y = "Profit (B.IDR)", fill = "B.IDR")+
scale_fill_viridis(discrete = F, alpha=0.6, labels = comma)+
theme_minimal()+
coord_flip()
Answer 4:
In term of which sectors of top 20 profitable companies running their business are energy (6 companies), consumers non-cyclicals (5 companies), and financials (5 compannies) while consumer cyclicals, healthcare, properties & real estate, and technology have no representing companies in top 20 profitable companies in 2021. Besides, the most profit contributing company in the Indonesia Stock Exchange in 2021 are PT Bank Central Asia, Tbk (31,440.16 B.IDR), PT Bank Rakyat Indonesia (Persero), Tbk (30,755.77 B.IDR), and PT Bank Mandiri (Persero) (30,551.1 B.IDR).
5.Conclusion and Recommendation
5.1. Conclusion:
- In 2021 based on the data used by us, there are 261 companies categorized into 11 sectors, namely financial, energy, technology, etc.
- Financial, energy, and consumer non-cyclicals sectors dominate the composition of company listed at Indonesia Stock Exchange in 2021 with accumulated of them is equal to 50.58%. The profit contribution from 3 sectors compared to all profit generated from all sectors is 74.1%.
- Company whose the highest profit are PT Bank Central Asia Tbk, PT Bank Mandiri Tbk, and PT Bank Rakyat Indonesia (Persero) Tbk with the profit of these companies are more than 30.000 B.IDR. Besides they run the business in financial sector.
- Even though financial sector is the sector where most companies
running their business and has the most accumulative profit contribution
in 2021, but in term of profit data distribution using median, it is
categorized in the middle-up profit sector (rank 4th of 11), but its
profit is only around 1/6 of energy sector and 1/2 of healthcare and
consumer non-cyclics sector.
- Even though technology sector is the sector where the least companies running their business and has the least accumulative profit contribution in 2021, but in term of profit data distribution using median, it is categorized in the middle-up profit sector (rank 5th of 11) and the difference of B.IDR with financial sector (rank 5th of 11) is only around 5 B.IDR. It implies that this sector is potential to generate more profit contribution, if the number company listed in Indonesia Stock Exchange in this sector increasing in the future.
5.2. Recommendation:
According to our analysis above, the 3 sector must be maintained to keep it generating more profit and to support other potential sectors to expand so that profit can be more gained.