Sales, inventory and cancellations by project

Source

CV CRM (reservations, units, cancellations) and ERP (contracts and installments)

Rule

Normalized status and canonical project

Outcome

Sales, cancellations, inventory, VSO and portfolio by project

Activation

Sales committee, AI agent and collections alert

What it solves

Sales, distratos (cancellation of a signed sale), cancelled proposals and expired reservations stop being mixed into the same number.
The same project has a single id in sales and in finance.
Overdue receivables by project and by customer come out with the right amount and no sale counted twice.

Business questions it answers

Ask your agent these questions once the case is live.

How many units have we sold, had cancelled and kept in inventory in each project?

What is the sales velocity (VSO) of each project since launch?

What is the month's net VGV, after cancellations?

How much is overdue by project and by brokerage, and in which aging bucket?

Which customers have the most overdue installments piling up and should be collected first?

How long does it take from reservation to sale in each project?

What you need

CV CRM and Sienge (or UAU) connected to Nekt
The list of projects with their name in each system
The official launch date of each project, if there is one

What you get

A reservations table with normalized status and canonical project
An inventory table by project with % sold and VSO
A portfolio installments table with status and aging bucket
An AI agent that tells apart a sale, a cancellation and a lost proposal

Where to activate

Sales committee

By project: sold, cancelled, net, inventory and VSO, with the proxy flag when it applies.

AI agent via MCP

Answers about sales and the portfolio, separating sale, distrato and lost proposal.

Collections alert

Weekly list of customers who now have two or more overdue installments piling up.

Ready to build?

The status rules are settled. The effort is in mapping projects between CRM and ERP and in the customer bridge, which tend to be manual. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

The problem

The sales CRM stores reservations with dozens of statuses, and the ERP stores contracts and installments with different names for the same project. Counting reservations without normalizing the status mixes sales with cancellations and lost proposals. In the ERP, balance fields that come back empty and tables that mirror live sales make the portfolio and total sales wrong with no visible error.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Counting distrato and cancellation as one thing, or counting every reservation as a sale.

Normalize the reservation status

A sale is situacao = 'VENDIDA', with the amount in valor_contrato. A distrato is a completed sale that was later undone (situacao = 'DISTRATO'). Cancelled is a proposal that fell through before becoming a sale, and expired is a reservation that ran out: both are lost proposals. Everything else is in progress. Use only active reservations (ativo = 'S').

Joining CRM and ERP on the project name.

A project has a canonical id

The CRM and the ERP write the same project differently (commercial name on one side, acronym and uppercase name on the other). Keep a project dimension with a canonical id, the name in each system and a flag for presence in the ERP. A recent launch may not exist in the ERP yet.

Grouping by raw text and seeing the same brokerage in three rows.

Normalize CRM text

Brokerage, unit, block, project and broker arrive with extra spaces and inconsistent casing. Apply uppercase, trim and single spacing before grouping. The keys idunidade, idreserva and idempreendimento are local to each CRM account: in multi-company setups, always use them together with the account id.

Counting only the sold status and leaving units under contract as available.

A sold unit follows a closed list

In inventory, count a unit as sold when its reservation is in the sale stage or in the stages after it (contract drafting, sent to the ERP). A blocked unit is its own category, neither sold nor available. In resales, the latest resale status overrides the unit's original status.

Dividing the month's sales by total units without considering time on offer.

VSO uses months on offer

VSO = cumulative units sold / (total units x months on offer). Months on offer run from launch to the reference month, counted inclusively. If inventory hit zero, the end is the month of the last sale. Without an official launch date, use the first sale as a proxy and flag the number as a proxy.

Reporting only VGV sold, or subtracting distratos without saying when they happened.

Net sales deduct distratos

Net VGV = VGV sold minus VGV cancelled. Declare which distrato date is used (event date, registration date or current status date). Show distrato volume and VGV separately, and say when a distrato this month refers to a sale from another month.

Unioning both tables without a status filter and counting each live sale twice.

In UAU, filter the sales mirror

A live sale sits in Vendas (Status_Ven = 0). Paid-off and cancelled sales move to VendasRecebidas (Status_VRec 3 and 1). VendasRecebidas also keeps a mirror with Status_VRec = 0 of every live sale. The sales universe is Vendas with status 0 plus VendasRecebidas with status 1 or 3. The key is company + construction site + sale number.

Summing currentBalance, or mixing original and adjusted amounts across reports.

Delinquency by installment amount and status

In Sienge, sienge_customer_debt_balance has an installments array with a status per installment: Paga (paid), Vencida (overdue) or A vencer (not yet due). Open overdue = sum of originalValue with status Vencida. currentBalance comes empty or zeroed and is not a balance. If you want the adjusted amount (correctedValue), declare it and always use the same one.

Ranking only by the largest overdue amount.

Collections prioritizes piled-up installments

Rank customers by the number of accumulated overdue installments, then by amount. Accumulation identifies who stopped paying better than one large late installment. Spread overdue amounts into buckets: over 180, 91 to 180, 61 to 90, 31 to 60 and 1 to 30 days.

