Inventory Control with Automatic Replenishment Alerts

# www.prompthubai.com.br
# Encontre prompts, agentes e workflows testados para vender, programar e automatizar com IA em português.

# Inventory Control with Automatic Replenishment Alerts

## Cabecalho
- Tipo: Conteudo
- Categoria: Automação Comercial
- Modulos: 0
- Agentes: 0

## Escopo
In addition to the spreadsheet structure, the prompt includes alerts through conditional formatting, a management dashboard, and, when needed, a VBA alternative for notifications and automated updates. The result prioritizes traceability, simple maintenance, and practical implementation.

## Prompt Principal
Act as a senior expert in Microsoft Excel, materials planning (MRP), purchasing, operations, and inventory control. Based on the data and context I provide, design a complete inventory control solution with automatic replenishment alerts, ready for implementation in Microsoft Excel. Do not deliver generic recommendations: produce an operational specification, copyable formulas, sheet structure, and automation rules suited to my scenario.

Before developing the solution, analyze the information I will paste below. If there is insufficient, inconsistent, or ambiguous data, list the gaps objectively and assume clearly identified temporary parameters, without stopping the delivery. Consider the Brazilian reality: formulas in Brazilian Portuguese, argument separator with semicolon, dates in dd/mm/yyyy format, and currency in R$ when applicable.

DATA AND CONTEXT TO BE PROVIDED BY THE USER:
1. Excel version: [Excel 365, 2021, 2019, or other].
2. Segment and purpose of the inventory: [retail, manufacturing, warehouse, e-commerce, etc.].
3. Approximate number of SKUs and storage locations.
4. Current replenishment policy, if any: [minimum/maximum, reorder point, days of coverage, etc.].
5. Average delivery time by supplier or by item, in days.
6. Desired safety stock or expected service level.
7. Update frequency: [daily, weekly, manual, ERP import].
8. Available data, pasted in table format: product master data, current balance, entries, exits/sales, open orders, suppliers, costs, and lead times. Include the names of the existing columns.
9. Constraints: use or non-use of VBA, need for multiple warehouses, lots, expiration, units of measure, and user permissions.

Follow this framework exactly:

STEP 1 — Diagnosis and assumptions: summarize the current process, identify risks of stockout, excess, missing data, and master data inconsistencies. Define the calculation assumptions, including average consumption, analysis period, lead time, safety stock, reorder point, and suggested purchase quantity. Briefly explain the logic behind each indicator.

STEP 2 — File architecture: propose a sheet structure with clear names, purpose, update owners, and main columns. At a minimum, assess the need for the sheets: Configuracoes, Produtos, Movimentacoes, Estoque_Atual, Fornecedores, Pedidos_Compra, Alertas_Reposicao, and Dashboard. Determine which ranges should be converted into Excel Tables and suggest technical names for them, such as tbProdutos and tbMovimentacoes.

STEP 3 — Fields and formulas: present a table with the final columns of the main inventory sheet, their definition, and the exact formula to insert. Use structured table references whenever possible. Include, as applicable: available balance, reserved stock, stock in transit, daily average consumption, monthly average consumption, lead time, safety stock, reorder point, days of coverage, stock status, suggested purchase quantity, stock value, and ABC classification. For each formula, provide the destination cell/column, the formula in Portuguese, and how to replicate it. If my version does not support modern functions such as FILTRO, ÚNICO, PROCX, or LET, provide a compatible alternative.

STEP 4 — Automatic alerts: create detailed conditional formatting rules for critical status, replenishment needed, caution, excess, and item with no movement. Specify the applied range, each rule's formula, priority, and suggested colors. Also create an Alertas_Reposicao view that lists critical items and prioritizes them by financial impact, days of coverage, and urgency.

STEP 5 — Optional automation: if VBA is allowed, deliver a complete, commented, and safe VBA code to update calculations, sort alerts, and display a message with the number of critical items when the file is opened or updated. Explain exactly where to paste the code, how to save as .xlsm, and how to enable macros. If VBA is not allowed, provide an alternative with Power Query, Tables, slicers, and controlled manual refresh.

STEP 6 — Dashboard and governance: propose 5 to 8 KPIs, suitable charts, filters/slicers, and an update routine. Include indicators such as total inventory value, items below reorder point, stockout risk, average coverage, slow-moving items, and potential purchase value. Also define data validations, protection for formula cells, and controls to prevent duplicate entries or negative balances.

EXACT RESPONSE FORMAT:
1. Diagnosis and assumptions.
2. Sheet map in table format.
3. Column structure by sheet.
4. Ready-to-copy formulas, in code blocks.
5. Conditional formatting rules.
6. Optional VBA code or non-VBA alternative.
7. Dashboard layout.
8. Implementation, testing, and operating checklist.

Quality constraints: do not invent fields that cannot be supported by the data; flag dependencies; use sheet names without problematic characters; prioritize auditable formulas; explain only what is necessary for application; and deliver a scalable solution for at least 5,000 SKUs and 50,000 transactions.