--- id: imobiliario/vendas-estoque-distratos-empreendimento industry: Real estate usage: common effort: medium grain: Reservation by unit and project; installment by customer for the portfolio --- # Sales, inventory and cancellations by project Units sold, cancelled and in inventory for each project, with VSO (sales-over-supply ratio) and the receivables portfolio of the same buyer, joining the sales CRM with the ERP. ## Executive summary **For:** Developers and builders that sell units off-plan and use CV CRM for sales and Sienge or UAU for finance. **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. **Why it matters:** A distrato is contracted revenue that goes away, and it is the developer's most expensive loss. If it is lumped in with cancelled proposals or disappears from VGV (gross sales value), sales velocity and % sold look optimistic, and the decision to launch the next phase or adjust pricing goes wrong. On the finance side, an inflated or undercounted portfolio changes collections and the SPE's cash flow. **Business questions it answers** - 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 ## Detailed ### 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. ### Business rules 1. **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'). (Avoid: Counting distrato and cancellation as one thing, or counting every reservation as a sale.) 2. **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. (Avoid: Joining CRM and ERP on the project name.) 3. **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. (Avoid: Grouping by raw text and seeing the same brokerage in three rows.) 4. **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. (Avoid: Counting only the sold status and leaving units under contract as available.) 5. **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. (Avoid: Dividing the month's sales by total units without considering time on offer.) 6. **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. (Avoid: Reporting only VGV sold, or subtracting distratos without saying when they happened.) 7. **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. (Avoid: Unioning both tables without a status filter and counting each live sale twice.) 8. **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. (Avoid: Summing currentBalance, or mixing original and adjusted amounts across reports.) 9. **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. (Avoid: Ranking only by the largest overdue amount.) 10. **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. (Avoid: Treating every debtor in the portfolio as a unit buyer.) ### Reservation and installment status CV CRM for sales, Sienge for the portfolio. - **VENDIDA** → Sale (`situacao = 'VENDIDA'`) - **DISTRATO** → Distrato: contracted revenue that went away (`situacao = 'DISTRATO'`) - **CANCELADA** → Proposal lost before the sale (`situacao = 'CANCELADA'`) - **VENCIDA** → Lost proposal: reservation expired (`situacao = 'VENCIDA'`) - **Other statuses** → In progress (`ativo = 'S'`) - **Installment Vencida** → Delinquency (`installments.status = 'Vencida'`) - **Installment A vencer** → Receivables portfolio (`installments.status = 'A vencer'`) - **Installment Paga** → Already received (`installments.status = 'Paga'`) ### Definitions | Metric | Definition | |---|---| | 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 - **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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: Keep personal data only in the detail table with restricted access. ### Acceptance test - [ ] 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. ### Context document ```text 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: - : statuses that count as sold in inventory: . - : official launch date of = . - : reported portfolio amount = . - : sales valid only in block ; block is for construction and is excluded. ``` ### 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.