Analysis of Construction and Infrastructure Sector using profitability and liquidity ratios.

To analyze the profitability and liquidity of Construction and Infrastructure Sector , the following ratios were used in order:

  • Current Ratio

  • Quick Ratio

  • Net Profit Margin

  • Return on Equity

The following points define the reason for choosing these ratio as the base metrics of analysis:

  • Liquidity Ratios (Short-Term Financial Stability)

    1. Current Ratio (Average Current Ratio)

  • Measures a company’s ability to cover its short-term liabilities with its short-term assets.

  • A higher ratio indicates better liquidity and financial security.

  • Important because companies need sufficient current assets to handle unexpected short-term obligations without financial distress.

    1. Quick Ratio (Average Quick Ratio)

  • A stricter liquidity measure than the current ratio, as it excludes inventory (which may not be easily liquidated).

  • Indicates how well a company can meet immediate financial obligations using only its most liquid assets (cash, receivables, marketable securities).

  • Crucial for assessing companies that operate in industries with slower inventory turnover (e.g., manufacturing, real estate).

  • Profitability Ratios (Long-Term Business Viability)

    1. Net Profit Margin (Average Net Profit Margin)

  • Measures how much profit a company retains from its total revenue.

  • A higher margin indicates better cost control and pricing strategy.

  • Critical because it shows whether the business model is sustainable and if the company can generate sufficient earnings to reinvest and grow.

    1. Return on Equity (ROE) (Average ROE)

  • Measures how effectively a company uses shareholder equity to generate profits.

  • A high ROE indicates strong financial performance and management efficiency.

  • Essential for investors because it shows how much return they are getting for every unit of capital invested.

The sector anlysis is based on the companies in the following order.

The following list contains the order of the companies analysed and the group members who did the coding and analysis part.

  • Man Infraconstruction Limited by Kishore

  • Welspun Enterprises Limited by Dharanidharan

  • Hindustan Construction Company Limited by Balaji

  • Reliance Infrastructure Limited by Arunachalam

  • Sadbhav Infrastructure Project Limited by Nakul

The following is coding done for analysis of Man Infraconstruction Limited by Kishore.

# Load necessary libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(scales)
library(gridExtra)
library(plotly)

# Read the Excel files
man_bs <- read_excel("D:/R_Programme/FA_Project/XL_Data/Man_BS.xlsx")
man_cf <- read_excel("D:/R_Programme/FA_Project/XL_Data/Man_CF.xlsx")
man_pl <- read_excel("D:/R_Programme/FA_Project/XL_Data/Man_P_L.xlsx")

### Step 1: Calculate Net Profit After Tax (Improved version)
net_profit_after_tax <- man_cf %>%
  filter(Items == "Net_Profit_Before_Tax") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "PBT") %>%
  left_join(
    man_pl %>%
      filter(Items %in% c("Current_Tax", "Deferred_Tax", "Tax_For_Earlier_Years")) %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Value") %>%
      group_by(Year) %>%
      summarise(Tax_Expenses = sum(Value, na.rm = TRUE)),
    by = "Year"
  ) %>%
  mutate(Net_Profit_After_Tax = PBT - Tax_Expenses)

### Step 2: Calculate Ratios (with fixes)
# Current Ratio (fixed column selection)
current_ratio <- man_bs %>%
  filter(Items %in% c("Current_Investments", "Inventories", "Trade_Receivables", 
                      "Cash_And_Cash_Equivalents", "Short_Term_Loans_And_Advances", 
                      "OtherCurrentAssets")) %>%
  select(-Items) %>%
  summarise(across(everything(), sum, na.rm = TRUE)) %>%
  mutate(Items = "Total_Current_Assets") %>%
  bind_rows(
    man_bs %>%
      filter(Items %in% c("Short_Term_Borrowings", "Trade_Payables", 
                          "Other_Current_Liabilities", "Short_Term_Provisions")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      mutate(Items = "Total_Current_Liabilities")
  ) %>%
  pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Current_Ratio = Total_Current_Assets / Total_Current_Liabilities)

# Quick Ratio (fixed inventory extraction)
inventories <- man_bs %>%
  filter(Items == "Inventories") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "Inventory")

quick_ratio <- current_ratio %>%
  left_join(inventories, by = "Year") %>%
  mutate(Quick_Assets = Total_Current_Assets - Inventory,
         Quick_Ratio = Quick_Assets / Total_Current_Liabilities)

