Credit Risk Audit: Dual-Perspective Modeling in the Tourism Sector

1. Business Context

ViaSul Experiências Turísticas is a fictional tourism agency serving as a Business Case. Starting in 2021, the company adopted an aggressive commercial expansion strategy. Aiming to boost sales of high-ticket products (like Patagonia expeditions and all-inclusive resorts in Búzios), the company drastically loosened access to credit by introducing Recurring Installments (a model where monthly installments are debited without tying up the customer's total credit card limit).

The business strategy succeeded in increasing sales volume; however, it required careful risk monitoring. The major business challenge was avoiding the trap of diluting risks. If the board analyzed defaults solely from a macro perspective, the volume of healthy sales could mask the default rates of premium products. On the other hand, analyzing only the recurring model would ignore the company's capacity to generate healthy revenue. My role was to build a data model that allowed the board to analyze both lenses simultaneously.

2. Project Objectives

This project required modeling focused on Business Scoping, with the following strategic objectives:

• Data Consolidation (ETL): Expand the analytical view by integrating 100% of the contracts (Traditional and Recurring) to form a global picture of the company's health.
• Risk Isolation by Modality: Build metrics that allowed for fiduciary "drill-down," mathematically separating the risk of upfront/traditional operations from the risk of recurring installments.
• Cohort Maturity Analysis (Aging): Evaluate the financial gap by discounting future receivables, focusing the loss calculation strictly on what was already past due.

3. Data, Modeling, and Audit (ETL)

The initial extraction encompassed a raw transactional CSV database containing 469,468 payment records, mapping 169,591 unique contracts (purchases) over years of operation.

The Consolidation Strategy in ETL: The initial analytical scope of many credit projects tends to isolate only the at-risk contracts to test their viability. However, to expand this analytical view and understand the relative weight of this risk within the business as a whole, I used Python scripts (Pandas) to ensure the incorporation of over 119,000 contracts from the "Traditional" model (upfront/PIX/standard credit card) into the global revenue calculation.

import pandas as pd
import numpy as np

# Revenue consolidation integrating the Traditional model for the global view
purchases['Valor_a_Pagar'] = np.where(
    purchases['Total Parcelas Recorrentes'] > 0,
    purchases['Total Parcelas Recorrentes'] * purchases['Valor'],
    purchases['Valor'] # Ensures accounting of upfront/PIX payments
)

print(f"Consolidated Global Revenue: BRL {purchases['Valor_a_Pagar'].sum():,.2f}")

With a 100% intact transactional base, I modeled the database in Power BI following Ralph Kimball's guidelines (Star Schema). Two heavy fact tables (fParcelamentos, centralizing the macro view of sales, and fPagamentos, detailing the installment ledger) were integrated with four dimensions (Customers, Products, Locations, and dCalendar).

To ensure accounting precision, I developed robust DAX measures using time intelligence (DATEDIFF) to calculate only the Expected Revenue of Mature Cohorts (Aging). Furthermore, risk segregation required advanced techniques to isolate the recurring model's behavior and avoid the context overwrite generated by the internal filters of the base measures:

// To isolate the risk of the Recurring model without suffering from "Context Overwrite" 
// (generated by the mature cohort calculation), I used the KEEPFILTERS function.

% Inadimplencia Recorrente = 
CALCULATE(
    [% Inadimplencia Global],
    KEEPFILTERS(fParcelamentos[Total Parcelas Recorrentes] > 0)
)

4. Dashboard Overview

The analytical interface was designed with a focus on Data Storytelling for the C-Level, translating technical complexity into easily consumable strategic direction.

• Macro View (Cards): Consolidate the audited BRL 171.43 Million in sales, BRL 160.96 Million in liquidations, and the Absolute Cash Gap (Loss) of BRL 10.47 Million in mature cohorts.
• Cash Decomposition (Waterfall): A waterfall chart that immediately highlights the corrosion of Expected Revenue by monthly defaults.
• Risk by Modality (Bars): The clustered bar chart brutally exposes the difference in financial behavior between the Traditional and Recurring portfolios, keeping both analyses side by side.
• Portfolio Performance (Heatmap): Detailed matrix by product. Conditional formatting surgically points out (in red alerts) where premium products are concentrating losses.

Integrated Perspectives

8. Data Quality & Analytical Limitations

The biggest technical challenge of the project was ensuring Integrity in Risk Segregation. Mathematically diluting a 26% (recurring) default rate into 0% (traditional) could mask the financial drain and give a false sense of security. The governance of the DAX code was vital to keep these two complementary readings side by side. The main limitation imposed on this current model is its descriptive and diagnostic nature: it evaluates the mature cohort with surgical precision, but bases forecasts for immature cohorts on historical behavior, not individualizing the isolated probability of each contract that has yet to mature.

9. Impact & Strategic Recommendations

With the dual analytical perspective implemented, the dashboard provides the board with guidelines that shape the future of credit granting:

10. Conclusion

This project symbolizes the importance of Business Scoping in Data Analytics. An isolated analysis focuses on the viability of a specific product (recurring installments); a consolidated analysis answers for the global health of the company. One reading does not invalidate the other; they complement each other to form executive intelligence.

By uniting the disciplines of Data Analytics, Managerial Accounting, and Risk Management, the model provides leadership with the ability to expand sales without assuming unsustainable leverage. The next step in technical maturity for this organization will be the implementation of Machine Learning models, aiming to predict individual default probability even before the travel package is issued.