Store targets with projections

Load the spreadsheet into a table with period (first day of the month), scope (chain, store, channel, salesperson or product), scope key, unit (reais or units) and value. The load replaces only the rows present in the spreadsheet for that period and keeps the others and the other months. Declare what an empty cell means: some write zero, but no target does not enter achievement.

The load stops entirely, writing nothing, when it finds an invalid period, a target that is not a number, a negative target, a target in units with decimals, or two different targets for the same period and key after normalization. The error message lists the rows, so the spreadsheet owner can fix them.

Normalize names (uppercase, no accents, single spaces) and run them through an alias table to the ERP's canonical key: the same brand spelled two ways, the model with and without accents, the store by its neighborhood nickname. When an alias and the canonical name appear together in the same month, the canonical one wins. A row that matches no store or product in the ERP stops the load and goes to the pending list.

The actuals that go into achievement are invoiced net sales: invoice issued or order completed, without canceled orders, with returns deducted, in the same unit as the target. A paid order still in progress, a quote and a deal won in the CRM without an invoice appear in a separate column, as in progress, and never add to achievement. On marketplaces, validate which status means completed in each channel.

The chain target, the store targets and the channel or product targets live in the table together. Always sum filtering one scope. The sum of stores must match the chain target, or the difference shows up in an explicit row (e-commerce, marketplace, corporate sales). Head office and distribution centers with no consumer sales have no store target.

Pace and projection count trading days, not calendar days. Each store has its own calendar: a mall store opens on Sundays and holidays, a street store does not; promotion days with extended hours weigh more. Keep the calendar in a table by store and day, with open or closed and a weight, maintained by operations.

Use the closed day: actuals up to yesterday (trading date, America/Sao_Paulo). Remaining = the greater of target minus actuals and zero; any surplus goes to another column. Required pace = remaining / trading days left starting today. Achieved pace = actuals / trading days elapsed. Show both side by side and the ratio between them: 1.4 means the store needs to sell 40% above what it has been selling.

The simplest projection is actuals / trading days elapsed x trading days in the month. When the calendar has weights, use the weight of the remaining days instead of the count, and, if there is history, weight each weekday by average sales on the same weekdays of previous weeks. State on the dashboard which method is in use. Once the month is over, the projection equals actuals.

Compute bands on the base target (for example, 80%, 100% and 120%) for bonuses and the traffic light, always with official revenue. When the chain has a contribution margin target, show realized margin next to it and the deviation in percentage points: a store that hits sales by discounting may be far from the margin target.