How we designed & deployed an Income Pipeline Report for a Texas, U.S. based Cybersecurity & AI Business Solutions Firm, via MS D365 Project Operations and Power BI.

Summary

1. Designed a two-page Power BI Income Pipeline Report for a Texas-based Cybersecurity & AI Business Solutions firm using Microsoft Dynamics 365 Project Operations.
2. Unified visibility across Opportunity, Unbilled Income, Billed Income, and Paid Income in a single view.
3. Introduced Average Turnaround to forecast realistic cash collection timelines based on actual payment behavior.
4. Integrated Dynamics 365 Project Operations with QuickBooks to connect sales, delivery, invoicing, and cash collection.
5. Enabled a 17-week rolling revenue forecast with week-by-week cash visibility.
6. Provided dual invoice status for contractual vs realistic payment tracking.

Table of Contents

• 1. Introduction
• 2. The Business Problem
• 3. Report Structure Overview
• 4. The Income Pipeline
• 5. Project Revenue Forecast
• 6. Design Principles
• 7. Business Impact
• 8. FAQs
• 9. Conclusion

Managing revenue across a professional services firm is rarely straightforward. When your business spans cybersecurity assessments, AI-driven solutions, and long-term managed services engagements, the gap between work being delivered and cash actually landing in the bank can be wide — and costly if left unmonitored.

This is precisely the challenge we set out to solve for a U.S.-based Cybersecurity and AI Business Solutions firm running their operations on Microsoft Dynamics 365 Project Operations. The result was a two-page Power BI report — the Income Pipeline Report — that gives leadership a real-time, end-to-end view of every dollar moving through the business: from early-stage opportunity, through unbilled and billed income, all the way to cash collected.

This post walks through how the report was built, how each data layer was modelled, and why the design decisions were made the way they were.

The firm needed clarity across four distinct but connected stages of their revenue lifecycle:

1. Sales opportunities and pipeline value
2. Delivered but unbilled work
3. Outstanding invoices and expected payments
4. Actual vs expected payment behavior

This would answer as well as resolve the following questions –

1. Where are active sales opportunities sitting, and how much pipeline value do they represent?
2. Which project work has been delivered but not yet invoiced?
3. Which invoices have been raised and sent to clients, and when are they realistically going to be paid?
4. And finally, how does actual payment behaviour compare against what was expected?

Each of these questions existed in isolation before.

1. Project managers had partial visibility into their own contracts, and needed a comprehensive bird’s eye view of all of these together.
2. Finance had QuickBooks data but lacked the context of the delivery pipeline.
3. Leadership had no consolidated view.

The Income Pipeline Report brought all of this together in a single, navigable Power BI experience.

The report consists of two pages:

1. Income Pipeline Report — a high-level pipeline view across four stages: Opportunity, Unbilled Income, Billed Income, and Paid Income, each with summary cards and interactive donut charts.
   
2. Project Revenue Forecast — a time-distributed breakdown of expected cash collection across a rolling 17-week horizon, organised by customer and contract.
   

The Four-Stage Pipeline Banner

Across the top of the report, four chevron-style stage indicators guide the revenue journey:

Opportunity → Unbilled Income → Billed Income → Paid Income

1. Each stage includes a summary card showing record count and total value
2. Provides immediate visibility into where revenue is sitting
3. Highlights potential bottlenecks across the pipeline

---

Stage 1 — Opportunity

1. Data sourced from Dynamics 365 Sales using Business Process Flow (BPF)
2. Uses active BPF stage (Develop, Propose, Close) instead of static fields
3. Ensures accurate reflection of real sales progression
4. Estimated revenue pulled directly from opportunity records
5. Donut chart shows distribution across Develop, Propose, and Close stages

---

Stage 2 — Unbilled Income

1. Represents contracted or delivered work not yet invoiced
2. Sourced from project contract lines in Dynamics 365 Project Operations
3. Includes:
   1. Fixed Fee milestones (explicit values)
   2. Time & Material (T&M) estimates based on resource allocations
4. T&M calculated as allocated hours × billing rate
5. Clearly marked as estimated until billing run is executed
6. Grouped into payment expectation buckets (30, 60, 90, 120, 180+ days)
7. Uses Average Turnaround to forecast realistic payment timing

---

Stage 3 — Billed Income (Confirmed Invoices)

1. Combines Dynamics 365 Project Operations and QuickBooks data
2. Tracks invoices that are confirmed and sent to clients
3. Introduces Average Turnaround:
   1. Average days from invoice creation to payment
   2. Based on historical payment behaviour
4. Each invoice has two statuses:
   1. Contractual (due date)
   2. Estimated (based on Average Turnaround)
5. Provides realistic vs contractual payment visibility
6. Includes:
   1. Due-date based categorisation
   2. Estimated overdue analysis
7. Prevents misleading insights from strict payment terms alone

---

Stage 4 — Paid Income

1. Tracks fully collected invoices
2. Uses QuickBooks for actual payment dates
3. Groups payments by time bands (under 30, 60, 90 days, etc.)
4. Enables comparison between actual vs estimated payment behaviour
5. Continuously improves accuracy of Average Turnaround

---

Tooltip Drill-Down

1. Hover shows:
   1. Payment band
   2. Record count
   3. Total value
2. Drill-through available for detailed record-level analysis

Overview

1. Distributes expected cash collection across a rolling 17-week window
2. Shifts view from pipeline stage to time-based forecasting

Hierarchy and Structure

1. Customer → Contract → Revenue Type
2. Revenue types include:
   1. T&M run schedules
   2. Fixed Fee milestones
   3. Confirmed invoices
3. Each row shows:
   1. Customer
   2. Contract
   3. Billing type
   4. Average Turnaround
   5. Value mapped to expected payment week
4. Weeks range from Week 0 to Week 16
5. Top row aggregates total expected cash per week

Colour Coding

1. Amber — Unbilled income
2. Green — Invoice within terms
3. Red — Overdue (based on estimated payment date)

Drill-Through to Detail

1. Click any row to view detailed breakdown
2. Includes:
   1. Billed invoices with due and estimated dates
   
   2. Unbilled milestones and run schedules
   
3. Connects high-level forecast to transactional detail

1. Average Turnaround over payment terms
   Reflects actual customer behaviour instead of contractual assumptions.
2. Dual invoice status
   Provides both contractual and realistic payment visibility.
3. Consistent time buckets
   Ensures comparability across Opportunity, Unbilled, Billed, and Paid stages.
4. Weekly forecasting instead of monthly
   Supports short-term cash flow planning aligned with operational rhythm.

1. Improved cash flow predictability
2. Earlier visibility of at-risk invoices
3. Unified cross-team visibility
4. Improved T&M billing discipline
5. Increased accountability

What is Average Turnaround and why does it matter?

It is the mean number of days a client historically takes to pay from invoice creation to settlement — used to forecast realistic cash collection dates rather than relying on contractual terms alone.

How are T&M amounts estimated before an invoice is raised?

By multiplying each team member’s allocated hours against their billing rate within D365 Project Operations, clearly labelled as an estimate until the billing run is formally executed.

What does the dual invoice status mean in practice?

Each confirmed invoice carries two statuses — one against the contractual due date and one against the estimated payment date — giving finance both the formal position and the realistic expected position in a single view.

The Income Pipeline Report provides a single, trusted view of revenue from opportunity to cash collection.

By combining Dynamics 365 Project Operations with QuickBooks and introducing behavioral forecasting, the firm improved both financial visibility and decision-making.