☕ COFFEE ROASTING BUSINESS FINANCIAL MODEL
From green-bean purchases to roasted coffee sales, break-even volume and cash flow
How much coffee can your roastery actually ship, and will the margin on those sales cover payroll, equipment and inventory? This editable 60-month Excel model helps you answer both questions in one forecast. It connects green-bean purchasing, roasting capacity, yield loss, product pricing and customer demand to inventory, profitability and cash.
Designed for specialty coffee founders, independent roasters, finance teams and consultants, the workbook includes fictional examples you can replace with your own products, costs and operating assumptions.
⚙️ ROASTING CAPACITY, YIELD & EQUIPMENT EXPANSION
Estimate monthly roasting capacity from batch size, batches per hour, hours per day, operating days and effective equipment utilization. Apply roast loss to translate green kilograms processed into saleable roasted kilograms.
Test an equipment expansion by changing the installation month, larger batch size, investment cost and useful life. Compare the additional output with the timing of the cash outlay. The workbook tracks unmet demand separately, so coffee that cannot be produced or supplied from available stock does not become revenue.
☕ 20 PRODUCT POSITIONS & THREE SALES CHANNELS
Configure up to 20 linked coffee products, including 15 prefilled examples across direct-to-consumer, wholesale and subscription sales. Set product prices, starting demand, landed bean costs, roast loss, packaging, shipping, merchant fees, launch month and growth assumptions.
Wholesale prices reflect discounts to list prices. Subscriptions are modeled as a sales channel, not a billing, renewal or retention system.
🧾 500-ROW FORECAST ORDER BOOK
Add incremental customer demand by SKU and forecast month. Confirmed orders with a valid active SKU, a valid month and a positive quantity flow into the forecast. Planned and cancelled orders do not increase demand.
Use the order book to test how an additional customer order affects production and shipments. It is a forward-looking planning tool, not a historical invoice ledger.
📦 GREEN BEANS, ROASTED STOCK & WORKING CAPITAL
Follow separate physical ledgers for green beans and finished roasted coffee. Trace purchases, green kilograms consumed, roast shrinkage, saleable output, shipments and closing stock. The model uses simplified weighted-average bean valuation and separately tracks packaging, outbound freight, payment fees and variable roasting energy.
Review receivables, supplier payables and inventory as operating assumptions change. Collection and payment timing use simplified days-based assumptions, not invoice-level aging. The workbook is not an audited manufacturing cost-absorption or lot-tracing system.
🏦 PAYROLL, CAPITAL SPENDING & FINANCING
Plan staffing for roasting, production and packing, sales, marketing, administration and logistics. Model payroll burden, rent, fixed utilities, overhead and variable energy costs.
The equipment schedule covers initial roastery assets, a planned expansion and depreciation. The financing schedule tracks one term loan, interest and scheduled principal payments. An expansion does not automatically raise new funding.
📑 THREE LINKED FINANCIAL STATEMENTS
Review the Income Statement, Cash Flow Statement and Balance Sheet in one linked worksheet across all 60 months. Trace net sales, direct costs, EBITDA, earnings, simplified tax-loss carryforwards, working capital, equipment investment, debt and closing cash. The five-year Annual Summary brings the results together for management review.
📈 BREAK-EVEN & PRODUCTION FEASIBILITY
Compare actual forecast shipments with illustrative EBITDA break-even volume and revenue. Review contribution per shipped kilogram and test whether the volume needed to break even is supported by both demand and roasting capacity.
When contribution is nonpositive, the break-even schedule uses a sentinel rather than showing a realistic positive break-even quantity. A cash-runway value of 999 indicates no recent operating cash burn when cash is nonnegative. It does not mean 999 months of assured liquidity.
🔀 FOUR EDITABLE SCENARIOS
Switch between Base, Upside, Downside and Stress. The active scenario adjusts green-bean costs, roast loss, wholesale discounts, freight, demand, selling prices, payroll and effective capacity.
With the fictional inputs supplied, Downside and Stress produce 45 and 50 months with negative closing cash, respectively. The workbook shows these deficits without assuming fresh funding. A technical PASS means the implemented checks reconcile. It does not establish profitability or funding adequacy.
📊 EXECUTIVE DASHBOARD & MODEL CHECKS
Eight native Excel charts cover revenue by channel, monthly revenue and EBITDA, gross profit and selling price per kilogram, roast output and capacity, closing cash and debt, green and roasted inventory days, EBITDA and net profit margins, and shipped volume versus break-even.
Dashboard cards summarize five-year revenue and EBITDA, Year-5 gross profit per shipped kilogram, closing cash, roaster utilization, inventory cover, cash runway and months passing model checks. The checks cover the balance sheet, cash and debt roll-forwards, inventory quantities, gross profit and roasting capacity.
📘 WHAT YOU RECEIVE
• One editable, macro-free Excel workbook with 17 worksheets, 60 monthly forecast periods, 20 linked product positions, 500 order rows, 4 scenarios, 3 linked financial statements and 8 native dashboard charts.
• One 23-page English PDF guide covering setup, product and order inputs, roasting capacity, inventory, financing, break-even, scenarios, model checks and troubleshooting.
🧭 GETTING STARTED
Save a working copy and start in START HERE. Select a scenario and review the global assumptions in CONTROL. Replace the fictional products and order records, then adjust roasting capacity, inventory targets, costs and funding. Review the statements, break-even schedule and model checks together after each material change.
⚙️ COMPATIBILITY & SCOPE
The sample forecast runs from January 2029 to December 2033. Changing the start year shifts the forecast calendar. The workbook requires no macros, VBA or live external data connections. Microsoft Excel Desktop is recommended. Native Excel Desktop behavior and Google Sheets import have not been independently verified.
🛡️ INDEPENDENT TEMPLATE
Developed by SheetworksStudio as an independent, unofficial financial planning template. All sample businesses, inputs and outputs are fictional. The workbook is not an ERP, subscription payment platform, audited forecast, engineering certification or lender-approved financing model. It does not provide investment, tax, accounting or legal advice or guarantee business outcomes. Validate business-specific assumptions before relying on the results.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Food & Beverage Industry, Integrated Financial Model Excel: Coffee Roasting Business Financial Model Excel (XLSX) Spreadsheet, SheetworksStudio
|
Download our FREE Strategy & Transformation Framework Templates
Download our free compilation of 50+ Strategy & Transformation slides and templates. Frameworks include McKinsey 7-S, Balanced Scorecard, Disruptive Innovation, BCG Curve, and many more. |