Capstone: The Big Question

The founder sits down, and looks worried. The bank is asking questions. Should we close our weakest cafes? That decision affects jobs, and hundreds of thousands of dollars. Everything you've learned leads to this. Let's work through it the right way.

• The bank is asking questions
• Close the weakest cafes?
• Everything leads to this

Framing the problem

First, frame it. The decision is, which cafes, if any, should we close? The metric is net profit after rent, not revenue. And we need to ask what we're missing. Would customers move to a nearby cafe? Is a weak cafe still new? What would closing cost? A good framing prevents an expensive mistake.

Step one: true profit

We take last year's sales, join store details, and estimate the rent, based on size. Net profit is what's left. Margin shows it as a share of revenue. Run it. Look at the top of this list. Giza and Milan. Milan is one of our biggest earners, but after rent, it's losing money. If we'd only looked at sales, we'd have got this wrong.

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["date"])
stores = pd.read_csv("stores.csv")

year = sales[sales["date"] >= "2025-01-01"]
city = year.groupby("store_id").agg(city=("city", "first"), revenue=("revenue", "sum"),
                                    profit=("profit", "sum"))
city = city.merge(stores[["store_id", "size_m2"]], on="store_id")
city["rent"] = city["size_m2"] * 1100 + 40000
city["net"] = city["profit"] - city["rent"]
city["margin"] = city["net"] / city["revenue"]

print(city[["city", "profit", "rent", "net", "margin"]].sort_values("net")
      .round(2).head(4).to_string(index=False))

  city    profit   rent       net  margin
  Giza 182744.36 228100 -45355.64   -0.16
 Milan 235284.57 256700 -21415.43   -0.06
Bergen 224706.90 241300 -16593.10   -0.05
  Lima 209040.67 220400 -11359.33   -0.04

Step two: is it real?

Before closing anything, make sure that it isn't just a bad year. We bootstrap the weekly profits of the two weakest cafes, to get a range for the year, and we compare that with their rent. Run it. Now we've got an honest range for each one. Both ranges sit fully below the rent, so this weakness is real. It isn't just a bad year.

import numpy as np
import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["date"])
w = sales.groupby(["city", pd.Grouper(key="date", freq="W")])["profit"].sum().reset_index()

rng = np.random.default_rng(0)
for city, rent in [("Giza", 228100), ("Milan", 256700)]:
    s = w[w["city"] == city]["profit"].tail(52).values
    boot = [rng.choice(s, 52).sum() for _ in range(3000)]
    low, high = np.percentile(boot, [5, 95])
    print(f"{city:<6} profit {low:,.0f} to {high:,.0f}, rent {rent:,}")

Giza   profit 173,725 to 185,914, rent 228,100
Milan  profit 224,023 to 238,990, rent 256,700

Step three: which way is it going?

A weak cafe that's improving fast is very different from one that's getting worse. We compare the latest quarter with the same quarter a year earlier, so that seasons don't fool us. Run it. Giza has the slowest growth in the group, but it's still growing, by about twelve percent. So nothing here is collapsing. That changes the conversation.

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["date"])
w = sales.groupby([pd.Grouper(key="date", freq="QE"), "city"])["profit"].sum().unstack()

trend = (w.iloc[-1] / w.iloc[-5] - 1).sort_values()
print("Change on the same quarter last year:")
print((trend * 100).round(1).head(4).to_string())

Change on the same quarter last year:
city
Giza     12.2
Cairo    13.1
Cusco    13.1
Rome     13.4

Summary