Analysis of Telecommunications Sector using profitability and liquidity ratios.

To analyze the profitability and liquidity of Telecommunications Sector using profitability, 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:

1. Liquidity Ratios (Short-Term Financial Stability)

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

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

2. Profitability Ratios (Long-Term Business Viability)

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

  • 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 coding and analysis part.

  • Bharati Airtel Limited

  • Mahanagar Telephone Nigam Limited

  • Optiemus Infracom Limited

  • Tata Communications Limited

  • Tata Teleservices Limited

  • Vodafone Idea Limited

The following is coding done for analysis of Bharati Airtel Limited:
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(purrr)

# 1. UPLOADING THE DATA ---------------------------------------------------
pl_data <- read_excel("E:/Tele_Com/airtel_pl.xlsx", sheet = "Sheet1")
bs_data <- read_excel("E:/Tele_Com/airtel_bs.xlsx", sheet = "Sheet1")
cf_data <- read_excel("E:/Tele_Com/airtel_cf.xlsx", sheet = "Sheet1")

# 2. DATA CLEANING FUNCTION -----------------------------------------------
clean_fin_data <- function(df) {
  df %>%
    rename(Items = 1) %>%
    filter(!is.na(Items)) %>%
    pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
    mutate(Year = as.numeric(Year),
           Value = as.numeric(Value)) %>%
    group_by(Year, Items) %>%
    summarise(Value = first(Value), .groups = "drop") %>%
    pivot_wider(names_from = "Items", values_from = "Value")
}

# 3. PROCESS AND CALCULATE RATIOS -----------------------------------------
pl_clean <- clean_fin_data(pl_data)
bs_clean <- clean_fin_data(bs_data)
cf_clean <- clean_fin_data(cf_data)

ratios <- bs_clean %>%
  left_join(pl_clean, by = "Year") %>%
  mutate(
    Current_Ratio = round(Total_Current_Assets / Total_Current_Liabilities, 2),
    Quick_Ratio = round((Total_Current_Assets - Inventories) / Total_Current_Liabilities, 2),
    Return_on_Assets = round(Profit_Loss_For_The_Period / Total_Assets, 4),
    Net_Profit_Ratio = round(Profit_Loss_For_The_Period / Total_Revenue, 4)
  ) %>%
  select(Year, Current_Ratio, Quick_Ratio, Return_on_Assets, Net_Profit_Ratio) %>%
  arrange(Year)

# 4. VIEW AND SAVE RESULTS ------------------------------------------------
cat("\nAirtel Financial Ratios (2020-2024):\n")
## 
## Airtel Financial Ratios (2020-2024):
print(as.data.frame(ratios))
##   Year Current_Ratio Quick_Ratio Return_on_Assets Net_Profit_Ratio
## 1 2020          0.63        0.63          -0.1201          -0.6381
## 2 2021          0.56        0.56          -0.0907          -0.3777
## 3 2022          0.51        0.51          -0.0127          -0.0503
## 4 2023          0.52        0.52          -0.0003          -0.0010
## 5 2024          0.43        0.43           0.0142           0.0522
# Calculate and display 5-year averages
cat("\n5-Year Average Financial Ratios for Airtel (2020-2024):\n")
## 
## 5-Year Average Financial Ratios for Airtel (2020-2024):
ratios %>%
  summarise(across(
    c(Current_Ratio, Quick_Ratio, Return_on_Assets, Net_Profit_Ratio),
    ~ mean(., na.rm = TRUE),
    .names = "Avg_{.col}"
  )) %>%
  mutate(across(
    starts_with("Avg"),
    ~ ifelse(grepl("Return|Profit", cur_column()),
             scales::percent(., accuracy = 0.1),
             round(., 2))
  )) %>%
  rename_with(~ gsub("Avg_|_", " ", .)) %>%
  knitr::kable(align = c("l", "r"),
               caption = "5-Year Averages") %>%
  print()
## 
## 
## Table: 5-Year Averages
## 
## | Current Ratio |  Quick Ratio| Return on Assets |  Net Profit Ratio|
## |:--------------|------------:|:-----------------|-----------------:|
## |0.53           |         0.53|-4.2%             |            -20.3%|
# 5. FINANCIAL RATIOS VISUALIZATION ---------------------------------------

# Custom theme for professional plots
finance_theme <- function(base_size = 12) {
  theme_minimal(base_size = base_size) +
    theme(
      plot.title = element_text(size = rel(1.2), face = "bold", hjust = 0.5),
      axis.title = element_text(size = rel(1)),
      axis.text = element_text(size = rel(0.9)),
      legend.position = "top",
      legend.title = element_blank(),
      panel.grid.minor = element_blank(),
      plot.margin = margin(1, 1, 1, 1, "cm"),
      strip.background = element_rect(fill = "grey90", color = NA),
      strip.text = element_text(size = rel(0.9), face = "bold")
    )
}

# Convert to long format for plotting
ratios_long <- ratios %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Ratio_Type = case_when(
    Ratio %in% c("Current_Ratio", "Quick_Ratio") ~ "Liquidity",
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ "Profitability"
  ))

