Profit Analysis of Public Companies Listed at Indonesia Stock Exchange in 2021

1.Introduction

Objective

The objective of this analysis is to describe profit of companies listed at Indonesia Stock Exchange in 2021

Scope of Analysis

The scope of the analysis is limited only to profit based on its sectors and companies.

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 Inputted & Data Cleansing

2.1. Data Input

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

2.2. Data Cleansing

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~

Missing values check 1 using anyNA: no missing value

anyNA(FSR_AN)

## [1] FALSE

Missing values check 2 using anyNA: no missing value

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                      0

3. Data Explanation

Brief Explanation

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”.

4. Data Distribution and Outlier 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.

5. Data Transformation and Manipulation

Question 1:

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.

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.

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.

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) , ]

##                       Sector Freq
## 4                     Energy    6
## 3     Consumer Non0Cyclicals    5
## 5                 Financials    5
## 7                Industrials    2
## 1            Basic Materials    1
## 8            Infrastructures    1
## 2         Consumer Cyclicals    0
## 6                 Healthcare    0
## 9   Properties & Real Estate    0
## 10                Technology    0
## 11 Transportation & Logistic    0

#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).

6. Data Conclusion and Recommendation

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.

Recommendations :

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.