Treating every debtor in the portfolio as a unit buyer.

Bridge sales and finance through the customer

The CRM customer links to the ERP customer through the integration code (idpessoa_int) or, without it, through CPF (individual tax ID) or normalized name. The ERP portfolio also has payers who are not unit buyers (government bodies, companies): flag them and remove them from customer delinquency.

Reservation and installment status

CV CRM for sales, Sienge for the portfolio.

VENDIDASalesituacao = 'VENDIDA'

DISTRATODistrato: contracted revenue that went awaysituacao = 'DISTRATO'

CANCELADAProposal lost before the salesituacao = 'CANCELADA'

VENCIDALost proposal: reservation expiredsituacao = 'VENCIDA'

Other statusesIn progressativo = 'S'

Installment VencidaDelinquencyinstallments.status = 'Vencida'

Installment A vencerReceivables portfolioinstallments.status = 'A vencer'

Installment PagaAlready receivedinstallments.status = 'Paga'

Definitions

Sales

distinct active reservations with sold status, by sale date, by project and month

VGV sold

sum of valor_contrato for the period's sales

Distratos

distinct reservations with distrato status by the declared distrato date; VGV cancelled = sum of their valor_contrato

Lost proposal

cancelled reservations plus expired reservations in the period

% sold

units sold / total units in the project at the reference date, without counting blocked units as sold

VSO

cumulative units sold / (total units x months on offer since launch, counted inclusively)

Cycle days

days between reservation creation and sale date, per sold reservation

Delinquency

sum of originalValue for installments with status Vencida at the cutoff date, by project and customer

Pitfalls that already happened

Double sales in UAU

A union of Vendas with VendasRecebidas without a filter counted each live sale twice, because every live sale has a mirror with status 0.

Use only Vendas status 0 and VendasRecebidas status 1 and 3.

Delinquency at zero

The portfolio summed by currentBalance came out near zero in projects with many late payments.

Sum originalValue by installment status.

Project with no match

A new launch existed in the CRM but not yet in the ERP, and the sales and finance join dropped it.

Keep the ERP presence flag and left join starting from the CRM.

Zero distratos from the wrong source

The agent answered zero distratos because it looked only at reservations, while distratos lived in their own table.

Before saying zero, check the account's distrato source.

Past position lost

The ERP portfolio shows only today, and no one could reproduce the overdue amount from the previous close.

Save an append-only monthly snapshot of the portfolio, with the close version.

Personal data on the dashboard

Buyer name, CPF and income ended up on a dashboard open to the sales team.

Keep personal data only in the detail table with restricted access.

Acceptance test

The case is done when every item passes.

For a project, sales + distratos + cancelled + expired + in progress = total active reservations.
Units sold + blocked + available = total units for each project.
A project's overdue amount matches the ERP delinquency report at the same cutoff date (difference below 1%).
No sale appears twice by the key company + construction site + number (UAU) or idreserva (CRM).
Every CRM project has a canonical id; those not in the ERP appear with the absence flag.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

Sales, inventory and cancellations by project
- Sale = active reservation (ativo = 'S') with situacao 'VENDIDA'; amount = valor_contrato.
- Distrato (cancellation of a signed sale) = a completed sale that was later undone (situacao 'DISTRATO'). It is not a cancelled proposal.
- Lost proposal = situacao 'CANCELADA' (fell through before the sale) + 'VENCIDA' (reservation expired).
- Always use the canonical project id from the CRM and ERP mapping dimension. Never join on the name.
- Net VGV (gross sales value) = VGV sold - VGV cancelled; say which distrato date was used.
- VSO (sales-over-supply ratio) = cumulative units sold / (total units x months on offer). Without an official launch date, the first sale is the proxy and VSO is flagged as a proxy.
- A blocked unit is neither sold nor available.
- UAU: sales universe = Vendas with Status_Ven 0 + VendasRecebidas with Status_VRec 1 (distrato) or 3 (paid off). Never union without a filter.
- Sienge: delinquency = sum of installments.originalValue with status 'Vencida'. Do not use currentBalance.
- Collections: rank by number of accumulated overdue installments, then by amount.
- Before answering zero distratos, check the distratos table.
- Do not expose buyer name, CPF (individual tax ID) or income in aggregated answers.
Client-specific exceptions:
- <client>: statuses that count as sold in inventory: <list>.
- <client>: official launch date of <project> = <date>.
- <client>: reported portfolio amount = <original or adjusted>.
- <client>: sales valid only in block <block>; block <block> is for construction and is excluded.

Build the "Sales, inventory and cancellations by project" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/imobiliario/vendas-estoque-distratos-empreendimento.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

Repasse SLA

Days between the repasse (mortgage handoff to the bank) sale date (cvcrm_repasses.data_venda) and entry into the stage where the bank releases the funds, with a ceiling set by the developer.

MCMV and mid-market funnel

MCMV: lead, appointment, visit, credit review, credit approved, sale. Mid and high end: lead, appointment, visit, proposal, sale.

Distrato profile

Distratos by brokerage, broker, income bracket and time since sale, with the distrato reason.