# A. LIQUIDITY RATIOS PLOT (Current Ratio vs Quick Ratio)
liquidity_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Liquidity"), 
                         aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = c(1, 1.5), linetype = "dashed", 
             color = c("red", "darkgreen")) +
  annotate("text", x = min(ratios$Year), y = 1.05, 
           label = "Minimum Standard (1.0)", hjust = 0, color = "red") +
  annotate("text", x = min(ratios$Year), y = 1.55, 
           label = "Healthy Threshold (1.5)", hjust = 0, color = "darkgreen") +
  labs(title = "Airtel Liquidity Position",
       subtitle = "Current Ratio vs Quick Ratio (2020-2024)",
       y = "Ratio Value",
       x = "Year") +
  scale_color_manual(values = c("Current_Ratio" = "#0066cc", "Quick_Ratio" = "#009933"),
                     labels = c("Current Ratio", "Quick Ratio")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# B. PROFITABILITY RATIOS PLOT (ROA vs Net Profit Margin)
profitability_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Profitability"), 
                             aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = 0, linetype = "solid", color = "black") +
  labs(title = "Airtel Profitability Metrics",
       subtitle = "Return on Assets vs Net Profit Margin (2020-2024)",
       y = "Percentage (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent_format(accuracy = 0.1)) +
  scale_color_manual(values = c("Return_on_Assets" = "#cc0000", "Net_Profit_Ratio" = "#ff9900"),
                     labels = c("Return on Assets", "Net Profit Margin")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# C. FACETED VIEW OF ALL RATIOS
faceted_plot <- ggplot(ratios_long, aes(x = Year, y = Value)) +
  geom_line(color = "#3366cc", size = 1.2) +
  geom_point(color = "#003366", size = 2.5) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2,
             labeller = labeller(Ratio = c(
               "Current_Ratio" = "Current Ratio",
               "Quick_Ratio" = "Quick Ratio",
               "Return_on_Assets" = "Return on Assets",
               "Net_Profit_Ratio" = "Net Profit Margin"))) +
  labs(title = "Airtel Financial Health Dashboard",
       subtitle = "Comprehensive Ratio Analysis (2020-2024)",
       y = "Ratio Value",
       x = "Year") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# D. HEATMAP VISUALIZATION
heatmap_plot <- ratios_long %>%
  mutate(Value_Formatted = case_when(
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ scales::percent(Value, accuracy = 0.1),
    TRUE ~ as.character(round(Value, 2))
  )) %>%
  ggplot(aes(x = factor(Year), y = Ratio, fill = Value)) +
  geom_tile(color = "white", size = 0.5) +
  geom_text(aes(label = Value_Formatted), color = "black", size = 3.5) +
  scale_fill_gradient2(low = "#cc0000", mid = "white", high = "#009933", 
                       midpoint = 0, na.value = "grey90") +
  labs(title = "Airtel Financial Performance Heatmap",
       subtitle = "Year-over-Year Comparison",
       x = "Year",
       y = "") +
  scale_y_discrete(labels = c("Current Ratio", "Quick Ratio", 
                              "Return on Assets", "Net Profit Margin")) +
  finance_theme()

# E. DUAL-AXIS PLOT (Liquidity + Profitability)
dual_axis_plot <- ggplot() +
  geom_line(data = filter(ratios_long, Ratio == "Current_Ratio"),
            aes(x = Year, y = Value, color = "Current Ratio"), size = 1.2) +
  geom_line(data = filter(ratios_long, Ratio == "Return_on_Assets"),
            aes(x = Year, y = Value * 10, color = "ROA (Right Axis)"), size = 1.2) +
  scale_y_continuous(
    name = "Current Ratio",
    sec.axis = sec_axis(~./10, name = "ROA", labels = scales::percent_format())
  ) +
  scale_color_manual(values = c("Current Ratio" = "#0066cc", "ROA (Right Axis)" = "#cc0000")) +
  labs(title = "Airtel Liquidity vs Profitability",
       subtitle = "Current Ratio and ROA Comparison",
       x = "Year",
       color = "Metric") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# Display all plots
print(liquidity_plot)

print(profitability_plot)

print(faceted_plot)

print(heatmap_plot)

print(dual_axis_plot)

# Save plots to files
plot_list <- list(liquidity_plot, profitability_plot, faceted_plot, heatmap_plot, dual_axis_plot)
plot_names <- c("airtel_liquidity.png", "airtel_profitability.png", "airtel_faceted.png", 
                "airtel_heatmap.png", "airtel_dual_axis.png")

walk2(plot_names, plot_list, ~ggsave(
  filename = .x,
  plot = .y,
  width = 10,
  height = 6,
  dpi = 300
))
The following is coding done for analysis of Mahanagar Telephone Nigam Limited:
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(purrr)

# 1. UPLOADING THE DATA ---------------------------------------------------
pl_data <- read_excel("E:/Tele_Com/airtel_pl.xlsx", sheet = "Sheet1")
bs_data <- read_excel("E:/Tele_Com/airtel_bs.xlsx", sheet = "Sheet1")
cf_data <- read_excel("E:/Tele_Com/airtel_cf.xlsx", sheet = "Sheet1")

# 2. DATA CLEANING FUNCTION -----------------------------------------------
clean_fin_data <- function(df) {
  df %>%
    rename(Items = 1) %>%
    filter(!is.na(Items)) %>%
    pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
    mutate(Year = as.numeric(Year),
           Value = as.numeric(Value)) %>%
    group_by(Year, Items) %>%
    summarise(Value = first(Value), .groups = "drop") %>%
    pivot_wider(names_from = "Items", values_from = "Value")
}

# 3. PROCESS AND CALCULATE RATIOS -----------------------------------------
pl_clean <- clean_fin_data(pl_data)
bs_clean <- clean_fin_data(bs_data)
cf_clean <- clean_fin_data(cf_data)

ratios <- bs_clean %>%
  left_join(pl_clean, by = "Year") %>%
  mutate(
    Current_Ratio = round(Total_Current_Assets / Total_Current_Liabilities, 2),
    Quick_Ratio = round((Total_Current_Assets - Inventories) / Total_Current_Liabilities, 2),
    Return_on_Assets = round(Profit_Loss_For_The_Period / Total_Assets, 4),
    Net_Profit_Ratio = round(Profit_Loss_For_The_Period / Total_Revenue, 4)
  ) %>%
  select(Year, Current_Ratio, Quick_Ratio, Return_on_Assets, Net_Profit_Ratio) %>%
  arrange(Year)

# 4. VIEW AND SAVE RESULTS ------------------------------------------------
cat("\nAirtel Financial Ratios (2020-2024):\n")
## 
## Airtel Financial Ratios (2020-2024):
print(as.data.frame(ratios))
##   Year Current_Ratio Quick_Ratio Return_on_Assets Net_Profit_Ratio
## 1 2020          0.63        0.63          -0.1201          -0.6381
## 2 2021          0.56        0.56          -0.0907          -0.3777
## 3 2022          0.51        0.51          -0.0127          -0.0503
## 4 2023          0.52        0.52          -0.0003          -0.0010
## 5 2024          0.43        0.43           0.0142           0.0522
# Calculate and display 5-year averages
cat("\n5-Year Average Financial Ratios for Airtel (2020-2024):\n")
## 
## 5-Year Average Financial Ratios for Airtel (2020-2024):
ratios %>%
  summarise(across(
    c(Current_Ratio, Quick_Ratio, Return_on_Assets, Net_Profit_Ratio),
    ~ mean(., na.rm = TRUE),
    .names = "Avg_{.col}"
  )) %>%
  mutate(across(
    starts_with("Avg"),
    ~ ifelse(grepl("Return|Profit", cur_column()),
             scales::percent(., accuracy = 0.1),
             round(., 2))
  )) %>%
  rename_with(~ gsub("Avg_|_", " ", .)) %>%
  knitr::kable(align = c("l", "r"),
               caption = "5-Year Averages") %>%
  print()
## 
## 
## Table: 5-Year Averages
## 
## | Current Ratio |  Quick Ratio| Return on Assets |  Net Profit Ratio|
## |:--------------|------------:|:-----------------|-----------------:|
## |0.53           |         0.53|-4.2%             |            -20.3%|
# 5. FINANCIAL RATIOS VISUALIZATION ---------------------------------------

# Custom theme for professional plots
finance_theme <- function(base_size = 12) {
  theme_minimal(base_size = base_size) +
    theme(
      plot.title = element_text(size = rel(1.2), face = "bold", hjust = 0.5),
      axis.title = element_text(size = rel(1)),
      axis.text = element_text(size = rel(0.9)),
      legend.position = "top",
      legend.title = element_blank(),
      panel.grid.minor = element_blank(),
      plot.margin = margin(1, 1, 1, 1, "cm"),
      strip.background = element_rect(fill = "grey90", color = NA),
      strip.text = element_text(size = rel(0.9), face = "bold")
    )
}

# Convert to long format for plotting
ratios_long <- ratios %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Ratio_Type = case_when(
    Ratio %in% c("Current_Ratio", "Quick_Ratio") ~ "Liquidity",
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ "Profitability"
  ))

