Bicycle Sales Performance Review

A multi-year, multi-country sales extract is the standard input to a performance review, and the standard output is a revenue chart by year and category. Both are easy to produce. The risk is that the chart looks like a trend when it is mostly a coverage artefact, and that a revenue ranking is presented as a profit recommendation when the two point in different directions.

The decision

The commercial team needed to decide where to push growth and where to protect margin: which categories and countries carry revenue, which carry profit, and whether the apparent year-on-year movement is real. That required separating volume, value and margin — and being explicit that the file cannot answer questions about orders, customers or retention.

What we worked with

113,036 sales lines with 18 fields covering 2011 to 2016:

• Time: date, plus separate day, month name and year fields.
• Customer: age, age group and gender.
• Geography: country and state.
• Product: category, sub-category and product.
• Commercials: order quantity, unit cost, unit price, cost, revenue and profit.

Across the file, revenue totals 85.3 million and profit 32.2 million in the file's own units — there is no currency column. The overall margin is 37.8%.

The traps we designed around

• The years are not comparable. 2011 and 2012 contain only 2,677 rows each, and 2014 and 2016 stop on 31 July. Only 2013 and 2015 are full calendar years. A revenue-by-year chart built on all six will show a collapse and a rebound that are almost entirely coverage. Trend statements were restricted to comparable periods.
• 1,000 rows are exact duplicates. They are identical across every column, including the date. Left in, they inflate both revenue and profit; they need to be removed or the duplication disclosed.
• The date is supplied three different ways. Day, month name and year sit alongside the full date. Sorting on the month name gives alphabetical order (April, August, December...), and the derived fields can disagree with the date. Everything was re-derived from the date and checked back against the supplied columns.
• Six countries share one revenue column with no currency. Cross-country comparisons assume a common currency and price level that the file does not state. The values can be compared as a single reporting basis only if that assumption is written down; otherwise the comparison should be avoided.
• Revenue ranking is not profit ranking. Bikes generate about 72% of revenue but carry a 33% margin, while Accessories generate about 18% of revenue at a 59% margin. Clothing sits at 34%. Ranking categories by revenue and by profit produces different answers, and neither is complete without volume.
• Negative and zero profit lines exist. 58 lines have negative profit and 424 have zero profit. These may be returns, deliberate discounts or data errors. Each needs a rule; silently including them distorts category margin.
• There is no order ID and no customer ID. The file cannot compute orders, average order value, repeat purchase or retention. It is a sales-line extract, and any customer analysis built from it would be fabricated by aggregation.
• State names need a country. States are country-specific and must be grouped with the country rather than on their own.
• Two age fields. Customer age and a supplied age group both exist and must be checked against each other rather than both being trusted.

How the analysis was built

1. Deduplicate and reconcile. Exact duplicates were removed and the retained row count was reconciled against the original file.
2. Rebuild the calendar. Date parts were derived from the full date, and the supplied day, month and year columns were compared and set aside.
3. Restrict the trend view. Year-on-year comparisons were limited to full, comparable periods, with partial years shown separately and labelled.
4. Separate value, margin and mix. Revenue, cost, profit, margin rate and category share were reported together, so a high-revenue, low-margin category could not be mistaken for the strongest one.
5. Apply profit-line rules. Negative and zero profit lines were flagged and reported, with the treatment stated.
6. Build the country-by-category view. Volume and value were shown alongside margin, and state-level detail was nested under country.
7. State the limits. The report lists the questions the file cannot answer and the identifiers needed to answer them.

What this changes

• Trend claims are only made where coverage allows. Partial years no longer masquerade as growth or decline.
• Category decisions use profit and mix. The highest-revenue category is not automatically the priority.
• Duplicates and loss-making lines have documented rules. Two analysts working from this file now get the same total.
• Customer questions are routed elsewhere. If retention is the business question, the first deliverable becomes an order and customer identifier, not a chart.

What you receive

• A deduplicated sales table with row reconciliation.
• A period coverage note identifying comparable and partial years.
• Revenue, profit and margin views by category and country.
• A category mix analysis showing revenue share against margin rate.
• A limits and data-request note for order-level and customer-level questions.

Where this applies

The method fits retailers, distributors and manufacturers reviewing category, channel and market performance from a sales extract. The minimum inputs are transaction lines with date, product, quantity, revenue and cost, plus geography. The first deliverable is usually the coverage note, because multi-year retail files rarely have six comparable years no matter what the date range suggests.

This is an illustrative case study using an anonymized public retail sales dataset; it does not report results from a specific client engagement. It reflects our standard method for sales performance review.