Home · Solutions · Sales & marketing
Solution · Sales & marketingEvery region forecasts on the same template, and planning gets one version with a timestamp
One sales forecast, not nine spreadsheets and a feeling
Each region plans on a template pre-filled with its own shipments and pipeline, submissions are validated as they arrive, deviations are explained in Teams, and one version is published.
Executive summary
Stop rebuilding the group forecast by hand from nine files that never agree.
The design starts by changing what a regional manager opens.
The forecast is published days after the cut-off instead of weeks, so planning commits production slots against a current number.
the SharePoint forecast site and its frozen archive; the Power BI semantic model; the handover file for planning
Business problem
Demand & forecast
A sales forecast exists because somebody has to buy packaging, book a production slot and hold stock before the order arrives. The production plan, the material call-offs, the promotional stock build and the revenue line the CFO repeats to the board all read from it. In most companies it is nine people's judgement in nine spreadsheets.
The spreadsheets are not the same spreadsheet. One region forecasts in cases, another in value because that is what its bonus is measured on. One puts promotional volume into the base line, another keeps it separate, a third forgets it. New listings appear under names the ERP has never seen. Each habit is reasonable locally, and each costs a correction round.
Three groups pay for it. Regional managers treat the exercise as administration with a deadline and no feedback, so they copy last month; sales operations spends half of every month assembling instead of analysing; supply chain plans against a number a week older than the cut-off. What breaks at scale is the explanation, not the arithmetic: every added channel pushes the question that matters, whether the pipeline supports a region running a third above its run rate, out of the cycle.
How it works today
This is the cycle we find wherever the forecast is collected rather than modelled.
- PersonSales operations copies last month's workbook, pastes shipment actuals from the ERP and emails out a file per region and channel
- PersonEach manager edits last month's version from memory, checking a CRM export if the week allows
- WaitingFiles return between the third and the eleventh working day, two or three as PDF or pasted into email
- PersonSales operations maps renamed products, converts units and pastes each file into one workbook
- Risk of errorNew listings, delisted items, double-counted promotions and value entered where cases were asked reach the group total
- WaitingDifferences against history and pipeline are queried by email; answers after the cut-off change nothing
- PersonThe consolidated number goes into the review deck and on to planning, by then describing last week
- Risk of errorNobody compares last month's forecast with what shipped, so the same optimism returns unchallenged
Why the current process costs more than it appears
Time that disappears before anyone measures it.
- Assembly is the cheap half of the bill. Working out what a region meant, chasing the missing file and repairing a broken product mapping cost more than the typing ever did, and none of it is recorded.
- Late numbers are paid for in stock. When the forecast lands after the production plan is fixed, the gap is covered by safety stock, an expedited run or a write-off.
- Optimism survives because it is never measured. Without accuracy per region, the manager who overstates by a fifth every month looks like the one who is right.
- Judgement disappears with the file. Why a region raised a line by 8,000 cases lives in an email or nowhere, so the argument returns next month.
- Two people hold the mapping between what the regions call a product and what the ERP calls it, and when either is away the cycle slips.
Cost of inaction
Two larger costs sit outside this table and neither is charged to sales. The first is stock bought against a number nobody challenged: safety stock held because the forecast is late, an expedited run when it is wrong, short-dated goods after a promotion counted twice. The second is the decision never put on the table, a launch phased on a feeling.
None of this corrects itself. A region a fifth optimistic every month stays a fifth optimistic, because the comparison is never made. Sales operations keeps the mapping between regional product names and ERP codes in two heads, and any reorganisation of the sales areas rebuilds every file by hand, exactly when the business is changing fastest.
A plausible organisation with realistic proportions. The figures are there to be recalculated on your data; they are not a client result.
A consumer goods manufacturer of household and personal care brands: two plants, about 1,100 employees, nine regional sales areas in Central Europe, three channels (modern trade, wholesale, e‑commerce). Microsoft 365 E3 is in place, shipments sit in the ERP, deals and promotions in the CRM.
27 submissions a month, one per region and channel, each covering roughly 70 product lines by month for the coming quarter and by quarter out to month twelve.
Sales operations builds the files from last month's version, emails them out, chases what is missing and rebuilds the group view by hand from what comes back.
About 95 minutes per submission across preparation, chasing, checking, mapping, the correction round and its share of consolidation. The forecast reaches supply chain in the second week, when the first production slots are committed.
Robots build one pre-filled workbook per region and channel carrying that region's shipments, its open pipeline, last month's forecast and how accurate it proved. Submissions are validated on arrival, deviations are explained in Microsoft Teams, and one agreed version is published with a timestamp.
In the modelled case the forecast is published days after the cut-off rather than weeks, every deviation carries a reason written by its author, and accuracy per region becomes a number the review can open. The figures illustrate the design; no client sits behind them.
Proposed solution
The design starts by changing what a regional manager opens. A robot builds one workbook per region and channel and fills it first: twelve months of that region's shipments by product line, the pipeline and promotions already committed in the CRM, last month's forecast and the bias it proved to have. What is left is judgement about what changes.
The file never travels. It sits in the region's folder on the SharePoint forecast site, with access from the Microsoft Entra ID group that follows the sales structure, and is edited in Excel Online with its structure locked. On submit the rules run: totals foot to the channel, quantities are in the unit asked for, every line either has history or is declared a new listing, promotional uplift sits on its own line, and any line beyond the agreed distance from its run rate or pipeline coverage carries a reason code. Failures return within minutes as a card in Microsoft Teams naming the line and the rule.
Consolidation then has nothing left to interpret. Accepted submissions are stamped with cycle and version and appended to the Power BI model behind the demand review, so the group view is open while the cycle still runs. The signed forecast is frozen as a read-only handover file for planning, and next month a robot compares each region's forecast with what shipped and publishes the accuracy. None of this replaces judgement about demand; it removes the assembly and the argument over versions.
SharePoint libraries with metadata, permissions and version history; Excel Online with Office Scripts run from Power Automate; Microsoft Lists as the submission register; Adaptive Cards through the Workflows app in Microsoft Teams; the Teams Approvals app; UiPath Orchestrator triggers, assets and audit logs; UiPath Integration Service connectors for Microsoft OneDrive & SharePoint, Microsoft Teams and Microsoft Dynamics 365 CRM; Power BI semantic models
The template generator and its pre-fill, the validation rules and the message each manager sees, the reason-code workflow, the consolidation and version stamp, the handover file for planning, the accuracy calculation, and the reminder and coverage logic
Shipment history and the product master from the ERP through its API or a scheduled extract; pipeline and committed promotions from the CRM (Microsoft Dynamics 365 Sales, Pipedrive or Salesforce) through UiPath Integration Service; the planning application's import format where it has no interface
How the automated process works
- AutomationAn Orchestrator trigger opens the cycle and pulls shipments, pipeline, committed promotions and last month's forecast with its error
- AutomationA robot writes the pre-filled workbooks into the regions' folders, locks the structure and opens the submission register
- SystemEach manager gets an Adaptive Card in Microsoft Teams with the deadline and a link; the workbook opens in Excel Online, no attachment
- PersonThe manager adjusts base volume, enters promotional uplift on its own line and writes a reason where the rules ask for one
- AutomationValidation runs at submission: units, totals, new and delisted lines, phasing, each line's distance from its history and pipeline coverage
- AutomationAccepted submissions are stamped, consolidated and appended to the Power BI model; coverage stays visible while the cycle is open
- PersonThe demand review works from the deviations and reasons already written; the sales director signs off in the Approvals app
- AutomationThe signed version is frozen and handed to planning as a read-only file with version and timestamp; next month each region's accuracy is published
Human-in-the-loop model
Automation handles
- Building the pre-filled workbooks, the shipment, pipeline and accuracy pre-fill, and the locks
- Validation at submission: units, totals, new and delisted lines, phasing, deviation against history
- Consolidation, version stamping, the handover to planning and the published model's refresh
- Reminders, escalation, live coverage and the monthly accuracy calculation per region
People decide
- Every volume in the forecast: the automation checks consistency, it proposes nothing
- Whether a deviation is accepted, on what evidence, and what the group total should be
- The thresholds, reason codes and deadlines, owned by the sales director and sales operations
- What the company does about a region consistently above or below its own forecast
Before and after
Systems and integrations
We do not add technology to make an architecture look serious. Every element below has a specific job in this process.
Inputs
- shipment actuals by product, channel and region from the ERP
- the product master with listing status
- pipeline and committed promotions from the CRM
- last cycle's forecast and its accuracy
- the promotional calendar
Automation layer
- UiPath Orchestrator
- UiPath Robots
- UiPath Integration Service
- Power Automate
- Office Scripts
Target systems
- the SharePoint forecast site and its frozen archive
- the Power BI semantic model
- the handover file for planning
Human touchpoints: Excel Online in the browser; Adaptive Cards and the coverage tab in Microsoft Teams; the Approvals app
Technologies used
the locked template; scripts write the pre-fill and read submissions back
Aa folder per region, the submission register and the frozen archive
Athe submit trigger, the Office Scripts runs, reminder timers and the Teams cards
Ascheduled pulls, generation, validation and consolidation runs, retries and audit
Afiles, Excel ranges, list items, channel messages and pipeline data
Adeadlines, validation feedback, deviation questions and the sign-off
Acoverage during the cycle, the published forecast after it, the accuracy scorecard
Agroup-based access: each region opens its own folder, the group view a separate permission
AIllustrative economic model
What it is worth, with the arithmetic shown.
Twenty-seven submissions is nine regions across three channels; ninety-five minutes is what one costs end to end, counting preparation, chasing, the mapping and checking in sales operations, the correction round and its share of the group view. It is an assumption about an illustrative company, not a client measurement, as is the €33 fully loaded hourly cost blending a regional sales role with a sales-operations analyst in Central Europe. The model prices the assembly; the stock consequences of a late forecast stay outside it.
Run the numbers on your data
An illustrative estimate from your own inputs. It models released capacity; it is not a promise of savings.
Business benefits
- The forecast is published days after the cut-off instead of weeks, so planning commits production slots against a current number
- Managers plan against their own shipments, pipeline and last month's error instead of a copy of last month's file, which lifts the first submission
- Mistakes are caught at submission by the person who can explain them, and every deviation carries a reason written by its author, so the review argues about the lines that matter
- Forecast accuracy per region and channel becomes a published number, which changes the behaviour it measures
- Sales operations moves from assembly to challenge, and a new region adds a folder, not a working day
The management view
- Submission coverage is visible while the cycle runs, region by region, not reconstructed once it has closed
- The number handed to planning carries a version, a timestamp and a named approver, so "which forecast are you using" stops being worth asking
- Bias and error per region turn a debate about optimism into a measurement anyone can open
- Planning capacity scales with the number of regions, not the number of people in sales operations
Board-level KPIs
Security and governance
Security is designed with the process, not after it.
- Each manager sees their own region and channel; the group view is a separate permission. Access follows Microsoft Entra ID groups built on the sales structure, so a territory change moves group membership, not a hand-kept sharing list.
- Robots read shipments, product master and pipeline through display-only accounts and write back to none of them; their credentials stay in the platform's own secret store.
- Every accepted submission is kept as a read-only snapshot with submitter, timestamp, rule set version and sign-off, so the forecast behind a production decision can be reproduced.
- A Microsoft Purview sensitivity label follows the pipeline and promotion data, which is commercially sensitive and never leaves your Microsoft 365 tenant; robot execution, queues and logs run in UiPath Automation Cloud, EU region.
Why now
Retail promotional calendars and e‑commerce volatility have shortened the useful life of a forecast. A number describing the market at the cut-off is worth planning against; two weeks later it is history.
The commercial team you have is the team you will have. The modelled €16,929 a year of assembly is small next to the stock a late forecast buys, and it is released without recruiting.
None of this needs a planning application or a developer. Excel Online with Office Scripts, SharePoint permissions, Teams cards and Power BI cover the template, the rules and the publication.
Relevant executive roles
The forecast becomes a commitment with a name and a measured track record, not a number assembled from nine opinions
Planning receives one version with a timestamp, days after the cut-off, and can see which regions run optimistic
The revenue line in the board pack traces to submissions, versions and approvals, and the stock held against forecast error becomes visible
Common questions and objections
Which is why the rules never touch the volume. They check units, listings, phasing and completeness, and ask for a sentence when a line moves far from its own history. The judgement stays with the manager; it simply gets written down.
Both store a number. Neither collects it from nine regions, validates it on arrival, records why it changed or measures who was right, which is where the month goes.
Publish bias next to error and sandbagging shows up as clearly as optimism. Treated as a coaching conversation the scorecard converges within a few cycles; treated as a bonus target, it will be gamed.
When this is not the right solution
- Fewer than roughly four submitting units, where a manager collects the forecast by phone faster than any workflow
- Sales is not asked for a forecast at all: where planning runs a statistical forecast without the commercial view, this changes nothing
- The product master does not reconcile with what the regions sell, in which case master data comes first and collection second
A question for the next management meeting
When supply chain commits next month's production, whose forecast is it working from, how many days old is that number, and did anyone ask the region to defend it?
Implementation approach
What we deliver, and what we need from you to start.
We deliver
- The governed template: locked structure, named input ranges, unit and listing validation, reason codes and a promotional line
- The pre-fill routine: shipments per region and channel, pipeline, promotions, last forecast and its accuracy
- The validation rules and the message each manager sees, agreed with sales operations
- The SharePoint forecast site, permissions from Microsoft Entra ID groups following the sales structure, and the frozen archive
- The Teams layer (deadlines, reminders, escalation, deviation questions, sign-off), consolidation, the handover file for planning and the accuracy scorecard in Power BI
We need from you
- The last three cycles as they happened: regional files, the consolidated version and the shipments that followed
- The region and channel structure with named submitters, deputies and the approval path
- A read account for shipment history and the product master, and one for the CRM
- A named owner in sales operations who decides the thresholds and reason codes
Stages
Discovery
Regions, channels, template variants, the rules that exist only as habits, recent exceptions
Design
Master template, pre-fill layout, validation thresholds, reason codes and the permission model
Build
Generation and pre-fill robots, submission and validation flows, consolidation, published model
Dry run
One closed cycle replayed on historical submissions, then one region live beside today's process
Go-live
The next monthly cycle under supervision, with hypercare through review and handover
Optimisation
Accuracy published per region, thresholds tuned, further channels and countries added
Departmental. Effort follows the number of regions and template variants, how far the product master reconciles with what the regions sell, and whether shipments and pipeline are readable through interfaces.
Nine regions, three channels, and a group forecast nobody can trace back to a pipeline.
Send us the last three cycles as they arrived: the regional files, the version that went to planning, and the shipments that followed. We come back with the accuracy each region achieved and the rules that would have caught the differences.
Score your last three forecast cyclesThe neighbouring process usually has the same problem
Stop spending a quarter of an analyst's year collecting spreadsheets from cost-centre owners.
View solution Management & planningThe management pack in Power BI, not fourteen Excel filesThe board pack should not depend on which analyst merged which spreadsheet on which day.
View solution Sales & marketingCRM data hygiene: duplicates, stale deals, missing fieldsThe pipeline review starts with an argument about the data, not about the deals.
View solutionIndustries we deliver this in most oftenManufacturing & industryRetail & e‑commerce