Coca-Cola US Sales in Excel

How can sales and profitability improve across US retailers?

1. Where: how do sales vary by retailer, region, state and brand?
2. When: is there a seasonal pattern in sales and margin?
3. How well: where are margins strong, and where are they weak?

The data: 3,888 invoice lines from 1 January to 31 December 2021, covering 4 retailers, 5 regions, 50 states and 6 brands.

Everything live in Excel, margins weighted

• Prepare. No missing values or duplicates; real dates and numbers; header frozen, filters on; a Month helper column. The original file stays untouched.
• Aggregate. SUMIFS for every retailer, region, brand, month and state total.
• Weight the margins. Operating profit divided by sales, never a simple average of row-level percentages, so big orders count in proportion.
• Show. A dashboard with KPI tiles and 7 charts, colour-scale heatmaps (region × brand), and an Explorer sheet with dropdowns for retailer, region and brand.

Volume and profit point in different directions

• $8.68Mtotal sales
• $3.17Moperating profit
• 36.5%weighted margin
• 17.15Munits sold

The same pattern shows by region: the West brings in about a third of sales at the lowest margin (32.6%), while the South is smaller but the most profitable (42.0%). Among brands, Coca-Cola leads at 39.3% and Diet Coke trails at 33.4%.

Demand is seasonal, and the margin barely moves, so the swings come from volume, not profitability:

What I would recommend: renegotiate terms with FizzySip and in the West, grow BevCo and the South, time promotions around July and December, and review Diet Coke and Sprite pricing or costs.

Three oddities I left in, and flagged

• Zero-sale rows. Units sold has a minimum of 0, so a few rows record no sales.
• Wyoming and Utah are identical, down to the cent: $199,937.50 in sales and $69,359.38 in profit.
• South region = BevCo. The two contain exactly the same rows.

I kept them as supplied so the totals tie back to the source, and recommended verifying them with the data owner before acting on any of the findings.