Blog · Territory, capacity and quota planning
Build an ecommerce financial model from orders, refunds, delivery costs and inventory records. This worked monthly layout connects profit to cash and shows how to compare workbook assumptions with actual trading.
An ecommerce financial model connects orders and returns to profit, then connects inventory and settlement timing to cash. Build it from the exports your finance and trading teams already use, with the same monthly boundaries for plan and actual. The essential proof is that the trading calculation and the cash bridge reconcile.
Bring an order export, refund register, product-cost file, fulfillment invoices, payment-processor settlement report, marketing-spend report and monthly ledger balances. Keep order ID and SKU where available. Order status matters: canceled orders, shipped orders and recognized sales are different populations.
Use separate columns for actual values and approved workbook assumptions. The commercial team owns order volume and realized selling price; operations owns fulfillment and inventory inputs; finance owns recognition, cost classification and cash reconciliation. Marketing owns acquisition spending, with customer acquisition cost reviewed separately from the complete trading result.
Remove discounts and refunds from the sales base consistently. Separate buyer-imposed sales taxes collected for remittance: the IRS explains that those collections are not income. Include customer shipping revenue separately if your business charges it; this example assumes none.
Name the sheet Trading. Column A holds the row labels below, column B the October 2026 plan and column C October actual. USD amounts are shown as whole dollars; percentages are exact in this example.
Assume one item per order, no discounts, and full refunds on returned items. Every returned item is resalable and goes back into inventory. Consequently, the refund-value percentage also equals the returned-unit percentage here. Real assortments need separate unit and dollar return assumptions.
| Row | Input or result | Plan | Actual |
|---|---|---|---|
| 2 | Orders | 1,000 | 1,000 |
| 3 | Average order value (USD) | 80 | 80 |
| 4 | Refund-value rate | 5% | 10% |
| 5 | Cost per item (USD) | 32 | 32 |
| 6 | Fulfillment cost per placed order (USD) | 6 | 6 |
| 7 | Processing fee / gross sales | 3% | 3% |
| 8 | Acquisition spending (USD) | 12,000 | 12,000 |
| 9 | Fixed operating expenses (USD) | 18,000 | 18,000 |
| 10 | Gross sales (USD) | 80,000 | 80,000 |
| 11 | Refunds (USD) | 4,000 | 8,000 |
| 12 | Net sales (USD) | 76,000 | 72,000 |
| 13 | Net cost of goods sold (USD) | 30,400 | 28,800 |
| 14 | Gross profit (USD) | 45,600 | 43,200 |
| 15 | Fulfillment expense (USD) | 6,000 | 6,000 |
| 16 | Processing fees (USD) | 2,400 | 2,400 |
| 17 | Operating profit (USD) | 7,200 | 4,800 |
| 18 | Gross margin | 60% | 60% |
For Excel, enter these formulas in column B, then copy them to column C. Microsoft's formula guide documents the arithmetic operators and cell references used here.
B10 = B2*B3
B11 = B10*B4
B12 = B10-B11
B13 = B2*(1-B4)*B5
B14 = B12-B13
B15 = B2*B6
B16 = B10*B7
B17 = B14-B15-B16-B8-B9
B18 = B14/B12
The left side identifies the destination cell; enter only the expression beginning with =. Covirage is not affiliated with Microsoft.
This is a management layout: fulfillment and processing sit below gross profit consistently in both columns. The processor contract is assumed to charge on gross sales without refunding fees. Fixed expenses include $2,000 of depreciation. There are no additional return-handling expenses in the example; add them when present in your records.
Actual operating profit is $2,400 below plan: =C17-B17. The refund increase reduces net sales by $4,000 while the returned saleable inventory reverses $1,600 of product cost. Together those changes reduce profit by $2,400; orders, price and the other expenses are unchanged.
Gross margin remains 60%, which shows why a percentage alone can miss a trading problem. The gross profit margin owner explains that ratio in detail. Here the review should focus on why more orders were refunded and whether that cause persists in later months.
Do not assume every return reverses product cost. Damaged stock, restocking charges and partial refunds need distinct inputs. Compare refunds by sale cohort as well as processing month so a late refund wave does not silently alter the meaning of your actual-versus-plan comparison.
Purchases and product expense are different inputs. The IRS inventory explanation distinguishes goods held in inventory from the goods used or sold and calls for book inventory to be checked against physical inventory. Use the company's accounting policies for its management reporting; the simplified roll-forward below assumes no shrinkage or write-downs.
The actual cash schedule uses these balances and movements. Processor receivables are shown before fees, and all customer refunds are settled within the month. All other operating expenses are paid in the month incurred, except depreciation.
| Cash and inventory step | Calculation | Amount (USD) |
|---|---|---|
| Opening cash | Bank balance | 30,000 |
| Opening inventory | Inventory ledger | 40,000 |
| Inventory purchases received | Purchasing records | 45,000 |
| Closing inventory | 40,000 + 45,000 − 28,800 | 56,200 |
| Opening processor receivable | Settlement report | 5,000 |
| Closing processor receivable | Settlement report | 9,000 |
| Customer receipts before fees | 72,000 + 5,000 − 9,000 | 68,000 |
| Net processor deposit | 68,000 − 2,400 | 65,600 |
| Opening supplier payables | Payables ledger | 10,000 |
| Closing supplier payables | Payables ledger | 15,000 |
| Cash paid for inventory | 45,000 + 10,000 − 15,000 | 40,000 |
| Cash fixed expenses | 18,000 − 2,000 | 16,000 |
| Operating cash movement | 65,600 − 40,000 − 6,000 − 12,000 − 16,000 | −8,400 |
| Closing cash | 30,000 − 8,400 | 21,600 |
On a Cash sheet, put opening cash in B2, opening and closing processor receivables in B3:B4, opening inventory in B5, purchases in B6, opening and closing supplier payables in B8:B9, and depreciation in B10. The calculated cells are:
B7 = B5+B6-Trading!C13
B11 = Trading!C12+B3-B4
B12 = B6+B8-B9
B13 = B11-Trading!C16-B12-Trading!C15-Trading!C8-(Trading!C9-B10)
B14 = B2+B13
Independently check cash through profit: $4,800 + $2,000 depreciation − $4,000 increase in processor receivables − $16,200 increase in inventory + $5,000 increase in payables = negative $8,400. Both routes must agree. This is an operating cash schedule; add taxes, capital expenditures, debt and equity movements before calling it a complete cash forecast.
The broader cash flow versus profit explanation covers that distinction. For a business still establishing its overall funding structure, use the neighboring startup financial model alongside this trading schedule.
Bring your financial workbook and prepared order, refund, cost and settlement exports. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the comparison with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See forecast analysis for the supporting planning context.
Include orders, order value, discounts, returns, product costs, fulfillment, payment fees, marketing and fixed operating expenses. Add inventory purchases, supplier balances, processor receivables and opening cash. Keep assumptions separate from actual records so each variance has a clear operational explanation.
In an inventory-based accrual management workbook, purchases increase inventory; the cost of goods sold reflects the inventory sold. Supplier payment timing belongs in the cash schedule. Reconcile opening inventory plus purchases less cost of goods sold to closing inventory, adding any write-downs separately.
Keep placed or shipped orders available as an operating count, then show the related refund value separately from gross sales. Product-cost reversals depend on whether goods return to saleable inventory. Do not use a revenue refund percentage as a unit-return percentage unless the example's assumptions justify it.
Cash can fall when inventory is purchased ahead of sales, processor balances increase or suppliers are paid faster. Profit recognizes revenue and expenses on its accounting basis. A cash bridge shows those timing differences, along with noncash expenses and any investing or financing payments.