# Net Profit Margin (fixed revenue extraction)
net_profit_margin <- net_profit_after_tax %>%
  left_join(
    man_pl %>%
      filter(Items == "Revenue_From_Operations_Net") %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Revenue"),
    by = "Year"
  ) %>%
  mutate(Net_Profit_Margin = Net_Profit_After_Tax / Revenue)

# Return on Equity (ROE) (fixed equity calculation)
return_on_equity <- net_profit_after_tax %>%
  left_join(
    man_bs %>%
      filter(Items %in% c("Equity_Share_Capital", "Reserves_and_Surplus")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Shareholders_Equity"),
    by = "Year"
  ) %>%
  mutate(ROE = Net_Profit_After_Tax / Shareholders_Equity)

# Combine all ratios (final output)
Man_Fin_Ratio <- current_ratio %>%
  select(Year, Current_Ratio) %>%
  left_join(quick_ratio %>% select(Year, Quick_Ratio), by = "Year") %>%
  left_join(net_profit_margin %>% select(Year, Net_Profit_Margin), by = "Year") %>%
  left_join(return_on_equity %>% select(Year, ROE), by = "Year")

# Print formatted results
Man_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year)
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020 10.35 10.29 0.68 0.08
2021 7.45 7.44 0.81 0.10
2022 8.17 8.14 0.45 0.11
2023 3.87 3.86 0.21 0.15
2024 6.41 6.39 0.28 0.15 
# View the original data
View(Man_Fin_Ratio)

# Calculate averages across all years (corrected version)
Man_Fin_Ratio_Averages <- Man_Fin_Ratio %>%
  summarise(across(where(is.numeric), mean, na.rm = TRUE)) %>%
  mutate(Year = "2020-2024 Average") %>%
  select(Year, everything())

# Print the formatted averages
Man_Fin_Ratio_Averages %>%
  mutate(across(where(is.numeric), ~ round(., 2)))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020-2024 Average 7.25 7.22 0.49 0.12 
# Combine with original data for comparison
Man_Fin_Ratio_With_Avg <- Man_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year) %>%
  bind_rows(Man_Fin_Ratio_Averages)

# View the combined table
View(Man_Fin_Ratio_With_Avg)

# Convert to long format for plotting and rename to Man_ratio_long
Man_ratio_long <- Man_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))  # Convert Year to numeric for proper axis scaling

