Interactively model supply-chain margins across roles—from farm to distributor to bodega.
| Column name | What it captures |
|---|---|
| Produce | The exact crop or item (acts as the unique key). |
| Category | Broad food group (Fruit, Vegetable, Herb, etc.) so the dashboard can apply group-level assumptions. |
| Retail Price $/lb | Shelf price shoppers pay per pound, taken from USDA-ERS national averages (editable). |
| Retail Margin % | Retailer's target gross-profit percentage on that item (default ≈ 37 %). |
| Retail Handling $/lb | In-store labor, utilities, packaging, and other costs per pound (default $0.15 for fruit/veg, $0.50 for herbs). |
| Bodega Shrink % | Percent of product lost inside the store to spoilage or theft; model subtracts this before profit calc (defaults ≈ 11–12 %). |
| Farm Markup % | Grower's desired markup added to production cost (default 15 %). |
| Farm Cost $/lb | Estimated cost for the farm to grow, harvest, cool, and pack one retail-ready pound. |
| Farm Sale $/lb | Actual price the farm receives: Farm Cost × (1 + Farm Markup %). |
| Farm Shrink % | Post-harvest loss before the load leaves the farm (default 2 %). |
| Dist. Handling $/lb | Cooling, storage, trucking, and admin cost for the distributor (default $0.22 fruit, $0.20 veg, $1.00 herbs). |
| Dist. Margin % | Distributor's target gross-profit percentage (default 12 %). |
| Dist. Sale $/lb | Price charged by the distributor: (Farm Sale + Dist. Handling) × (1 + Dist. Margin %). |
| Dist. Shrink % | Loss in the warehouse or during transit (default 4 %). |
| Qty lbs | Planned volume in pounds you expect to move for this item (user editable; powers the totals). |
| Farm Gross Profit $/lb | Extra cents the farm keeps on each pound after costs: Farm Sale − Farm Cost (read-only). |
| Farm Gross Margin % | Farm gross profit expressed as a percent of Farm Sale price. |
| Dist. Gross Profit $/lb | Distributor's leftover cents per pound after paying the farm and its own handling cost. |
| Dist. Gross Margin % | Distributor gross profit as a percent of Dist. Sale price. |
| Bodega Gross Profit $/lb | Store's leftover cents per pound after paying the distributor, subtracting shrink, and covering retail handling costs. |
| Bodega Gross Margin % | That gross profit written as a percent of the shelf price. |
| KPI | Formula (column names) | Why it matters & how to read | If the number looks too low … | If the number looks very high … |
|---|---|---|---|---|
| Farm Gross Profit per Pound | Farm Sale $/lb − Farm Cost $/lb |
How many cents the farm retains on each pound after all growing, harvesting, cooling, and packing costs. | Raise sale price to distributor; trim production costs (inputs, labor, packaging); improve yield to spread fixed costs. | Consider reinvesting in quality upgrades or paying down debt—avoid over-pricing the market. |
| Farm Gross Margin % | (Farm GP $/lb ÷ Farm Sale $/lb) × 100 |
Percentage profit. Many produce farms aim for 15–25 %. | Same levers as above; also verify markup % is realistic. | If above 30 % for a staple crop you may risk losing buyers. |
| Distributor Gross Profit per Pound | Dist. Sale $/lb − Farm Sale $/lb − Dist. Handling $/lb |
Left-over cents for wholesaler after paying the farm and covering cooling, storage, trucking, and labor (goal 8–15 ¢). | Negotiate lower farm price; raise distributor margin % ; cut fuel or warehouse costs. | Use cushion to offer volume discounts or invest in better cold-chain equipment. |
| Distributor Gross Margin % | (Dist. GP $/lb ÷ Dist. Sale $/lb) × 100 |
Efficiency score—most regional produce houses run 10–15 %. | Below 8 %: route may be unprofitable—revisit prices or drop slow items. | Over 18 % could trigger retailer push-back; consider strategic discounts. |
| Bodega Gross Profit per Pound | (Retail Price $/lb × (1 − Bodega Shrink % /100)) − Dist. Sale $/lb − Retail Handling $/lb |
Store's cents after paying distributor, factoring spoilage/theft, and in-store handling. Target 35–45 ¢. | Raise shelf price; run promos to cut shrink; reduce handling costs. | Leverage surplus for specials to draw traffic or upgrade displays. |
| Bodega Gross Margin % | (Bodega GP $/lb ÷ Retail Price $/lb) × 100 |
Percent margin. Supermarkets average ~37 %. | Below 25 %: under-pricing or wasting too much. | Above 45 %: check competitiveness; shoppers may trade down. |
| Total Quantity (lbs) | Σ Quantity lbs for visible rows | Planned volume for selected crops. | — | — |
| Total Revenue ($) | If Farm → Σ(Farm Sale $/lb × Qty) Distributor → Σ(Dist. Sale $/lb × Qty) Bodega → Σ(Retail Price $/lb × (1 − Shrink %) × Qty) |
Top-line sales dollars for chosen role, adjusted for shrink when viewing as Bodega. | Increase quantity, price, or both; trim shrink (retail role). | Check capacity (acreage, truck or shelf space) to handle volume. |
| Total Gross Profit ($) | Role-specific GP per pound × Qty, summed for visible rows | Absolute earnings for the batch. | Adjust per-pound GP levers starting with highest-volume items. | Reinvest; ensure long-term demand isn't hurt. |
| Average Gross Margin % | (Total Gross Profit ÷ Total Revenue) × 100 | Weighted margin across everything selected—big-picture health. | Below benchmark: focus on low-margin items or renegotiate large contracts. | Above benchmark: great—monitor competitor prices to stay sustainable. |
| # | Source description | Verified link |
|---|---|---|
| 1 | USDA ERS – Fruit & Vegetable Prices (interactive tables with national average retail prices per pound) | View |
| 2 | USDA ERS – Price Spreads from Farm to Consumer (farm-share percentages for fresh fruits and vegetables) | View |
| 3 | USDA ERS – Food Dollar Series, 2023 Chart of Note (wholesale-trade share = 11.4 ¢ of the food dollar) | View |
| 4 | USDA ERS – Chart of Note: Supermarket shrink varies by type of fresh fruit and vegetable (shrink averages = 12.6 % fruit / 11.6 % veg) | View |
| 5 | USDA ERS – Updated Supermarket Shrink Estimates for Fresh Foods (full methodology PDF, EIB-155) | |
| 6 | International Fresh Produce Association – Supermarket Produce Department Benchmarks (2024) (reports 37 % average produce gross margin) | View |