Sapphire Business Tech designed an end-to-end portfolio planning platform where advisors can define multi-layer investment profiles, simulate “New Money” allocations, compare suggested vs. current positions (by custodian/bank), and share a Power BI client report — all orchestrated by a secure Power Apps application backed by Dataverse and automated with Power Automate.
Project Highlights
- Industry: Financial Advisory & Wealth Management
- Departments Impacted: Advisory, Compliance, Back Office, Sales
- Estimated Time Saved: 25–35 hours per week (setup + ongoing operations)
- Tools Used: Power Apps (Model-Driven + Canvas), Dataverse, Power Automate, Power BI, Excel (import), OneDrive/SharePoint
- Project Duration: 6 business days (MVP)
Development Story
Advisors struggled to scale portfolio construction using spreadsheets. Our team reimagined the experience in Power Platform: a Power Apps app to register profiles and rules (3-tier taxonomy), a New Money simulator that proposes an allocation split automatically, and a Portfolio Comparison workspace that contrasts the suggested mix with current holdings by custodian and overall consolidated position.
Data flows to Power BI for client-facing analytics, enabling branded PDF exports. The platform brings security roles, audit trails, and a modular data model to evolve into a full CRM-grade wealth solution.
Dashboards & Features
Profile Designer (3 tiers):
- Level 1: Fixed Income / Equities
- Level 2: Yield Type (e.g., Pre, Post, Inflation) tied to Level 1
- Level 3: Subcategory (e.g., Government, Private) — add unlimited sub-types
- Client Profiles: Multi-profile support with risk tolerance, horizons, liquidity flags
New Money Engine: Enter investable cash → instant suggested portfolio split per profile rules
Position Onboarding: Capture current holdings by bank/custodian; attach statements
Portfolio Comparison: vs. each bank and vs. consolidated client position
Compliance Guardrails: Drift thresholds, concentration checks, suitability alerts
Document Kit: One-click Power BI client report (PDF) + Terms/Disclaimers
Workflow Automation: Notifications, approval routing, and change log via Power Automate
Key Results
- 1-click client portfolio proposal with configurable guardrails
- Standardized taxonomy across advisors; reduced manual consolidation
- Clear comparison between suggested and as-is holdings (per bank & consolidated)
- Executive analytics with branded Power BI dashboards and PDF exports
Technical Deep Dive
Architecture Overview
– Data Layer (Dataverse):
– InvestorProfile (Name, Risk, Horizon, Liquidity, Owner)
– ProfileRule (L1 Class, L2 YieldType, L3 SubCategory, Min/Target/Max %)
– Client (KYC fields, suitability checklist)
– ClientPosition (Client, Custodian, Asset, Class L1/L2/L3, Qty, UnitPrice, MarketValue, AsOfDate)
– NewMoneyScenario (Client, Amount, Date, ProfileRef, Status)
– AllocationItem (ScenarioRef, RuleRef, TargetPct, TargetValue)
– BankAccount / Custodian master tables
– AuditLog (Entity, Field, Old/New, User, Timestamp)
Power Apps (Model-driven + Canvas hybrid)
– Model-driven areas: Master data, Client profiles, Positions, Audit logs
– Canvas pages:
– Profile Designer: cascading dropdowns (L1→L2→L3) with dynamic validation of min/target/max (sum=100%)
– New Money Simulator: inputs InvestableAmount, selects profile → creates NewMoneyScenario and AllocationItems (TargetValue = Amount * TargetPct)
– Portfolio Comparison: gallery by Custodian and a consolidated tab; Donut/Bar visuals; drift and concentration alerts
Business Rules & Validation
– Suitability checks: If client age <X and L1=Equities Target%>Threshold → warning
– Drift: Abs(CurrentPct – TargetPct) > DriftTolerance → flag
– Concentration: L3 bucket > Max% → block approval
– Sum rule: Σ Target% must equal 100% (real-time indicator)
Power Automate Flows
– On new NewMoneyScenario: notify advisor + create approval task
– On approval: writeback final AllocationItems and trigger Power BI dataset refresh
– On profile change: log in AuditLog and email compliance
– Scheduled: nightly import of statement files from SharePoint library (CSV/XLSX) to ClientPosition
Data Ingestion
– SharePoint/OneDrive drop for bank statements → Parse with Power Automate to Dataverse
– Excel Import for legacy sheets (mapping: columns → Dataverse schema)
– Optional: Custom Connector for custodians’ APIs (OAuth2)
Power BI Semantic Model
– Tables: Client, Custodian, Positions (fact), ProfileRule (dim), AllocationItem (fact), Calendar
– Core Measures (DAX):
– Total MV := SUM(Positions[MarketValue])
– Target MV := SUM(AllocationItem[TargetValue])
– % Drift := DIVIDE([Total MV] – [Target MV], [Target MV])
– % Allocation by L1 := DIVIDE(SUM(Positions[MarketValue]), CALCULATE([Total MV], ALL(Positions[L1])))
– Gap by Custodian := [Total MV] – CALCULATE([Target MV], TREATAS(VALUES(Custodian[Name]), AllocationItem[Custodian])) (if custodian mapping is used)
Power BI Report Pages
1) Overview: Client summary, Total MV, Suggested vs. Current (L1/L2/L3)
2) By Custodian: Each bank’s gap vs. target; top assets
3) Risk & Suitability: Age, horizon, liquidity, concentration breaches
4) What-if: Slider for InvestableAmount to stress-test allocation
5) Appendix: Glossary + methodology; Export to PDF with branding
Security & Governance
– Azure AD roles: Advisor, Manager, Compliance, Back Office
– Row-Level Security: Dataverse security roles; Power BI RLS by Client.Owner
– PII controls: Field-level security for KYC; masked on non-privileged roles
– ALM: Solutions in Dev/Test/Prod; environment variables; connection references
– Telemetry: Dataverse analytics + Power BI Usage Metrics
Scalability & Roadmap (Already Accounted in MVP)
– Mobile-first Canvas screens; offline cache with SaveData/LoadData (where feasible)
– Rebalancing Wizard: Auto-generate trades list per drift threshold
– Fee Simulation: Advisory fee tiers; client net performance
– ESG Tags: Additional profile dimensions for ESG constraints
– Client Portal (Power Pages): Read-only reports + document vault