# Create individual plots for each ratio
plot_current <- ggplot(Man_ratio_long %>% filter(Ratio == "Current_Ratio"), 
                       aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", linewidth = 1.5) +
  geom_point(size = 3, color = "steelblue") +
  labs(title = "Current Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_quick <- ggplot(Man_ratio_long %>% filter(Ratio == "Quick_Ratio"), 
                     aes(x = Year, y = Value)) +
  geom_line(color = "darkorange", linewidth = 1.5) +
  geom_point(size = 3, color = "darkorange") +
  labs(title = "Quick Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_npm <- ggplot(Man_ratio_long %>% filter(Ratio == "Net_Profit_Margin"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "forestgreen", linewidth = 1.5) +
  geom_point(size = 3, color = "forestgreen") +
  labs(title = "Net Profit Margin Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_roe <- ggplot(Man_ratio_long %>% filter(Ratio == "ROE"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "purple", linewidth = 1.5) +
  geom_point(size = 3, color = "purple") +
  labs(title = "Return on Equity (ROE) Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

# Display all individual plots
grid.arrange(plot_current, plot_quick, plot_npm, plot_roe, ncol = 2)

# Create and display combined plot
ggplot(Man_ratio_long, aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year",
       color = "Ratio Type") +
  scale_y_continuous(labels = scales::comma) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
        legend.position = "none")

The following is coding done for analysis of Welspun Enterprises Limited by Dharanidharan.

# Load necessary libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(scales)
library(gridExtra)
library(plotly)

# Read the Excel files
wel_bs <- read_excel("D:/R_Programme/FA_Project/XL_Data/Wel_BS.xlsx")
wel_cf <- read_excel("D:/R_Programme/FA_Project/XL_Data/Wel_CF.xlsx")
wel_pl <- read_excel("D:/R_Programme/FA_Project/XL_Data/Wel_PL.xlsx")

### Step 1: Get Revenue data from PL statement (using the correct item name)
revenue_data <- wel_pl %>%
  filter(Items %in% c("Revenue_From_Operations_Gross_", "Revenue_From_Operations_Net_")) %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "Revenue") %>%
  group_by(Year) %>%
  # Take the first non-NA value if both exist
  summarise(Revenue = first(na.omit(Revenue)))

### Verify the revenue data
print(revenue_data)
## # A tibble: 5 × 2
##   Year  Revenue
##   <chr>   <dbl>
## 1 2020    1760.
## 2 2021    1410.
## 3 2022    1301.
## 4 2023    2620.
## 5 2024    2405.
### Step 2: Calculate Net Profit After Tax (using PBT from CF and taxes from PL)
net_profit_after_tax <- wel_cf %>%
  filter(Items == "Net_Profit_Before_Tax") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "PBT") %>%
  left_join(
    wel_pl %>%
      filter(Items %in% c("Current_Tax", "Deferred_Tax", "Tax_For_Earlier_Years")) %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Value") %>%
      group_by(Year) %>%
      summarise(Tax_Expenses = sum(Value, na.rm = TRUE)),
    by = "Year"
  ) %>%
  mutate(Net_Profit_After_Tax = PBT - Tax_Expenses)

### Step 3: Calculate Current Ratio
current_ratio <- wel_bs %>%
  # Current Assets
  filter(Items %in% c("Current_Investments", "Inventories", "Trade_Receivables", 
                      "Cash_And_Cash_Equivalents", "Short_Term_Loans_And_Advances", 
                      "Other_Current_Assets")) %>%
  select(-Items) %>%
  summarise(across(everything(), sum, na.rm = TRUE)) %>%
  mutate(Items = "Current_Assets") %>%
  bind_rows(
    # Current Liabilities
    wel_bs %>%
      filter(Items %in% c("Short_Term_Borrowings", "Trade_Payables", 
                          "Other_Current_Liabilities", "Short_Term_Provisions")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      mutate(Items = "Current_Liabilities")
  ) %>%
  pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Current_Ratio = Current_Assets / Current_Liabilities)

### Step 4: Calculate Quick Ratio
quick_ratio <- current_ratio %>%
  left_join(
    wel_bs %>%
      filter(Items == "Inventories") %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Inventory"),
    by = "Year"
  ) %>%
  mutate(Quick_Ratio = (Current_Assets - Inventory) / Current_Liabilities)

### Step 5: Calculate Net Profit Margin
net_profit_margin <- net_profit_after_tax %>%
  left_join(revenue_data, by = "Year") %>%
  mutate(Net_Profit_Margin = Net_Profit_After_Tax / Revenue)

### Step 6: Calculate Return on Equity
roe <- net_profit_after_tax %>%
  left_join(
    wel_bs %>%
      filter(Items %in% c("Equity_Share_Capital", "Reserves_and_Surplus")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Equity"),
    by = "Year"
  ) %>%
  mutate(ROE = Net_Profit_After_Tax / Equity)

### Combine all ratios
Wel_Fin_Ratio <- current_ratio %>%
  select(Year, Current_Ratio) %>%
  left_join(quick_ratio %>% select(Year, Quick_Ratio), by = "Year") %>%
  left_join(net_profit_margin %>% select(Year, Net_Profit_Margin), by = "Year") %>%
  left_join(roe %>% select(Year, ROE), by = "Year")

# Final output with proper formatting
Wel_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 4))) %>%  # Using 4 decimal places for better precision
  arrange(Year) %>%
  mutate(Net_Profit_Margin = scales::percent(Net_Profit_Margin, accuracy = 0.01),
         ROE = scales::percent(ROE, accuracy = 0.01))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020 1.6011 1.6003 9.05% 9.18%
2021 1.6747 1.6691 7.62% 5.91%
2022 1.6076 1.5613 7.18% 4.93%
2023 2.0721 2.0212 27.22% 28.96%
2024 2.3956 2.3019 11.86% 11.78% 
# Calculate averages across all years (corrected version)
Wel_Fin_Ratio_Averages <- Wel_Fin_Ratio %>%
  summarise(across(where(is.numeric), mean, na.rm = TRUE)) %>%
  mutate(Year = "2020-2024 Average") %>%
  select(Year, everything())

