Coffee Shop Financial Model: How to Build Projections in Excel

A coffee shop lives or dies on thin margins, so the numbers need to work on paper before you sign a lease. This guide walks through a coffee shop financial plan in Excel — what it costs to open, how to forecast revenue, the cost benchmarks that matter, and how many cups a day you need to break even.

A practical guide from the team behind DraftPlan.

What is a coffee shop financial model?

A coffee shop financial model is a spreadsheet that projects sales, costs, and cash for your shop based on your own assumptions about traffic, pricing, rent, and staffing.

It connects the levers you actually control — transactions per day, average ticket, hourly wages, lease terms — to the outcomes a lender or landlord cares about: monthly profit, cash on hand, and when the shop pays back its startup cost. Because café margins are slim, the model's real job is to answer hard questions before you spend money: Can you afford this rent at realistic traffic? How many baristas can you staff? What happens to cash in a slow January?

How much does it cost to open a coffee shop?

Opening a coffee shop typically costs $80,000–$300,000 for an espresso bar or small café, and can exceed $450,000 for a full-service shop with a kitchen and seating. A cart or kiosk can start at $25,000–$75,000.

Model startup costs as a one-time block of spending before opening, and don't forget working capital — the cash to cover the first few months while sales ramp. Typical ranges for an independent espresso bar:

Illustrative startup cost ranges for an independent coffee shop (US).

Line item	Typical range
Build-out / leasehold improvements	$50,000–$200,000
Equipment (espresso machine, grinders, refrigeration, POS)	$40,000–$120,000
Furniture & fixtures	$10,000–$40,000
Initial inventory	$5,000–$15,000
Licenses, permits & deposits	$5,000–$20,000
Working capital (3–6 months of operating costs)	$30,000–$80,000

Ranges are industry rules of thumb and vary widely by city, lease, and whether the space is a conversion or a full build. Use them as a starting point, then replace with real quotes.

How do you forecast coffee shop revenue?

Forecast revenue from two drivers — transactions per day × average ticket — rather than guessing a monthly total.

An independent shop typically does 150–500 transactions a day at an average ticket of $5–$8. So a shop doing 250 transactions at a $6 ticket makes about $1,500 a day, roughly $45,000 a month, or near $540,000 a year. To make the forecast credible, layer in three adjustments:

• Opening ramp. Don't assume full traffic on day one. Most shops build over the first 6–12 months as regulars form.
• Daypart and day-of-week. Mornings and weekends carry most cafés. Model a weekday/weekend split rather than a flat average.
• Seasonality. Foot traffic and drink mix shift through the year (iced drinks in summer, a winter dip in many markets).

What are the main costs to model?

The three costs that decide whether a café works are cost of goods, labor, and rent — together they should leave room for a profit.

Model each as a percentage of revenue and watch the benchmarks:

Typical operating cost benchmarks as a share of revenue.

Cost	Benchmark (% of revenue)
Cost of goods (coffee, milk, food, cups)	28–35%
Labor (baristas, manager, payroll taxes)	30–35%
Rent & occupancy	10–15%
Other (utilities, supplies, marketing, insurance, repairs)	10–15%

Prime cost — cost of goods plus labor — is the number operators live by. Keep it around 60–65% of revenue. If rent runs above 15%, the model has to make it up somewhere, and usually can't.

What profit margin does a coffee shop make?

A healthy independent coffee shop nets roughly 5–12% after all costs, though many run thinner at 2–8%.

That thinness is the whole point of modeling carefully: at a 7% net margin, a shop doing $500,000 a year keeps about $35,000 — so a few points of slippage on prime cost or rent is the difference between a living and a loss. The model lets you see that before it happens, and test whether a higher ticket, tighter labor schedule, or better lease changes the answer.

How many cups a day to break even?

Most small coffee shops break even around 150–250 transactions a day, depending on rent, staffing, and average ticket.

You calculate it from fixed costs and contribution margin:

1. Add up fixed monthly costs — rent, salaried staff, utilities, insurance. Say that's $18,000.
2. Find the contribution margin per transaction — average ticket minus the variable cost of serving it (ingredients plus hourly labor). At a $6 ticket and ~$3 variable cost, that's $3.
3. Break-even transactions per month = $18,000 ÷ $3 = 6,000, or about 200 a day across a 30-day month.

A good model makes this live: change rent or price and the break-even count updates, so you can see exactly how much cushion a location really gives you.

Building the model in Excel, step by step

1. Put assumptions on their own tab — transactions/day, average ticket, cost percentages, wage rates, rent. Reference them everywhere; never hard-code a number inside a formula.
2. Build revenue monthly for the first 1–2 years, applying the opening ramp and weekday/weekend split.
3. Drive costs off revenue using the benchmark percentages, and add fixed costs (rent, salaried labor) as flat monthly figures.
4. Add the startup block and cash flow so you can see the low point of the bank balance — the moment a café is most likely to fail.
5. Build low / base / high scenarios on traffic and ticket, and check break-even in each.

For the full mechanics of structuring and linking the statements, see our guide to building a financial model.

Common coffee shop modeling mistakes

• Assuming day-one traffic. A shop that hits peak transactions in month one looks profitable on paper and runs out of cash in reality.
• Underpricing labor. Open-to-close staffing, overlapping shifts at peak, and payroll taxes add up fast — labor is usually the largest single cost.
• Signing rent the model can't carry. If occupancy is above ~15% of realistic revenue, the margin rarely survives.
• Ignoring waste and comps. Spilled milk, expired pastry, and staff drinks are real cost-of-goods that a tidy spreadsheet forgets.
• No cash view. A profitable P&L can still hit a cash crunch during the ramp. Model the bank balance, not just profit.