Navgold Forecast Report

The master replenishment worksheet: for every FBA Proxy SKU it fuses Amazon report files (FBA inventory, sales, Keepa, pricing) with live ERPNext stock, supplier and PO data to recommend how much to send to Amazon, and lets you raise the Material Request and Purchase Orders straight from the grid.

Purpose

• Demand planners / stock managers use it to decide replenishment quantities per Amazon SKU.
• It blends external Amazon exports (FBA inventory, units-ordered, Keepa, your-price) with internal ERPNext stock, supplier rank and open-PO data into one screen.
• It computes a sales velocity (highest weekly rate) and a forecast / program forecast with a plain-English reason per SKU.
• From the grid you can edit the per-SKU "Quantity to Transfer" and create a Material Request, Purchase Orders, or both in one action.

How to Access

Desk > Reports > Navgold Forecast Report, or /app/query-report/Navgold Forecast Report. Report type: Script Report. Reference DocType: Proxy SKU. Roles: System Manager, Stock Manager, Stock User, Purchase Manager, Sales Manager.

Data Inputs (Navgold Forecast Asset)

All external data is read from the single Navgold Forecast Asset doctype (a singleton holding file attachments). Each forecast run re-parses these files fresh:

The SKU universe is driven by the FBA inventory CSV — only SKUs present there are matched to Proxy SKUs via get_proxy_sku_map(). If no Proxy SKUs match, the report returns empty.

Filters

(Filters are applied in-memory after all rows are built.)

Columns

How It Works

flowchart TD
  subgraph Files[Navgold Forecast Asset files]
    FBA[FBA inventory CSV]
    S3[sales 3/7/14/30 CSV]
    KP[Keepa XLSX]
    YP[Your Price XLSX]
    PR[Previous Report XLSX]
  end
  FBA -->|SKU list| PS[get_proxy_sku_map]
  PS --> ERP
  subgraph ERP[ERPNext lookups by item_code]
    IT[Item brand/name]
    SUP[Supplier Item Price rank=1 + Offers]
    PRC[Amazon Product Pricing active strategy]
    PO[Open Purchase Orders]
    SO[30d Sales Orders by channel]
    PK[Open Pick Lists]
    FQ[Free Qty map]
  end
  S3 --> CALC
  KP --> CALC
  YP --> CALC
  PR --> CALC
  ERP --> CALC[Per-SKU calc:\nhighest weekly rate\nforecast / program forecast\nstrategy / comment / ranking]
  CALC --> ROWS[Report rows]
  ROWS -->|edit Qty to Transfer + select| ACT[Create MR / PO / both]
  ACT --> MR[Material Request - Transfer]
  ACT --> POs[Purchase Orders per top supplier]

Key formulas (from the code):

• Highest Weekly Rate = max(week1, week2, weeks3_4) where week1 = sales_7d, week2 = max(0, sales_14d − sales_7d), weeks3_4 = max(0, sales_30d − sales_14d) / 2. This picks the busiest recent week as the demand signal.
• 3 Week Lead Time Stock = max(0, fba_stock − rate×3) — FBA cover beyond three weeks.
• Forecast = rate×3 − instocks − current − receiving − three_week_lt — three weeks of demand minus everything already inbound/on-hand.
• Sales Dropped = YES when (sales_7d / max(hwr, prev_hwr)) − 1 ≤ −0.35 and FBA stock > 0 (a ≥35% week-over-week velocity drop on a stocked SKU).

Program Forecast decision tree (compute_program_forecast, evaluated top-down; first match wins):

1. No Buy Box price → blank, "No Buy Box price".
2. FBA stock ≈ sales_7d × 4 → 0, "Enough FBA stock (≈7dX4)".
3. Zero 3d & 7d sales but min_margin > 0.05 and forecast > 0 → if three_week_lt ≥ hwr×4 send 0 (hold, high lead-time), else send forecast + hwr.
4. Forecast < 0, or zero recent sales, or min_margin ≤ 0.05 → if low margin return Margins / "Low margin", else 0 / "hold".
5. FBA stock ≥ sales_7d×4 → 0, "AFN alone covers 4 weeks; hold".
6. Total buffer (fba+instocks+current+receiving) ≥ sales_7d×4 → send forecast + hwr if lead OK, else hold.
7. Otherwise → forecast + hwr, "Insufficient buffer; send".

The 0.05 margin floor and the sales_7d × 4 (four-week) coverage target are the core thresholds.

Creating MR / POs from the grid — three whitelisted actions back the inner buttons:

• Create Material Request → create_material_requests_from_forecast_report: one submitted Material Request (type Material Transfer) into the AFN warehouse (from Amazon SP API Settings) for every selected SKU with Qty to Transfer > 0.
• Create PO + Material Request → create_purchase_orders_and_material_request_from_forecast_report: builds the transfer MR, then groups selected SKUs by top supplier and raises one submitted Purchase Order per supplier (qty converted into the supplier's UOM, rounded up via ceil), and back-links each MR item to its PO via custom_purchase_order.
• A PO-only action (create_purchase_orders_from_forecast_report) also exists. POs use the company + warehouse from Amazon SP API Settings.

Notes

• The whole report re-parses the attached files on every run — keep Navgold Forecast Asset current. Amazon report exports are GZIP/cp1252; the asset stores the decoded CSV/XLSX attachments.
• eBay Sales, Fulfilment Cost Type and Fulfilment Cost are placeholders (0/blank) — not yet wired to a source.
• Qty edits live only in the browser session (forecast_report_state) until you trigger a create action; refreshing the report clears them.
• Strategy here is the simple FBA vs SFP stock check — the full FBA/SFP/Backorder/Default selection lives in the pricing subsystem, not this report.
• Related: To Purchase consumes the Material Requests this report creates and drives the supplier-ranked PO run.