4.8 Merchandise Planning: Product Operations Analysis & Inventory Risk Alerts
One of the core responsibilities for merchandise planning and operations teams is the weekly product analysis — what's selling, what's profitable, what's at risk, what to push and what to clear. The process itself isn't complicated, but it's the same routine every week: open the sales table, inventory table, match SKUs, calculate sell-through and margin, find best-sellers and slow-movers, compile a report. After hours of work, only a few questions really need your judgment.
With AOS Work, you can have the Agent run the entire analysis pipeline — from data checking, metric calculation, product stratification, anomaly detection, root cause analysis to action recommendations. You make the business decisions.
The Merchandise Planning Workflow
Whether you're a merchandise planning manager or an operations specialist, the product analysis chain looks similar:
| Step | Traditional approach | Pain point |
|---|---|---|
| 1. Data preparation | Export sales, inventory, and product master data from ERP — manually format | SKU codes don't match across tables; data is missing, duplicated, or messy |
| 2. Metric calculation | Calculate sales, margin, sell-through, inventory turnover in Excel | Same formulas and pivot tables every week — redo everything for new data |
| 3. Product stratification | Break down by category, price tier — find core and low-performing products | Many dimensions, cross-analysis is time-consuming, easy to focus on sales while ignoring inventory |
| 4. Anomaly detection | Manually filter slow-movers, high-inventory, margin-anomaly SKUs | Fixed threshold catches everything — different categories have different norms, lots of false positives |
| 5. Root cause analysis | Guess based on experience: "demand dropped," "seasonal factors" | No data evidence, conclusions not traceable |
| 6. Action planning | Write a report, list recommendations, review in meeting | Report is outdated by the time the next week rolls around |
With AOS Work, these six steps happen in one conversation: the Agent reads the table data, auto-calculates metrics, breaks down structure, identifies anomalies, analyzes causes, and outputs a report with data-backed evidence and action recommendations.
Resources Used
| Type | Resource | What it does here | Notes |
|---|---|---|---|
| App | Base | Sales, inventory, product master data, analysis results | Throughout; keep SKU codes consistent across tables |
| App | Library | Historical reports and product image references | Weekly archive for year-on-year comparison; name by date |
| Scheduled task | Scheduled task | Daily anomaly monitoring, weekly analysis | Scenario B; run once manually before scheduling |
| Skill | Product operations analysis | Fixed analysis flow | Solidify after Scenario A; swap data weekly |
For the basic sales–inventory join and sell-through calculation, see Data analysis and insights. Turn analysis into presentation materials with PPT and HTML generation. This lesson extends the analysis to product segments, inventory age, and risk rules. Configure result lists with Base views and daily execution with Scheduled tasks.
Sub-scenario A: Weekly Product Operations Analysis — Six-Step Pipeline, From Data to Action
Pain Point
"Every week, I open three tables, match SKUs, calculate metrics, make pivot tables, find anomalies, write a report — half a day. Only a few SKUs really need my judgment, but 80% of the time is spent moving data and calculating metrics."
Steps
Step 1: Data Check
Don't start by saying "analyze the product sales." First check if the data is complete and SKUs can be linked.
-
Upload or reference three datasets: sales detail, inventory detail, product master data into Base
-
Ask the Agent to check: field completeness, time range, data type, duplicate records, missing values
-
Focus on whether SKU codes can be correctly linked across the three tables
-
Confirm data is usable before moving to analysis
Step 2: Run the Six-Step Analysis
After confirming the data is usable, follow the six analysis steps in the sample prompt.
Step 3: Review and Confirm
After the agent outputs the analysis report, confirm whether anomaly judgments match business reality, confirm whether root cause analysis is reasonable, and make the final business decisions (replenish / clear / reprice / delist).
Sample Prompt
1. Results: Summarize total sales, units sold, gross profit, margin, sell-through rate, inventory value, turnover rate, turnover days. Compare with the previous period and identify metrics with significant changes. Describe results first — don't assign causes.
2. Structure: Drill down by category → price tier → SKU. Analyze sales, margin, and inventory value share. Identify the categories with the highest sales contribution, highest margin contribution, and highest inventory share.
3. Efficiency: Link sales performance with inventory performance. For each SKU, calculate sales, margin, and turnover days. Classify into four types: high-sales high-turnover (core) / high-sales low-turnover (cash pressure) / low-sales high-inventory (risk) / low-sales low-inventory (to be determined).
4. Anomalies: Detect anomalies from four dimensions: sales, profit, inventory, sell-through. Don't use fixed thresholds — use year-over-year, week-over-week, and category averages. Cross-validate at least two indicators. Sort by impact amount.
5. Root cause: For SKUs with sales decline >20%, break down by: units sold, price, channel, stockout, promotion. Every conclusion must cite data evidence.
6. Actions: For high-inventory and low-sell-through SKUs, perform inventory aging analysis (0–30/31–60/61–90/91–180/180+ days). Combine with sales trend to assess risk. Output in "Problem → Data evidence → Cause → Impact → Action" format. Write all results to the "Product Analysis" table in the Base. Also output a summary in the conversation.
Exercise: Create a merchandise action report from three tables
Select Base and read the sales, inventory, and product master tables, matching records by product code (SKU). Identify zero or missing receipt quantities, then use the specified formulas to calculate revenue, gross profit, sell-through, and inventory turnover days. Save the results to the Product Analysis table.
Select Base and Library to read those results, group products by performance, and list stock age, issues, and supporting figures. Generate Merchandise Review.md with an owner and next action for each issue.
Expected Results
-
Data quality check report: field issues, missing values, SKU linkage status
-
Six-step analysis report: from overall results to SKU-level full pipeline
-
Anomaly SKU list: sorted by impact amount, with cause analysis and data evidence
-
Four-quadrant matrix: core / cash pressure / inventory risk / to be determined
-
Action recommendation list: each problem with "Problem → Evidence → Cause → Impact → Action"
-
All written to the table, queryable and referable anytime
Advanced: From One-Time Analysis to a Solidified Skill
Re-organising the data and rewriting the prompt every week means the flow is not yet fixed. Solidify it into a Skill and only the data changes each week.
Follow Creating a Skill to draft a "Product Operations Analysis Skill" with:
-
Data checking rules (SKU code consistency, missing value handling, anomaly identification)
-
Metric definitions and calculation formulas
-
Six-step analysis standard procedure and output format
-
Anomaly detection logic (cross-validation, category average comparison)
-
Action output template (Problem → Evidence → Cause → Impact → Action)
Once solidified, you just swap the data each week, and the Skill loads the analysis flow automatically. You go from "re-doing the analysis every week" to "re-running the flow every week." See Chapter 2: "Mastering Skills."
Sub-scenario B: Inventory Risk Alerts — Daily Auto-Monitoring, From Detection to Action
Pain Point
"Inventory risk doesn't happen once a week. SKUs turn into slow-movers, approach stockout, or exceed inventory age every single day. But I can't manually run the analysis every day. By the time the weekly report comes out, it's too late."
Steps
-
Set up an inventory monitoring view in the Base with: SKU, current inventory, last 7/14/30-day sales, inventory age, turnover days, safety stock level
-
Set alert rules: turnover days > 90 AND inventory age > 60 → alert; inventory < safety stock AND sales in last 7 days → stockout alert
-
Set up a scheduled task: every day at 9:00 AM, auto-scan the table, filter alert SKUs, generate a risk list
-
The Agent outputs: SKU list sorted by risk level, with initial cause assessment and suggested actions
-
You open AOS Work every morning — today's risk list is ready. No data pulling needed.
Sample Prompt
1. Slow-mover alert: Turnover days > 90 AND inventory age > 60 → flag "slow-mover risk"
2. Stockout alert: Current inventory < safety stock AND sales recorded in last 7 days → flag "stockout risk"
3. Excess aging: Inventory age > 180 days AND no sales in last 30 days → flag "dead stock"
4. Abnormal fluctuation: Last 7-day sales week-over-week decline > 50% → flag "demand drop"
Output: SKU list sorted by risk level, including SKU, product name, risk type, risk level, current inventory, inventory age, last 7-day sales, suggested action. Write to "Daily Risk List" table in the Base. Also output a summary in the conversation.
Exercise: Detect inventory risks and update follow-up
Select Base to check products for slow movement, stockout risk, aged stock, and falling sales. Save the issues to the Daily Risks table.
Assign the replenishment review for product A103 to the Operations team and mark it In progress. Query the remaining open high risks. Finally, generate the daily monitoring instructions in Inventory Monitoring Plan.md and configure execution using the Scheduled tasks instructions.
Expected Results
-
Daily risk list: sorted by level, with suggested actions (replenish / clear / reprice / monitor)
-
Four alert types: slow-mover risk / stockout risk / dead stock / demand drop
-
Auto-written to the table — team members can view directly
-
Downstream: weekly report auto-pulls this week's daily risk lists to generate a weekly inventory risk report
Summary
In short: Agent handles product analysis and risk alerts; you focus on business decisions.