Learn by Directing AI
All materials

analysis-spec.md

Analysis Specification — Khumalo & Grain Sales Analysis

Background

Naledi Khumalo runs Khumalo & Grain, an eighteen-person furniture workshop in Cape Town. The workshop designs and builds handcrafted hardwood furniture — dining tables, shelving units, desks, side tables, benches, and custom pieces. Sales flow through three channels: retail partners (Weylandts, @home, Stellenbosch Gallery), direct commissions (via Instagram and word of mouth), and online (through the workshop's website). Revenue is growing, but Naledi wants confirmation of which channel is actually profitable after accounting for retailer commissions, material costs, production time, and delivery fees. Her bookkeeper exported twelve months of clean sales data. This analysis answers her four business questions.

Business Questions

  1. Which sales channel is most profitable after all costs? Metric: channel profit margin. Compares retail, commission, and online channels after accounting for retailer commissions, material costs, and delivery costs.

  2. Which product types generate the most revenue per workshop-week? Metric: revenue per workshop-week. Normalizes revenue by production time to reveal which products use workshop capacity most efficiently. A high-price item that takes four weeks generates less per workshop-week than a lower-price item that takes one week.

  3. Are there channel-product patterns? Cross-tabulation: which product types sell best through which channels. Reveals whether certain products concentrate in certain channels.

  4. What is the monthly revenue trend? Metric: monthly net revenue. Twelve data points showing whether revenue is growing, flat, or seasonal.

Chart Specifications

Chart 1 — Net Revenue by Channel

  • Type: Bar chart
  • Data: Net revenue (excluding refunds) for each channel
  • Axes: x = channel names, y = ZAR
  • Labels: Descriptive title stating the question (e.g., "Which channel brings in the most revenue?"). Y-axis labelled in ZAR. Bar values labelled.
  • Purpose: Answers the revenue component of Question 1.

Chart 2 — Profit Margin by Channel and Product Type

  • Type: Grouped bar chart
  • Data: Profit margin percentage for each channel-product combination
  • Axes: x = product type, y = percentage, grouped by channel
  • Labels: Descriptive title. Percentage labels on bars. Legend for channels.
  • Purpose: Deepens Question 1 by showing how margins vary across products within each channel.

Chart 3 — Revenue per Workshop-Week by Product Type

  • Type: Bar chart
  • Data: Revenue per workshop-week for each product type
  • Axes: x = product type, y = ZAR per week
  • Labels: Descriptive title (e.g., "Which products generate the most revenue per week of workshop time?"). Y-axis in ZAR. Bar values labelled.
  • Purpose: Answers Question 2 — the capacity-adjusted view.

Chart 4 — Monthly Net Revenue Trend

  • Type: Line chart
  • Data: Net revenue by calendar month (12 data points)
  • Axes: x = month (date axis), y = ZAR
  • Labels: Descriptive title. Date axis formatted as month names. Y-axis in ZAR.
  • Purpose: Answers Question 4.

Verification Targets

These are the correct values computed from the dataset. Every metric you compute must match these targets. If your number does not match, check your computation against the metric definition.

Target Value
Total row count 845
Date range 2024-01-01 to 2024-12-31
Channel values commission, online, retail
Product type values bench, custom, desk, dining_table, shelving, side_table
Refund rows (negative sale_amount) 25
Total net revenue (excluding refunds) ZAR 11,574,000
Total gross revenue (including refunds) ZAR 11,232,000
Refund difference 3.0%
Net revenue — retail ZAR 5,326,500
Net revenue — commission ZAR 3,761,500
Net revenue — online ZAR 2,486,000
Retail channel profit margin 28.2%
Commission channel profit margin 65.5%
Online channel profit margin 59.2%
Revenue per workshop-week — dining_table ZAR 6,880
Revenue per workshop-week — shelving ZAR 8,622
Revenue per workshop-week — desk ZAR 6,971
Revenue per workshop-week — custom ZAR 7,173
Revenue per workshop-week — side_table ZAR 6,611
Revenue per workshop-week — bench ZAR 6,788

Key insight to verify: Shelving (ZAR 8,622/week) generates more revenue per workshop-week than dining tables (ZAR 6,880/week), despite dining tables having a higher sale price. This is the finding that changes how Naledi thinks about her product focus.

Common AI error to catch: AI will compute SUM(sale_amount) across all rows, including refunds, and report ZAR 11,232,000 as total revenue. The correct net revenue (excluding refunds) is ZAR 11,574,000. The difference is 3.0% — enough to catch by checking against the target, not dramatic enough to be obvious.

Prompt Starting Points

Use these as starting points. Adjust based on what you learn as you work.

Data loading prompt:

Load materials/khumalo-sales.csv into DuckDB. Profile every column: row count, column types, null counts, distinct values for categorical columns, min/max/mean for numeric columns, and the date range. Show me the results so I can check them against the data dictionary.

Metric computation prompt:

Using the metric definitions in materials/metric-definitions.md, compute net revenue by channel. Revenue EXCLUDES refund rows (negative sale_amount values). Show me the total and the per-channel breakdown so I can verify against the targets in the analysis spec.

Chart generation prompt:

Create a bar chart of net revenue by channel. Use a descriptive title that states the question the chart answers (not "Figure 1"). Label the y-axis in ZAR. Label each bar with its value. Use matplotlib or seaborn.

Narrative draft prompt:

Using the narrative template in materials/narrative-template.md, draft the executive summary, key findings, and recommendation. Use the verified metric values from my analysis. The audience is Naledi — she is not technical but understands her business. Use specific ZAR amounts in every finding.

Deliverables

What Naledi receives:

  1. Executive summary — 2-3 sentences identifying the most important findings
  2. Key findings — 3-4 numbered points, each with a specific metric and ZAR amount, explaining what it means for her business
  3. Four charts — One per business question, each with a descriptive title and labelled axes
  4. Recommendation — 1-2 paragraphs on what Naledi should focus on next year, grounded in the numbers