Inventory Management

"You can't manage what you can't measure." — Peter Drucker

TL;DR

Creates a complete inventory system in Google Sheets with three interconnected sheets: Catalogo (product master), Movimientos (stock movements log), and Resumen (dashboard with formulas for totals, valuation, and low-stock alerts). Designed for small businesses that need reliable stock tracking without enterprise software. [EXPLICIT]

Procedure

Step 1: Create Spreadsheet Structure

Create a new Google Sheets spreadsheet with three sheets using mcp__workspace-mcp__create_spreadsheet.

Sheet 1 — Catalogo (Product Master)

Column	Header	Type	Notes
A	SKU	Text	Unique identifier, format: CAT-001
B	Nombre	Text	Product display name
C	Categoria	Text	From standardized list (see knowledge/inventory-categories.md)
D	Subcategoria	Text	Second-level classification
E	Precio Unitario	Currency (MXN)	Purchase/cost price
F	Precio Venta	Currency (MXN)	Retail selling price
G	Stock Actual	Number	Current quantity on hand
H	Stock Minimo	Number	Reorder threshold
I	Proveedor	Text	Primary supplier name
J	Ubicacion	Text	Storage location or shelf code
K	Ultima Actualizacion	Date	Last stock change date

• Apply header formatting: bold, frozen row 1, background #1a73e8, white text.
• Set data validation on Categoria (column C) from the standardized category list.
• Apply conditional formatting on Stock Actual (column G): red background when value < Stock Minimo (column H).

Step 2: Configure Movimientos Sheet

Sheet 2 — Movimientos (Stock Movement Log)

Column	Header	Type	Notes
A	Fecha	Date	Movement date, default TODAY()
B	SKU	Text	Must match a SKU in Catalogo
C	Nombre Producto	Text	Auto-filled via VLOOKUP from Catalogo
D	Tipo	Text	Data validation: "Entrada" or "Salida"
E	Cantidad	Number	Always positive; Tipo determines direction
F	Stock Anterior	Number	Before this movement (formula)
G	Stock Nuevo	Number	After this movement (formula)
H	Motivo	Text	Reason: Compra, Venta, Devolucion, Ajuste, Merma
I	Responsable	Text	Person who registered the movement
J	Notas	Text	Optional comments

Key formulas:

• C2: =IFERROR(VLOOKUP(B2,Catalogo!A:B,2,FALSE),"")
• F2: =IFERROR(VLOOKUP(B2,Catalogo!A:G,7,FALSE),0) (snapshot at entry time)
• G2: =IF(D2="Entrada",F2+E2,IF(D2="Salida",F2-E2,F2))

Apply header formatting consistent with Catalogo sheet. Set data validation on Tipo (column D) and Motivo (column H).

Step 3: Build Resumen Dashboard

Sheet 3 — Resumen (Summary Dashboard)

Layout the dashboard in sections:

Section A — Inventario General (Row 2-8)

Cell	Label	Formula
A2	Total Productos	=COUNTA(Catalogo!A2:A)
A3	Total Unidades en Stock	=SUM(Catalogo!G2:G)
A4	Valor Total Inventario (Costo)	=SUMPRODUCT(Catalogo!E2:E,Catalogo!G2:G)
A5	Valor Total Inventario (Venta)	=SUMPRODUCT(Catalogo!F2:F,Catalogo!G2:G)
A6	Margen Bruto Potencial	=A5-A4
A7	Categorias Activas	=COUNTA(UNIQUE(Catalogo!C2:C))

Section B — Alertas de Stock Bajo (Row 10+)

Cell	Label	Formula
A10	Header: "Productos con Stock Bajo"	—
A11+	Dynamic list	=FILTER(Catalogo!A2:B,Catalogo!G2:G<Catalogo!H2:H)

Section C — Movimientos Recientes (Row 20+)

Cell	Label	Formula
A20	Header: "Ultimos 10 Movimientos"	—
A21+	Dynamic list	=SORT(FILTER(Movimientos!A2:I,Movimientos!A2:A<>""),1,FALSE) limited to 10 rows

Section D — Resumen por Categoria (Row 32+)

Cell	Label	Formula
A32	Header: "Stock por Categoria"	—
A33+	Category breakdown	=UNIQUE(Catalogo!C2:C) with adjacent SUMIFS for units and value

Step 4: Apply Formatting and Protection

• Format currency columns with $#,##0.00 (MXN).
• Apply alternating row colors on Catalogo and Movimientos for readability.
• Protect formula cells in Resumen (prevent accidental edits).
• Set column widths for comfortable reading.
• Add a "Last Updated" timestamp cell on Resumen: =MAX(Movimientos!A2:A).

Quality Criteria

• Every SKU in Catalogo is unique (no duplicates)
• Stock Actual is never negative (validated or flagged)
• All Categorias come from the standardized list
• Movimientos SKUs all resolve to valid Catalogo entries
• Resumen formulas calculate correctly with sample data
• Conditional formatting highlights low-stock products in red
• Currency formatting is consistent (MXN with 2 decimals)
• Evidence tags applied to all claims

Anti-Patterns

• Manual stock counts without movement logging (loses audit trail)
• Free-text categories (prevents aggregation and reporting)
• Negative stock allowed without investigation (indicates data error)
• Single sheet for everything (mixes master data with transactions)

Related Skills

• sales-tracker — sales generate outbound stock movements
• office-workflow-runner — chains inventory updates with reporting
• brand-xlsx — branded spreadsheet formatting

Usage

Example invocations:

• "/inventory-management" — Create a full inventory system from scratch
• "Crea un inventario para mi tienda" — Triggers via Spanish keyword matching
• "I need to track stock for 50 products" — Builds catalog with capacity for growth
• "Actualiza el inventario con estas entradas" — Logs movements to existing spreadsheet