Free Excel templates

A free coffee shop financial model in Excel, with a full worked case

Recipe costs of every item, the menu mix and the average ticket, labor and burden, rent and other costs, a P&L with adjusted EBITDA and SDE, the break-even in tickets per day, stress tests and two offers for the cafe.

The file follows Tilbury Lane Coffee Co., a fictional neighborhood cafe of 1,800 square feet. It serves 340 tickets a day at an average of $7.25, takes in $886,957, earns adjusted EBITDA of $109,173 (12.3%) after a market salary for its manager and breaks even at 278 tickets a day. Every figure in the book comes from the full model; this simplified version keeps the same logic and the same results.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.

Coffee_Shop_Model_Tilbury_Lane.xlsx (13 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is the worked case of The Coffee Shop Owner. The book explains every assumption and every tab: what a cup costs, how the average ticket is built, what a shift must bring, how the lease reads in tickets, when roasting pays, why cash falls in winter, whether a drive-thru kiosk pays, and how two buyers would value the cafe.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Six tabs. Blue cells are inputs; every other cell is a formula.

Inputs
Days open and tickets per day; the item mix and menu prices; recipe doses and the costs of coffee, milk, cups and add-ons; waste; card fees; the schedule, wages and burden; the manager’s market salary; rent, charges and other costs; the owner’s actual pay and loan payments; the buyers’ multiples; three stress factors.
Cup Costs
The cost of each item, its price, its cost as a share of price and its contribution: a latte at $1.01, a drip at $0.77 in the case.
P&L
Tickets, units, revenue, cost of goods with waste, labor, prime cost, occupancy, card fees and other costs, adjusted EBITDA, reported EBITDA and SDE ($167,173).
Break-Even and Stress
Contribution per ticket ($4.87), the costs that do not move with tickets, the break-even in tickets per day with and without loan payments, and what one shift must bring.
Value
An individual buyer’s offer on SDE with an SBA loan and its coverage, and a local operator’s offer on adjusted EBITDA.
Checks
A comparison with the figures printed in the book: with the case’s inputs every line reads OK.

Three things to try

Open a competitor next door. Set the tickets factor to 0.88: revenue falls, the schedule and the rent do not, and adjusted EBITDA drops to $37,598.

Raise every cost by 5%. Set the cost factor to 1.05 with prices unchanged: adjusted EBITDA falls to $70,284. Then find the price rise that brings it back.

Weigh the dose. Add two grams to the double shot on the Inputs tab and watch the latte’s cost and the year’s EBITDA move.

Questions

What does a ticket leave in the case?

$4.87 of contribution after goods, card fees, supplies and the employer’s tax on tips, before labor, rent and the other fixed costs. That is why the cafe needs 278 tickets a day to break even.

Does it use macros?

No macros and no circular reference. It recalculates in Excel, LibreOffice or Google Sheets.

Can I use it for my own cafe?

Yes: replace every blue input, starting with your tickets, mix, prices and recipe doses. Tilbury Lane and every figure in it are fictional case assumptions, not market data.

Related reading