# Print the formatted averages
Wel_Fin_Ratio_Averages %>%
  mutate(across(where(is.numeric), ~ round(., 2)))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020-2024 Average 1.87 1.83 0.13 0.12 
# Combine with original data for comparison
Wel_Fin_Ratio_With_Avg <- Man_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year) %>%
  bind_rows(Wel_Fin_Ratio_Averages)

# View the combined table
View(Wel_Fin_Ratio_With_Avg)

# Convert to long format for plotting
Wel_ratio_long <- Wel_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))

# Create individual plots for each ratio
plot_current <- ggplot(Wel_ratio_long %>% filter(Ratio == "Current_Ratio"), 
                       aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", linewidth = 1.5) +
  geom_point(size = 3, color = "steelblue") +
  labs(title = "Current Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_quick <- ggplot(Wel_ratio_long %>% filter(Ratio == "Quick_Ratio"), 
                     aes(x = Year, y = Value)) +
  geom_line(color = "darkorange", linewidth = 1.5) +
  geom_point(size = 3, color = "darkorange") +
  labs(title = "Quick Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_npm <- ggplot(Wel_ratio_long %>% filter(Ratio == "Net_Profit_Margin"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "forestgreen", linewidth = 1.5) +
  geom_point(size = 3, color = "forestgreen") +
  labs(title = "Net Profit Margin Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_roe <- ggplot(Wel_ratio_long %>% filter(Ratio == "ROE"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "purple", linewidth = 1.5) +
  geom_point(size = 3, color = "purple") +
  labs(title = "Return on Equity (ROE) Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

# Display all individual plots
grid.arrange(plot_current, plot_quick, plot_npm, plot_roe, ncol = 2)

# Create and display combined plot
ggplot(Wel_ratio_long, aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year",
       color = "Ratio Type") +
  scale_y_continuous(labels = scales::comma) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
        legend.position = "none")

The following is coding done for analysis of Hindustan Construction Company Limited by Balaji.

# Load necessary libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(scales)
library(gridExtra)
library(plotly)

# Read the Excel files
hind_bs <- read_excel("D:/R_Programme/FA_Project/XL_Data/Hind_BS.xlsx")
hind_cf <- read_excel("D:/R_Programme/FA_Project/XL_Data/Hind_CF.xlsx")
hind_pl <- read_excel("D:/R_Programme/FA_Project/XL_Data/Hind_PL.xlsx")

### Step 1: Calculate Net Profit After Tax
net_profit_after_tax <- hind_cf %>%
  filter(Items == "Net_Profit_Before_Tax") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "PBT") %>%
  left_join(
    hind_pl %>%
      filter(Items %in% c("Current_Tax", "Deferred_Tax")) %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Value") %>%
      group_by(Year) %>%
      summarise(Tax_Expenses = sum(Value, na.rm = TRUE)),
    by = "Year"
  ) %>%
  mutate(Net_Profit_After_Tax = PBT - Tax_Expenses)

### Step 2: Calculate Ratios
# Current Ratio
current_ratio <- hind_bs %>%
  filter(Items %in% c("Current_Investments", "Inventories", "Trade_Receivables", 
                      "Cash_And_Cash_Equivalents", "Short_Term_Loans_And_Advances", 
                      "OtherCurrentAssets")) %>%
  select(-Items) %>%
  summarise(across(everything(), sum, na.rm = TRUE)) %>%
  mutate(Items = "Total_Current_Assets") %>%
  bind_rows(
    hind_bs %>%
      filter(Items %in% c("Short_Term_Borrowings", "Trade_Payables", 
                          "Other_Current_Liabilities", "Short_Term_Provisions")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      mutate(Items = "Total_Current_Liabilities")
  ) %>%
  pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Current_Ratio = Total_Current_Assets / Total_Current_Liabilities)

# Quick Ratio
inventories <- hind_bs %>%
  filter(Items == "Inventories") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "Inventory")

quick_ratio <- current_ratio %>%
  left_join(inventories, by = "Year") %>%
  mutate(Quick_Assets = Total_Current_Assets - Inventory,
         Quick_Ratio = Quick_Assets / Total_Current_Liabilities)

# Net Profit Margin
net_profit_margin <- net_profit_after_tax %>%
  left_join(
    hind_pl %>%
      filter(Items == "Revenue_From_Operations_Net") %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Revenue"),
    by = "Year"
  ) %>%
  mutate(Net_Profit_Margin = Net_Profit_After_Tax / Revenue)

# Return on Equity (ROE)
return_on_equity <- net_profit_after_tax %>%
  left_join(
    hind_bs %>%
      filter(Items %in% c("Equity_Share_Capital", "Reserves_and_Surplus")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Shareholders_Equity"),
    by = "Year"
  ) %>%
  mutate(ROE = Net_Profit_After_Tax / Shareholders_Equity)

# Combine all ratios
Hind_Fin_Ratio <- current_ratio %>%
  select(Year, Current_Ratio) %>%
  left_join(quick_ratio %>% select(Year, Quick_Ratio), by = "Year") %>%
  left_join(net_profit_margin %>% select(Year, Net_Profit_Margin), by = "Year") %>%
  left_join(return_on_equity %>% select(Year, ROE), by = "Year")

# Print formatted results
Hind_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year)
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020 0.70 0.68 -0.05 -0.14
2021 1.00 0.98 -0.24 -0.93
2022 1.00 0.98 -0.04 -0.33
2023 1.13 1.09 0.05 0.35
2024 1.10 1.07 0.04 0.19 
# Calculate averages across all years (corrected version)
Hind_Fin_Ratio_Averages <- Hind_Fin_Ratio %>%
  summarise(across(where(is.numeric), mean, na.rm = TRUE)) %>%
  mutate(Year = "2020-2024 Average") %>%
  select(Year, everything())

# Print the formatted averages
Hind_Fin_Ratio_Averages %>%
  mutate(across(where(is.numeric), ~ round(., 2)))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020-2024 Average 0.99 0.96 -0.05 -0.17 
# Combine with original data for comparison
Hind_Fin_Ratio_With_Avg <- Hind_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year) %>%
  bind_rows(Hind_Fin_Ratio_Averages)

