Home · Solutions · Other solutions
Solution · Other solutionsOne dataset with a memory, so the Monday call is about deals and not about whose number wins
Close rates, targets and the forecast on one trusted page
Pipeline, targets and invoiced revenue are consolidated nightly under agreed definitions, so attainment, close rates and slippage sit on one page that reaches managers before the call.
Executive summary
Forecast_Q3_v7_FINAL_CFOcomments.xlsx has nine tabs and three definitions of committed.
The change is smaller than it sounds: we turn the forecast from a file into a dataset with a memory.
One number for the CEO, the CFO and the sales director, because the definitions and the source are shared rather than negotiated each week.
the reporting database in Azure SQL; the Power BI semantic model and report
Business problem
Sales reporting
The data exists. It is simply never in one place at one time. The pipeline lives in the CRM, targets live in a spreadsheet owned by a sales-operations analyst, invoiced revenue lives in the ERP, and the forecast is assembled by hand from all three, differently in every region. One region's "commit" is another's "best case", and both are defensible, which is why the argument never ends.
Because each version overwrites the last, the forecast has no memory. Slippage, the quiet movement of a deal from this quarter into the next, leaves no trace at all: nobody can show that the same three deals have moved twice. Close rates are quoted as impressions rather than measured, so a seller who loses at proposal stage looks the same as one who loses at discovery, and coaching is guesswork.
Sellers feel the same gap from below. Attainment is something they have to ask for, so they find out at a review rather than in time to act, and a win is announced only if a manager remembers the email. The CFO, unable to reconcile the sales number with the ledger, then discounts the forecast privately, which makes planning conservative in good years and wrong in bad ones. Every fix so far has produced another spreadsheet.
How it works today
This is the weekly cycle in most sales organisations with more than one region.
- PersonSales operations updates the target file each quarter and mails the current version to the managers
- PersonEach regional manager exports the pipeline on Friday, applies judgement and fills a tab
- WaitingTabs arrive between Friday and Sunday, two of them in a different unit or a different category scheme
- PersonThe sales director merges nine tabs into one workbook on Sunday evening
- Risk of errorThe CFO rebuilds the total from the ledger and the CRM and gets a different figure
- Risk of errorThe merged file is saved as the new version, so last week's forecast ceases to exist
- WaitingA seller asks a manager where they stand against target and waits for someone to look it up
Why the current process costs more than it appears
The most expensive part of this process has no cost line.
- Manager hours are the visible cost. What the forecast drives is the real one: hiring, delivery capacity, cash planning and the guidance given to owners, all resting on a number that moves for reasons nobody can trace.
- Slippage that cannot be seen cannot be managed. Deals slide from quarter to quarter without a single conversation, and the pattern only becomes obvious when a quarter is already lost.
- Unmeasured close rates make coaching impossible. Nobody can say whether a seller loses at discovery, proposal or negotiation, so every performance conversation runs on impressions.
- Two numbers, one for management and one for pay, produce commission disputes at exactly the moment the company can least afford the distraction.
Cost of inaction
A forecast with no history can never be shown to have been wrong, which is precisely why nothing forces it to improve. Every added seller adds a row, every new region adds a tab, and every reorganisation breaks a merge formula, so the manager hours grow in a straight line while trust in the number falls. Meanwhile the commitments made on that number get larger: hiring ahead of revenue, capacity contracted with partners, guidance given to owners.
The compounding part is the slippage nobody sees. A deal that has moved three quarters in a row is indistinguishable, in a file with no memory, from one that appeared last week, so the conversation that would have qualified it out never happens. The two-number problem between sales and finance then surfaces at the worst possible moment, in a financing round or an audit, and the hours of nine managers and a director continue to go into spreadsheet work, which is the least valuable use of the most expensive people in the organisation.
A plausible organisation with realistic proportions. The figures are there to be recalculated on your data; they are not a client result.
An IT services and cloud provider, 750 employees, selling managed services and projects in three countries through 62 sellers under nine regional and segment managers. Microsoft Dynamics 365 Sales holds the pipeline, invoiced revenue comes from the ERP, targets are set annually per seller in an Excel file, and a Microsoft Fabric capacity is already running for finance reporting.
Nine regional forecast tabs merged every week, 62 sellers whose attainment exists only on request, and one target file that is emailed each quarter.
Managers export the pipeline on Friday, apply judgement, and send a tab. The sales director merges them on Sunday, the CFO rebuilds the total on Monday, and the leadership call reconciles the difference.
About 3.5 hours a week per manager on exporting, adjusting and merging, roughly 135 hours a month across the nine of them, plus 45 hours a month of sales-operations time spent maintaining and reconciling the files.
A nightly robot takes a dated snapshot of the pipeline, targets and invoiced revenue into a governed dataset with agreed definitions; Power BI shows attainment, coverage, forecast by category, close rates and what moved since any earlier snapshot; subscriptions deliver the page to Microsoft Teams before the call and alerts fire when coverage thins.
In the modelled case the call opens on deals rather than on arithmetic, slippage is a chart instead of a suspicion, and every seller sees attainment without asking. Read the numbers as a model built on stated assumptions.
Proposed solution
The change is smaller than it sounds: we turn the forecast from a file into a dataset with a memory. Every night a UiPath robot takes a dated snapshot of the pipeline from Microsoft Dynamics 365 Sales through the Dynamics 365 CRM connector, reads invoiced revenue from the ERP, and pulls targets and forecast categories from a governed list that sales operations maintains in SharePoint. The three sources land in a reporting database in Azure SQL under one agreed set of definitions for stage, category and close, so "committed" finally means the same thing in every region.
On top of that sits a Power BI semantic model and one report. It shows attainment against target by seller and team, pipeline coverage, forecast by category, close rates by stage, segment and seller, and slippage: which deals moved, by how much, and since which snapshot. Because every snapshot is kept, the question that could never be answered before, what changed and when, becomes a chart rather than an argument.
The page then travels. Power BI subscriptions deliver it to managers in Microsoft Teams and Outlook before the weekly call, and a data alert fires when coverage falls below the agreed threshold, naming the region and the gap, so the pipeline is discussed while it can still be filled. Where a Microsoft Fabric capacity exists, Copilot in Power BI lets a manager ask which deals above a value moved out of the quarter this week, on the same secured model. A second robot posts a win card in the sales channel when an opportunity closes won and sends each seller a monthly card with attainment, gap and days remaining, so recognition stops depending on who noticed.
Power BI semantic models, subscriptions, data alerts and the Power BI app in Microsoft Teams; Copilot in Power BI where a Microsoft Fabric capacity of F2 or above exists; SharePoint lists with version history as the audit trail on targets and definitions; UiPath Orchestrator triggers, credential store and run audit; UiPath Integration Service connectors for Microsoft Dynamics 365 CRM and the ERP
The agreed definitions of stage, category, close, attainment and coverage; the nightly consolidation and snapshot logic; the reporting database; the semantic model, the report pages and row-level security by Microsoft Entra ID group; the slippage comparison; the alert thresholds; the win and monthly target cards
Invoiced revenue from the ERP through the OData or API connector for that system; the target and definition list where sales operations still maintains it in Excel today
How the automated process works
- AutomationA robot takes a dated snapshot of the pipeline from the CRM every night, with targets from the governed list and invoiced revenue from the ERP
- SystemThe three sources are consolidated under one set of definitions for stage, category and close, and the snapshot is kept rather than replaced
- AutomationThe Power BI semantic model refreshes: attainment, coverage, forecast by category, close rates and what moved since any earlier snapshot
- AutomationA subscription delivers the page to managers in Microsoft Teams and Outlook before the weekly call
- AutomationA data alert fires when coverage falls below the agreed threshold, naming the region and the size of the gap
- PersonManagers change the judgement where it belongs, in the CRM, and the next snapshot records both the change and its date
- AutomationA win card is posted in the sales channel when an opportunity closes won, and each seller receives a monthly card with attainment, gap and days remaining
Human-in-the-loop model
Automation handles
- The nightly snapshot and the consolidation of pipeline, targets and invoiced revenue
- Attainment, coverage, close rates and the slippage comparison between any two snapshots
- Delivery of the page to Teams and Outlook by subscription, and the threshold alerts
- Win cards and monthly personal target cards to sellers
People decide
- Forecast judgement itself: the stage, the category and the probability, set in the CRM where history keeps them
- Targets, and their reallocation during the year
- What the definitions are, which is a management decision and not a reporting one
- What to do about a deal that has moved twice, which is the conversation the page exists to start
Before and after
Systems and integrations
Where a rule suffices we do not use a model. Where judgement is needed, a person decides.
Inputs
- Microsoft Dynamics 365 Sales pipeline and closed deals
- invoiced revenue from the ERP
- the governed target and definition list in SharePoint
Automation layer
- UiPath Orchestrator
- UiPath Robots
- UiPath Integration Service
- the consolidation and snapshot logic
Target systems
- the reporting database in Azure SQL
- the Power BI semantic model and report
Human touchpoints: the page delivered to Microsoft Teams and Outlook by subscription; coverage alerts in Teams; win and monthly target cards in the sales channel
Technologies used
the nightly snapshot, the consolidation and the card posting, scheduled with retries and a run audit
Apipeline, closed deals and invoiced revenue read over APIs rather than exported on Fridays
Athe governed list of targets, categories and definitions, with version history as its audit trail
Athe reporting database that keeps every dated snapshot instead of overwriting it
Asemantic model and report for attainment, coverage, close rates and slippage, with subscriptions and data alerts
Aplain-language questions of the same secured model, on a Microsoft Fabric capacity of F2 or above
Awhere the page arrives, where the alert lands and where wins are posted
Agroup-based row-level security, so a regional manager sees her region and the director sees all of it
AIllustrative economic model
Start by questioning the assumptions.
Nothing below prices the decisions this page improves, which is deliberate: the value of a forecast that can be trusted, of a deal rescued because its movement was seen early, and of a commission dispute that never happens cannot be sized without your data. What is priced is time, in three pools on three rates, none of them measured at a client. Nine managers spend 3.5 hours a week on exporting, adjusting and merging, of which 0.7 is removed at €71 an hour. Sixty-two sellers spend 20 minutes a week asking where they stand, of which 0.8 is removed at €55. A sales-operations analyst spends 45 hours a month maintaining and reconciling the files, of which 0.6 is removed at €43. Treat all three as placeholders until measured.
Business benefits
- One number for the CEO, the CFO and the sales director, because the definitions and the source are shared rather than negotiated each week
- Slippage becomes visible and therefore manageable, since every snapshot is kept and any two can be compared
- Close rates by stage, segment and seller turn coaching from anecdote into evidence, and show where a seller actually loses
- Managers get their Sunday back, and the weekly call opens on the deals at risk rather than the arithmetic
- Sellers see attainment every month without asking, a win is recognised the same day whoever their manager is, and commission can draw on the same governed data
The management view
- Forecast accuracy becomes a measured quantity per manager and per quarter, which is what makes accountability possible at all
- Coverage alerts arrive while the pipeline can still be filled, rather than in the post-mortem after the quarter closes
- The CFO can reconcile the sales view against invoiced revenue without an analyst and without a private discount
- Definitions are owned by sales operations and applied by the robot, so a reorganisation does not break the meaning of the numbers
Board-level KPIs
Security and governance
The automation holds exactly the rights it needs, and not one more.
- The consolidation robot reads the CRM and the ERP through service accounts limited to the entities the model needs, with credentials held in a managed store rather than in a workflow
- Row-level security by Microsoft Entra ID group applies to the semantic model, so a regional manager sees her region and Copilot in Power BI can answer nothing the report would not show her
- Targets and definitions change only in the governed SharePoint list, where version history records who changed what and when
- Snapshots are retained under a Microsoft Purview retention label, so forecast history survives reorganisations and departures rather than depending on a folder
- Data stays inside the Microsoft 365 EU Data Boundary and the EU region of UiPath Automation Cloud
Why now
Owners and boards increasingly ask for forecast accuracy as a management metric, and a forecast that overwrites itself every week cannot produce one
Power BI delivers reports by subscription into Teams and Outlook and raises data alerts, so numbers reach managers instead of waiting to be opened, and CRM and ERP connectors turn the Friday export into a scheduled job
The modelled cost of leaving it alone is about €11,811 a month of manager, seller and analyst time, before anything is said about the decisions that number drives
Relevant executive roles
One forecast with a measurable accuracy record replaces a weekly argument about whose number is right
Slippage and close rates make coaching and accountability factual, and the Sunday merge disappears from the job
The sales forecast reconciles to invoiced revenue, so it can be used for cash planning without a private discount
Common questions and objections
They lack targets, invoiced revenue and history, which is why they cannot show attainment or slippage and why managers stop opening them. The governed dataset with dated snapshots and the subscription that pushes the page are the difference.
Snapshots make every movement visible and close rates by stage expose systematic optimism, so gaming becomes measurable rather than suspected. That is the point of keeping history, not a side effect.
Correct, and it is optional. The report, the subscriptions and the alerts work on Power BI alone; plain-language questioning is added when the capacity exists.
When this is not the right solution
- A single team of a dozen sellers whose manager knows every deal, where a weekly conversation genuinely is enough
- The CRM is not used for the pipeline, so there is nothing to snapshot; the data and the habits come first
- Targets are not set per seller, or they change informally during the year, in which case there is nothing stable to measure attainment against
A question for the next management meeting
Last quarter's forecast changed every week: can anyone in this company show what moved, when, and who decided that it had?
Implementation approach
We start with one slice of the process and extend only once it is proven.
We deliver
- A definition workshop that settles stage, category, close, attainment and coverage before a line of code is written
- The nightly consolidation robot with dated snapshot history and the ERP feed
- The reporting database, the Power BI semantic model and the report pages, with row-level security by Entra ID group
- Subscriptions, coverage alerts, the win card and the monthly seller card
- Testing against three past quarters, so the page is compared with what actually happened before anyone relies on it
We need from you
- Read access for a service user to the CRM and the ERP, limited to the entities the model needs
- The current target file and whoever owns it
- A sales-operations owner for the definitions, because these are decisions and not settings
- A Microsoft Fabric capacity if plain-language questioning is wanted; the report works without one
Stages
Definitions
Stage, category, close, attainment, coverage and thresholds agreed and written down
Build
Consolidation robot, snapshot store, semantic model, report pages and security
Validation
Three past quarters rebuilt and compared with the forecasts that were actually filed
Pilot
One region runs the page beside its spreadsheet for two review cycles
Rollout
Remaining regions, the ERP feed, alerts and the seller cards, with the spreadsheet retired
Quick win where the CRM already holds the pipeline. Effort is driven by how far the regional definitions differ and by the state of the target file, not by the reporting technology.
Nine tabs, three definitions of committed, and one number the CFO does not use.
Send us your last three forecast spreadsheets and a CRM export covering the same weeks. We rebuild them as one page and come back with the slippage those files hide, the close rates behind them, and a short read-out call with the numbers.
Rebuild your last three forecast filesThe neighbouring process usually has the same problem
Stop rebuilding the group forecast by hand from nine files that never agree.
View solution Sales & marketingSales commissions calculated and explainedCommission is calculated in one enormous workbook, and every dispute means rebuilding it.
View solution Other solutionsRenewals surfaced in Teams and a CRM cleaned every nightRenewals surface when the customer mentions them, and the pipeline is tidied only before the quarterly review.
View solution Other solutionsFinance questions answered in Teams from governed numbersThree versions of ARR are in circulation, and the board saw one of them last quarter.
View solution Case studyThe morning executive brief, daily at 7:00The board starts the day with the full picture — before anyone has to ask for it.
View case study Case studyThe sales assistantThe agent reads the enquiry, builds the quote from SAP prices and availability, enforces the discount policy and prepares a brief before every meeting.
View case studyIndustries we deliver this in most oftenManufacturing & industryServices & IT