Flat Forecast Column Reference¶
Overview¶
The Flat Forecast table (#FlatForecast) stores a complete week-by-week inventory projection for each item processed during BOM explosion. Each planning week contributes ten columns, identified by the zero-padded week index suffix _### (e.g., _000 for the current week, _001 for the next, and so on). This reference defines each of the ten columns in detail.
See Flat Forecast Table for context on how the table is built and used within the regeneration process.
BaseForecast_###¶
What it means: The prorated sales forecast demand for the item in this planning week. Represents anticipated future demand from the configured forecast source, distributed proportionally across the week based on how many days of the fiscal month fall within the planning week.
How it is set: Read from either the Pulse Budget module or Macola's MRP Forecast file, depending on the Forecasting (Demand) setting. Monthly forecast quantities are multiplied by a BudgetMultiplier (the proportion of the month's days that fall within the week) to produce the weekly figure. For split weeks (weeks spanning two fiscal months), separate multipliers are calculated for each month's portion and summed.
- If Use Pulse Budget: Pulse nets this value against open orders and current-month invoices before applying it.
- If Use Macola's MRP Forecast: The value is read as-is; Macola has already deducted confirmed orders from the forecast balance.
CustomerDemand_###¶
What it means: The total quantity from open customer orders that is due to ship within this planning week. Represents confirmed sales demand from the order book.
How it is set: Aggregated from open customer order lines where the demand date (set by the Customer Orders Demand Date Field in MRP Setup) falls within the planning week's date range. Lines with excluded statuses or (optionally) on-hold lines are omitted per MRP Setup configuration.
ProductionDemand_###¶
What it means: The total component quantity consumed by open Production Processing Orders (POPs) and Shop Floor Orders (SFOs) that are due within this planning week. Applicable to items that are used as components in manufactured assemblies.
How it is set: Derived from the bill of materials build quantities for open production orders, using the demand date fields configured in MRP Setup. The quantity reflects the remaining open quantity on each order (ordered quantity minus reported/completed quantity) multiplied by the BOM usage ratio.
ComponentShortageDemand_###¶
What it means: Additional demand for this item generated by the BOM shortage cascade. When a parent item (a finished good or sub-assembly that uses this item as a component) has a projected shortage in this week, the shortage quantity — multiplied by the BOM qty_per_par — is added to this column for the child item.
How it is set: Initialized to zero. Updated by the BOM cascade UPDATE statement after each BOM level's shortage values are calculated. The cascade runs level by level, from the top of the BOM downward. A parent item's Shortage_### value is multiplied by Qty Per Par and summed across all parent relationships to produce the total cascade demand for each child item.
Replenish_###¶
What it means: The total incoming supply expected to arrive for this item within this planning week, from all replenishment order types. This is the supply side of the weekly netting equation.
How it is set: Aggregated from open Purchase Orders, Production Processing Orders (replenishment side), and Shop Floor Orders (replenishment side), where the replenishment date (as configured in MRP Setup for each order type) falls within the planning week. Quantities reflect remaining open balances (ordered minus received/completed), adjusted by the relevant inventory-to-order ratio.
NetDemand_###¶
What it means: The total effective demand used in the weekly inventory projection calculation. This is the quantity subtracted from the starting inventory (plus replenishment) to arrive at EndQty_###.
How it is set: The formula depends on the item's BOM level:¶
Finished goods (BOM Level 0): CustomerDemand_### + BaseForecast_###¶
Components (BOM Level 1 and deeper): ProductionDemand_### + ComponentShortageDemand_### + CustomerDemand_### + BaseForecast_###
NetForecast_###¶
What it means: A forecast-only view of the demand for this item in this week, isolating the forecast and BOM-cascade components from direct order demand. Provided as an informational column.
How it is set: BaseForecast_### + ComponentShortageDemand_###
Warning
NetForecast_### is populated but is not used in the downstream MRP shortage calculation or in the generation of Forecast demand entries in the MRP Core table. It is available for reporting or diagnostic purposes only.
EndQty_###¶
**What it means:** The raw projected ending inventory for this item at the close of the planning week, before the minimum quantity floor is applied. A negative value means demand exceeds available supply in the week.
**How it is set:** - **Week 0 (current week):** `qty_on_hand + Replenish_000 − NetDemand_000` - **Week 1 and beyond:** `AdjustedEndQty_[prior week] + Replenish_### − NetDemand_###`
Where `qty_on_hand` is the item's current on-hand inventory quantity at the time of regeneration.
AdjustedEndQty_###¶
**What it means:** The ending inventory for this week after applying the minimum quantity floor configured for BOM explosion. This value is carried forward as the starting inventory for the next week's calculation.
**How it is set:** `MAX(EndQty_###, min_qty_on_hand)`
Where `min_qty_on_hand` is determined by the **Forecasting Item/Location Minimum Quantity** setting: Zero (no floor), Safety Stock, or Reorder Level. When **Forecasting Aggregation** is set to **Item** or **Item and MRP Group**, `min_qty_on_hand` is the sum of the individual location minimums for the item.
Why AdjustedEndQty Is Necessary¶
`AdjustedEndQty` exists to prevent shortage deficits from one week silently compounding into all future weeks. Without it, the BOM explosion calculation would produce incorrect and increasingly distorted projections — understanding why clarifies the entire design of the weekly cascade.
**The carry-forward problem**
The `EndQty_###` formula for weeks beyond the current week is:
EndQty_### = AdjustedEndQty_[prior week] + Replenish_### − NetDemand_###
If `EndQty` itself were used as the carry-forward instead of `AdjustedEndQty`, a negative balance from Week 0 would become the starting point for Week 1. That negative balance would then be partially or fully "cancelled" by Week 1's replenishments — making it appear as though Week 1's supply retroactively covered Week 0's unmet demand. Physically, that is impossible: inventory that was short in Week 0 was not shipped, and Week 1's arrivals do not change that.
**What the floor enforces**
By capping the carry-forward at `min_qty_on_hand`, `AdjustedEndQty` ensures each planning week starts from a clean, non-negative baseline — the minimum the system considers "available." The deficit is recorded separately in `Shortage_###` and used to generate Forecast demand entries for components. Each week is then evaluated on its own supply and demand, without inheriting a compounding debt from prior weeks.
**Visualized example**
Item: WHEEL-ASSY · Reorder Level (min_qty_on_hand) = 4 · No replenishments in any week.
| Week | Carry-Forward | Demand | EndQty | Shortage | AdjustedEndQty |
|---|---|---|---|---|---|
| 0 | 10 (on-hand) | 8 | 2 | 2 | 4 ← floored |
| 1 | 4 | 6 | −2 | 6 | 4 ← floored |
| 2 | 4 | 3 | 1 | 3 | 4 ← floored |
| 3 | 4 | 2 | 2 | 2 | 4 ← floored |
Without the floor, Week 1 would carry forward `2` (Week 0's raw EndQty), Week 2 would start at `−4`, Week 3 at `−7`, and so on — an ever-deepening deficit that has no physical meaning and would cause massively overstated Forecast demand entries for components at every BOM level.
With the floor, each week starts at `4` regardless of prior shortfalls. Shortages are properly isolated to the weeks they occur in, and the component demand cascade remains proportional and accurate.
Developer Reference
In FlatForecastTable.vb, the AdjustedEndQty value is calculated inline within the per-level UPDATE statement using a SQL CASE expression:
CASE WHEN qty_on_hand + replenish_### - NetDemand >= min_qty_on_hand
THEN qty_on_hand + replenish_### - NetDemand
ELSE min_qty_on_hand
END AS AdjustedEnd_qty
This result is immediately used as the starting inventory in the next week's UPDATE, making the floor enforcement a direct part of the iterative level-by-level calculation loop.
Shortage_###¶
**What it means:** An internal calculation value representing the quantity by which projected inventory falls short of the minimum floor in this planning week. This value is **not** exposed as a shortage flag in the MRP Core table or in any Pulse report. It serves two internal purposes: triggering the generation of **Forecast demand entries** for this item's BOM components, and seeding the `ComponentShortageDemand_###` cascade to those child items. Whether the resulting Forecast demand entries cause a shortage in the MRP Core table depends on whether the components have sufficient inventory to absorb the new demand.
**How it is set:** `MAX(0, min_qty_on_hand − EndQty_###)`
When `EndQty_###` is at or above `min_qty_on_hand`, `Shortage_###` is zero and neither Forecast demand entries nor a cascade are generated.
Settings That Affect All Columns¶
- [Fully Explode Production Orders](../../mrp/settings/mrp-setup/#fully-explode-production-orders) — this table is not built when this is off
- [Forecasting (Demand)](../../mrp/settings/mrp-setup/#forecasting-demand) — source of `BaseForecast_###`
- [Forecasting Aggregation](../../mrp/settings/mrp-setup/#forecasting-aggregation) — row granularity and `min_qty_on_hand` aggregation
- [Forecasting Item/Location Minimum Quantity](../../mrp/settings/mrp-setup/#forecasting-itemlocation-minimum-quantity) — `min_qty_on_hand` used in `AdjustedEndQty` and `Shortage`
- [Number of Weeks to Forecast](../../mrp/settings/mrp-setup/#number-of-weeks-to-forecast) — how many `_###` column sets exist