Microfinance Performance Dashboard with R
Maria Consuelo Mangonon
2025-06-06
Introduction
This document presents an exploratory data analysis (EDA) of a sampled subset of microfinance data, focusing specifically on records from the year 2024. The primary objective is to understand initial trends and patterns in key performance indicators (KPIs) to inform the development of an interactive dashboard.
# Load the dataset
microfinance <- readRDS("~/Microfinance-Mangonon/dataset.rds")
# Check structure
str(microfinance)## 'data.frame': 14875 obs. of 43 variables:
## $ Identifier : int 1880 2 3 4 5 1881 7 8 9 1882 ...
## $ Branch_code : int 266 266 387 594 282 387 309 393 634 594 ...
## $ Branch_name : chr " Baguio 2" "Baguio 2" "La Trinidad" "Tuba" ...
## $ Department : chr "Operations" "Operations" "Operations" "Operations" ...
## $ No_of_credit_officer : int 3 3 3 2 3 3 3 3 2 2 ...
## $ Total_member : int 1062 1086 750 388 546 735 763 645 373 419 ...
## $ Deposit_balance : num 6230627 6226967 2548977 1452749 2441626 ...
## $ Total_borrower : int 995 1035 720 356 506 708 675 612 340 390 ...
## $ Loan_amount : num 30478000 30520000 20469000 8244000 11943000 ...
## $ PAR_1_30_amount : num 143222 217455 100351 51972 1416 ...
## $ PAR_1_30_borrower : int 8 11 5 2 1 2 6 11 1 4 ...
## $ PAR_31_60_amount : num 146379 106799 122532 23959 9847 ...
## $ PAR_31_60_borrower : int 5 5 8 4 2 4 5 2 0 0 ...
## $ PAR_61_90_amount : num 52000 106818 56694 123774 927 ...
## $ PAR_61_90_borrower : int 4 4 6 11 1 4 1 6 0 2 ...
## $ PAR_91_180_amount : num 260529 215850 231220 143693 52737 ...
## $ PAR_91_180_borrower : int 12 18 17 19 10 17 9 11 3 15 ...
## $ PAR_181_365_amount : num 237605 243038 461475 200591 53645 ...
## $ PAR_181_365_borrower : int 20 22 45 26 9 40 18 8 2 36 ...
## $ PAR_above_365_amount : num 2904929 2734415 3697509 449441 595126 ...
## $ PAR_above_365_borrower : int 334 321 343 67 99 359 327 101 43 70 ...
## $ Portfolio_at_risk : num 3744664 3624374 4669781 993430 713698 ...
## $ Past_due_ratio : num 0.22 0.21 0.46 0.2 0.12 0.44 0.35 0.12 0.1 0.17 ...
## $ Portfolio_at_risk_par_or_olb: num 0.24 0.23 0.47 0.22 0.12 0.45 0.36 0.13 0.1 0.18 ...
## $ Monthly_income : num 574102 646696 270242 174073 267786 ...
## $ As_of_month : chr "Apr" "Jan" "Jan" "Jan" ...
## $ As_of_year : chr "2024" "2024" "2024" "2024" ...
## $ CreatedAt : POSIXct, format: "2024-05-29 22:23:00" "2024-05-11 20:46:08" ...
## $ UpdatedAt : POSIXct, format: "2024-06-11 14:32:00" "2024-05-11 20:46:08" ...
## $ Division : chr "Division 1" "Division 1" "Division 1" "Division 1" ...
## $ District : chr "District 01" "District 01" "District 01" "District 01" ...
## $ Area : chr "Area 001" "Area 001" "Area 001" "Area 001" ...
## $ Branch_opening_date : Date, format: "2010-04-05" "2010-04-05" ...
## $ status : chr "TRUE" "TRUE" "TRUE" "TRUE" ...
## $ street : chr "#18A6" "#18A6" "Lola IPPI Building" "157" ...
## $ barangay : chr "Queen of Peace Road" "Queen of Peace Road" "KC 57 Upper Cruz" "Lower Poblacion" ...
## $ City_municipality : chr "Baguio City" "Baguio City" "La Trinidad" "Tuba" ...
## $ province : chr "Benguet" "Benguet" "Benguet" "Benguet" ...
## $ region : chr "Cordillera Administrative Region" "Cordillera Administrative Region" "Cordillera Administrative Region" "CORDILLERA ADMINISTRATIVE REGION" ...
## $ postal_code : chr "2600" "2600" "2601" "2603" ...
## $ Latitude : num 16.4 16.4 16.5 16.4 17.4 ...
## $ Longitude : num 121 121 121 121 120 ...
## $ As_Of_Date : Date, format: "2024-04-01" "2024-01-01" ...Data Sampling
# Set seed for reproducibility
set.seed(123)
# Simple random sampling - 20% of data
sample_data <- microfinance %>% sample_frac(0.2)
# Optional: View sample size
nrow(sample_data)## [1] 2975We applied simple random sampling to retain a manageable and representative 20% of the original dataset, maintaining variability and business relevance for 2024 analysis.
Focus on 2024 Data
# Filter for 2024
sample_2024 <- sample_data %>%
filter(year(As_Of_Date) == 2024)
# Check monthly distribution
table(month(sample_2024$As_Of_Date))##
## 1 2 3 4 5 6 7 8 9 10 11 12
## 110 126 115 125 161 149 130 141 141 160 116 123Initial Business Questions
We begin with the following questions:
How many active members and borrowers are there per month?
What is the monthly trend in loan disbursement?
How does income and deposit balance change over time?
Are there any signs of portfolio risk clustering in certain months?
Monthly Number of Members and Borrowers
library(dplyr)
library(lubridate)
monthly_members <- sample_2024 %>%
mutate(month = month(As_Of_Date, label = TRUE)) %>% # Replace `date_column` with your actual date field
group_by(month) %>%
summarise(
Members = sum(Total_member, na.rm = TRUE), # Replace with actual column name
Borrowers = sum(Total_borrower, na.rm = TRUE) # Replace with actual column name
)
DT::datatable(monthly_members)scale_factor <- max(monthly_members$Members) / max(monthly_members$Borrowers)
ggplot(monthly_members, aes(x = month)) +
geom_bar(aes(y = Members), stat = "identity", fill = "steelblue") +
geom_line(aes(y = Borrowers * scale_factor, group = 1), color = "darkred") +
scale_y_continuous(
name = "Members",
sec.axis = sec_axis(~./scale_factor, name = "Borrowers")
) +
labs(title = "Monthly Members and Borrowers")
Monthly Loan Disbursement
loan_trend <- sample_2024 %>%
group_by(month = lubridate::month(As_Of_Date, label = TRUE)) %>%
summarise(Total_Loans = sum(Loan_amount))
ggplot(loan_trend, aes(x = month, y = Total_Loans)) +
geom_col(fill = "darkgreen") +
labs(title = "Monthly Loan Disbursement")
Deposit Balance & Income Trend
deposit_income <- sample_2024 %>%
group_by(month = lubridate::month(As_Of_Date, label = TRUE)) %>%
summarise(Deposits = sum(Deposit_balance), Income = sum(Monthly_income))
ggplot(deposit_income, aes(x = month)) +
geom_line(aes(y = Deposits, color = "Deposits")) +
geom_line(aes(y = Income, color = "Income")) +
labs(title = "Deposits vs Income Over 2024")## `geom_line()`: Each group consists of only one observation.
## ℹ Do you need to adjust the group aesthetic?
## `geom_line()`: Each group consists of only one observation.
## ℹ Do you need to adjust the group aesthetic?