Free Excel templates

A free hardware store financial model in Excel, with a full worked case

Six departments and their margins, inventory turns and GMROI, co-op patronage in cash and in equity, tool rental, staffing from the schedule, a P&L with adjusted EBITDA and SDE, break-even and four stresses, the quarters and the line of credit, a new store, a power equipment shop and two offers for the business.

The file follows Pellowmere Hardware & Rental, a fictional independent store of 16,500 sq ft, member of a dealer-owned co-op, with six departments, a paint desk, services, a tool rental department and contractor accounts. It earns revenue of $4,403,000 and EBITDA of $415,724 (9.4%) and breaks even at 77.9% of its volumes. Every figure in the book comes from this model; replace the inputs with yours and the whole case recalculates.

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.

Hardware_Store_Model_Pellowmere.xlsx (81 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 Hardware Store Owner. The book explains every assumption and every tab: why the margin is set department by department, why patronage paid in equity is income without cash, what turns and GMROI say about the shelves, why the schedule and not the sales sets the payroll, which machines a 16-year-old may not run, why the first quarter consumes cash, what a power equipment shop costs, and how two buyers would value the store.

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

What is in the file

Thirteen tabs, in the order of the book’s appendix A. Blue cells are inputs; every other cell is a formula.

Assumptions
Opening hours, transactions and ticket, the department mix and margins, turns, the co-op’s terms, rental and services, tenders and credit, the schedule and pay, occupancy and other costs, loans and the line, the plan and the shop, the 2023 purchase and a new store, the stresses and the two offers.
Departments
Sales and gross margin dollars of the six departments ($1,630,110 of merchandise gross margin in the case).
Inventory
Average inventory at cost, days of inventory, GMROI, dead stock and the spring build (GMROI of $1.77).
Co-op
Purchases, warehouse purchases, the patronage and its cash and equity parts, the equity held at the co-op, and the customer balances.
Rental
Rental revenue, the damage waiver inside it, repairs, parts and fuel ($288,960 after those costs).
Schedule and Payroll
Hours from the opening hours and positions, hours paid with sick leave, pay with burden and the count toward fifty (31,408 hours paid in the hourly pool).
P&L
Revenue, variable costs with the patronage deducted, contribution, fixed costs, EBITDA, adjusted EBITDA and SDE ($527,609).
Break-Even and Stresses
The share of 2026 volumes at which EBITDA is zero (77.9%), and the year rerun with lower volumes, lost margin points, higher wages and a halved patronage rate.
Cash by Quarter
Four quarters: only the prior year’s patronage cash part as cash, the spring build and its dating, loan payments, tax distributions on the tax calendar, the line and the cash floor, and the volume stress.
Purchase and New Store
The 2023 purchase, sources equal to uses, and a new store’s first year with the patronage kept out of its cash and the receivables built from zero.
Plan and Shop
2027 to 2029 with the power equipment shop: EBITDA, coverage, working capital, the line quarter by quarter, the shop’s own EBITDA and its reserve.
Valuation
An operator’s offer on adjusted EBITDA and an individual buyer’s offer on SDE, inventory at cost added once, the cash the seller keeps, and the buyer’s coverage test on cash.
Checks
Internal checks (shares, totals, hours, patronage parts, quarters, tax passes, offer layers), the tests of the case (the line, reserves, distributions, the buyer’s coverage) and a comparison with every figure printed in the book.

Three things to try

Halve the patronage rate. In the book, a co-op that cuts its rate by half changes EBITDA by -$30,672. Read the Cash by Quarter tab as well: only the cash part of the patronage ever reaches the bank.

Lose two points of margin. Set the margin stress: two points in every department change EBITDA by -$78,377 in the case, more than the patronage stress.

Cut the volumes. Set the volume stress and read the line of credit: in the case it would end the year $4,795 past its limit, while distributions cut to the tax and a deferred fleet replacement keep it within.

Questions

Does it separate the patronage paid in cash from the patronage paid in equity?

Yes. The store earned $61,343 of patronage in the case, $21,470 in cash and $39,873 in co-op equity. The equity part sits in EBITDA and in the owner’s taxable income, but never in the cash tabs or the coverage tests on cash.

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 store?

Yes: replace every blue input, starting with your transactions, ticket, department mix and margins, turns and the co-op’s terms. Pellowmere and every figure in it are fictional case assumptions, not market data.

Related reading