E-commerce Model: Contribution Margin and Break-Even Orders – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Returns reduce revenue before cost of goods and fees are applied - the correct order, and the place where many templates quietly overstate profit.
2. Shipping modelled as a cost per order rather than a share of revenue, so a lower average order value hits the contribution margin the way it really does.
3. Fourteen integrity tests inside the file, including one that refuses to accept a break-even count computed from a negative contribution margin.

ECOMMERCE EXCEL DESCRIPTION

E-commerce Model: Contribution Margin and Break-Even Orders is an Excel template (XLSX) available for immediate download upon purchase.

An e-commerce P&L that only shows revenue and total costs cannot answer the question that actually runs the business: does one more order make money, and how many do you need. This model puts contribution margin per order at the centre and derives the break-even order count from it, then builds twenty-four months of profit and loss and cash on the same assumptions.

The example that ships in the file: 2,000 orders in month one growing 4% a month, an average order value of $75, returns at 5% of gross revenue, cost of goods at 55% of net revenue, $6 of outbound shipping per order, platform and payment fees at 8% of net revenue, $20,000 a month of marketing and $15,000 a month of fixed cost.

On those numbers: contribution margin per order $20.36, break-even 1,719 orders a month, and a net margin across the twenty-four months of 13.5%.

Three things this model does that comparable e-commerce templates do not.

First, returns are handled before the cost lines, not after. Returns reduce gross revenue to net revenue, and cost of goods and platform fees are then taken on net revenue. That is the correct order and it is where many templates quietly overstate profit, because taking 55% of a revenue figure that still includes refunded orders inflates both the revenue and the cost in a way that does not cancel out in the margin.

Second, shipping is a cost per order, not a percentage. Shipping does not scale with the basket value, and modelling it as a share of revenue hides exactly the effect you most need to see: that a lower average order value destroys the contribution margin far faster than the revenue line suggests. In this model you can drop the AOV by ten dollars and watch the break-even order count move.

Third, the break-even order count is tested against itself. The Checks sheet verifies that the break-even order count multiplied by the contribution margin actually equals the fixed cost base, and that the contribution margin per order is positive at all. A break-even figure computed from a negative margin is a number that looks fine and means nothing; the file refuses to pass that check.

What is inside. Six sheets. ReadMe with the colour key and a sheet guide. Assumptions – monthly orders, average order value, cost of goods as a share of net revenue, shipping cost per order, platform and payment fees, monthly marketing spend, monthly fixed costs, returns rate and monthly order growth. P&L – twenty-four months of orders, gross revenue, returns, net revenue, cost of goods, shipping, fees, gross profit, marketing, fixed costs and EBITDA. Cash Flow – monthly net cash flow and the cumulative running total. Metrics – contribution margin per order, break-even order count and the twenty-four-month net margin. Checks – fourteen pass/fail tests.

How you know the numbers are right. The Checks sheet runs fourteen tests inside the workbook and all fourteen pass in the shipped file, written in plain English: that orders grow at the stated rate in every month, that gross revenue is orders times average order value, that returns are never a positive number, that net revenue is gross revenue after returns, that cost of goods and fees are the stated shares of net revenue, that shipping is the per-order cost times the order count, that gross profit is net revenue less goods, shipping and fees, that EBITDA is gross profit less marketing and fixed costs, that the cumulative cash flow is the running total of the monthly flows, that contribution margin per order follows the stated formula, that break-even orders times the margin equals the fixed base, that the net margin is total net profit over total net revenue, and that the contribution margin is positive.

Separately, every output was re-derived in an independent script using different arithmetic – a check performed across this model and three others in the same family, 691 comparisons in all, with no disagreements.

What this model does not do. It is a single-channel, single-product-economics model. There is no SKU-level inventory, no purchase order and stock-on-hand schedule, no working capital timing between paying a supplier and being paid by a platform, no channel split with different fee structures, and no customer cohort or repeat-purchase behaviour – growth is a monthly order growth rate. If you need inventory planning or cohort LTV, that is a different file. If you need to know what one order contributes, how many you need, and what twenty-four months look like on honest cost treatment, this does exactly that.

Source: Best Practices in Ecommerce, Integrated Financial Model Excel: E-commerce Model: Contribution Margin and Break-Even Orders Excel (XLSX) Spreadsheet, Paweł Dudek, Balancewright