Ecommerce Financial Model Spreadsheet

Purpose

This recipe produces a working 12-month ecommerce financial model spreadsheet that forecasts revenue (traffic x conversion x AOV), models fully-landed COGS, plans inventory purchases, accounts for category-specific return rates, applies seasonal adjustment factors, and calculates true contribution margin per order. The output is a multi-tab spreadsheet with P&L, unit economics dashboard, and three-scenario sensitivity analysis ready for operational decision-making or investor presentations. [src1]

Prerequisites

• Product catalog with costs — SKU list with supplier cost per unit, estimated weight, packaging cost, and planned retail price
• Traffic data or estimates — monthly sessions by channel from Shopify/GA, or pre-launch benchmarks (2.5-3% conversion rate, category AOV) [src6]
• Shipping rate cards — carrier rates by weight/zone (USPS, UPS, FedEx) or 3PL fulfillment pricing
• Supplier payment terms — net-30, net-60, or prepayment requirements (affects cash flow timing)
• Google Sheets or Excel — free at sheets.google.com
• Historical sales data (if existing business) — 6+ months of order, return, and marketing spend data

Constraints

• COGS must use fully landed cost: product cost + freight-in + duties + packaging. Omitting freight and duties understates true COGS by 15-30%. [src3]
• Return rate reserves must be category-specific. Apparel returns average 25-40%, electronics 8-10%, consumables 5-8%. Using a flat rate produces materially wrong margins. [src4]
• Revenue projections for new stores must use conservative conversion benchmarks (2.5-3% industry average), not optimistic targets. [src6]
• Inventory purchases must lead revenue by supplier lead time (typically 30-90 days for overseas, 7-14 days domestic).
• Payment processing fees (2.9% + $0.30 per transaction on Shopify) must be included as variable costs. At scale these represent 3-4% of revenue.

Tool Selection Decision

Which path?
├── Pre-launch, no sales data, basic spreadsheet skills
│   └── PATH A: Estimate-Based Model — Google Sheets with benchmark assumptions
├── Existing business, < 12 months data, intermediate skills
│   └── PATH B: Data-Driven Model — Google Sheets with actual data import
├── Established business, 12+ months data, advanced skills
│   └── PATH C: Advanced Model — Excel/Sheets with cohort analysis and LTV modeling
└── Multi-channel business (DTC + Amazon + Wholesale)
    └── PATH D: Multi-Channel Model — Excel with channel-specific P&L and blended view

Execution Flow

Step 1: Build Revenue Forecast Tab

Duration: 45-90 minutes · Tool: Google Sheets

Set up the core revenue engine: Traffic x Conversion Rate x Average Order Value = Gross Revenue. Apply discounts and return deductions to calculate net revenue. Benchmark conversion rates: food & beverage 4-6%, health & beauty 2.5-3.5%, apparel 1.5-2.5%, electronics 1-2%. [src6]

REVENUE = Monthly Sessions × Conversion Rate × AOV
NET REVENUE = Revenue × (1 - Discount%) × (1 - Return%)

Verify: Annual revenue should pass sanity check against market sizing. Most new DTC stores generate $10K-50K/month in year one. · If failed: Reduce traffic growth rate or conversion rate to conservative benchmarks.

Step 2: Model COGS with Fully Landed Cost

Duration: 45-60 minutes · Tool: Google Sheets

Build landed cost per SKU: product cost (FOB) + freight-in + import duties + customs brokerage + packaging + QC inspection. Target gross margins: health & beauty 60-70%, home & garden 50-60%, apparel 55-65%, electronics 25-40%, sporting goods 40-50%. [src2]

LANDED COST = Product FOB + Freight/Unit + Duty + Customs + Packaging + QC
MONTHLY COGS = Units Sold × Weighted Average Landed Cost