# Convert to long format for plotting
Hind_ratio_long <- Hind_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))

# View the combined table
View(Hind_Fin_Ratio_With_Avg)

# Create individual plots for each ratio
plot_current <- ggplot(Hind_ratio_long %>% filter(Ratio == "Current_Ratio"), 
                       aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", linewidth = 1.5) +
  geom_point(size = 3, color = "steelblue") +
  labs(title = "Current Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_quick <- ggplot(Hind_ratio_long %>% filter(Ratio == "Quick_Ratio"), 
                     aes(x = Year, y = Value)) +
  geom_line(color = "darkorange", linewidth = 1.5) +
  geom_point(size = 3, color = "darkorange") +
  labs(title = "Quick Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_npm <- ggplot(Hind_ratio_long %>% filter(Ratio == "Net_Profit_Margin"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "forestgreen", linewidth = 1.5) +
  geom_point(size = 3, color = "forestgreen") +
  labs(title = "Net Profit Margin Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_roe <- ggplot(Hind_ratio_long %>% filter(Ratio == "ROE"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "purple", linewidth = 1.5) +
  geom_point(size = 3, color = "purple") +
  labs(title = "Return on Equity (ROE) Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

# Display all individual plots
grid.arrange(plot_current, plot_quick, plot_npm, plot_roe, ncol = 2)

# Create and display combined plot
ggplot(Hind_ratio_long, aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year",
       color = "Ratio Type") +
  scale_y_continuous(labels = scales::comma) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
        legend.position = "none")

The following is coding done for analysis of Reliance Infrastructure Limited by Arunachalam.

# Load necessary libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(scales)
library(gridExtra)
library(plotly)

# Read the Excel files
ril_bs <- read_excel("D:/R_Programme/FA_Project/XL_Data/RILBS.xlsx")
ril_cf <- read_excel("D:/R_Programme/FA_Project/XL_Data/RILCFS.xlsx")
ril_pl <- read_excel("D:/R_Programme/FA_Project/XL_Data/RILPL.xlsx")

### Step 1: Calculate Net Profit After Tax
net_profit_after_tax <- ril_pl %>%
  filter(Items == "ProfitorLossForThePeriod") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "Net_Profit_After_Tax")

### Step 2: Calculate Ratios
# Current Ratio
current_ratio <- ril_bs %>%
  filter(Items %in% c("CurrentInvestments", "Inventories", "TradeReceivables", 
                      "CashAndCashEquivalents", "ShortTermLoansAndAdvances", 
                      "OtherCurrentAssets")) %>%
  select(-Items) %>%
  summarise(across(everything(), sum, na.rm = TRUE)) %>%
  mutate(Items = "Total_Current_Assets") %>%
  bind_rows(
    ril_bs %>%
      filter(Items %in% c("ShortTermBorrowings", "TradePayables", 
                          "OtherCurrentLiabilities", "ShortTermProvisions")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      mutate(Items = "Total_Current_Liabilities")
  ) %>%
  pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Current_Ratio = Total_Current_Assets / Total_Current_Liabilities)

# Quick Ratio
inventories <- ril_bs %>%
  filter(Items == "Inventories") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "Inventory")

quick_ratio <- current_ratio %>%
  left_join(inventories, by = "Year") %>%
  mutate(Quick_Assets = Total_Current_Assets - Inventory,
         Quick_Ratio = Quick_Assets / Total_Current_Liabilities)

# Net Profit Margin Calculation (using updated PL data)
net_profit_margin <- net_profit_after_tax %>%
  left_join(
    ril_pl %>%
      filter(Items == "RevenueFromOperationsNet") %>%  # Explicitly using net revenue
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Revenue"),
    by = "Year"
  ) %>%
  mutate(Net_Profit_Margin = Net_Profit_After_Tax / Revenue)

# Return on Equity (ROE)
return_on_equity <- net_profit_after_tax %>%
  left_join(
    ril_bs %>%
      filter(Items %in% c("EquitySharCapital", "Reserves andSurplus")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Shareholders_Equity"),
    by = "Year"
  ) %>%
  mutate(ROE = Net_Profit_After_Tax / Shareholders_Equity)

# Combine all ratios (final output)
RIL_Fin_Ratio <- current_ratio %>%
  select(Year, Current_Ratio) %>%
  left_join(quick_ratio %>% select(Year, Quick_Ratio), by = "Year") %>%
  left_join(net_profit_margin %>% select(Year, Net_Profit_Margin), by = "Year") %>%
  left_join(return_on_equity %>% select(Year, ROE), by = "Year")

# Print formatted results
RIL_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year)
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020 1.85 1.85 0.09 2.96
2021 1.48 1.48 0.13 3.08
2022 1.40 1.40 0.09 3.85
2023 1.16 1.16 0.08 6.01
2024 1.12 1.12 0.08 6.67 
# Calculate averages across all years (corrected version)
RIL_Fin_Ratio_Averages <- RIL_Fin_Ratio %>%
  summarise(across(where(is.numeric), mean, na.rm = TRUE)) %>%
  mutate(Year = "2020-2024 Average") %>%
  select(Year, everything())

# Print the formatted averages
RIL_Fin_Ratio_Averages %>%
  mutate(across(where(is.numeric), ~ round(., 2)))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020-2024 Average 1.4 1.4 0.1 4.51 
# Combine with original data for comparison
RIL_Fin_Ratio_With_Avg <- RIL_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year) %>%
  bind_rows(RIL_Fin_Ratio_Averages)

