Key Roles
1. Leadership
2. Advisors
3. Operations
4. Compliance
5. Marketing (Indirect Stakeholders)
Key Performance Indicators
1. Client KPIs
a. Growth
b. Segmentations
c. Experience
2. Revenue
a. Advisory
b. Planning
c. AUM-Driven
3. Operational KPIs
a. Advisor Capacity
b. Client Service Associate Capacity
c. Workflow Efficiency
d. Technology
4. Compliance KPIs
a. Documentation
b. Review & Audit
c. Risk
Existing Reports & Gaps
1. Existing Reports
a. Reports
• AUM & Revenue Reports
• Advisor Book of Business Snapshots
• Compliance Review Logs
• Onboarding Status Trackers
• Marketing Lead Reports
b. Gaps
• These reports exist, but are siloed, inconsistent, and lack integration
2. Missing Reports
a. Executive-Level
• Executive KPI Dashboard
• Capacity & Staffing Dashboard
• Forecast vs Actual Performance Report
b. Advisor-Level
• Advisor Productivity Dashboard
• Advisor Task Mix Report
c. Operational-Level
• CSA Throughput & Service Level Agreement (SLA) Dashboard
• Workflow Efficiency Dashboard
d. Operational-Level
• Compliance KPI Dashboard
• Risk & Exception Trend Report
e. Gaps
• Capacity strain, workflow breakdowns, compliance pressure, lack of standardization
3. Duplicate Reports
a. Reports
• AUM & Revenue Reports
• Onboarding Status Reports
• Client Lists & Segmentation Reports
• Compliance Documentation Checks
b. Gaps
• Creates inconsistent workflows, unnecessary rework, and confusion
Model Spreadsheet
Dimension Tables
This section provides a high‑level snapshot of the BI Model dimensions data, using the data and KPIs available in the generated dataset. Each table contains sample data to demonstrate how the underlying model is structured. While the current model is limited to yearly values and a subset of operational fields, the BI framework is designed to incorporate daily, monthly, and event‑level data once connected to real RIA systems. This preview allows an understanding of how the data flows into the model. The data, and overall model will expand as richer data becomes available.
DimDate
| Date | Year | Quarter | Month | Month Name | Quarter Name |
|---|---|---|---|---|---|
| 1/31/2013 | 2013 | Q1 | 1 | January | Q1 2013 |
| 2/28/2013 | 2013 | Q1 | 2 | February | Q1 2013 |
| 3/31/2013 | 2013 | Q1 | 3 | March | Q1 2013 |
| … | … | … | … | … | … |
| 12/31/2031 | 2031 | Q4 | 12 | December | Q4 2031 |
DimClient
| Client ID | Household Size | Segment (Core / Premium / Private Client) | Advisor ID | CSA ID | Referral Source |
|---|---|---|---|---|---|
| NWP0001 | 1 | Private Client | A02 | CSA01 | Website |
| NWP0002 | 5 | Core | A09 | CSA07 | CPA |
| NWP0003 | 2 | Private Client | A01 | CSA02 | Seminar |
| … | … | … | … | … | … |
| NWP0725 | 6 | Core | A09 | CSA03 | Center of Influence |
DimAdvisor
| Advisor ID | Name | Role (Lead / Associate) | Start Date | Team Assignment |
|---|---|---|---|---|
| A01 | ||||
| A02 | ||||
| A03 | ||||
| … | … | … | … | … |
| A10 |
DimCSA
| CSA ID | Name | Workflow Ownership | Tenure |
|---|---|---|---|
| CSA01 | |||
| CSA02 | |||
| CSA03 | |||
| … | … | … | … |
| CSA07 |
DimAccount
| Account ID | Custodian | Account Type | Status (Open / Pending / Closed) |
|---|---|---|---|
DimCompliance
| Reviewer ID | Role | Review Type | Regulatory Category |
|---|---|---|---|
| CR01 | |||
| CR02 | |||
| CR03 |
Fact Tables
This section provides a high‑level snapshot of the BI Fact Tables, using the measurable events and calculating KPIs from data available in the generated dataset. Each fact table includes a small sample to demonstrate how the data operate within the model. Once connected to real RIA systems, such as custodial feeds, CRM activity logs, planning software exports, and workflow automation tools, the Fact Tables will support daily, monthly, and event‑level entries. This preview illustrates how quantitative data flows into the model today and how each fact table will expand to deliver granular performance insights, operational analytics, and KPI calculations as the firm’s reporting maturity increases.
FactTabClientGrowth
| Date | New Clients | Lost Clients | Net New Clients | Total Clients | Referral Clients | Advisor Sourced Clients | Digital Leads Converted | Acquisition Clients | Running Total Clients | Advisors | Clients/ Advisors |
|---|---|---|---|---|---|---|---|---|---|---|---|
| … | … | … | … | … | … | … | … | … | … | … | … |
| 10/31/2013 | 11 | 11 | 57 | 57 | 2 | 29 | |||||
| 11/30/2013 | 10 | 10 | 67 | 67 | 2 | 34 | |||||
| 12/31/2013 | 7 | 7 | 74 | 74 | 2 | 37 | |||||
| … | … | … | … | … | |||||||
| 12/31/2031 |
FactAUM&Rev
| Date | Total AUM | Advisory Rev | Planning Rev | Net New AUM | Market Return AUM | Effective Fee Rate | Revenue/ Client | AUM/ Advisor | Revenue/ Advisor |
|---|---|---|---|---|---|---|---|---|---|
| … | … | … | … | … | … | … | … | … | … |
| 11/30/2013 | |||||||||
| 12/31/2013 | $205,503,895 | $1,798,159 | 0.875% | $24,299 | $102,751,948 | $899,080 | |||
| … | … | … | … | … | |||||
| 12/31/2014 | $237,940,866 | $2,081,983 | 0.875% | $23,659 | $118,970,433 | $1,040,992 | |||
| … | … | … | … | … | … | … | … | … | … |
| 12/31/2031 |
FactAdvisorActivity
| Date | Advisor ID | Meetings Held | Plans Delivered | Plan Updates | New Clients Sourced | Referrals Generated | Hours Admin | Hours Planning | Hours Meetings |
|---|---|---|---|---|---|---|---|---|---|
| 1/31/2013 | A01 | ||||||||
| 1/31/2013 | A02 | ||||||||
| 1/31/2013 | A03 | ||||||||
| … | … | … | … | … | … | … | … | … | … |
| 12/31/2031 | A09 | ||||||||
| 12/31/2031 | A10 |
FactOperationWorkflow
| Date | Onboarding Cycle Time | Account Transfer Cycle Time | NIGO Count | Rework Count | Tasks Completed | Tasks Past SLA | CRM Task Completion Rate | Manual Entry Count |
|---|---|---|---|---|---|---|---|---|
| 1/31/2013 | ||||||||
| 2/28/2013 | ||||||||
| 3/31/2013 | ||||||||
| … | … | … | … | … | … | … | … | … |
| 12/31/2031 |
FactComplianceReview
| Date | Files Reviewed | Documentation Completion Rate | Meeting Notes Compliance | Audit Exceptions | Repeat Exceptions | Review Cycle Time | CRM Task Completion Rate | Manual Entry Count |
|---|---|---|---|---|---|---|---|---|
| 1/31/2013 | ||||||||
| 2/28/2013 | ||||||||
| 3/31/2013 | ||||||||
| … | … | … | … | … | … | … | … | … |
| 12/31/2031 |
Key Performance Indicator Calculations
Below is a comprehensive look at the BI Model’s KPIs, their calculation formulas and the source of the data used for the Business Intelligence Model for Northwest Wealth Partners.
| KPI Name | Description | Formula | Source Fact Table |
|---|---|---|---|
| Total AUM | Total assets under management across all clients for the selected period. | SUM(FactAUMRevenue[AUM]) | FactAUMRevenue |
| YoY AUM Growth | Percentage change in AUM compared to the same month one year earlier. | (SUMIFS(FactAUMRevenue[AUM], FactAUMRevenue[Date], EOMONTH(TODAY(),0)) – SUMIFS(FactAUMRevenue[AUM], FactAUMRevenue[Date], EOMONTH(TODAY(),-12))) / SUMIFS(FactAUMRevenue[AUM], FactAUMRevenue[Date], EOMONTH(TODAY(),-12)) | FactAUMRevenue |
| Net New AUM | Month‑over‑month change in AUM after accounting for contributions, withdrawals, and market movement. | SUMIFS(FactAUMRevenue[AUM], FactAUMRevenue[Date], EOMONTH(TODAY(),0)) – SUMIFS(FactAUMRevenue[AUM], FactAUMRevenue[Date], EOMONTH(TODAY(),-1)) | FactAUMRevenue |
| Revenue | Total advisory fees generated based on AUM multiplied by the firm’s fee schedule. | SUMPRODUCT(FactAUMRevenue[AUM], FactAUMRevenue[FeeRate]) | FactAUMRevenue |
| Revenue/Advisor | Average revenue generated per advisor, used to assess productivity and workload distribution. | SUMPRODUCT(FactAUMRevenue[AUM], FactAUMRevenue[FeeRate]) / COUNTA(DimAdvisor[AdvisorID]) | FactAUMRevenue + DimAdvisor |
| Revenue/Client | Average revenue generated per client, used to evaluate client profitability and segmentation. | SUMPRODUCT(FactAUMRevenue[AUM], FactAUMRevenue[FeeRate]) / SUM(FactClientGrowth[ActiveClients]) | FactAUMRevenue + FactClientGrowth |
| Total Clients | Total number of active clients in the selected month. | SUM(FactClientGrowth[ActiveClients]) | FactClientGrowth |
| New Clients | Number of clients added during the selected month. | SUM(FactClientGrowth[NewClients]) | FactClientGrowth |
| Lost Clients | Number of clients who terminated or became inactive during the selected month. | SUM(FactClientGrowth[LostClients]) | FactClientGrowth |
| Net Client Growth | New clients minus lost clients for the selected month. | SUM(FactClientGrowth[NewClients]) – SUM(FactClientGrowth[LostClients]) | FactClientGrowth |
| YoY Client Growth | Percentage change in total active clients compared to the same month one year earlier. | (SUMIFS(FactClientGrowth[ActiveClients], FactClientGrowth[Date], EOMONTH(TODAY(),0)) – SUMIFS(FactClientGrowth[ActiveClients], FactClientGrowth[Date], EOMONTH(TODAY(),-12))) / SUMIFS(FactClientGrowth[ActiveClients], FactClientGrowth[Date], EOMONTH(TODAY(),-12)) | FactClientGrowth |
| Clients/Advisor | Average number of clients assigned to each advisor, used for capacity and staffing analysis. | SUM(FactClientGrowth[ActiveClients]) / COUNTA(DimAdvisor[AdvisorID]) | FactClientGrowth + DimAdvisor |
| Advisor Capacity Utilization | Percentage of advisor capacity used, calculated as clients per advisor divided by the 80‑client capacity threshold. | (SUM(FactClientGrowth[ActiveClients]) / COUNTA(DimAdvisor[AdvisorID])) / 80 | FactClientGrowth + DimAdvisor |
| Advisor Workload | Total advisor activity volume for the month, including meetings, reviews, and planning deliverables. | SUM(FactAdvisorActivity[Meetings]) + SUM(FactAdvisorActivity[Reviews]) + SUM(FactAdvisorActivity[PlansDelivered]) | FactAdvisorActivity |
| Advisor Activity Score | Weighted productivity score combining meetings, reviews, and plans delivered to measure advisor output. | SUM(FactAdvisorActivity[Meetings]*1 + FactAdvisorActivity[Reviews]*2 + FactAdvisorActivity[PlansDelivered]*3) | FactAdvisorActivity |
| Advisor Productivity Trend | Month‑over‑month change in advisor activity score to identify productivity increases or declines. | (SUMIFS(FactAdvisorActivity[ActivityScore], FactAdvisorActivity[Date], EOMONTH(TODAY(),0)) – SUMIFS(FactAdvisorActivity[ActivityScore], FactAdvisorActivity[Date], EOMONTH(TODAY(),-1))) | FactAdvisorActivity |
| Onboarding Cycle Time | Average number of days required to complete the onboarding process for new clients. | AVERAGE(FactOperationsWorkflow[OnboardingCycleTime]) | FactOperationsWorkflow |
| Transfer Cycle Time | Average number of days required to complete account transfers. | AVERAGE(FactOperationsWorkflow[TransferCycleTime]) | FactOperationsWorkflow |
| NIGO Rate | Percentage of submitted paperwork returned “Not In Good Order,” indicating quality and accuracy issues. | SUM(FactComplianceReview[NIGOCount]) / SUM(FactComplianceReview[FilesReviewed]) | FactComplianceReview |
| Rework Rate | Percentage of tasks requiring correction or rework, used to measure operational efficiency. | SUM(FactOperationsWorkflow[ReworkCount]) / SUM(FactOperationsWorkflow[TasksCompleted]) | FactOperationsWorkflow |
| SLA Breach Rate | Percentage of tasks completed outside their defined service‑level agreement timeframe. | SUM(FactOperationsWorkflow[TasksPastSLA]) / SUM(FactOperationsWorkflow[TasksCompleted]) | FactOperationsWorkflow |
| Documentation Completion Rate | Percentage of required documentation completed accurately and on time. | SUM(FactComplianceReview[DocumentationCompletionRate]) | FactComplianceReview |
| Meeting Notes Compliance | Percentage of advisor meeting notes that meet compliance standards. | SUM(FactComplianceReview[MeetingNotesCompliance]) | FactComplianceReview |
| Audit Exception Rate | Percentage of files reviewed that contain compliance exceptions. | SUM(FactComplianceReview[AuditExceptions]) / SUM(FactComplianceReview[FilesReviewed]) | FactComplianceReview |
| Manual Entry Volume | Total number of manual data entries performed, used as a risk and efficiency indicator. | SUM(FactOperationsWorkflow[ManualEntryCount]) | FactOperationsWorkflow |
| Advisor Staffing Requirement | Number of advisors required to support projected client growth based on the 80‑client capacity threshold. | SUM(FactClientGrowth[ProjectedClients]) / 80 | FactClientGrowth |
| CSA Staffing Requirement | Number of CSAs required to support projected client growth based on the 115‑client capacity threshold. | SUM(FactClientGrowth[ProjectedClients]) / 115 | FactClientGrowth |
| Forecasted Revenue | Projected advisory fee revenue based on forecasted AUM and fee schedule. | SUMPRODUCT(FactAUMRevenue[ProjectedAUM], FactAUMRevenue[FeeRate]) | FactAUMRevenue |
| Forecasted AUM Growth | Percentage increase in AUM between the baseline year and the forecast horizon. | (ProjectedAUM_2031 – ProjectedAUM_2026) / ProjectedAUM_2026 | FactAUMRevenue |
KPI Definitions
Clear, standardized KPI definitions ensure consistent measurement across reporting periods and support reliable decision‑making. Each KPI is defined based on the firm’s available data and aligned with industry norms:
1. Total Clients – The number of active client households at year‑end
2. New Clients – Households added during the reporting period
3. Net Client Growth – Year‑over‑year change in Total Clients driven by new household acquisition
4. AUM – Total assets under management at period end, inclusive of market movement and new inflows
5. Revenue – Calculated using the firm’s fee schedule applied to AUM
6. Acquisition Channel Totals – Count of new households by referral, digital, COI, or other acquisition sources
7. Service Tier Totals – Number of households assigned to each service tier (Core, Enhanced, Premier)
KPI Ownership
KPI ownership clarifies accountability and ensures each metric is actively monitored, maintained, and acted upon:
1. Client KPIs (Total Clients, New Clients, Net Growth)
• Owner: Client Services Associates (CSAs)
2. AUM & Revenue KPIs
• Owner: CEO / Managing Partner – Founder 1
3. Acquisition Channel KPIs
• Owner: Digital Marketing Specialists (Marketing Team)
4. Service Tier KPIs
• Owner: COO / Director of Advisor Services – Founder 2
Clear ownership ensures each KPI has a responsible steward who maintains data quality, interprets trends, and drives improvements.
Reporting Packages & Reviews
A structured reporting cadence ensures leadership, advisors, operations, compliance, and marketing receive timely, actionable insights that support both short‑term decisions and long‑term strategic planning.
1. Weekly Reporting Package
• Owner: Operations Specialists
• Delivered by: Inputs from CSAs, Lead Advisors, Marketing Team
2. Monthly Reporting Package
• Owner: Collective Leadership (CEO / Managing Partner & COO / Director of Advisor Services)
• Delivered by: Operations Specialists, CSAs, Lead Advisors, Marketing Team
3. Quarterly Business Review (QBR)
• Owner: Collective Leadership (CEO, COO, CFO, CCO, Advisor Services Director)
• Delivered by: Lead Advisors, Operations Specialists, Compliance Reviewers, Marketing Team
4. Annual Strategic Review
• Owner: CEO / Managing Partner & COO / Director of Advisor Services
• Delivered by: All Departments (Advisors, Operations, Compliance, Marketing)