Verify: COGS as % of net revenue should fall within category benchmarks. If 10+ points above median, review landed cost components for errors. · If failed: Most common error is omitting freight-in or duties. Freight adds 5-15% to product cost for overseas goods. [src7]

Step 3: Add Return Rate Modeling

Duration: 30 minutes · Tool: Google Sheets

Model returns as revenue offset plus processing cost line. Category benchmarks: apparel 25-40%, footwear 30-35%, home goods 15-20%, electronics 8-10%, health & beauty 5-8%, food 2-4%. Processing costs $15-25/return for apparel, $8-12 for electronics. Apply seasonal adjustments: +5% in January (holiday returns), +3% in Q4 (bracketing). [src4]

RETURN COST = Returned Orders × (Avg Item Value - Restocking Salvage + Processing Cost)
NET RETURN IMPACT = Revenue Reversed + Processing Cost - Salvage Recovery

Verify: Total annual return cost as % of gross revenue should be 3-8% for most categories. · If failed: Recheck category-specific return rates. For apparel, 25-30% is normal.

Step 4: Build Shipping Cost Model

Duration: 30-45 minutes · Tool: Google Sheets

Model outbound shipping (to customer), inbound freight (to warehouse, included in landed COGS), and return shipping separately. Shipping typically represents 8-15% of revenue for DTC brands. Free shipping thresholds increase AOV 15-25% but increase per-order shipping cost 5-10%.

NET SHIPPING COST = Outbound Cost - Customer Shipping Revenue + Return Label Cost
SHIPPING AS % OF REVENUE = Net Shipping Cost / Net Revenue

Verify: Total shipping cost as % of net revenue should be 8-15%. · If failed: Negotiate volume rates, increase free shipping threshold, or add surcharges for oversized items.

Step 5: Model Marketing Spend and Customer Acquisition

Duration: 45-60 minutes · Tool: Google Sheets

Build channel-level marketing budget with ROAS targets and blended CAC. Good ROAS depends on margin: 2:1 for 50% margins, 4:1+ for 25% margins. Marketing as % of net revenue: 15-30% for growth-stage DTC, 10-20% for established brands. [src5]

BLENDED CAC = Total Marketing Spend / Total New Customers
CHANNEL ROAS = Channel Revenue / Channel Spend
LTV:CAC = Customer Lifetime Value / Blended CAC (target: > 3:1)

Verify: Blended CAC should be less than one-third of first-order AOV. · If failed: If CAC exceeds AOV, model LTV:CAC ratio; the business requires repeat purchases to work.

Step 6: Calculate Contribution Margin and Build P&L

Duration: 30-45 minutes · Tool: Google Sheets

Assemble all variable costs into contribution margin waterfall: Net Revenue minus COGS, outbound shipping, return processing, return shipping, payment processing (2.9% + $0.30/txn), and marketing spend. Add fixed costs for full P&L. DTC CM range: 30-40% (median 25%). EBITDA benchmarks: 3-5% median, 7-10% healthy. [src2] [src5]

CONTRIBUTION MARGIN = Net Revenue - COGS - Shipping - Returns - Payment Fees - Marketing
EBITDA = Contribution Margin - Fixed Costs (platform, warehouse, team, overhead)

Verify: CM should be positive by month 3-6. If negative beyond month 6, unit economics do not work. · If failed: Identify largest variable cost drag; common fixes: raise prices, reduce COGS, cut underperforming ad channels.

Step 7: Apply Seasonal Adjustments

Duration: 20-30 minutes · Tool: Google Sheets

Apply monthly seasonality multipliers to revenue, marketing spend, and inventory purchases. Most ecommerce sees 30-40% of annual revenue in Q4. EBITDA peaks in November (~11%) and troughs in October (~2%). Front-load inventory purchases in Q3 for Q4 demand. [src2]

ADJUSTED REVENUE = Base Monthly × Seasonal Revenue Factor
INVENTORY ORDER = Expected Demand(t + lead_time) × Safety Stock Multiplier (1.2-1.5)

