Bajaj_analysis
2025-03-27
#We are using the financial data of Bajaj to analyze its performances throughout the years based on profitability and liquidity of the Enterprise. To analyze the profitability and liquidity of Bajaj auto limited, the following ratios were used in order:
Profitablity - Gross Profit Margin - EBTMargin - Net Profit - Return on equity Liqudity - currentratio Solvency - Debt to Equity
Loading the necessary pacakages for analysis:
library(readxl)
library(ggplot2)
library(gridExtra)Loading all the necessary data sets for analysis:
# Loading the balance sheet data for analysis
bajaj_bs=read_xlsx("C:/Users/Admin/Downloads/bajaj_bs.xlsx")
bajaj=as.data.frame(bajaj_bs)
# Loading the Profit and loss data for analysis
balancesheet=read_xlsx("C:/Users/Admin/Downloads/bajaj_pl.xlsx")
balancesheet=as.data.frame(balancesheet)Cleaning the columns of data sets for easier analysis:
colnames(bajaj) <- make.names(colnames(bajaj))
colnames(balancesheet) <- make.names(colnames(balancesheet))Reshaping:
We are using the financial data of Bajaj Automobiles to analyze its performances throughout the years based on profitability,liqudity and solvency of the Enterprise.
bajaj_long<- reshape(bajaj,
varying = c("X2020", "X2021", "X2022", "X2023", "X2024"),
v.names = "Amount",
timevar = "Year",
times = c("2020", "2021", "2022", "2023", "2024"),
direction = "long")
balancesheet_long<- reshape(balancesheet,
varying = c("X2020", "X2021", "X2022", "X2023", "X2024"),
v.names = "Amount",
timevar = "Year",
times = c("2020", "2021", "2022", "2023", "2024"),
direction = "long")Calculation of ratios
The following codes and outputs will show the workings of ratio analysis of Bajaj Auto Limited
The Ratios will be calculated and presented in the above mentioned order.
#Calculation of Profitablity Ratios #GROSS PROFIT RATIO= GROSS PROFIT/REVENUE*100
bajaj_pl_long<-data.frame(Year=c(2020, 2021, 2022, 2023, 2024),
Total_Operating_Revenues=c(29918.65,27741.08,33144.71,36427.60,44685.23),
Revenue_From_Operations_net=c(29111.54,27132.90,32135.98,35359.15,43578.87))#CALCULATE GROSS PROFIT RATIO
bajaj_pl_long$GROSS_PROFIT_RATIO<- (bajaj_pl_long$Total_Operating_Revenues/bajaj_pl_long$Revenue_From_Operations_net*100)
round(bajaj_pl_long$GROSS_PROFIT_RATIO)## [1] 103 102 103 103 103#view results
print(bajaj_pl_long$GROSS_PROFIT_RATIO)## [1] 102.7725 102.2415 103.1389 103.0217 102.5388#_____________________________________________________ #Calculation of EBT Margin
EBTMargin = function(x)
{
nr=x[17]
dr=x[2]
return(paste("EBTMargin = ",round(nr/dr,2)))
}
EBTMargin(balancesheet_long$Amount)## [1] "EBTMargin = 0.23"aggregate(Amount~Year,balancesheet_long,EBTMargin)## Year Amount
## 1 2020 EBTMargin = 0.23
## 2 2021 EBTMargin = 0.22
## 3 2022 EBTMargin = 0.2
## 4 2023 EBTMargin = 0.21
## 5 2024 EBTMargin = 0.23#_____________________________________________________ #Calculation Of Net profit
NetProfit = function(x)
{
nr=x[21]
dr=x[2]
return(paste("Netprofit = ",round(nr/dr,2)))
}
NetProfit(balancesheet_long$Amount)## [1] "Netprofit = 0.05"aggregate(Amount~Year,balancesheet_long,NetProfit)## Year Amount
## 1 2020 Netprofit = 0.18
## 2 2021 Netprofit = 0.17
## 3 2022 Netprofit = 0.16
## 4 2023 Netprofit = 0.16
## 5 2024 Netprofit = 0.17#_____________________________________________________ #Calculation Return on Equity
Returnonequity <-data.frame(Year = c(2020, 2021, 2022, 2023, 2024),
Netincome = c(29111.54, 27132.90, 32135.98, 35359.15, 43578.87),
Equity_Share_Capital = c(289.37, 289.37, 289.37, 282.96, 279.18),
Reserves_and_Surplus = c(19636.12, 24912.89, 26379.43, 25142.90, 24581.32))#calcultion of Shareholersequity
Returnonequity$shareholersequity<-Returnonequity$Equity_Share_Capital+Returnonequity$Reserves_and_Surplus#calculation of ROE
Returnonequity$Return_on_equity<-Returnonequity$Netincome/Returnonequity$shareholersequity#view results
print(Returnonequity$Return_on_equity)## [1] 1.461020 1.076606 1.205003 1.390677 1.752936#_____________________________________________________ #Calculation of Liqudity Ratio #calculation of Debt to Equity Ratio #Creating a Sub Subset to Find Current Ratio
bajaj_bs_long<-data.frame(Year = c(2020, 2021, 2022, 2023, 2024),
Current_Investment = c(2779.75, 8028.11, 4969.13, 4419.37, 4879.48),
Inventories = c(1063.50, 1493.89, 1230.51, 1397.90, 1695.62),
Trade_Receivables = c(1725.10, 2716.85, 1516.38, 1776.12, 2122.40),
Cash_and_Cash_Equivalents = c(308.27, 527.36, 588.34, 285.75, 536.62),
Short_Term_Loans_and_Advances = c(6.11, 5.74, 4.17, 3.62, 3.21),
Other_Current_Assets = c(714.23, 1403.18, 1685.94, 987.65, 1119.85),
Short_Term_Borrowings = c(0, 0, 0, 0, 834.05),
Trade_Payables = c(3199.70, 4573.81, 3633.18, 4073.88, 5610.18),
Other_Current_Liabilities = c(895.54, 917.03, 902.51, 958.21, 2093.26))#CALCULATE CURRENT ASSETS
bajaj_bs_long$CURRENT_ASSETS<- bajaj_bs_long$Current_Investment+bajaj_bs_long$Inventories+bajaj_bs_long$Trade_Receivables+bajaj_bs_long$Cash_and_Cash_Equivalents+bajaj_bs_long$Short_Term_Loans_and_Advances+bajaj_bs_long$Other_Current_Asset#CALCULATE CURRENT LIABILITIES
bajaj_bs_long$CURRENT_LIABILITIES<- bajaj_bs_long$Short_Term_Borrowings+bajaj_bs_long$Trade_Payables+bajaj_bs_long$Other_Current_Liabilities#CALCULATE CURRENT RATIO
bajaj_bs_long$CURRENT_RATIO<- (bajaj_bs_long$CURRENT_ASSETS/bajaj_bs_long$CURRENT_LIABILITIES)
round(bajaj_bs_long$CURRENT_RATIO)## [1] 2 3 2 2 1#view results
print(bajaj_bs_long$CURRENT_RATIO)## [1] 1.610885 2.581596 2.203517 1.762769 1.213141#_____________________________________________________ #Calculation of Solvency Ratio #Debt to Equity #Creating a Sub Subset to Find debt to Equity Ratio
debttoequity<-data.frame(Short_Term_Borrowings = c(0, 0, 0, 0, 834.05),
Trade_Payables = c(3199.70, 4573.81, 3633.18, 4073.88, 5610.18),
Equity_Share_Capital = c(289.37, 289.37, 289.37, 282.96, 279.18),
Reserves_and_Surplus = c(19636.12, 24912.89, 26379.43, 25142.90, 24581.32))#calcultion of Totaldebt
debttoequity$Totaldebt<-debttoequity$Short_Term_Borrowings+debttoequity$Trade_Payables
#calcultion of Totalshareholdersfund
debttoequity$Totalshareholdersfund<-debttoequity$Equity_Share_Capital+debttoequity$Reserves_and_Surplus
#Calcultion of Debt to Equity
debttoequity$Debt_to_equity<-debttoequity$Totaldebt/debttoequity$Totalshareholdersfund
print(debttoequity$Debt_to_equity)## [1] 0.1605833 0.1814841 0.1362334 0.1602258 0.2592156#_____________________________________________________
#Competitors analysis we are taking TVS as our Competitor
###Loading the balance sheet data for analysis
tvs_bs <-read_excel("C:/Users/Admin/Downloads/tvs_bs.xlsx")
tvs_bs<-as.data.frame(tvs_bs)
####Loading the Profit and loss data for analysis
tvs_pl <-read_excel("C:/Users/Admin/Downloads/tvs_pl.xlsx")
tvs_pl <-as.data.frame(tvs_pl)#_____________________________________________________
Cleaning the columns of data sets for easier analysis:
####Balancesheet
colnames(tvs_bs) <- make.names(colnames(tvs_bs))
colnames(tvs_bs) <- trimws(colnames(tvs_bs))
#####P&L
colnames(tvs_pl) <- make.names(colnames(tvs_pl))
colnames(tvs_pl) <- trimws(colnames(tvs_pl))#_____________________________________________________
##### Reshaping:
####Balancesheet
tvs_bs_long <- reshape(tvs_bs,
varying = c("X2024", "X2023", "X2022", "X2021", "X2020"),
v.names = "Amount",
timevar = "Year",
times = c("2024","2023","2022","2021", "2020"),
direction = "long")
####P&L
tvs_pl_long <- reshape(tvs_pl,
varying = c("X2024", "X2023", "X2022", "X2021", "X2020"),
v.names = "Amount",
timevar = "Year",
times = c("2024","2023","2022","2021", "2020"),
direction = "long")#_____________________________________________________
The 2 Ratios are taken to comparison are
##Net Profit ##Current ratio
#CURRENT RATIO= (CURRENT ASSETS/CURRENT LIABILITIES)
tvs_bs_long<-data.frame(Year = c(2020, 2021, 2022, 2023, 2024),
Current_Investment = c(0,0,130.23,191.92,163.19),
Inventories = c(1038.93,1151.81,1122.68,1236.36,1370.80),
Trade_Receivables = c(1281.36,869.98,950.69,955.07,1302.14),
Cash_and_Cash_Equivalents = c(419.17,929.81,401.2,241.96,530.96),
Short_Term_Loans_and_Advances = c(0,0,0,0,0),
Other_Current_Assets = c(482.13,495.19,757.39,1042.19,850.73),
Short_Term_Provisions = c(83.65,76.24,97.18,98.54,143.6),
Short_Term_Borrowings = c(1070,70.8,433.43,1033.04,526.53),
Trade_Payables = c(2886.39,3921.6,3991.44,4130.56,5112.17),
Other_Current_Liabilities = c(454.12,516.77,664.45,746.4,824.5))#—————————————————-
#CALCULATE CURRENT ASSETS
tvs_bs_long$CURRENT_ASSETS<- tvs_bs_long$Current_Investment+tvs_bs_long$Inventories+tvs_bs_long$Trade_Receivables+tvs_bs_long$Cash_and_Cash_Equivalents+tvs_bs_long$Short_Term_Loans_and_Advances+tvs_bs_long$Other_Current_Asset
#CALCULATE CURRENT LIABILITIES
tvs_bs_long$CURRENT_LIABILITIES<- tvs_bs_long$Short_Term_Provisions+tvs_bs_long$Short_Term_Borrowings+tvs_bs_long$Trade_Payables+tvs_bs_long$Other_Current_Liabilities
#CALCULATE CURRENT RATIO
tvs_bs_long$CURRENT_RATIO<- (tvs_bs_long$CURRENT_ASSETS/tvs_bs_long$CURRENT_LIABILITIES)
round(tvs_bs_long$CURRENT_RATIO)## [1] 1 1 1 1 1#view results
print(tvs_bs_long$CURRENT_RATIO)## [1] 0.7168392 0.7516863 0.6482580 0.6103812 0.6384059#_____________________________________________________ #Creating Fuction to find Net profit
Netprofit = function(x)
{
nr=x[31]
dr=x[3]
return(paste("Netprofit = ",round(nr/dr,2)))
}##Applyig Function in the Data to Find Netprofit
Netprofit(tvs_pl_long$Amount)## [1] "Netprofit = 0.07"aggregate(Amount~Year,tvs_pl_long,Netprofit)## Year Amount
## 1 2020 Netprofit = 0.04
## 2 2021 Netprofit = 0.04
## 3 2022 Netprofit = 0.04
## 4 2023 Netprofit = 0.06
## 5 2024 Netprofit = 0.07#_____________________________________________________
##Creating comaparison subsets to Analysis #comparsion of Bajaj vs TVS with Current Ratio
comparsion_data<-data.frame(Bajaj = c(1.610885, 2.581596, 2.203517, 1.762769, 1.213141),
TVS = c(0.7168392, 0.7516863, 0.6482580, 0.6103812, 0.6384059))
comparsion_data_2<-data.frame(Bajaj = c(0.18,0.17,0.16,0.16,0.17),
TVS = c(0.01,0.01,0.01,0.02,0.02))#______________________________________________________ #Bar plot for the comparison of the companies
bp <- barplot(
height = t(comparsion_data), # Transpose the matrix
beside = TRUE, # Bars side by side
col = c("red","yellow"), # Colors for companies
main = "Bajaj vs TVS",
xlab = "Year", # X-axis label
ylab = "Current Ratio", # Y-axis label
ylim = c(0, 3),lwd = 0.5) # Set y-axis limits
bp2 <- babp2 <- babp2 <- barplot(
height = t(comparsion_data_2), # Transpose the matrix
beside = TRUE, # Bars side by side
col = c("red","yellow"), # Colors for companies
main = "Bajaj vs TVS",
xlab = "Year", # X-axis label
ylab = "Net Profit", # Y-axis label
ylim = c(0,0.3),lwd = 0.5) # Set y-axis limits
#Contributions of the team
1)Surya Prakash-Calculation of Net profit and Gross profit ratio 2)Naveen Kumar-Calculation of return on equity and Interpretation of the data 3)Hari-Calculation of current ratio, comparison of data and Barplot 4)Rohit-Calculation of EBT Margin and debt to equity ratio 5)Hari-Cleaning of the data in the excel and uploaded in R