Chapter 4 · Role-Specific Scenarios

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:

StepTraditional approachPain point
1. Data preparationExport sales, inventory, and product master data from ERP — manually formatSKU codes don't match across tables; data is missing, duplicated, or messy
2. Metric calculationCalculate sales, margin, sell-through, inventory turnover in ExcelSame formulas and pivot tables every week — redo everything for new data
3. Product stratificationBreak down by category, price tier — find core and low-performing productsMany dimensions, cross-analysis is time-consuming, easy to focus on sales while ignoring inventory
4. Anomaly detectionManually filter slow-movers, high-inventory, margin-anomaly SKUsFixed threshold catches everything — different categories have different norms, lots of false positives
5. Root cause analysisGuess based on experience: "demand dropped," "seasonal factors"No data evidence, conclusions not traceable
6. Action planningWrite a report, list recommendations, review in meetingReport 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

TypeResourceWhat it does hereNotes
AppBaseSales, inventory, product master data, analysis resultsThroughout; keep SKU codes consistent across tables
AppLibraryHistorical reports and product image referencesWeekly archive for year-on-year comparison; name by date
Scheduled taskScheduled taskDaily anomaly monitoring, weekly analysisScenario B; run once manually before scheduling
SkillProduct operations analysisFixed analysis flowSolidify 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.

  1. Upload or reference three datasets: sales detail, inventory detail, product master data into Base

  2. Ask the Agent to check: field completeness, time range, data type, duplicate records, missing values

  3. Focus on whether SKU codes can be correctly linked across the three tables

  4. 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

Read the sales detail, inventory detail, and product master data from the Base. First check data quality: field completeness, time range, duplicate records, missing values, whether SKU codes can be correctly linked across the three tables. Output a data quality check report, listing issues to fix. After confirming data is usable, proceed with the six-step analysis:
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

  1. 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

  2. Set alert rules: turnover days > 90 AND inventory age > 60 → alert; inventory < safety stock AND sales in last 7 days → stockout alert

  3. Set up a scheduled task: every day at 9:00 AM, auto-scan the table, filter alert SKUs, generate a risk list

  4. The Agent outputs: SKU list sorted by risk level, with initial cause assessment and suggested actions

  5. You open AOS Work every morning — today's risk list is ready. No data pulling needed.

Sample Prompt

Run daily at 9:00 AM: Query the "Inventory Monitoring" view in the Base, scan all active SKUs, detect risks by the following rules:
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.