# A. LIQUIDITY RATIOS PLOT (Current Ratio vs Quick Ratio)
liquidity_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Liquidity"), 
                         aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = c(1, 1.5), linetype = "dashed", 
             color = c("red", "darkgreen")) +
  annotate("text", x = min(ratios$Year), y = 1.05, 
           label = "Minimum Standard (1.0)", hjust = 0, color = "red") +
  annotate("text", x = min(ratios$Year), y = 1.55, 
           label = "Healthy Threshold (1.5)", hjust = 0, color = "darkgreen") +
  labs(title = "Airtel Liquidity Position",
       subtitle = "Current Ratio vs Quick Ratio (2020-2024)",
       y = "Ratio Value",
       x = "Year") +
  scale_color_manual(values = c("Current_Ratio" = "#0066cc", "Quick_Ratio" = "#009933"),
                     labels = c("Current Ratio", "Quick Ratio")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# B. PROFITABILITY RATIOS PLOT (ROA vs Net Profit Margin)
profitability_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Profitability"), 
                             aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = 0, linetype = "solid", color = "black") +
  labs(title = "Airtel Profitability Metrics",
       subtitle = "Return on Assets vs Net Profit Margin (2020-2024)",
       y = "Percentage (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent_format(accuracy = 0.1)) +
  scale_color_manual(values = c("Return_on_Assets" = "#cc0000", "Net_Profit_Ratio" = "#ff9900"),
                     labels = c("Return on Assets", "Net Profit Margin")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# C. FACETED VIEW OF ALL RATIOS
faceted_plot <- ggplot(ratios_long, aes(x = Year, y = Value)) +
  geom_line(color = "#3366cc", size = 1.2) +
  geom_point(color = "#003366", size = 2.5) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2,
             labeller = labeller(Ratio = c(
               "Current_Ratio" = "Current Ratio",
               "Quick_Ratio" = "Quick Ratio",
               "Return_on_Assets" = "Return on Assets",
               "Net_Profit_Ratio" = "Net Profit Margin"))) +
  labs(title = "Airtel Financial Health Dashboard",
       subtitle = "Comprehensive Ratio Analysis (2020-2024)",
       y = "Ratio Value",
       x = "Year") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# D. HEATMAP VISUALIZATION
heatmap_plot <- ratios_long %>%
  mutate(Value_Formatted = case_when(
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ scales::percent(Value, accuracy = 0.1),
    TRUE ~ as.character(round(Value, 2))
  )) %>%
  ggplot(aes(x = factor(Year), y = Ratio, fill = Value)) +
  geom_tile(color = "white", size = 0.5) +
  geom_text(aes(label = Value_Formatted), color = "black", size = 3.5) +
  scale_fill_gradient2(low = "#cc0000", mid = "white", high = "#009933", 
                       midpoint = 0, na.value = "grey90") +
  labs(title = "Airtel Financial Performance Heatmap",
       subtitle = "Year-over-Year Comparison",
       x = "Year",
       y = "") +
  scale_y_discrete(labels = c("Current Ratio", "Quick Ratio", 
                              "Return on Assets", "Net Profit Margin")) +
  finance_theme()

# E. DUAL-AXIS PLOT (Liquidity + Profitability)
dual_axis_plot <- ggplot() +
  geom_line(data = filter(ratios_long, Ratio == "Current_Ratio"),
            aes(x = Year, y = Value, color = "Current Ratio"), size = 1.2) +
  geom_line(data = filter(ratios_long, Ratio == "Return_on_Assets"),
            aes(x = Year, y = Value * 10, color = "ROA (Right Axis)"), size = 1.2) +
  scale_y_continuous(
    name = "Current Ratio",
    sec.axis = sec_axis(~./10, name = "ROA", labels = scales::percent_format())
  ) +
  scale_color_manual(values = c("Current Ratio" = "#0066cc", "ROA (Right Axis)" = "#cc0000")) +
  labs(title = "Airtel Liquidity vs Profitability",
       subtitle = "Current Ratio and ROA Comparison",
       x = "Year",
       color = "Metric") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# Display all plots
print(liquidity_plot)

print(profitability_plot)

print(faceted_plot)

print(heatmap_plot)

print(dual_axis_plot)

# Save plots to files
plot_list <- list(liquidity_plot, profitability_plot, faceted_plot, heatmap_plot, dual_axis_plot)
plot_names <- c("airtel_liquidity.png", "airtel_profitability.png", "airtel_faceted.png", 
                "airtel_heatmap.png", "airtel_dual_axis.png")

walk2(plot_names, plot_list, ~ggsave(
  filename = .x,
  plot = .y,
  width = 10,
  height = 6,
  dpi = 300
))
The following is coding done for analysis of Optiemus Infracom Limited.
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)

# ----------------------------
# Enhanced Data Cleaning
# ----------------------------

# Improved numeric cleaner with NA handling
clean_numeric <- function(x) {
  # Convert to character and clean
  x_clean <- gsub("[^0-9.-]", "", as.character(x))
  
  # Convert empty strings and lone hyphens to NA
  x_clean[x_clean %in% c("", "-")] <- NA
  
  # Convert to numeric with warning suppression
  suppressWarnings(as.numeric(x_clean))
}

# ----------------------------
# Data Loading and Processing
# ----------------------------

process_sheet <- function(file) {
  read_excel(file) %>%
    mutate(across(-Items, clean_numeric)) %>%
    pivot_longer(-Items, names_to = "Year", values_to = "Value") %>%
    pivot_wider(names_from = Items, values_from = Value) %>%
    mutate(Year = as.numeric(Year))
}

# Process all sheets
bs <- process_sheet("E:/Tele_Com/op_bs.xlsx")
pl <- process_sheet("E:/Tele_Com/op_pl.xlsx")

