Plan vs Actual

Where actuals broke from plan, by how much, and which planner needs to act on it. That last clause is what makes this a different instrument from a forecast-accuracy report: the error has to land on a named worklist, at the grain the planner actually owns, or it is trivia.

The boundary with the pattern above it is worth stating plainly. The executive cycle review — is the agreed plan still the plan, where are demand and supply diverging — is the S&OP / Demand Review pattern, at family grain, on a monthly cadence. This is the planner instrument underneath it: item × location, worked continuously, ranked by who has to fix what. Same discipline, different grain and different audience — build one dashboard for both and it will serve neither.

One rule governs everything on the board: score the lag-locked snapshot, never the last-edited forecast. A forecast that keeps being edited until the month closes will always look accurate, because it was walked toward the answer. The fact stores the forecast as it stood at a declared number of months out, and the scored lag is the horizon the business actually commits at.

A cascade, the same geometry the inventory pattern uses: every level of granularity visible at once, no clicks required. That fits a worklist, where the planner arrives already knowing which cut is theirs.

What each region reads from the schema below:

1. 1Filters — dim_lag sits in the filter bar beside the plan month, not buried in a settings panel — the lag is the single most consequential choice on the board, and it belongs where the room can see which one is selected.
2. 2KPI row — five tiles at the filtered grain: WMAPE and bias as the diagnostic pair, attainment as the plan-denominator view the supply side asks for, absolute error summed at the selected lag as the size, and the exception count. Each tile states its variant on its face — a bare "forecast accuracy" label is the number nobody can reproduce.
3. 3Plan vs actual — two series over dim_date's plan_month, filtered to the scored lag from dim_lag — and the axis label says which lag, because the same fact drawn at a different lag is a different chart with the same title.
4. 4Cuts — the same components grouped by dim_item planner and family and dim_location site, ranked by summed absolute error rather than by percentage — ranking on a ratio floats tiny-volume items to the top of every list.
5. 5Worklist — the fact's own grain — item × location at the scored lag, sorted by absolute error, with the ratios shown beside each row as diagnosis rather than as the sort key. This is the row a planner actually acts on.

The fact is a periodic snapshot with an unusual second time axis: every plan month is stored once per lag, so a single month carries the forecast as it stood one month out, two months out, three, and so on. That is what makes forecast accuracy and forecast improvement answerable from the same table — and it is why the grain has four parts rather than three.

No error column anywhere. Store an error rate on the row and you have frozen one variant, one denominator, and one aggregation rule at build time — and every rollup above it becomes an average of ratios. Store plan_qty and actual_qty, and every grain the board rolls up to gets an honest number from the same two columns.

The second time axis carries one rule with it: the quantities sum within a lag, never across lags. Each lag re-states the same plan month, so a total that spans two lags counts that month's error twice. The lag is the first filter on this board for exactly that reason.

Five measures — two summed quantities and the three ratios over the same components:

Name the MAPE variant, then publish exactly one. There are at least three numbers commonly called "forecast accuracy" and they disagree materially: WMAPE, a ratio of summed absolute error to summed actual — the one this pattern publishes; the average of per-item MAPEs, which lets a single low-volume item with a 900% error dominate a portfolio number; and the symmetric variants, which put plan and actual both in the denominator to bound the result. Each is defensible; mixing them across two reports is not. Pick one, label it on the tile, and write the choice down where the business can find it.

WMAPE and bias are a pair, not alternatives. A planner can have terrible WMAPE and zero bias — noisy but unbiased, a demand-variability problem — or excellent WMAPE and a persistent lean, which is a process problem someone can fix tomorrow. Showing only the first hides exactly the one that is actionable. And bias nets out by design: at portfolio level a chronically over-planned family and a chronically under-planned one cancel to a comforting zero, which is why the bias tile is always read at the cuts below it, never alone.

The exception tile counts items breaching an error threshold the business set for itself, labeled as an internal goal on the tile. There is no credible external benchmark for forecast error — it is a function of your demand variability, not of your competence — so nothing on this pattern draws one.

Rendering notes for building this wireframe on Databricks AI/BI dashboards. Where a capability below comes from hands-on checking rather than documentation, it was verified in one workspace on 2026-08-24 — a point-in-time observation, not a vendor commitment; re-check in your own workspace before committing a design to it.

Table roles.Same honest starting point as the S&OP pattern: the plan side does not live in ECC or S/4 — it lives in APO or IBP. What the reference gives you is the mapping layer, /SAPAPO/MATKEY for the APO product master and /SAPAPO/MATLOC for the location-product record, which tie planning output back to the material and plant keys the gold layer already uses. The actuals side is a choice with three defensible answers: VBAK / VBAP on an orders basis, LIPS delivery items on a shipments basis, or VBRP billing items on an invoiced basis.

The structural insight. The actuals basis changes the number, so it gets named on the axis and in the definition, not assumed. Orders basis measures demand as the customer expressed it; shipments basis measures what you could fulfill — which means a supply failure shows up as a forecast miss, attributing to the planner something they did not do. For scoring demand planners, orders basis is usually the right call, and the reason should be written down.

