Celtic Capital Power BI Report

Requirement Document for Celtic Capital Power BI Report

1. Introduction

The Celtic Capital Power BI Report is a financial dashboard that tracks real estate investment performance, account values, returns by month, and client portfolio balances. The report provides investors and stakeholders with insights into account performance, equity appreciation, and investment growth trends.

2. Objectives

Monitor Account Performance – Track net account value, equity appreciation, and returns by month.
Analyze RP Growth & Balances – Understand real property (RP) growth rates and total RP balances.
Client Portfolio Insights – View investment performance at an individual client level.
Track Gross Appreciation & Performance Fees – Measure financial growth and applied charges.

3. Data Components

3.1 Account Summary

Net Account Value – Total account valuation.
Equity Appreciation – Growth in portfolio value.
Returns by Month – Monthly financial performance.
Gross Appreciation & Performance Fee – Charges associated with investment returns.

3.2 Client Portfolio Performance

RP Balance by Client Name – Real Property investment per client.
Total RP Balance Growth – Portfolio expansion over time.
RP Processing Fees – Costs deducted from RP investments.

3.3 Investment Trends & Growth

Returns Analysis by Month – Month-over-month investment returns.
RP Growth Rate (%) – Percentage change in real property value.
Top Performing Clients – High-value portfolios with growth trends.

4. Data Sources

Celtic Capital CRM System – Provides investor account information.
Financial Portfolio Management System – Tracks equity appreciation and gross appreciation.
Investment Performance Logs – Historical records of RP balance and returns.

5. Performance Metrics

Net Account Value ($)
Monthly Returns Trend (%)
RP Growth Rate (%)
Sum of RP Balance ($)
Total Investment Appreciation ($)
Client Portfolio Performance
Real Property Processing Fees ($)

6. Expected Outputs

Power BI Dashboard with Financial KPIs
Portfolio Growth Trends by Month
Client-Wise Investment Breakdown
Real Property Investment Performance
Equity & Gross Appreciation Insights

7. Use Cases

Investors & Shareholders – Monitor financial growth and account performance.
Wealth Management Teams – Track client portfolios and real property investments.
Financial Analysts – Analyze equity appreciation trends.
Celtic Capital Executives – Monitor overall investment health.

8. Constraints & Assumptions

Data Accuracy Dependent on CRM Updates – Regular updates ensure real-time reporting.
Power BI Access Required – Restricted to authorized personnel.
Market Fluctuations Impact Performance Trends – External factors influence financial growth.

Celtic Capital Final Download

9. Project Completion Method

Step 1: Data Extraction (SQL Queries)

SQL queries retrieve financial data from Celtic Capital’s investment and portfolio database.

1. Extracting Account Summary Data

sqlCopyEditSELECT 
    AccountID, 
    ClientName, 
    NetAccountValue, 
    EquityAppreciation, 
    GrossAppreciation, 
    PerformanceFee, 
    RPBalance
FROM Account_Performance
WHERE ReportMonth = '2023-09-01';

2. Extracting Monthly Returns Data

sqlCopyEditSELECT 
    AccountID, 
    ReportMonth, 
    MonthlyReturns
FROM Returns_History
WHERE ReportMonth >= DATEADD(YEAR, -1, GETDATE());

3. Extracting RP Balance by Client

sqlCopyEditSELECT 
    ClientName, 
    SUM(RPBalance) AS TotalRPBalance
FROM Client_Investments
GROUP BY ClientName;

Step 2: Data Transformation (Power Query – M Language)

Power Query cleans and structures the data for Power BI visualization.

Power Query Transformations

Remove DuplicatesmCopyEditTable.Distinct(Source)
Replace Null ValuesmCopyEditTable.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"EquityAppreciation", "GrossAppreciation"})
Format Date ColumnsmCopyEditTable.TransformColumnTypes(Source, {{"ReportMonth", type date}})
Calculate Growth RatemCopyEditTable.AddColumn(Source, "GrowthRate", each ([NetAccountValue] - [EquityAppreciation]) / [NetAccountValue], type number)

Step 3: Data Modeling & Relationships

Tables are linked in Power BI to create relationships between datasets:

Table 1 (Account Summary)	Table 2 (Returns History)	Table 3 (Client Investments)	Relationship
`AccountID`	`AccountID`	`ClientName`	One-to-Many
`ReportMonth`	`ReportMonth`	`ClientName`	One-to-Many

A Date Table is created to enable time-based filtering.

Step 4: Data Visualization (DAX Measures & Calculations)

DAX measures track key financial insights in Power BI.

1. Total Net Account Value

DAXCopyEditTotalNetAccountValue = SUM('Account Summary'[NetAccountValue])

2. Monthly Returns Trend

DAXCopyEditMonthlyReturns = 
CALCULATE(
    SUM('Returns History'[MonthlyReturns]), 
    DATESMTD('Date'[Date])
)

3. RP Growth Rate

DAXCopyEditRPGrowthRate = 
DIVIDE(
    SUM('Client Investments'[TotalRPBalance]) - 
    SUM('Client Investments'[TotalRPBalance]), 
    SUM('Client Investments'[TotalRPBalance]), 
    0
) * 100

4. Performance Fee Calculation

DAXCopyEditPerformanceFee = SUM('Account Summary'[PerformanceFee])

5. Equity Appreciation Analysis

DAXCopyEditEquityAppreciation = AVERAGE('Account Summary'[EquityAppreciation])

Step 5: Power BI Report Development

Power BI Visuals Used

KPI Cards – Net Account Value, RP Growth Rate, Monthly Returns.
Bar Charts – Monthly Performance Trends.
Pie Chart – RP Balance Distribution by Client.
Line Chart – Equity & Gross Appreciation Over Time.
Table Visual – Client Portfolio Breakdown.

10. Deployment & Maintenance

Automated Data Refresh – SQL queries update data daily.
Power BI Service Deployment – Reports accessible to investors and executives.
Data Accuracy Monitoring – Regular validation of financial trends.
Enhancements – Future insights and additional performance tracking.

Conclusion

This structured Power BI Report enables real-time monitoring of investment performance, portfolio growth, and real property investments, ensuring data-driven financial decision-making.

Three sample data files based on the Celtic Capital Power BI Report.

Investment_Performance_Sample_Data Download

Client_Portfolio_Sample_Data Download

Investment_Returns_Sample_Data Download