Forecast and target vs. actuals for new MRR

Load the target into a long table: period, scope (total, channel, team, rep, product), scope key, revenue type and value. Total and cuts live in the same table: always sum filtering one scope. Attainment is computed in the pipeline, never by the agent at question time. On load, treat the spreadsheet as a source that changes format: month columns often change every year.

Use the monthly recurring value of the won deal, not the total contract value: on an annual deal, the total is 12 times the MRR. Classify each win as new logo, upsell or one-off (implementation, one-time service). One-off stays out of the MRR target. If there is a target per type, attainment is per type too, and overall attainment is a declared weighted average (for example, 80% new logo and 20% upsell).

A deal in the new-business funnel whose company is already a customer, by the company flag in the CRM or by self-declaration on the deal itself, is expansion, not new logo. Generate the list of these cases every week so the manager can fix them before close.

A won deal's period is the won date's period; an open deal's is the expected close date's. An open deal with no date, or with an overdue date, doesn't go into the current period: it goes to an overdue line for review. CS, renewal, partner and legacy pipelines stay out of the new-business forecast.

If the team flags deals as commit and upside, show commit, upside and weighted in their own columns, never summed. Filter status open or won before summing: a lost deal that still carries the commit flag, because nobody cleared the field when it was lost, comes out of the count.

Save the forecast every week, because custom CRM fields (such as the forecast category in Pipedrive) have no change history. Compare two snapshots and classify each deal: in, won, slipped, out or MRR changed. Apply the period filter to both snapshots: filtering only the current one makes a deal that moved quarters vanish instead of showing up as slipped.

Map each owner to a channel or team through a table maintained by the manager, with start and end dates, and normalize names before joining. A joint deal between two channels counts for each on its own row and once in the total. CRM loads are usually daily: an owner change made in the afternoon only shows up the next day.

To track mid-month, spread the monthly target across days with weights per week and the business-day calendar, preserving the month's total and with no day negative or zeroed by compensation. If the team agrees on an intermediate checkpoint (a fraction of the target by a date in the month), it goes in as a written rule. Compare with actuals through yesterday, in the São Paulo time zone.

For finance, new MRR starts when the first invoice is paid. Show actuals by won date (sales view) and by first charge (finance view), with the difference between the two. In self-serve products, the charge can come before the deal is marked won; in sales with implementation, weeks later.

Deals in another currency go to the reporting currency at a declared rate (a fixed budget rate, for example) kept stable across weeks. Prefer the account-currency value field the CRM already computes (amount_in_home_currency in HubSpot) when the CRM rate is the one agreed with finance.