Table roles. F3460 holds forecast quantities keyed by item, branch, and forecast type — and the forecast type is the variant axis that matters here: the statistically generated forecast and the planner-adjusted forecast are separate types, which is what makes forecast value added answerable at all. Score the adjusted type, but keep the generated one, because the difference between them is the measure of whether the adjustment helped. Actuals come from F4211 plus F42119 — the union is mandatory, since lines move to history at sales update.

The traps. F3460 carries no lag history: each planning run overwrites the rows for its own forecast type, so the snapshot-per-lag grain is entirely a lakehouse construction — capture the file on a schedule and stamp each capture with its months-out. Miss the captures and no amount of later work reconstructs them. Then the standing encoding: quantities carry implied decimals, the branch plant is space-padded (TRIM before joining), and dates are Julian CYYDDD integers.

Table roles. ForecastSalesholds the demand forecast per forecast model, item, dimension set, and period start — and the forecast model plays the same version-axis role JDE's forecast type does, letting a baseline model and an adjusted model coexist. Actuals come from SalesTable / SalesLine on an orders basis, CustPackingSlipJour on a shipments basis, or CustInvoiceTrans on an invoiced basis.

The traps. Forecast models get recycled — copied over and reused under the same name — so the model alone will not give you lag history without a dated capture. Forecast lines hang off a dimension set (InventDim), so resolving them to a site or warehouse for the item × location grain is a join, not a column. And every table is company-partitioned (the DataAreaId quirk), which will otherwise fan a portfolio WMAPE across legal entities.

Table roles. Stated plainly again: the M3 reference carries no forecast table. MITPLOis the material plan — one row per planned demand or supply transaction per item and warehouse — and it is regenerated by the planning run, so it describes today's plan and nothing about last quarter's. Actuals come from OOLINE on an orders basis, MHDISL delivery lines on a shipments basis, or ODLINE invoiced lines; MITTRA is the movement ledger behind the shipment view.

The traps.Every lag snapshot is a lakehouse capture — the fact's lag dimension exists only downstream, and it is only ever as complete as the extract schedule was. Then the usual mechanics: pin CONO in every join, read column names as a two-char prefix plus a four-char alias (the prefixes quirk), normalize quantities out of the item's basic unit of measure before summing an error, and convert the numeric YYYYMMDD dates before bucketing by plan month.

Table roles. The plan side is MRP_FORECAST_DESIGNATORS, which names the forecast and its set, over MRP_FORECAST_DATES, the fact itself — a row per item, org, forecast name, and bucket date, except that a row with RATE_END_DATE set spans every bucket through to that date and has to be expanded before it is summed. Actuals come from OE_ORDER_LINES_ALL on an orders basis, WSH_DELIVERY_DETAILS on a shipments basis, or RA_CUSTOMER_TRX_LINES_ALL on an invoiced basis — whichever the actuals-basis decision picked.

The traps. The designator is the identity of a forecast, so it belongs in every join or two forecast versions merge into one plan quantity. The quantities are consumed as sales orders arrive — CURRENT_FORECAST_QUANTITY is post-consumption, which is not the plan the lag was locked at, so capture the vintage rather than reading it back later. Every lag snapshot is therefore a lakehouse capture, and it is only as complete as the extract schedule was. Pin operating unit on the _ALL tables (MOAC in practice), and on the invoiced basis filter LINE_TYPE = 'LINE' or tax and freight rows inflate the actual.

Table roles. Actuals are DOO_FULFILL_LINES_ALL at the fulfillment-line grain on an orders basis, with WSH_DELIVERY_DETAILS for a shipments basis and RA_CUSTOMER_TRX_LINES_ALL for an invoiced one. The plan side is not in this reference: forecasts and plans live in Demand and Supply Planning and arrive through their own BICC extracts.

The traps. Fusion Cloud has no direct SQL path; the physical names describe the source model and the extract must use the verified BICC/PVO surfaces available for those tables (no SQL path), and those extract definitions drift with each release (PVO drift). So the lag-locked fact is assembled from two extract paths — the planning one and the order-management one — and the lag key is stamped in the lakehouse, never inherited from either. Fulfillment lines split as they process, so sum knowing that; keep business-unit scope explicit (_ALL means BU here); and on the invoiced basis filter LINE_TYPE = 'LINE' before summing.

Three decisions, all of them upstream of any build:

• Pick the error variant and write it down. WMAPE, the average of per-item MAPEs, or a symmetric variant. The choice belongs to the business, the label belongs on the tile, and the number belongs in exactly one place so two reports cannot quietly disagree.
• Agree the scored lag. Which horizon the business actually commits at — usually the lead time it has to buy or build against. That lag is the headline; the others stay on the board as context, never blended into it.
• Agree the actuals basis. Orders, shipments, or invoices. Shipments basis charges the planner for supply failures they did not cause, so it needs a deliberate reason if it is chosen.

And the anti-patterns to name out loud: averaging MAPEs (one low-volume item with a 900% error hijacks the portfolio number), scoring the current forecast (edited toward actual all month, so accuracy is manufactured), and bias hidden by netting across the portfolio — a portfolio bias of zero can be two large, opposite, entirely fixable process errors cancelling each other out.