library(readxl)
library(dplyr)
library(ggplot2)
library(scales)
library(knitr)
library(car)
library(lmtest)
library(sandwich)This report presents a demand and pricing analysis for EL-Lab Diagnostic Centre, Lagos, Nigeria, using monthly billing data from January 2022 to October 2025 (46 months). The analysis focuses on the FBC - FULL BLOOD COUNT (whole blood) test — the most frequently performed service with 22,396 records — making it the most suitable candidate for robust demand estimation.
The effective price per test (derived from net amount paid divided by quantity) rose steadily from ₦2,500 in early 2022 to ₦7,500 by 2025, while monthly test volume peaked at 625 in mid-2023 before declining to around 400 by late 2025. A log-log regression model estimated the price elasticity of demand at β = -0.1479, indicating inelastic demand (|β| < 1). This means that price increases lead to proportionally smaller reductions in quantity, so total revenue rises when prices go up. Based on this finding, EL-Lab is advised to proceed with a moderate price increase. A simulated 10% price increase is estimated to raise monthly revenue by approximately ₦204,926 (8.36%) while reducing monthly test volume by only about 7 patients. Limitations including autocorrelation, multicollinearity between price and time, and data quality issues are discussed, along with suggested extensions for more rigorous future analysis.
All 46 monthly sheets from the Excel file are loaded, converted to character type to avoid type-conflict errors across sheets, and combined into a single data frame.
sheets <- excel_sheets("2022 - 2025.xlsx")
all_sheets_clean <- lapply(sheets, function(s) {
df <- read_excel("2022 - 2025.xlsx", sheet = s)
df$sheet_name <- s
df <- mutate(df, across(everything(), as.character))
return(df)
})
raw_data <- bind_rows(all_sheets_clean)
cat("Total sheets loaded:", length(sheets), "\n")## Total sheets loaded: 46
## Total rows (raw): 375245
## Total columns: 41
Relevant columns are selected and renamed, total/summary rows are removed, dates are parsed, and numeric columns are converted. Rows with missing or zero price/quantity are dropped.
clean_data <- raw_data %>%
select(
sheet_name,
bill_date = `Bill Date`,
test_name = `Test Name`,
quantity = `Test Quantity`,
price = `Test MRP`,
net_amount = `Test Net Amount`
) %>%
filter(
!is.na(test_name),
!grepl("^Total", test_name, ignore.case = TRUE),
!is.na(bill_date),
bill_date != "NA"
) %>%
mutate(
bill_date = as.Date(bill_date, format = "%d/%m/%Y"),
quantity = as.numeric(quantity),
price = as.numeric(price),
net_amount = as.numeric(net_amount)
) %>%
filter(!is.na(price), !is.na(quantity), quantity > 0, price > 0)
cat("Rows after cleaning:", nrow(clean_data), "\n")## Rows after cleaning: 282986
The top 20 most frequent tests are examined to select a service with sufficient observations across all months.
clean_data %>%
count(test_name, sort = TRUE) %>%
head(20) %>%
kable(caption = "Top 20 Most Frequent Tests",
col.names = c("Test Name", "Count"))| Test Name | Count |
|---|---|
| FBC - FULL BLOOD COUNT (whole blood) | 22396 |
| MP - MALARIA PARASITE | 14232 |
| WIDAL REACTION | 12374 |
| EUCR-SERUM ELECTROLYTES, UREA, CREATININE | 10143 |
| HIV I and II TEST | 9066 |
| 2D - PELVIC SCAN - (Trans-Abd) | 7293 |
| 2D - OBSTETRICS SCAN - ANC | 6971 |
| URINE CULTURE (M/C/S) | 6892 |
| LIPID PROFILE (SERUM) | 6769 |
| PREGNANCY TEST (BLOOD) - BPT | 6739 |
| URINALYSIS ROUTINE | 6603 |
| AUTOMATED URINE M/C/S | 6152 |
| FBS - FASTING BLOOD SUGAR | 5920 |
| HBSAG-HEPATITIS B SURFACE ANTIGEN SCREENING | 5914 |
| ADVANCED MALARIA PARASITE TEST | 5699 |
| 2D - ABDOMINO-PELVIC SCAN | 5661 |
| LFT - LIVER FUNCTION TEST | 5603 |
| PSA-TOTAL (SERUM) | 5321 |
| HBA1C - GLYCOSYLATED HAEMOGLOBIN | 4326 |
| CHEST X-RAY (PA) | 4166 |
FBC - FULL BLOOD COUNT (whole blood) is selected as the focus service with 22,396 records — the highest frequency across all 46 months, ensuring a complete and consistent monthly time series.
Since Test MRP (list price) shows no variation across
months for FBC (fixed at ₦9,000 throughout), the effective
price is computed as net_amount / quantity, which
captures actual amounts paid after discounts. This provides meaningful
price variation for demand estimation.
fbc_data <- clean_data %>%
filter(test_name == "FBC - FULL BLOOD COUNT (whole blood)")
fbc_monthly <- fbc_data %>%
mutate(
month_year = format(bill_date, "%Y-%m")
) %>%
filter(!is.na(net_amount), net_amount > 0) %>%
mutate(effective_price = net_amount / quantity) %>%
filter(effective_price > 0) %>%
group_by(month_year) %>%
summarise(
total_quantity = sum(quantity, na.rm = TRUE),
median_effective_price = median(effective_price, na.rm = TRUE),
.groups = "drop"
) %>%
arrange(month_year) %>%
mutate(
time_index = row_number(),
month = substr(month_year, 6, 7),
season = factor(month),
log_Q = log(total_quantity + 1),
log_P = log(median_effective_price),
time_index_c = time_index - mean(time_index)
)
cat("Monthly observations:", nrow(fbc_monthly), "\n")## Monthly observations: 46
fbc_monthly %>%
select(month_year, time_index, total_quantity, median_effective_price) %>%
rename(
`Month` = month_year,
`Time Index` = time_index,
`Total Quantity` = total_quantity,
`Effective Price (₦)` = median_effective_price
) %>%
kable(caption = "Monthly Aggregates: FBC Full Blood Count") %>%
kableExtra::kable_styling(
bootstrap_options = c("striped", "hover", "condensed"),
full_width = FALSE
) %>%
kableExtra::scroll_box(height = "400px")| Month | Time Index | Total Quantity | Effective Price (₦) |
|---|---|---|---|
| 2022-01 | 1 | 458 | 2500 |
| 2022-02 | 2 | 402 | 2500 |
| 2022-03 | 3 | 443 | 2500 |
| 2022-04 | 4 | 438 | 3500 |
| 2022-05 | 5 | 469 | 3500 |
| 2022-06 | 6 | 503 | 3500 |
| 2022-07 | 7 | 447 | 3500 |
| 2022-08 | 8 | 434 | 3500 |
| 2022-09 | 9 | 425 | 4000 |
| 2022-10 | 10 | 524 | 4000 |
| 2022-11 | 11 | 502 | 3400 |
| 2022-12 | 12 | 435 | 4000 |
| 2023-01 | 13 | 436 | 4000 |
| 2023-02 | 14 | 485 | 4000 |
| 2023-03 | 15 | 504 | 4000 |
| 2023-04 | 16 | 511 | 4000 |
| 2023-05 | 17 | 576 | 4000 |
| 2023-06 | 18 | 592 | 4000 |
| 2023-07 | 19 | 613 | 5000 |
| 2023-08 | 20 | 624 | 5000 |
| 2023-09 | 21 | 531 | 5000 |
| 2023-10 | 22 | 571 | 5000 |
| 2023-11 | 23 | 558 | 5000 |
| 2023-12 | 24 | 550 | 5000 |
| 2024-01 | 25 | 683 | 4125 |
| 2024-02 | 26 | 525 | 5000 |
| 2024-03 | 27 | 501 | 5400 |
| 2024-04 | 28 | 496 | 5400 |
| 2024-05 | 29 | 435 | 5400 |
| 2024-06 | 30 | 566 | 5400 |
| 2024-07 | 31 | 568 | 5400 |
| 2024-08 | 32 | 575 | 5400 |
| 2024-09 | 33 | 498 | 5400 |
| 2024-10 | 34 | 485 | 5400 |
| 2024-11 | 35 | 515 | 5400 |
| 2024-12 | 36 | 423 | 5400 |
| 2025-01 | 37 | 372 | 7500 |
| 2025-02 | 38 | 377 | 7500 |
| 2025-03 | 39 | 425 | 7500 |
| 2025-04 | 40 | 410 | 7500 |
| 2025-05 | 41 | 419 | 7500 |
| 2025-06 | 42 | 403 | 7500 |
| 2025-07 | 43 | 399 | 7500 |
| 2025-08 | 44 | 382 | 7500 |
| 2025-09 | 45 | 385 | 7500 |
| 2025-10 | 46 | 422 | 7500 |
ggplot(fbc_monthly, aes(x = as.Date(paste0(month_year, "-01")),
y = total_quantity)) +
geom_line(colour = "#2c7bb6", linewidth = 1) +
geom_point(colour = "#2c7bb6", size = 2) +
geom_smooth(method = "loess", se = TRUE, colour = "#d7191c",
linetype = "dashed") +
labs(title = "Monthly FBC Tests Performed (Jan 2022 – Oct 2025)",
x = "Month", y = "Total Tests") +
scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))Demand grew steadily from early 2022, peaking at approximately 625 tests in mid-2023, before declining through 2024–2025. Monthly fluctuations suggest seasonal patterns, justifying the inclusion of seasonal dummies in the model.
ggplot(fbc_monthly, aes(x = as.Date(paste0(month_year, "-01")),
y = median_effective_price)) +
geom_line(colour = "#1a9641", linewidth = 1) +
geom_point(colour = "#1a9641", size = 2) +
labs(title = "Median Effective Price – FBC (Jan 2022 – Oct 2025)",
x = "Month", y = "Effective Price (₦)") +
scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
scale_y_continuous(labels = comma) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))The effective price increased in discrete upward steps — from ₦2,500 in early 2022 to ₦7,500 by January 2025 — reflecting periodic pricing revisions by EL-Lab. A brief dip occurred in early 2024 before a sharp increase in 2025.
scale_factor <- max(fbc_monthly$total_quantity) /
max(fbc_monthly$median_effective_price)
ggplot(fbc_monthly, aes(x = as.Date(paste0(month_year, "-01")))) +
geom_line(aes(y = total_quantity, colour = "Quantity"), linewidth = 1) +
geom_line(aes(y = median_effective_price * scale_factor,
colour = "Effective Price"),
linewidth = 1, linetype = "dashed") +
scale_y_continuous(
name = "Total Quantity",
sec.axis = sec_axis(~ . / scale_factor,
name = "Effective Price (₦)", labels = comma)
) +
scale_colour_manual(
values = c("Quantity" = "#2c7bb6", "Effective Price" = "#1a9641")
) +
scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
labs(title = "FBC: Quantity vs Effective Price Over Time",
x = "Month", colour = "") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1),
legend.position = "top")The dual-axis chart reveals a clear inverse relationship: as the price line rises (particularly the sharp jump in early 2025), the quantity line falls. This visual pattern provides initial evidence of price-sensitive demand.
fbc_monthly %>%
summarise(
`Mean Quantity` = round(mean(total_quantity), 1),
`Median Quantity` = round(median(total_quantity), 1),
`SD Quantity` = round(sd(total_quantity), 1),
`Mean Price (₦)` = round(mean(median_effective_price), 0),
`Median Price (₦)` = round(median(median_effective_price), 0),
`SD Price (₦)` = round(sd(median_effective_price), 0)
) %>%
kable(caption = "Summary Statistics: FBC Full Blood Count")| Mean Quantity | Median Quantity | SD Quantity | Mean Price (₦) | Median Price (₦) | SD Price (₦) |
|---|---|---|---|---|---|
| 484.7 | 485 | 74.4 | 5055 | 5000 | 1537 |
fbc_monthly %>%
mutate(year = substr(month_year, 1, 4)) %>%
group_by(year) %>%
summarise(
`Avg Monthly Quantity` = round(mean(total_quantity), 1),
`Avg Effective Price (₦)` = round(mean(median_effective_price), 0),
Months = n()
) %>%
kable(caption = "Year-on-Year Comparison: FBC (2022–2025)")| year | Avg Monthly Quantity | Avg Effective Price (₦) | Months |
|---|---|---|---|
| 2022 | 456.7 | 3367 | 12 |
| 2023 | 545.9 | 4500 | 12 |
| 2024 | 522.5 | 5260 | 12 |
| 2025 | 399.4 | 7500 | 10 |
The year-on-year table confirms the inverse trend: as average price rose from ₦3,367 (2022) to ₦7,500 (2025), average monthly quantity fell from 545.9 (2023 peak) to 399.4 (2025). This pattern is consistent with downward-sloping demand.
A log-log demand model is estimated:
\[\ln(Q_t) = \alpha + \beta \ln(P_t) + \delta S_t + \epsilon_t\]
where \(Q_t\) is monthly quantity, \(P_t\) is median effective price, \(S_t\) is a vector of monthly seasonal dummies, and \(\beta\) is the price elasticity of demand.
The time trend variable was excluded after diagnostic checks revealed severe multicollinearity with log price (VIF > 10 for both), since price itself trended upward over time and effectively proxies for the time trend.
##
## Call:
## lm(formula = log_Q ~ log_P + season, data = fbc_monthly)
##
## Residuals:
## Min 1Q Median 3Q Max
## -0.20095 -0.10961 -0.03841 0.11289 0.36088
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 7.3983902 0.6575771 11.251 7.81e-13 ***
## log_P -0.1479082 0.0782599 -1.890 0.0676 .
## season02 -0.0611814 0.1119932 -0.546 0.5885
## season03 -0.0063152 0.1120539 -0.056 0.9554
## season04 -0.0047272 0.1125557 -0.042 0.9668
## season05 0.0148889 0.1125557 0.132 0.8956
## season06 0.0951572 0.1125557 0.845 0.4040
## season07 0.0810554 0.1130988 0.717 0.4786
## season08 0.0703322 0.1130988 0.622 0.5383
## season09 -0.0041587 0.1135029 -0.037 0.9710
## season10 0.0824892 0.1135029 0.727 0.4725
## season11 0.1104345 0.1210326 0.912 0.3682
## season12 0.0005352 0.1213064 0.004 0.9965
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 0.1583 on 33 degrees of freedom
## Multiple R-squared: 0.1881, Adjusted R-squared: -0.1071
## F-statistic: 0.6372 on 12 and 33 DF, p-value: 0.7953
The estimated price elasticity is β = -0.1479, meaning a 1% increase in price leads to a 0.1479% decrease in quantity demanded. Since |β| < 1, demand is inelastic.
vif_result <- as.data.frame(vif(demand_model)) vif_result$Variable <- rownames(vif_result) rownames(vif_result) <- NULL vif_result <- vif_result[, c("Variable", colnames(vif_result)[colnames(vif_result) != "Variable"])] kable(vif_result, caption = "Variance Inflation Factors")
After removing the time index, VIF values for log_P and season are within acceptable ranges. Seasonal dummies show VIF ≈ 1, confirming no multicollinearity concern there.
##
## Durbin-Watson test
##
## data: demand_model
## DW = 0.38094, p-value = 2.851e-10
## alternative hypothesis: true autocorrelation is greater than 0
The Durbin-Watson statistic of 0.38 (p < 0.001) indicates strong positive autocorrelation in the residuals — expected in monthly time series data. Newey-West robust standard errors are applied to correct for this.
##
## t test of coefficients:
##
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 7.3983902 1.3334390 5.5484 3.655e-06 ***
## log_P -0.1479082 0.1658179 -0.8920 0.3789
## season02 -0.0611814 0.0753830 -0.8116 0.4228
## season03 -0.0063152 0.0964301 -0.0655 0.9482
## season04 -0.0047272 0.1043091 -0.0453 0.9641
## season05 0.0148889 0.1173127 0.1269 0.8998
## season06 0.0951572 0.0990523 0.9607 0.3437
## season07 0.0810554 0.1176144 0.6892 0.4955
## season08 0.0703322 0.1177300 0.5974 0.5543
## season09 -0.0041587 0.1190336 -0.0349 0.9723
## season10 0.0824893 0.1086015 0.7596 0.4529
## season11 0.1104345 0.0893455 1.2360 0.2252
## season12 0.0005352 0.0539804 0.0099 0.9921
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
With robust standard errors, the elasticity estimate (β = -0.1479) becomes statistically insignificant (p = 0.379). This reflects a limitation of the data — price changed in only a few discrete steps over 46 months, providing limited variation to precisely identify the elasticity. Nevertheless, the sign and magnitude are economically meaningful and consistent with theory.
Residuals are approximately normally distributed (Q-Q plot), with no strong non-linear patterns in the Residuals vs Fitted plot. Observation 25 (January 2024 — the month of the price dip) appears as a mildly influential point but does not distort the overall fit.
## Price Elasticity (β): -0.1479
## |β| = 0.1479 → Demand is INELASTIC (|β| < 1)
## Recommendation: INCREASE PRICE — revenue will rise.
Since |β| = 0.1479 < 1, demand is inelastic: a price increase causes a proportionally smaller reduction in quantity, so total revenue increases. EL-Lab is advised to proceed with a moderate price increase.
baseline_price <- mean(fbc_monthly$median_effective_price)
baseline_quantity <- mean(fbc_monthly$total_quantity)
baseline_revenue <- baseline_price * baseline_quantity
scenarios <- data.frame(
Scenario = c("Baseline (0%)", "+5% Price", "+10% Price", "+20% Price"),
price_increase = c(0, 0.05, 0.10, 0.20)
) %>%
mutate(
`New Price (₦)` = round(baseline_price * (1 + price_increase), 0),
`Est. Quantity` = round(baseline_quantity * (1 + beta * price_increase), 1),
`Est. Revenue (₦)` = round(`New Price (₦)` * `Est. Quantity`, 0),
`Revenue Change (₦)` = `Est. Revenue (₦)` - round(baseline_revenue, 0),
`Rev Change (%)` = round((`Revenue Change (₦)` / baseline_revenue) * 100, 2)
) %>%
select(-price_increase)
kable(scenarios, caption = "Revenue Simulation: Price Increase Scenarios")| Scenario | New Price (₦) | Est. Quantity | Est. Revenue (₦) | Revenue Change (₦) | Rev Change (%) |
|---|---|---|---|---|---|
| Baseline (0%) | 5055 | 484.7 | 2450158 | 184 | 0.01 |
| +5% Price | 5308 | 481.1 | 2553679 | 103705 | 4.23 |
| +10% Price | 5560 | 477.5 | 2654900 | 204926 | 8.36 |
| +20% Price | 6066 | 470.3 | 2852840 | 402866 | 16.44 |
A 10% price increase is estimated to raise monthly revenue by ₦204,818 (+8.36%) while reducing monthly volume by only ~7 tests. Even a 20% increase generates substantially higher revenue with modest demand loss.
sim_data <- data.frame(price_change_pct = seq(-20, 30, by = 1)) %>%
mutate(
new_price = baseline_price * (1 + price_change_pct / 100),
new_quantity = baseline_quantity * (1 + beta * price_change_pct / 100),
new_revenue = new_price * new_quantity
)
ggplot(sim_data, aes(x = price_change_pct, y = new_revenue)) +
geom_line(colour = "#2c7bb6", linewidth = 1.2) +
geom_vline(xintercept = 0, linetype = "dashed", colour = "grey40") +
geom_vline(xintercept = 10, linetype = "dotted", colour = "#d7191c") +
geom_point(data = filter(sim_data, price_change_pct == 10),
aes(x = price_change_pct, y = new_revenue),
colour = "#d7191c", size = 4) +
annotate("text", x = 11,
y = filter(sim_data, price_change_pct == 10)$new_revenue,
label = "+10% scenario", hjust = 0, colour = "#d7191c", size = 3.5) +
labs(title = "Estimated Revenue Under Various Price Change Scenarios",
x = "Price Change (%)", y = "Estimated Revenue (₦)") +
scale_y_continuous(labels = comma) +
theme_minimal()The upward-sloping revenue curve confirms that under inelastic demand, any price increase generates higher total revenue. The relationship is approximately linear within the range shown, reflecting the small magnitude of the elasticity.
Endogeneity: EL-Lab may set prices partly in response to patient volume, creating reverse causality. An instrumental variable (IV) approach would produce cleaner estimates.
Multicollinearity: Price and time trend are highly correlated (both rose steadily), making it difficult to isolate the pure price effect from underlying demand growth.
Statistical insignificance: With only 46 monthly observations and price changing in a few discrete steps, the model lacks sufficient variation to produce a statistically significant elasticity estimate. More granular data (e.g., daily or patient-level) would improve precision.
Omitted variables: Marketing spend, competitor pricing, insurance coverage changes, and macroeconomic conditions (e.g., inflation) are not captured in the model.
Autocorrelation: Strong serial correlation (DW = 0.38) suggests time series structure that OLS does not fully account for. A dynamic model (e.g., ARIMA with covariates) would be more appropriate.
Suggested extensions: - A/B testing: Implement controlled price experiments across patient cohorts to obtain cleaner causal estimates. - Segmented analysis: Estimate separate elasticities for insurance vs. out-of-pocket patients, or by referral source. - Competitor pricing data: Incorporate prices from competing diagnostic centres in Lagos to model cross-price elasticity.
## R version 4.5.1 (2025-06-13 ucrt)
## Platform: x86_64-w64-mingw32/x64
## Running under: Windows 10 x64 (build 19045)
##
## Matrix products: default
## LAPACK version 3.12.1
##
## locale:
## [1] LC_COLLATE=English_United States.utf8
## [2] LC_CTYPE=English_United States.utf8
## [3] LC_MONETARY=English_United States.utf8
## [4] LC_NUMERIC=C
## [5] LC_TIME=English_United States.utf8
##
## time zone: Africa/Lagos
## tzcode source: internal
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] sandwich_3.1-1 lmtest_0.9-40 zoo_1.8-15 car_3.1-5 carData_3.0-6
## [6] knitr_1.50 scales_1.4.0 ggplot2_4.0.2 dplyr_1.1.4 readxl_1.4.5
##
## loaded via a namespace (and not attached):
## [1] sass_0.4.10 generics_0.1.4 xml2_1.4.1 stringi_1.8.7
## [5] lattice_0.22-7 digest_0.6.37 magrittr_2.0.4 evaluate_1.0.5
## [9] grid_4.5.1 RColorBrewer_1.1-3 fastmap_1.2.0 Matrix_1.7-3
## [13] cellranger_1.1.0 jsonlite_2.0.0 Formula_1.2-5 mgcv_1.9-3
## [17] viridisLite_0.4.2 textshaping_1.0.4 jquerylib_0.1.4 abind_1.4-8
## [21] cli_3.6.5 rlang_1.1.6 splines_4.5.1 withr_3.0.2
## [25] cachem_1.1.0 yaml_2.3.10 tools_4.5.1 kableExtra_1.4.0
## [29] vctrs_0.6.5 R6_2.6.1 lifecycle_1.0.4 stringr_1.6.0
## [33] pkgconfig_2.0.3 pillar_1.11.1 bslib_0.9.0 gtable_0.3.6
## [37] glue_1.8.0 systemfonts_1.3.1 xfun_0.53 tibble_3.3.0
## [41] tidyselect_1.2.1 rstudioapi_0.17.1 farver_2.1.2 nlme_3.1-168
## [45] htmltools_0.5.8.1 labeling_0.4.3 rmarkdown_2.30 svglite_2.2.2
## [49] compiler_4.5.1 S7_0.2.1
Analysis prepared for EL-Lab Diagnostic Centre. Data is anonymised and used for academic purposes only.