Verify: Sum of seasonal revenue factors should approximate 12.0. · If failed: Calibrate against prior-year data or industry-standard seasonal curves.

Step 8: Build Scenario Analysis Tab

Duration: 20-30 minutes · Tool: Google Sheets

Create three scenarios by varying conversion rate, AOV, CAC, return rate, and COGS. Pessimistic: -15% AOV, +20% CAC, +5% returns. Optimistic: +15% AOV, -20% CAC, -3% returns. Focus on 5-8 key drivers. [src1]

SCENARIO INPUTS: Conversion Rate, AOV, Traffic Growth, CAC, Return Rate, COGS Change
OUTPUTS: Annual Revenue, Gross Margin, Contribution Margin, EBITDA, Cash Breakeven Month

Verify: Pessimistic scenario should still show a path to profitability. · If failed: Re-examine pricing power and cost structure. If optimistic margins are thin, the category may not support standalone DTC.

Output Schema

{
  "output_type": "ecommerce_financial_model",
  "format": "XLSX or Google Sheets",
  "tabs": [
    {"name": "Revenue Forecast", "description": "Monthly traffic, conversion, AOV, gross and net revenue with seasonal adjustments"},
    {"name": "COGS & Inventory", "description": "Landed cost per SKU, monthly COGS, inventory purchase schedule"},
    {"name": "Returns Model", "description": "Category-specific return rates, processing costs, revenue impact"},
    {"name": "Shipping", "description": "Outbound, inbound, and return shipping cost model"},
    {"name": "Marketing", "description": "Channel-level spend, ROAS, CAC, and blended metrics"},
    {"name": "P&L", "description": "Full contribution margin waterfall and monthly P&L"},
    {"name": "Unit Economics", "description": "Per-order CM, LTV:CAC, breakeven analysis"},
    {"name": "Scenarios", "description": "Base, optimistic, pessimistic with sensitivity analysis"},
    {"name": "Assumptions", "description": "All input assumptions in one tab for easy adjustment"}
  ]
}

Quality Benchmarks

If below minimum: A model with only product cost as COGS and flat return rate will understate true costs by 20-40%. Add landed cost components and category-specific return rates before using for decisions.

Error Handling

Cost Breakdown

Anti-Patterns

Wrong: Using supplier invoice price as COGS

Many founders record only the product purchase price as COGS, ignoring freight-in, duties, and packaging. This understates true cost of goods by 15-30% and creates phantom profits that evaporate when cash flow is analyzed. [src3]

Correct: Calculate fully landed cost per unit

Include product cost + freight per unit + import duties + customs brokerage + packaging materials + quality inspection fees. This is the real cost basis. [src7]

Wrong: Using a flat return rate across all product categories

A flat 10% return rate applied to apparel (real rate: 25-40%) dramatically overstates net revenue. For electronics (real rate: 8-10%), it slightly understates costs. Neither produces accurate margins. [src4]

Correct: Apply category-specific return rates with seasonal adjustments

Use industry benchmark return rates for the specific product category, then layer in seasonal spikes. Model the processing cost per return as a separate cost line.

Wrong: Ignoring seasonality in both revenue and marketing spend

A linear monthly model (annual / 12) misses that Q4 may generate 30-40% of annual revenue while January is the slowest month. Marketing CPMs also increase 30-50% in Q4. [src2]

Correct: Apply monthly seasonal adjustment factors to revenue, marketing, and inventory

Use seasonal factor tables as a starting point, calibrate with actual data after 6-12 months. Front-load inventory purchases in Q3 for Q4 peak.

When This Matters

Use this recipe when an agent needs to produce an actual working financial model spreadsheet for an ecommerce business, not a strategy document about financial planning. Requires product catalog with cost data and either historical traffic data or willingness to use industry benchmarks. The output feeds directly into cash runway analysis, fundraising projections, and monthly operating reviews.