# Merge data with safe join
merged_data <- bs %>%
  inner_join(pl, by = "Year", suffix = c("_BS", "_PL"))

# ----------------------------
# Financial Ratio Calculations
# ----------------------------

result <- merged_data %>%
  mutate(
    CurrentRatio = Total_Current_Assets / Total_Current_Liabilities,
    QuickRatio = (Total_Current_Assets - Inventories) / Total_Current_Liabilities,
    ROA = `Profit_Loss_For_The_Period` / Total_Assets,
    NetProfitRatio = `Profit_Loss_For_The_Period` / Total_Revenue
  ) %>%
  select(Year, CurrentRatio, QuickRatio, ROA, NetProfitRatio)

# Calculate 5-year averages for each ratio
ratio_averages <- result %>%
  summarise(across(
    c(CurrentRatio, QuickRatio, ROA, NetProfitRatio),
    mean,
    na.rm = TRUE,
    .names = "Avg_{.col}"
  ))

# Format the results for better presentation
formatted_averages <- ratio_averages %>%
  pivot_longer(
    everything(),
    names_to = "Ratio",
    values_to = "Average_Value"
  ) %>%
  mutate(
    Ratio = gsub("Avg_", "", Ratio),
    Formatted_Value = case_when(
      Ratio %in% c("ROA", "NetProfitRatio") ~ scales::percent(Average_Value, accuracy = 0.01),
      TRUE ~ as.character(round(Average_Value, 2))
    ),
    Interpretation = case_when(
      Ratio == "CurrentRatio" & Average_Value > 1.5 ~ "Healthy (>1.5)",
      Ratio == "CurrentRatio" & Average_Value > 1 ~ "Adequate (1-1.5)",
      Ratio == "CurrentRatio" ~ "Caution (<1)",
      Ratio == "QuickRatio" & Average_Value > 1 ~ "Strong liquidity",
      Ratio == "QuickRatio" ~ "Potential liquidity concerns",
      Ratio %in% c("ROA", "NetProfitRatio") & Average_Value > 0.1 ~ "Excellent (>10%)",
      Ratio %in% c("ROA", "NetProfitRatio") & Average_Value > 0.05 ~ "Good (5-10%)",
      Ratio %in% c("ROA", "NetProfitRatio") & Average_Value > 0 ~ "Marginal (0-5%)",
      TRUE ~ "Negative profitability"
    )
  )

# Display the results
cat("\n5-Year Average Financial Ratios:\n")
## 
## 5-Year Average Financial Ratios:
formatted_averages %>%
  select(Ratio, Formatted_Value, Interpretation) %>%
  knitr::kable(align = c("l", "r", "l"), 
               caption = "5-Year Average Financial Ratios with Interpretation")
5-Year Average Financial Ratios with Interpretation
Ratio Formatted_Value Interpretation
CurrentRatio 3.1 Healthy (>1.5)
QuickRatio 3.06 Strong liquidity
ROA 4.52% Marginal (0-5%)
NetProfitRatio 4.63% Marginal (0-5%) 
# ----------------------------
# Results with NA Handling
# ----------------------------

# Print cleaned results
print(result)
## # A tibble: 5 × 5
##    Year CurrentRatio QuickRatio     ROA NetProfitRatio
##   <dbl>        <dbl>      <dbl>   <dbl>          <dbl>
## 1  2020         2.93       2.88 -0.125         -0.223 
## 2  2021         4.63       4.54  0.213          0.323 
## 3  2022         2.41       2.39  0.0345         0.0392
## 4  2023         2.41       2.40  0.0586         0.0541
## 5  2024         3.11       3.10  0.0451         0.0375
# Formatted table with NA indicators
knitr::kable(result, digits = 3, 
             caption = "Financial Ratios Analysis",
             na = "--")
Financial Ratios Analysis
Year CurrentRatio QuickRatio ROA NetProfitRatio
2020 2.926 2.885 -0.125 -0.223
2021 4.629 4.536 0.213 0.323
2022 2.407 2.386 0.035 0.039
2023 2.410 2.405 0.059 0.054
2024 3.108 3.101 0.045 0.037 
# ----------------------------
# Enhanced Plotting with NA Handling
# ----------------------------

library(ggplot2)
library(gridExtra)
library(scales)

# Convert to long format for plotting
result_long <- result %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value")

# Custom theme for consistent styling
finance_theme <- theme_minimal() +
  theme(
    plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
    axis.title = element_text(size = 12),
    axis.text = element_text(size = 10),
    legend.position = "top",
    legend.title = element_blank(),
    panel.grid.minor = element_blank()
  )

# 1. Liquidity Ratios Plot with NA handling
liquidity_plot <- ggplot(result_long %>% filter(Ratio %in% c("CurrentRatio", "QuickRatio")), 
                         aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(size = 1.2, na.rm = TRUE) +
  geom_point(size = 3, na.rm = TRUE) +
  labs(title = "Liquidity Ratios Trend",
       y = "Ratio Value",
       x = "Year") +
  scale_color_manual(values = c("CurrentRatio" = "#1f77b4", "QuickRatio" = "#ff7f0e"),
                     labels = c("Current Ratio", "Quick Ratio")) +
  finance_theme

# 2. Profitability Ratios Plot with percentage formatting
profitability_plot <- ggplot(result_long %>% filter(Ratio %in% c("ROA", "NetProfitRatio")), 
                             aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(size = 1.2, na.rm = TRUE) +
  geom_point(size = 3, na.rm = TRUE) +
  labs(title = "Profitability Ratios Trend",
       y = "Ratio Value",
       x = "Year") +
  scale_y_continuous(labels = percent_format(accuracy = 0.1)) +
  scale_color_manual(values = c("ROA" = "#2ca02c", "NetProfitRatio" = "#d62728"),
                     labels = c("Return on Assets", "Net Profit Margin")) +
  finance_theme

# 3. All Ratios Facet Plot with free scales
facet_plot <- ggplot(result_long, aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2, na.rm = TRUE) +
  geom_point(size = 2, na.rm = TRUE) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2,
             labeller = labeller(Ratio = c(
               "CurrentRatio" = "Current Ratio",
               "QuickRatio" = "Quick Ratio",
               "ROA" = "Return on Assets",
               "NetProfitRatio" = "Net Profit Margin"))) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year") +
  scale_color_brewer(palette = "Set1") +
  finance_theme +
  theme(legend.position = "none")

# 4. Heatmap-style visualization for ratio comparison
heatmap_plot <- ggplot(result_long, aes(x = factor(Year), y = Ratio, fill = Value)) +
  geom_tile(color = "white", size = 0.5) +
  geom_text(aes(label = ifelse(is.na(Value), "NA", round(Value, 2))), 
            color = "black", size = 3.5) +
  scale_fill_gradient2(low = "red", mid = "white", high = "green", 
                       midpoint = 0, na.value = "grey90") +
  labs(title = "Financial Ratios Heatmap",
       x = "Year",
       y = "Ratio") +
  scale_y_discrete(labels = c("Current Ratio", "Quick Ratio", 
                              "Return on Assets", "Net Profit Margin")) +
  finance_theme +
  theme(axis.text.x = element_text(angle = 0))

# Display plots
grid.arrange(liquidity_plot, profitability_plot, ncol = 2)

print(facet_plot)

print(heatmap_plot)

# Save plots with improved quality
ggsave("liquidity_ratios.png", liquidity_plot, width = 8, height = 5, dpi = 300)
ggsave("profitability_ratios.png", profitability_plot, width = 8, height = 5, dpi = 300)
ggsave("faceted_ratios.png", facet_plot, width = 9, height = 7, dpi = 300)
ggsave("ratios_heatmap.png", heatmap_plot, width = 8, height = 5, dpi = 300)
The following is coding done for analysis of Tata Communications Limited.
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)

