XiKey™DESKTOP
العربيةGet XiLauncher

Learn

Task Guide: Building a Specialty Tea & Botanical Inventory Forecast

Alpha status (14 October 2026): XiSheet is planned and its public installer is not verified. This page describes the checked GridCraft foundation; XiKey integration is still in progress. Alpha is free today.

Scenario Overview

In this guide, you will build a multi-sheet inventory replenishment and landed-cost model for a specialty loose-leaf tea and botanical importer. You will convert raw supplier catalogs into Structured Tables, use XLOOKUP and LET formulas to calculate landed customs duties and reorder points, apply Conditional Formatting data bars and icon sets, and audit formula dependencies.

Step 1: Convert Ranges into Structured Tables and Define Names

  1. Open a new workbook (Ctrl+N) and rename the first two sheets BotanicalLots and SupplierRates.
  2. On SupplierRates, enter columns for OriginCode, SupplierName, FreightPerKg, DutyRate, and LeadTimeDays.
  3. Select a cell inside the supplier range and press Ctrl+L (Insert › Table). Confirm My table has headers and name the table tblSuppliers. Enter inventory output columns in order: G for landed cost, H for days remaining, I for reorder status.
  4. Press Ctrl+F3 to open the Name Manager and define a workbook-level constant SafetyBufferDays set to 14.

Step 2: Calculate Landed Cost and Reorder Triggers with XLOOKUP & LET

  1. Switch to BotanicalLots, which tracks SKU, LotName, OriginCode, StockKg, DailyBurnKg, and FOBCostPerKg. Convert it into a structured table (Ctrl+L) named tblInventory.
  2. Add a column LandedCostPerKg and enter a formula combining LET and XLOOKUP:
    =LET(
  fob, F2,
  freight, XLOOKUP(C2, SupplierRates!A$2:A$20, SupplierRates!C$2:C$20, 0),
  duty, XLOOKUP(C2, SupplierRates!A$2:A$20, SupplierRates!D$2:D$20, 0),
  ROUND((fob + freight) * (1 + duty), 2)
)
  3. Add a column DaysRemaining (=IF(E2>0,ROUND(D2/E2,1),0)) and a column ReorderStatus:
    =LET(
  lead, XLOOKUP(C2, SupplierRates!A$2:A$20, SupplierRates!E$2:E$20, 21),
  threshold, lead + SafetyBufferDays,
  IF(H2 <= lead, "URGENT AIR", IF(H2 <= threshold, "REORDER SEA", "OK"))
)
  4. Use Data › Data Validation on the OriginCode column (Allow: List, Source: =SupplierRates!$A$2:$A$20) so warehouse staff can pick valid origins via Alt+Down.

Step 3: Apply Conditional Formatting and In-Cell Sparklines

  1. Select the StockKg column (D2:D85) and choose Home › Conditional Formatting › Data Bars to visualize relative stock weight inside each cell.
  2. Select the DaysRemaining column (H2:H85) and apply Conditional Formatting › Icon Sets (3 Traffic Lights) and configure thresholds for your stock policy.
  3. Add a rule on ReorderStatus (Highlight Cells Rules › Text that Contains "URGENT AIR") with a bold crimson fill.
  4. In columns J:U, paste 12 months of historical consumption per lot, then select V2:V85 and choose Insert › Sparklines › Line (Location: V2:V85, Data: J2:U85) with High Point and Low Point markers enabled.

Step 4: Audit Formulas and Freeze Headers

  1. Select cell I14 (ReorderStatus for a Japanese Gyokuro lot) and click Formulas › Trace Precedents to display dependency arrows pointing back to StockKg, DailyBurnKg, and SafetyBufferDays.
  2. If you want to step through the nested LET evaluation and compare the result with an independent calculation, click Formulas › Evaluate Formula.
  3. Select row 2 and choose View › Freeze Panes › Freeze Top Row so headers stay pinned while scrolling through hundreds of botanical lots.

Sources