# Convert to long format for plotting
RIL_ratio_long <- RIL_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))

# View the combined table
View(RIL_Fin_Ratio_With_Avg)

# Convert to long format for plotting and rename to RIL_ratio_long
RIL_ratio_long <- RIL_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))  # Convert Year to numeric for proper axis scaling

# Create individual plots for each ratio
plot_current <- ggplot(RIL_ratio_long %>% filter(Ratio == "Current_Ratio"), 
                       aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", linewidth = 1.5) +
  geom_point(size = 3, color = "steelblue") +
  labs(title = "Current Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_quick <- ggplot(RIL_ratio_long %>% filter(Ratio == "Quick_Ratio"), 
                     aes(x = Year, y = Value)) +
  geom_line(color = "darkorange", linewidth = 1.5) +
  geom_point(size = 3, color = "darkorange") +
  labs(title = "Quick Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_npm <- ggplot(RIL_ratio_long %>% filter(Ratio == "Net_Profit_Margin"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "forestgreen", linewidth = 1.5) +
  geom_point(size = 3, color = "forestgreen") +
  labs(title = "Net Profit Margin Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_roe <- ggplot(RIL_ratio_long %>% filter(Ratio == "ROE"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "purple", linewidth = 1.5) +
  geom_point(size = 3, color = "purple") +
  labs(title = "Return on Equity (ROE) Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

# Display all individual plots
grid.arrange(plot_current, plot_quick, plot_npm, plot_roe, ncol = 2)

# Create and display combined plot
ggplot(RIL_ratio_long, aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(title = "Reliance Industries Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year",
       color = "Ratio Type") +
  scale_y_continuous(labels = scales::comma) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
        legend.position = "none")

The following is coding done for analysis of Sadbhav Infrastructure Project Limited by Nakul.

# Load necessary libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(scales)
library(gridExtra)
library(plotly)

# Read the Excel files
sad_bs <- read_excel("D:/R_Programme/FA_Project/XL_Data/Sad_BS.xlsx")
sad_cf <- read_excel("D:/R_Programme/FA_Project/XL_Data/Sad_CF.xlsx")
sad_pl <- read_excel("D:/R_Programme/FA_Project/XL_Data/Sad_PL.xlsx")

### Step 1: Calculate Net Profit After Tax (Improved version)
net_profit_after_tax <- sad_cf %>%
  filter(Items == "Net_Profit_Before_Tax") %>%
  select(-Items) %>%
  pivot_longer(cols = everything(), names_to = "Year", values_to = "PBT") %>%
  left_join(
    sad_pl %>%
      filter(Items %in% c("Current_Tax", "Deferred_Tax", "Tax_For_Earlier_Years")) %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Value") %>%
      group_by(Year) %>%
      summarise(Tax_Expenses = sum(Value, na.rm = TRUE)),
    by = "Year"
  ) %>%
  mutate(Net_Profit_After_Tax = PBT - Tax_Expenses)

### Step 2: Calculate Ratios (with fixes)
# Current Ratio (fixed column selection)
current_ratio <- sad_bs %>%
  filter(Items %in% c("Trade_Receivables", "Cash_And_Cash_Equivalents", 
                      "Short_Term_Loans_And_Advances", "Other_Current_Assets")) %>%
  select(-Items) %>%
  summarise(across(everything(), sum, na.rm = TRUE)) %>%
  mutate(Items = "Total_Current_Assets") %>%
  bind_rows(
    sad_bs %>%
      filter(Items %in% c("Short_Term_Borrowings", "Trade_Payables", 
                          "Other_Current_Liabilities", "Short_Term_Provisions")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      mutate(Items = "Total_Current_Liabilities")
  ) %>%
  pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Current_Ratio = Total_Current_Assets / Total_Current_Liabilities)

# Quick Ratio (fixed inventory extraction)
# Note: Since "Inventories" is not present in the balance sheet, we'll assume it's zero
quick_ratio <- current_ratio %>%
  mutate(Quick_Assets = Total_Current_Assets,  # No inventories to subtract
         Quick_Ratio = Quick_Assets / Total_Current_Liabilities)

# Net Profit Margin (fixed revenue extraction)
net_profit_margin <- net_profit_after_tax %>%
  left_join(
    sad_pl %>%
      filter(Items == "Revenue_From_Operations_Net") %>%
      select(-Items) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Revenue"),
    by = "Year"
  ) %>%
  mutate(Net_Profit_Margin = Net_Profit_After_Tax / Revenue)

# Return on Equity (ROE) (fixed equity calculation)
return_on_equity <- net_profit_after_tax %>%
  left_join(
    sad_bs %>%
      filter(Items %in% c("Equity_Share_Capital", "Reserves_and_Surplus")) %>%
      select(-Items) %>%
      summarise(across(everything(), sum, na.rm = TRUE)) %>%
      pivot_longer(cols = everything(), names_to = "Year", values_to = "Shareholders_Equity"),
    by = "Year"
  ) %>%
  mutate(ROE = Net_Profit_After_Tax / Shareholders_Equity)

# Combine all ratios (final output)
Sad_Fin_Ratio <- current_ratio %>%
  select(Year, Current_Ratio) %>%
  left_join(quick_ratio %>% select(Year, Quick_Ratio), by = "Year") %>%
  left_join(net_profit_margin %>% select(Year, Net_Profit_Margin), by = "Year") %>%
  left_join(return_on_equity %>% select(Year, ROE), by = "Year")

# Print formatted results
Sad_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year)
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020 0.65 0.65 2.07 0.22
2021 0.32 0.32 0.11 0.01
2022 0.88 0.88 -0.94 -0.12
2023 1.15 1.15 -4.00 -0.29
2024 0.72 0.72 25.00 0.85 
# Calculate averages across all years (corrected version)
Sad_Fin_Ratio_Averages <- Sad_Fin_Ratio %>%
  summarise(across(where(is.numeric), mean, na.rm = TRUE)) %>%
  mutate(Year = "2020-2024 Average") %>%
  select(Year, everything())

# Print the formatted averages
Sad_Fin_Ratio_Averages %>%
  mutate(across(where(is.numeric), ~ round(., 2)))
Year Current_Ratio Quick_Ratio Net_Profit_Margin ROE
2020-2024 Average 0.74 0.74 4.45 0.13 
# Combine with original data for comparison
Sad_Fin_Ratio_With_Avg <- Sad_Fin_Ratio %>%
  mutate(across(where(is.numeric), ~ round(., 2))) %>%
  arrange(Year) %>%
  bind_rows(Sad_Fin_Ratio_Averages)

# Convert to long format for plotting
Sad_ratio_long <- Sad_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))

# View the combined table
View(Sad_Fin_Ratio_With_Avg)


# Convert to long format for plotting and rename to Sad_ratio_long
Sad_ratio_long <- Sad_Fin_Ratio %>%
  pivot_longer(cols = -Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Year = as.numeric(Year))  # Convert Year to numeric for proper axis scaling

# Create individual plots for each ratio
plot_current <- ggplot(Sad_ratio_long %>% filter(Ratio == "Current_Ratio"), 
                       aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", linewidth = 1.5) +
  geom_point(size = 3, color = "steelblue") +
  labs(title = "Current Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_quick <- ggplot(Sad_ratio_long %>% filter(Ratio == "Quick_Ratio"), 
                     aes(x = Year, y = Value)) +
  geom_line(color = "darkorange", linewidth = 1.5) +
  geom_point(size = 3, color = "darkorange") +
  labs(title = "Quick Ratio Trend", 
       y = "Ratio Value",
       x = "Year") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_npm <- ggplot(Sad_ratio_long %>% filter(Ratio == "Net_Profit_Margin"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "forestgreen", linewidth = 1.5) +
  geom_point(size = 3, color = "forestgreen") +
  labs(title = "Net Profit Margin Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

plot_roe <- ggplot(Sad_ratio_long %>% filter(Ratio == "ROE"), 
                   aes(x = Year, y = Value)) +
  geom_line(color = "purple", linewidth = 1.5) +
  geom_point(size = 3, color = "purple") +
  labs(title = "Return on Equity (ROE) Trend", 
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold"))

# Display all individual plots
grid.arrange(plot_current, plot_quick, plot_npm, plot_roe, ncol = 2)

# Create and display combined plot
ggplot(Sad_ratio_long, aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year",
       color = "Ratio Type") +
  scale_y_continuous(labels = scales::comma) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
        legend.position = "none")

Analysis and ranking of companies:

After the analysis we as group concluded the results they are as follows:

Ranking Companies:-

Ranking Current Ratio Quick Ratio Net Profit Margin Return on Equity
1. Man Infraconstruction Limited Man Infraconstruction Limited Sadbhav Infrastructure Project Limited Reliance Infrastructure Limited
2. Welspun Enterprises Welspun Enterprises Reliance Infrastructure Limited Sadbhav Infrastructure Project Limited
3. Reliance Infrastructure Limited Reliance Infrastructure Limited Welspun Enterprises Welspun Enterprises
4. Hindustan Construction Company Limited Hindustan Construction Company Limited Man Infraconstruction Limited Man Infraconstruction Limited
5. Sadbhav Infrastructure Project Limited Sadbhav Infrastructure Project Limited Hindustan Construction Company Limited Hindustan Construction Company Limited
From the above table we can observe the following table:

The following table contains the actual rankings of compaines based on Liquidity and Profitability.

Quality Liquidity Profitability
BEST Man Infraconstruction Limited Reliance Infrastructure Limited
SECOND BEST Welspun Enterprises Sadbhav Infrastructure Project Limited
MODERATE Reliance Infrastructure Limited Welspun Enterprises
POOR Hindustan Construction Company Limited Man Infraconstruction Limited
WORST Sadbhav Infrastructure Project Limited Hindustan Construction Company Limited