# ----------------------------
# Load and Prepare Data
# ----------------------------

# Custom numeric cleaner function
clean_numeric <- function(x) {
  as.numeric(gsub("[^0-9.-]", "", x)) # Handles negative values and decimals
}

# Read and clean balance sheet
bs <- read_excel("E:/Tele_Com/tc_bs.xlsx") %>%
  mutate(across(-Items, clean_numeric)) %>%
  pivot_longer(-Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Year = as.numeric(Year))

# Read and clean profit & loss
pl <- read_excel("E:/Tele_Com/tc_pl.xlsx") %>%
  mutate(across(-Items, clean_numeric)) %>%
  pivot_longer(-Items, names_to = "Year", values_to = "Value") %>%
  pivot_wider(names_from = Items, values_from = Value) %>%
  mutate(Year = as.numeric(Year))

# Merge datasets
merged_data <- bs %>%
  left_join(pl, by = "Year")

# ----------------------------
# Calculate Financial Ratios (CORRECTED COLUMN NAMES)
# ----------------------------

result <- merged_data %>%
  mutate(
    # Current Ratio = Current Assets / Current Liabilities
    CurrentRatio = Total_Current_Assets / Total_Current_Liabilities,
    
    # Quick Ratio = (Current Assets - Inventory) / Current Liabilities
    QuickRatio = (Total_Current_Assets - Inventories) / Total_Current_Liabilities,
    
    # Return on Assets (ROA) = Net Profit / Total Assets
    ROA = `Profit/Loss_For_The_Period` / Total_Assets,
    
    # Net Profit Ratio = Net Profit / Total Revenue
    NetProfitRatio = `Profit/Loss_For_The_Period` / Total_Revenue
  ) %>%
  select(Year, CurrentRatio, QuickRatio, ROA, NetProfitRatio)

# Calculate 5-year averages for each ratio
ratio_averages <- result %>%
  summarise(
    Avg_CurrentRatio = mean(CurrentRatio, na.rm = TRUE),
    Avg_QuickRatio = mean(QuickRatio, na.rm = TRUE),
    Avg_ROA = mean(ROA, na.rm = TRUE),
    Avg_NetProfitRatio = mean(NetProfitRatio, na.rm = TRUE)
  )

# Print the averages in a clean format
cat("\n5-Year Averages of Financial Ratios:\n")
## 
## 5-Year Averages of Financial Ratios:
ratio_averages %>%
  pivot_longer(everything(), names_to = "Ratio", values_to = "Average") %>%
  mutate(Ratio = gsub("Avg_", "", Ratio),
         Average = case_when(
           Ratio %in% c("ROA", "NetProfitRatio") ~ scales::percent(Average, accuracy = 0.01),
           TRUE ~ as.character(round(Average, 2))
         )) %>%
  knitr::kable(caption = "5-Year Average Financial Ratios")
5-Year Average Financial Ratios
Ratio Average
CurrentRatio 0.73
QuickRatio 0.72
ROA 4.84%
NetProfitRatio 10.17% 
# ----------------------------
# Display Results
# ----------------------------

# Print raw results
print(result)
## # A tibble: 5 × 5
##    Year CurrentRatio QuickRatio    ROA NetProfitRatio
##   <dbl>        <dbl>      <dbl>  <dbl>          <dbl>
## 1  2020        0.650      0.634 0.0155         0.0352
## 2  2021        0.872      0.867 0.0678         0.148 
## 3  2022        1.03       1.03  0.0793         0.163 
## 4  2023        0.673      0.646 0.0436         0.0861
## 5  2024        0.429      0.422 0.0359         0.0759
# Formatted table
knitr::kable(result, digits = 3, caption = "Financial Ratios Analysis")
Financial Ratios Analysis
Year CurrentRatio QuickRatio ROA NetProfitRatio
2020 0.650 0.634 0.015 0.035
2021 0.872 0.867 0.068 0.148
2022 1.033 1.028 0.079 0.163
2023 0.673 0.646 0.044 0.086
2024 0.429 0.422 0.036 0.076 
# ----------------------------
# Plotting Financial Ratios
# ----------------------------

library(ggplot2)
library(gridExtra)

# Convert to long format for easier plotting
result_long <- result %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value")

