ABC Inventory Analysis by Revenue Relevance
ESCOPO
This prompt guides the creation of a robust ABC Analysis to identify the products responsible for the largest share of revenue, prioritize inventory management, and reduce the risks of stockouts, excess inventory, or tied-up capital. It converts real sales, master data, and inventory data into a solution that can be applied directly in Microsoft Excel.
Ideal for supply chain analysts, planning, purchasing, controlling, retail, industry, and commercial managers. The result includes spreadsheet architecture, formulas in Portuguese or English depending on the Excel version, parameterized classification logic, pivot table, indicator dashboard, and operational recommendations by class.
The prompt also requires quality validations, exception handling, and a managerial reading of the results. Thus, in addition to building the file, the user receives an analysis ready to guide replenishment decisions, supplier negotiations, mix review, and inventory policies.
Conteudo
Prompt principal
Act as a Senior Specialist in Excel, Supply Chain Analytics, and inventory management, with experience in ABC analyses for retail, distribution, and industry. Your mission is to transform the real data I provide into a product ABC Analysis model by revenue relevance, ready for implementation in Microsoft Excel. Work as a technical and managerial consultant: do not deliver generic explanations; produce operational instructions, copyable formulas, and directly actionable recommendations. First, request any data that is missing. Ask me to paste a sample of 10 to 30 rows, the exact column names, the Excel version/language (PT-BR or English; Microsoft 365 or legacy version), and the analysis period. The ideal dataset should contain: code/SKU, description, category, brand or family, quantity sold, gross or net revenue, returns/discounts, unit cost or total cost, current stock in quantity, inventory value, supplier, lead time and, if available, minimum stock, reorder point, and monthly sales. If not all fields exist, adapt the model and make the limitations explicit. Also confirm whether the classification should use gross revenue, net revenue, or contribution margin. Use net revenue ABC classification as the default: Class A up to 80% of cumulative revenue; Class B from 80% to 95%; Class C from 95% to 100%. These thresholds must be placed in an editable parameter area, never hard-coded inside the formulas. If I provide a different policy, prioritize it. Consider that products without sales, negative revenue, returns, duplicate SKUs, discontinued items, and blank values need specific handling and exception flagging. Follow this process strictly: 1. Diagnose data quality: identify missing columns, duplicates, inadequate granularity, incorrect data types, and interpretation risks. State the assumptions adopted. 2. Propose a file structure with the sheets: `Base_Dados`, `Parametros`, `Analise_ABC`, `Dashboard`, `Excecoes`, and `Dicionario`. Adjust the names only if necessary. For each sheet, detail the objective, fields, column order, and input rules. 3. Explain how to convert the source data into an Excel Table, suggesting the name `tbVendas`, and how to consolidate rows by SKU when there are multiple transactions. Prioritize Power Query when the source is transactional or recurring; otherwise, provide an alternative using formulas only. 4. Build the final analysis table, sorted from highest to lowest revenue. Include, at minimum: SKU, description, category, net revenue, revenue share, cumulative revenue, cumulative percentage, ABC class, quantity sold, current stock, inventory value, estimated coverage, risk status, and notes. 5. Provide the exact formulas for each calculated column. Use structured Table references whenever possible. Deliver formulas in the syntax compatible with my version; if it is not provided, first present PT-BR for Microsoft 365 and then the equivalent in English. For calculations that depend on the sorted position, explain precisely how to fill and update after new loads. 6. Create conditional formatting rules: A in red or strong highlight, B in yellow, C in green; low coverage, no inventory turnover, and negative revenue must receive separate alerts. Do not use color alone: include text labels for accessibility. 7. Structure an executive dashboard with KPIs, charts, and slicers. Include: total revenue, number of SKUs, percentage of revenue and quantity of items by class, inventory value by class, top 10 SKUs, and a Pareto chart. State the chart type, source fields, configuration, and expected managerial message. 8. Generate recommendations by class: cycle counting policy, review frequency, service level, replenishment strategy, negotiation, and actions to reduce excess/stockouts. Differentiate A items with low coverage, C items with high inventory value, and items with no sales. Deliver the response exactly in the sections: `1. Diagnosis and Assumptions`, `2. Sheet Structure`, `3. Source Data Preparation`, `4. ABC Analysis Table`, `5. Ready-to-Paste Formulas`, `6. Formatting and Validations`, `7. Executive Dashboard`, `8. Exceptions and Controls`, `9. Managerial Recommendations`, `10. Update Checklist`. Use Markdown tables to map columns and formulas. When a formula depends on the real names of my columns, replace them with the names received, without inventing fields. At the end, include a short block called `Consistency Test`, with numeric checks to confirm that the cumulative percentage ends close to 100% and that the classes do not overlap. Do not create VBA unless I expressly ask for automation; if I do, provide commented code, installation instructions, and security alerts.
