Requirement Document for FID Connectivity Power BI Report

1. Introduction

1.1 Purpose

The FID Connectivity Power BI Report provides data-driven insights into opportunities, fund activity, deal summaries, and diverse segment summaries. The report is intended for financial analysts, investment professionals, and business executives to track fund investments, opportunities, and deal progress in a structured and interactive format.

1.2 Scope

The report aggregates and visualizes financial data related to FID commitments, opportunities by LOB, deal details, and active funds. The key functionalities include:

• Monitoring total opportunities
• Tracking status of deals
• Analyzing fund commitments by segment and location
• Providing insights into most active funds
• Summarizing financial activities over time

---

2. Key Features & Functionalities

2.1 Dashboard Components

1. Total Opportunities Overview
   • Displays the total number of opportunities (e.g., 355)
   • Categorization by LOB (Line of Business) (e.g., CIB, WFSC, MMB)
   • Interactive filtering options
2. Opportunities by LOB
   • Visual representation of opportunities by LOB
   • Provides a breakdown of business units (e.g., ABLL, WIM, Other WFC, etc.)
3. Deal Status Summary
   • Status distribution: Active, Closed, Lost
   • Summary statistics (e.g., Active: 21, Closed: 160, Lost: 2)
4. Most Active Funds
   • Displays ranked list of funds based on activity
   • Includes fund name and the number of associated deals (e.g., LBC: 62, Cyprium: 15)
5. LTM Deals Per Month
   • Historical trend visualization of deals per month over time (e.g., since 2005)
6. Deal Summary
   • Detailed table containing:
     • Fund Name
     • Tier Classification
     • RM (Relationship Manager)
     • Coverage Banker
     • Location (e.g., Austin, TX)
7. Deal Details
   • Status breakdown with details:
     • Date, Company, Opportunity, Referred to, Amount, Product, Comments
   • Example:
     • Active | 3/13/23 | Fund line of credit | MMB – Texas | Michael Friesch | TM Reach out to Michael for update
     • Closed | CRI Investment Banking | $50,000,000 | August 21 Survey Response
8. Diverse Segments Summary
   • Fund commitments categorized by primary location and segment
   • Identification of diverse fund ownership (e.g., Woman-led, Minority-led)
   • Example:
     • Avante | $5,000,000 | CA | Woman-led | Kaitlyn Hull
     • Centerfield | $7,000,000 | IN | Minority | Michael Parent

---

3. Data Sources & Integration

• Data Sources:
  • Internal financial databases
  • Investment fund records
  • Business intelligence systems
• Integration:
  • Direct Power BI connections to SQL-based financial data stores
  • Scheduled data refresh and update mechanism
  • User authentication and role-based access control

---

4. User Access & Permissions

• Roles & Access Levels:
  • Executives & Senior Management: Full access to all dashboards and reports
  • Investment Analysts: Read-only access with filtering options
  • Relationship Managers: Access limited to their assigned deals and clients

---

5. Reporting & Visualization Requirements

• Interactivity:
  • Filter options for different time periods
  • Drill-down capabilities on fund details
• Visual Elements:
  • Bar charts for opportunity distribution
  • Line graphs for LTM deals
  • Tabular views for deal summaries
• Data Refresh Rate:
  • Weekly updates
  • On-demand refresh for executives

---

6. Performance & Security Considerations

• Performance Optimization:
  • Data caching for improved load time
  • Indexing high-volume financial records
• Security Measures:
  • Role-based access control
  • Encryption of sensitive financial data
  • Compliance with financial data governance standards

---

7. Project Completion Method

7.1 SQL Queries

• Extract raw financial data from transactional databases
• Use JOIN, GROUP BY, and WHERE clauses to filter and aggregate fund data
• Example:

SELECT FundName, SUM(InvestmentAmount) AS TotalInvestment FROM FundTransactions WHERE InvestmentDate >= '2023-01-01' GROUP BY FundName

7.2 Power Query (M Language)

• Transform raw SQL data into structured tables
• Perform data cleaning, removing null values, and formatting columns
• Merge multiple data sources for comprehensive insights
• Example:

let Source = Sql.Database("ServerName", "DatabaseName"), FilteredRows = Table.SelectRows(Source, each [Status] <> null) in FilteredRows

7.3 DAX (Data Analysis Expressions)

• Create calculated columns and measures for analytics
• Example Measures:

Total Deals = COUNTROWS(DealsTable) Closed Deals % = DIVIDE(COUNTROWS(FILTER(DealsTable, DealsTable[Status] = "Closed")), COUNTROWS(DealsTable), 0)

• Implement time intelligence functions for trend analysi

sYTD Investment = CALCULATE(SUM(FundTransactions[InvestmentAmount]), DATESYTD(FundTransactions[InvestmentDate]))

---

8. Future Enhancements

• Machine Learning Integration: Predictive insights into fund performance
• Automated Alerts: Email notifications for high-value deal closures
• Mobile Accessibility: Optimized UI for mobile users

---

9. Conclusion

The FID Connectivity Power BI Report is a critical tool for financial tracking and decision-making. By providing an interactive, real-time view of fund activities, opportunities, and deal progress, this report enhances investment strategy and financial oversight.

sample data files for project:

1. Fund Transactions Data – Contains fund names, investment amounts, dates, and statuses.
2. Deals Table Data – Contains deal details such as company, opportunity type, referred entity, and amount.
3. LOB Opportunities Data – Contains opportunities by Line of Business (LOB).