# 1. Current Ratio and Quick Ratio Plot
liquidity_plot <- ggplot(result_long %>% filter(Ratio %in% c("CurrentRatio", "QuickRatio")), 
                         aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  labs(title = "Liquidity Ratios Trend",
       y = "Ratio Value",
       color = "Ratio Type") +
  theme_minimal() +
  theme(legend.position = "top")

# 2. ROA and Net Profit Ratio Plot
profitability_plot <- ggplot(result_long %>% filter(Ratio %in% c("ROA", "NetProfitRatio")), 
                             aes(x = Year, y = Value, color = Ratio, group = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  labs(title = "Profitability Ratios Trend",
       y = "Ratio Value",
       color = "Ratio Type") +
  theme_minimal() +
  theme(legend.position = "top")

# 3. Bar plot for all ratios in one view
all_ratios_plot <- ggplot(result_long, aes(x = factor(Year), y = Value, fill = Ratio)) +
  geom_col(position = "dodge") +
  labs(title = "Financial Ratios by Year",
       x = "Year",
       y = "Ratio Value") +
  theme_minimal() +
  theme(legend.position = "right",
        axis.text.x = element_text(angle = 45, hjust = 1))

# 4. Facet plot for individual ratio trends
facet_plot <- ggplot(result_long, aes(x = Year, y = Value)) +
  geom_line(color = "steelblue", size = 1.2) +
  geom_point(color = "steelblue", size = 3) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2) +
  labs(title = "Individual Ratio Trends",
       y = "Ratio Value") +
  theme_minimal()

# Display all plots
grid.arrange(liquidity_plot, profitability_plot, ncol = 2)

print(all_ratios_plot)

print(facet_plot)

# Optionally save plots
ggsave("liquidity_ratios.png", liquidity_plot, width = 8, height = 6)
ggsave("profitability_ratios.png", profitability_plot, width = 8, height = 6)
ggsave("all_ratios_comparison.png", all_ratios_plot, width = 10, height = 6)
ggsave("individual_ratio_trends.png", facet_plot, width = 10, height = 8)
The following is coding done for analysis of Tata Teleservices Limited.
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(purrr)

# 1. UPLOADING THE DATA ---------------------------------------------------

# Read each Excel file
pl_data <- read_excel("E:/Tele_Com/tt_pl.xlsx", sheet = "Sheet1")
bs_data <- read_excel("E:/Tele_Com/tt_bs.xlsx", sheet = "Sheet1")
cf_data <- read_excel("E:/Tele_Com/tt_cf.xlsx", sheet = "Sheet1")

# 2. CLEANING AND PREPARING THE DATA --------------------------------------

# Function to clean and transform financial data
clean_fin_data <- function(df) {
  df %>%
    rename(Items = 1) %>%
    filter(!is.na(Items)) %>%
    pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
    mutate(Year = as.numeric(Year),
           Value = as.numeric(Value)) %>%
    group_by(Year, Items) %>%
    summarise(Value = first(Value), .groups = "drop") %>%
    pivot_wider(names_from = "Items", values_from = "Value")
}

# Clean each dataset
pl_clean <- clean_fin_data(pl_data)
bs_clean <- clean_fin_data(bs_data)
cf_clean <- clean_fin_data(cf_data)

# 3. CALCULATING FINANCIAL RATIOS -----------------------------------------

# Merge balance sheet and profit/loss data
financial_data <- bs_clean %>%
  left_join(pl_clean, by = "Year")

# Calculate financial ratios
ratios <- financial_data %>%
  mutate(
    Current_Ratio = round(Total_Current_Assets / Total_Current_Liabilities, 2),
    Quick_Ratio = round((Cash_And_Cash_Equivalents + Trade_Receivables) / Total_Current_Liabilities, 2),
    Net_Profit_Margin = round(Profit_Loss_For_The_Period / Total_Revenue, 4),
    Return_on_Equity = ifelse(Total_Shareholders_Funds < 0, 
                              round(Profit_Loss_For_The_Period / Total_Shareholders_Funds, 4),
                              NA)
  ) %>%
  select(Year, Current_Ratio, Quick_Ratio, Net_Profit_Margin, Return_on_Equity) %>%
  arrange(Year)

# Calculate 5-year averages for each ratio
ratio_averages <- ratios %>%
  summarise(across(
    c(Current_Ratio, Quick_Ratio, Net_Profit_Margin, Return_on_Equity),
    ~ mean(., na.rm = TRUE),
    .names = "Avg_{.col}"
  ))

# Format and present the results
formatted_averages <- ratio_averages %>%
  pivot_longer(
    everything(),
    names_to = "Ratio",
    values_to = "Average"
  ) %>%
  mutate(
    Ratio = gsub("Avg_", "", Ratio),
    Formatted = case_when(
      Ratio %in% c("Net_Profit_Margin", "Return_on_Equity") ~ scales::percent(Average, accuracy = 0.01),
      TRUE ~ as.character(round(Average, 2))
    ),
    Benchmark = case_when(
      Ratio == "Current_Ratio" & Average > 1.5 ~ "Strong (>1.5)",
      Ratio == "Current_Ratio" & Average > 1 ~ "Adequate (1-1.5)",
      Ratio == "Current_Ratio" ~ "Weak (<1)",
      Ratio == "Quick_Ratio" & Average > 1 ~ "Strong liquidity",
      Ratio == "Quick_Ratio" ~ "Potential concerns",
      Ratio == "Net_Profit_Margin" & Average > 0.1 ~ "Excellent (>10%)",
      Ratio == "Net_Profit_Margin" & Average > 0.05 ~ "Good (5-10%)",
      Ratio == "Net_Profit_Margin" & Average > 0 ~ "Marginal (0-5%)",
      Ratio == "Return_on_Equity" & Average > 0.15 ~ "Outstanding (>15%)",
      Ratio == "Return_on_Equity" & Average > 0.1 ~ "Strong (10-15%)",
      TRUE ~ "Needs improvement"
    )
  )

# Display the results
cat("\n5-Year Average Financial Ratios:\n")
## 
## 5-Year Average Financial Ratios:
formatted_averages %>%
  select(Ratio, Formatted, Benchmark) %>%
  knitr::kable(align = c("l", "r", "l"),
               col.names = c("Financial Ratio", "5-Year Avg", "Benchmark Assessment"),
               caption = "Five-Year Average Financial Performance")
Five-Year Average Financial Performance
Financial Ratio 5-Year Avg Benchmark Assessment
Current_Ratio 0.04 Weak (<1)
Quick_Ratio 0.02 Potential concerns
Net_Profit_Margin -167.07% Needs improvement
Return_on_Equity 8.31% Needs improvement 
# VIEWING RESULTS ------------------------------------------------------
  
  # Print the calculated ratios
  cat("\nFinancial Ratios Analysis (2020-2024):\n")
## 
## Financial Ratios Analysis (2020-2024):
print(as.data.frame(ratios))
##   Year Current_Ratio Quick_Ratio Net_Profit_Margin Return_on_Equity
## 1 2020          0.05        0.02           -3.3392           0.1800
## 2 2021          0.05        0.01           -1.8700           0.0883
## 3 2022          0.04        0.01           -1.0993           0.0510
## 4 2023          0.03        0.01           -1.0222           0.0493
## 5 2024          0.04        0.03           -1.0227           0.0469
# 4. VISUALIZING FINANCIAL RATIOS -----------------------------------------

# Custom theme for consistent styling
finance_theme <- theme_minimal() +
  theme(
    plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
    axis.title = element_text(size = 12),
    axis.text = element_text(size = 10),
    legend.position = "top",
    legend.title = element_blank(),
    panel.grid.minor = element_blank(),
    plot.margin = unit(c(1, 1, 1, 1), "cm")
  )

# Convert to long format for plotting
ratios_long <- ratios %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value")

# A. LIQUIDITY RATIOS PLOT
liquidity_plot <- ggplot(ratios_long %>% 
                           filter(Ratio %in% c("Current_Ratio", "Quick_Ratio")), 
                         aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = 1, linetype = "dashed", color = "red") +
  labs(title = "Liquidity Ratios Trend",
       y = "Ratio Value",
       x = "Year") +
  scale_color_manual(values = c("Current_Ratio" = "#3498db", "Quick_Ratio" = "#2ecc71"),
                     labels = c("Current Ratio", "Quick Ratio")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme

# B. PROFITABILITY RATIOS PLOT
profitability_plot <- ggplot(ratios_long %>% 
                               filter(Ratio %in% c("Net_Profit_Margin", "Return_on_Equity")), 
                             aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  labs(title = "Profitability Ratios Trend",
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent_format(accuracy = 0.1)) +
  scale_color_manual(values = c("Net_Profit_Margin" = "#e74c3c", "Return_on_Equity" = "#9b59b6"),
                     labels = c("Net Profit Margin", "Return on Equity")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme

# C. FACETED RATIOS PLOT
faceted_plot <- ggplot(ratios_long, aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 2) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2,
             labeller = labeller(Ratio = c(
               "Current_Ratio" = "Current Ratio",
               "Quick_Ratio" = "Quick Ratio",
               "Net_Profit_Margin" = "Net Profit Margin",
               "Return_on_Equity" = "Return on Equity"))) +
  labs(title = "Financial Ratios Trend Analysis",
       y = "Ratio Value",
       x = "Year") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  scale_color_brewer(palette = "Set1") +
  finance_theme +
  theme(legend.position = "none")

# D. HEATMAP VISUALIZATION
heatmap_plot <- ggplot(ratios_long, aes(x = factor(Year), y = Ratio, fill = Value)) +
  geom_tile(color = "white", size = 0.5) +
  geom_text(aes(label = ifelse(is.na(Value), "NA", 
                               ifelse(Value > 1, round(Value, 2), 
                                      scales::percent(Value, accuracy = 0.1)))), 
            color = "black", size = 3.5) +
  scale_fill_gradient2(low = "red", mid = "white", high = "green", 
                       midpoint = 0, na.value = "grey90") +
  labs(title = "Financial Ratios Heatmap Comparison",
       x = "Year",
       y = "") +
  scale_y_discrete(labels = c("Current Ratio", "Quick Ratio", 
                              "Net Profit Margin", "Return on Equity")) +
  finance_theme

# E. BAR PLOT COMPARISON
bar_plot <- ggplot(ratios_long, aes(x = factor(Year), y = Value, fill = Ratio)) +
  geom_col(position = "dodge") +
  labs(title = "Financial Ratios by Year",
       x = "Year",
       y = "Ratio Value") +
  scale_fill_brewer(palette = "Set2") +
  finance_theme +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

# Display all plots
grid.arrange(liquidity_plot, profitability_plot, ncol = 2)

print(faceted_plot)

print(heatmap_plot)

print(bar_plot)

# Save plots with high quality
ggsave("liquidity_ratios.png", liquidity_plot, width = 8, height = 5, dpi = 300)
ggsave("profitability_ratios.png", profitability_plot, width = 8, height = 5, dpi = 300)
ggsave("faceted_ratios.png", faceted_plot, width = 9, height = 7, dpi = 300)
ggsave("ratios_heatmap.png", heatmap_plot, width = 8, height = 5, dpi = 300)
ggsave("ratios_barplot.png", bar_plot, width = 10, height = 6, dpi = 300)
The following is coding done for analysis of Vodafone Idea Limited.
# Load required packages
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(purrr)
library(scales)

# 1. UPLOADING THE DATA WITH SPECIFIED PATHS ------------------------------

# Read each Excel file using your specific file paths
pl_data <- read_excel("E:/Tele_Com/vi_pl.xlsx", sheet = "Sheet1")
bs_data <- read_excel("E:/Tele_Com/vi_bs.xlsx", sheet = "Sheet1")
cf_data <- read_excel("E:/Tele_Com/vi_cf.xlsx", sheet = "Sheet1")

# 2. CLEANING AND PREPARING THE DATA --------------------------------------

# Function to clean and transform financial data
clean_fin_data <- function(df) {
  df %>%
    # Set first column as 'Items' and remove empty rows
    rename(Items = 1) %>%
    filter(!is.na(Items)) %>%
    # Convert to long format
    pivot_longer(cols = -Items, names_to = "Year", values_to = "Value") %>%
    # Convert Year to numeric and ensure values are numeric
    mutate(Year = as.numeric(Year),
           Value = as.numeric(Value)) %>%
    # Handle duplicate values by taking the first occurrence
    group_by(Year, Items) %>%
    summarise(Value = first(Value), .groups = "drop") %>%
    # Convert back to wide format with clean column names
    pivot_wider(names_from = "Items", values_from = "Value")
}

# Clean each dataset
pl_clean <- clean_fin_data(pl_data)
bs_clean <- clean_fin_data(bs_data)
cf_clean <- clean_fin_data(cf_data)

# 3. MERGING DATA AND CALCULATING RATIOS ----------------------------------

# Merge all datasets by Year
financial_data <- bs_clean %>%
  left_join(pl_clean, by = "Year") %>%
  left_join(cf_clean, by = "Year")

# Calculate financial ratios
ratios <- financial_data %>%
  mutate(
    # Current Ratio = Current Assets / Current Liabilities
    Current_Ratio = round(Total_Current_Assets / Total_Current_Liabilities, 2),
    
    # Quick Ratio = (Current Assets - Inventories) / Current Liabilities
    Quick_Ratio = round((Total_Current_Assets - Inventories) / Total_Current_Liabilities, 2),
    
    # Return on Assets = Net Profit / Total Assets
    Return_on_Assets = Profit_Loss_For_The_Period / Total_Assets,
    
    # Net Profit Ratio = Net Profit / Total Revenue
    Net_Profit_Ratio = Profit_Loss_For_The_Period / Total_Revenue
  ) %>%
  select(Year, Current_Ratio, Quick_Ratio, Return_on_Assets, Net_Profit_Ratio) %>%
  arrange(Year)

# Calculate 5-year averages (keeping numeric for calculations)
ratio_calc <- ratios %>%
  summarise(
    Current_Ratio_Avg = mean(Current_Ratio, na.rm = TRUE),
    Quick_Ratio_Avg = mean(Quick_Ratio, na.rm = TRUE),
    ROA_Avg = mean(Return_on_Assets, na.rm = TRUE),
    Net_Profit_Margin_Avg = mean(Net_Profit_Ratio, na.rm = TRUE)
  )

# Create display version with proper formatting
ratio_display <- ratio_calc %>%
  pivot_longer(everything(), names_to = "Ratio", values_to = "Average") %>%
  mutate(
    Ratio = gsub("_Avg", "", Ratio) %>% gsub("_", " ", .),
    Formatted_Average = case_when(
      Ratio %in% c("ROA", "Net Profit Margin") ~ scales::percent(Average, accuracy = 0.01),
      TRUE ~ as.character(round(Average, 2))
    )
  )

# Display results
cat("\n5-Year Average Financial Ratios:\n")
## 
## 5-Year Average Financial Ratios:
ratio_display %>%
  select(Ratio, Formatted_Average) %>%
  rename(Average = Formatted_Average) %>%
  knitr::kable(align = c("l", "r"),
               caption = "Financial Performance Averages (5 Years)")
Financial Performance Averages (5 Years)
Ratio Average
Current Ratio 0.3
Quick Ratio 0.3
ROA -20.07%
Net Profit Margin -97.39% 
# 4. VIEWING AND SAVING RESULTS -------------------------------------------

# Print the calculated ratios (numeric version)
cat("\nVodafone Idea Financial Ratios Analysis (2020-2024):\n")
## 
## Vodafone Idea Financial Ratios Analysis (2020-2024):
print(as.data.frame(ratios))
##   Year Current_Ratio Quick_Ratio Return_on_Assets Net_Profit_Ratio
## 1 2020          0.23        0.23       -0.3195102       -1.5967193
## 2 2021          0.29        0.29       -0.2279003       -1.1040421
## 3 2022          0.35        0.35       -0.1456041       -0.7369135
## 4 2023          0.30        0.30       -0.1413970       -0.6946985
## 5 2024          0.31        0.31       -0.1688430       -0.7370070
# 5. FINANCIAL RATIOS VISUALIZATION ---------------------------------------

# Custom theme for professional plots
finance_theme <- function(base_size = 12) {
  theme_minimal(base_size = base_size) +
    theme(
      plot.title = element_text(size = rel(1.2), face = "bold", hjust = 0.5),
      axis.title = element_text(size = rel(1)),
      axis.text = element_text(size = rel(0.9)),
      legend.position = "top",
      legend.title = element_blank(),
      panel.grid.minor = element_blank(),
      plot.margin = margin(1, 1, 1, 1, "cm"),
      strip.background = element_rect(fill = "grey90", color = NA),
      strip.text = element_text(size = rel(0.9), face = "bold")
    )
}

# Convert to long format for plotting
ratios_long <- ratios %>%
  pivot_longer(-Year, names_to = "Ratio", values_to = "Value") %>%
  mutate(Ratio_Type = case_when(
    Ratio %in% c("Current_Ratio", "Quick_Ratio") ~ "Liquidity",
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ "Profitability"
  ))

# A. LIQUIDITY RATIOS PLOT
liquidity_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Liquidity"), 
                         aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = 1, linetype = "dashed", color = "red") +
  annotate("text", x = min(ratios$Year), y = 1.05, 
           label = "Minimum Standard (1.0)", hjust = 0, color = "red") +
  labs(title = "Vodafone Idea Liquidity Ratios",
       subtitle = "Current Ratio vs Quick Ratio (2020-2024)",
       y = "Ratio Value",
       x = "Year") +
  scale_color_manual(values = c("Current_Ratio" = "#3498db", "Quick_Ratio" = "#2ecc71"),
                     labels = c("Current Ratio", "Quick Ratio")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# B. PROFITABILITY RATIOS PLOT
profitability_plot <- ggplot(ratios_long %>% filter(Ratio_Type == "Profitability"), 
                             aes(x = Year, y = Value, color = Ratio)) +
  geom_line(size = 1.2) +
  geom_point(size = 3) +
  geom_hline(yintercept = 0, linetype = "solid", color = "black") +
  labs(title = "Vodafone Idea Profitability Ratios",
       subtitle = "ROA and Net Profit Margin (2020-2024)",
       y = "Ratio Value (%)",
       x = "Year") +
  scale_y_continuous(labels = scales::percent_format(accuracy = 0.1)) +
  scale_color_manual(values = c("Return_on_Assets" = "#e74c3c", "Net_Profit_Ratio" = "#9b59b6"),
                     labels = c("Return on Assets", "Net Profit Margin")) +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# C. FACETED VIEW OF ALL RATIOS
faceted_plot <- ggplot(ratios_long, aes(x = Year, y = Value)) +
  geom_line(color = "#3498db", size = 1.2) +
  geom_point(color = "#2980b9", size = 2.5) +
  facet_wrap(~Ratio, scales = "free_y", ncol = 2,
             labeller = labeller(Ratio = c(
               "Current_Ratio" = "Current Ratio",
               "Quick_Ratio" = "Quick Ratio",
               "Return_on_Assets" = "Return on Assets",
               "Net_Profit_Ratio" = "Net Profit Margin"))) +
  labs(title = "Vodafone Idea Financial Ratios Trend Analysis",
       subtitle = "2019-2023 Performance Metrics",
       y = "Ratio Value",
       x = "Year") +
  scale_x_continuous(breaks = unique(ratios$Year)) +
  finance_theme()

# D. HEATMAP VISUALIZATION
heatmap_plot <- ratios_long %>%
  mutate(Value_Formatted = case_when(
    Ratio %in% c("Return_on_Assets", "Net_Profit_Ratio") ~ scales::percent(Value, accuracy = 0.1),
    TRUE ~ as.character(round(Value, 2))
  )) %>%
  ggplot(aes(x = factor(Year), y = Ratio, fill = Value)) +
  geom_tile(color = "white", size = 0.5) +
  geom_text(aes(label = Value_Formatted), color = "black", size = 3.5) +
  scale_fill_gradient2(low = "#e74c3c", mid = "white", high = "#2ecc71", 
                       midpoint = 0, na.value = "grey90") +
  labs(title = "Vodafone Idea Financial Ratios Heatmap",
       subtitle = "Comparative Analysis Across Years",
       x = "Year",
       y = "") +
  scale_y_discrete(labels = c("Current Ratio", "Quick Ratio", 
                              "Return on Assets", "Net Profit Margin")) +
  finance_theme()

# Display all plots
print(liquidity_plot)

print(profitability_plot)

print(faceted_plot)

print(heatmap_plot)

# Save plots to files
plot_list <- list(liquidity_plot, profitability_plot, faceted_plot, heatmap_plot)
plot_names <- c("vi_liquidity.png", "vi_profitability.png", "vi_faceted.png", "vi_heatmap.png")

walk2(plot_names, plot_list, ~ggsave(
  filename = .x,
  plot = .y,
  width = 10,
  height = 6,
  dpi = 300
))

The above codes conclude our project.