Procurement Analysis（采购运营分析）
Overview
This skill performs a comprehensive weekly procurement operations analysis for FQF (a packaging & printing company). It processes three data tables (raw materials, auxiliaries, outsourced processes), computes multi-dimensional KPIs, and generates a formatted Excel workbook + a consulting-style PPT presentation.
Trigger Conditions
Invoke this skill when:
- User says "我想做采购分析" or "采购分析"
- User provides procurement data files and asks for analysis
- User asks to generate a weekly/monthly procurement operations report
- User wants to analyze supplier performance, purchasing efficiency, or cost savings
Company Context
- Company: FQF — a packaging & printing enterprise
- Business Model: Purchases paper/corrugated board → processes/printing → sells packaging boxes to downstream customers
- Procurement Categories: Three major categories:
1. 原料 (Raw Materials): base paper, corrugated board, white cardstock, kraft paper, etc.
2. 辅料 (Auxiliaries): ink, glue, film, PE bags, dies, consumables, etc.
3. 外发工序 (Outsourced Processes): outsourced printing, lamination, die-cutting, mounting, etc.
Input Data
User provides three Excel files (one per category). Expected key fields:
原料 & 辅料 (shared schema, 61 columns):
| Field | Description |
|---|---|
| 采购单号 | Unique order ID |
| 采购单日期 | Purchase order creation date |
| 请购单日期 | Purchase requisition date |
| 供应商简称 | Supplier name |
| 物料名称 | Material name |
| 物料编码 | Material code |
| 类别名称 | Sub-category |
| 数量 | Quantity |
| 数量单位 | Unit (张/KG/包/etc.) |
| 报价单价 | Quoted unit price (原料 uses this for price analysis) |
| 单价 | Actual unit price (辅料 uses this) |
| 报价单价单位 | Price unit (M2/T/无) |
| 采购单金额 | Total amount |
| 创建人姓名 | Buyer name |
| 请购人姓名 | Requisitioner name |
| 请购部门名称 | Requesting department |
| 交货日期 | Delivery date |
| 请购交期 | Requested delivery date |
| 入库数量 | Received quantity |
| 退货数量 | Return quantity |
| 取消 | Cancelled flag |
| 工作流状态 | Workflow status |
外发工序 (88 columns):
| Field | Description |
|---|---|
| 外发单号 | Outsourcing order ID |
| 外发单日期 | Order creation date |
| 供应商名称 | Supplier name |
| 产品名称 | Product name |
| 工序名称 | Process name (拉瓦/丝印/贴撕拉条) |
| 数量 | Quantity |
| 单价 | Unit price |
| 金额 | Total amount |
| 交货日期 | Delivery date |
| 入库数量 | Received quantity |
| 次品数量 | Defect quantity |
| 付款期名称 | Payment terms |
| 创建人姓名 | Buyer name |
Analysis Framework
1. 采购全貌分析 (Spend Overview)
- Total metrics: Total spend, order count, per-category amount & share (with WoW comparison if historical data available)
- ABC Classification (by material-level spend, Pareto principle):
- A类 (Strategic): Cumulative 0-70% of spend
- B类 (Normal): Cumulative 70-90%
- C类 (General): Cumulative 90-100%
- Supplier Analysis:
- Concentration: CR5, CR10 (top 5/10 supplier share)
- Dependency: Flag suppliers with >30% share (single-source risk)
- Supplier count and ranking
2. 工作效率分析 (Efficiency Analysis)
Three lead-time metrics (split by category):
| Metric | Formula | Meaning |
|---|---|---|
| Internal Processing Time | 采购单日期 - 请购单日期 | Procurement dept efficiency |
| Supplier Delivery Performance | 实际交货日期 - 请购交期 | Supplier fulfillment |
| Full Lead Time | 实际交货日期 - 请购单日期 | End-to-end cycle |
- Buyer Efficiency: Per-buyer average processing time, identify bottleneck personnel
- On-time Delivery Rate = (on-time + early) / total × 100%
- Urgent Procurement: Defined as (请购交期 - 请购单日期) ≤ 1 day. Calculate urgent order count, percentage, and amount
3. 效果分析 (Effectiveness)
- Price Analysis: Per-category unit price stats (min/max/avg), with WoW/YoY if historical data available
- Cost Savings Quantification: Savings = (baseline price - actual price) × quantity; attribute to market vs buyer effort
- Market Reference (paper industry specific):
- 原纸/纸板: 中纸网, 隆众资讯纸业板块
- 纸浆: 中国造纸协会价格指数, Fastmarkets RISI
- 化工辅料: 卓创资讯化工板块
- 废纸浆: 海关总署进口废纸价格指数
- If market data unavailable, mark as "需人工补充市场数据"
- Delivery Quality: Receiving rate, return rate, defect rate
4. 规划与改善 (Planning & Improvement)
- Key findings (TOP 3-6 with specific data)
- Improvement actions with owner and timeline
- Risk alerts (single-source dependency, urgent procurement, low receiving rate)
Workflow
- Data Loading: Read three Excel files, clean numeric/date fields, tag category
- Compute Metrics: Calculate all KPIs per the framework above
- Generate Excel (5 sheets):
- Sheet 1: 采购全貌 (summary + ABC classification + supplier concentration)
- Sheet 2: 效率分析 (lead time + delivery + buyer efficiency + urgent procurement)
- Sheet 3: 效果分析 (price analysis + quality + payment terms)
- Sheet 4: 图表Dashboard (5+ charts: pie, bar, comparison)
- Sheet 5: 规划与改善 (key findings + improvement plan) - User Confirmation: Present key findings, ask for corrections
- Generate PPT (12 slides, consulting style):
- Slide 1: Cover
- Slide 2: Executive Summary (6 KPI cards)
- Slide 3-5: Spend Overview, ABC Classification, Supplier Concentration
- Slide 6-8: Efficiency, Delivery Performance, Urgent Procurement
- Slide 9-10: Price Analysis, Delivery Quality
- Slide 11: Improvement Plan
- Slide 12: Closing
Excel Generation Rules
- Use openpyxl with formulas (not hardcoded values)
- Font: 微软雅黑
- Header: dark blue fill (#2F5496) + white bold text
- Zebra striping: alternating #FFFFFF / #F7F9FC
- KPI highlight: light blue fill (#EAF2FF)
- ABC color coding: A=red(#FFC7CE), B=amber(#FFEB9C), C=green(#C6EFCE)
- Risk colors: green=good, red=risk, amber=warning
- Include charts (pie, bar) using openpyxl.chart
- Run
scripts/recalc.pyto validate formulas after generation - Numeric precision: amounts to 2 decimals, rates to 1 decimal
PPT Generation Rules
- Tool: pptxgenjs
- Layout: LAYOUT_16x9 (10" × 5.625")
- Theme: Midnight Executive (primary=#1E2761, secondary=#3B4990, accent=#CADCFC)
- Title font: Georgia (serif), Body font: Calibri
- Each slide MUST have visual elements (charts, cards, tables) — no text-only slides
- KPI cards: white background, colored left accent bar, large number + small label
- Run
scripts/validate_layout.py— must pass with 0 issues - Content QA via markitdown extraction
Data Quality Notes
- 原料 may have 23+ records with null 请购单日期 — exclude from efficiency calc, note in report
- 牛卡类 may have mixed price units (T/M2/无) — flag as data quality issue
- 外发工序 has no 请购单日期 — internal processing time cannot be calculated
- 外发工序 交货日期 may be system timestamp, not actual receiving date — note caveat
- If historical data is unavailable, skip WoW/YoY analysis and note explicitly
Market Reference Sources (Paper Industry)
| Material | Recommended Source |
|---|---|
| 原纸/纸板 (base paper) | 中纸网, 隆众资讯纸业板块 |
| 纸浆 (pulp) | 中国造纸协会价格指数, Fastmarkets RISI |
| 油墨/化工 (ink/chemicals) | 卓创资讯化工板块 |
| 废纸浆 (waste paper pulp) | 海关总署进口废纸价格指数 |
NOT suitable for paper industry: 上海有色网 (non-ferrous metals), 中国建设工程造价信息网 (construction cost), Bloomberg (too broad)
Output File Naming
- Excel:
FQF采购部周度运营分析_{周期}.xlsx - PPT:
FQF采购部周度运营分析_{周期}.pptx - Example:
FQF采购部周度运营分析_7月第二周.xlsx
Quality Checklist
Before delivering:
- [ ] Excel: zero formula errors (validated via recalc.py)
- [ ] Excel: all 5 sheets populated with data + charts
- [ ] Excel: ABC classification thresholds correct (A≤70%, B 70-90%, C 90-100%)
- [ ] PPT: layout validation passed (0 issues)
- [ ] PPT: 12 slides, no blank slides
- [ ] PPT: every slide has visual element (chart/card/table)
- [ ] Data quality issues noted in output (null dates, mixed units, etc.)
- [ ] Market reference data marked as "需人工补充" if unavailable
