CRM deals and targets spreadsheet
Gap = target - won; only pipeline that closes in the period
Coverage by period, channel and rep
Revenue meeting, sales TV and alerts
What it solves
- The target gap is calculated the same way for everyone, net of what was already won.
- Only pipeline that can close within the period counts toward coverage.
- The question 'will we hit it?' gets an answer weeks before month end.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- CRM connected with open and won deals, expected date and owner
- The targets spreadsheet connected to Nekt, with period, scope and amount
- The table linking each owner to a channel or team
What you get
- A table of target, won, gap and pipeline by period and scope
- Nominal and weighted coverage, with the required multiple alongside
- Pro rata target to date and required pace until period end
- An AI agent that answers whether the target is covered
Where to activate
Target, won, gap and coverage by channel, with the required multiple alongside.
Won through yesterday and required pace per business day, by rep.
Notice when a scope's coverage falls below the required multiple with more than N business days ahead.
The math is simple. The work is putting the targets spreadsheet into a stable format and agreeing each target's scope with the team. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
The target sits in the spreadsheet in several cuts at once (total, channel, product, rep) and the pipeline sits in the CRM with expected dates that have already passed. Adding every spreadsheet row triples the target, and dividing the whole open pipeline by the full target forgets what was already won and counts deals that will not close in time. Coverage looks comfortable until the last week.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
Load the spreadsheet into a long table with period, scope (total, channel, team, rep, product), scope key and amount. Total and cuts live in the same table: always sum filtering one scope, or the target doubles or triples. On load, update only the rows present in the spreadsheet for the period, keeping the others.
Gap = period target - revenue won in the period, by win date and in the same scope. Coverage is over the gap, not the full target. When won exceeds the target, the gap is zero and coverage does not apply.
Eligible pipeline = open deals in acquisition pipelines with an expected date between today and period end. Past, blank or out of period dates stay out. A deal that just entered an early stage, with an average cycle longer than the days left, does not cover the current period either.
The required multiple is 1 divided by the eligible pipeline's historical win rate: a 25% rate calls for 4 times the gap. Show nominal coverage against that multiple and weighted coverage against 1. Do not use a market 3x in place of the measured rate.
Link each owner to a channel or team through a table kept by the manager, with validity start and end. Use the owner as of the reference date; some teams freeze the book on a fixed day of the month, after the transfer deadline. Normalize names before matching with the spreadsheet.
A joint deal between two channels or with two reps counts for each one in their own row, but only once in the total row. Remove the overlap from the total, separately for open and won.
To track mid period, compare won through yesterday (D-1, America/Sao_Paulo) with the pro rata target. The simplest way is target x elapsed days / days in the period. If sales concentrate at month end, spread the target by week and calendar weights, keeping the total and with no negative days.
Required pace = gap / remaining business days, with the holiday calendar. Show it next to the actual pace (won / elapsed business days). It is the number the rep understands.
The required pipeline for the next period is only bottom-up if ticket, conversion and cycle are measured. If any is missing, present it as a scenario, as a minimum and maximum range, with the list of what still needs measuring. A market benchmark does not fill a missing input.
How to read coverage
The required multiple is 1 divided by the scope's historical win rate.
Definitions
Pitfalls that already happened
The total target and the cuts by channel and by product were on the same rows, and summing without a scope filter tripled the target.
Always filter one scope before summing.
A rep who served two channels was left out of the owner x channel table, and their pipeline covered no target at all.
Disambiguate by deal when an owner serves more than one channel and flag owners with no channel.
A target set per half year was split into quarters by a rule nobody had agreed, and one quarter looked uncovered.
Declare the target split rule (for example, half in each quarter) with the spreadsheet.
Upgrades from the base entered the new sales won amount and covered a gap that belonged to acquisition.
Upgrade and expansion count only in base revenue actuals, never in new sales.
Sources that counted as bonus sat in the same spreadsheet as the ones that counted toward the target, and the target looked bigger than agreed.
Keep target and bonus target in separate columns.
The spreadsheet's month columns changed every year and the load broke in January.
Load the spreadsheet into a long format and validate period and scope on load.
Acceptance test
The case is done when every item passes.
- For each period, the sum of the channel scope targets equals the total scope target, or the difference is declared.
- Won for a closed month matches the CRM won deals report in the same scope (difference under 1%).
- No deal with a past, blank or out of period expected date enters the eligible pipeline.
- The won and pipeline totals count each deal once, including those shared between channels.
- The gap is never negative and coverage does not show when the gap is zero.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Pipeline coverage vs target - Source: coverage table (period x scope), with target, won, gap, eligible pipeline, nominal coverage, weighted coverage and required multiple. - Always filter one scope (total, channel, team, rep or product) before summing targets. Never add scopes. - Gap = target - won in the period, never negative. Coverage is over the gap. - Eligible pipeline = open acquisition deals with an expected date between today and period end. Past and blank dates stay out. - Required multiple = 1 / the scope's historical win rate. Do not use a market 3x. - Pro rata target with won through D-1, in America/Sao_Paulo. Required pace in business days. - Upgrades and expansion do not count in new sales won. - A projection without measured ticket, conversion and cycle is a scenario: say which inputs are missing. Client exceptions: - <client>: half year target split into quarters by <rule>. - <client>: owner x channel in <table>; book frozen on day <n> of the month.
Build the "Pipeline coverage vs target" case in my Nekt workspace following the rules at https://academy.nekt.com/en/casos/cobertura-de-pipeline.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
How many deals and leads to generate for the next period, from the expected gap and the measured win rate, ticket and cycle.
When the target is a number of sales or enrollments, the same rule with a count of distinct deals.
Attainment by rep feeds variable pay, with the owner frozen as of the reference date.