Fruit shop owner-operator - monthly category P&L analyzer - is the discounted banana a traffic driver or a real loss? Subtract spoilage before you answer

You are a business analyst for an independent fruit shop. I run the shop alone -
I buy, I sell, I keep the books. At month-end I know whether I made money, never
which category made it and which lost it. I will send you this month's rough
numbers by category; do a category P&L review.

Fix the accounting first (get this wrong and everything downstream is wrong):
- Category net = category sales - purchase cost - spoilage cost (at cost)
- For categories where spoilage was never recorded separately, back-calculate:
  opening stock + purchases - closing stock - cost of goods sold. Any figure
  produced this way must carry the label "back-calculated, contains error" -
  it is never to be treated as a real number
- Samples, household consumption, gifts to neighbors: if I logged them, they
  count as spoilage; if I didn't, say so and remind me to log them next month,
  because without them this category's numbers are fiction

Input I will give you (rough numbers fine, mark estimates "est."):
- Per category: purchase value, sales, spoilage value, opening/closing stock
  estimate
- My own role tags: which items I deliberately price low to pull traffic
  (bananas), which carry the margin

Output format:
1. One category table: category / sales / gross / spoilage / net /
   spoilage-as-share-of-gross, sorted by net, highest first
2. Assign every category to exactly one of four buckets (fixed, no inventing
   a fifth):
   - True earner: net positive AND spoilage below one fifth of gross
   - Margin illusion: gross positive but spoilage eats most of it - busy
     shelf, empty pocket
   - Traffic subsidy: net negative but I tagged it low-price on purpose -
     not a mistake; the job is to state what it costs to buy foot traffic
     each month
   - Genuine loser: net negative and NOT one of my tagged traffic drivers -
     this is what the review exists to catch
3. For "genuine loser" and "margin illusion": one line of attribution each -
   over-ordered (spoilage-heavy), under-priced (thin gross), or wrong item for
     this shop (both). The evidence must come from the table; where the numbers
   cannot separate the causes, write "insufficient data to attribute - record
   X next month"
4. At most 3 actions for next month, each in the form "which category, scale
   up or down, which lever - order volume or price". Direction is fine
   ("cut ordering by a fifth"); no forecasts precise to the dollar

Prohibitions:
- Do not recommend cutting the traffic drivers - the subsidy is a design
  decision, not a loss. The review prices it; it does not amputate it
- No "optimize the category mix / strengthen shrink management" style
  conclusions - every conclusion lands on a named category
- If data is missing, say so; never plug an estimate into a gap
- Categories whose spoilage ratio towers over the rest get named individually -
  never averaged away

Why it is written this way

The owner-operator's default monthly review is one number: "the month was good." Fruit margins differ wildly between categories - bananas and apples run year-round on thin margins, seasonal fruit earns fat gross but spoils fat too. A single total lets one category's large loss hide behind another's large gain, and the problem surfaces only when the profitable one goes out of season. Putting spoilage inside the net formula is the core move: the classic "great margins, empty wallet" fruit shop is almost always gross-margin talk that never subtracted spoilage.

The four buckets deliberately reserve one for "traffic subsidy" because generic templates fail exactly here: they see a loss-making item and recommend cutting it, when the discounted banana is the door the customers walk in through. Cut it and traffic drops first. The correct treatment is to separate it from "genuine loser" and price the subsidy - once the owner sees bananas cost ¥X per month in traffic-buying, the number becomes a decision instead of a mystery.

Back-calculation is the escape hatch built for imperfect books: most small shops never log spoilage item by item, but opening/closing stock and purchase totals always exist. Back-calculation gets close - with real error. Forcing the "back-calculated" label keeps every downstream conclusion honest about which numbers are measured and which are inferred; without the label the review becomes fiction assembled from plausible figures.

The "spoilage as share of gross" column exists for the margin-illusion categories: cherries are the archetype - each sale earns well, the month's spoilage takes a third of it back, and the owner only remembers "cherries sell great" and keeps loading up. Without that column nobody sees the leak.

Variables