--- # === IDENTITY === id: finance/startup-finance/ecommerce-financial-model-spreadsheet/2026 canonical_question: "How do I build an ecommerce financial model — inventory, COGS, shipping, return rates, seasonal adjustments?" aliases: - "Ecommerce P&L spreadsheet template with COGS and shipping" - "DTC financial model with inventory planning and return rates" - "How to forecast ecommerce revenue using traffic, conversion, and AOV" entity_type: execution_recipe domain: finance > startup-finance > ecommerce financial model spreadsheet region: global jurisdiction: global temporal_scope: 2024-2026 # === VERIFICATION === last_verified: 2026-03-11 confidence: 0.87 version: 1.0 first_published: 2026-03-11 # === TEMPORAL VALIDITY === temporal_validity: status: evolving last_breaking_change: "Tariff increases in early 2025 shifted landed cost assumptions for US-imported goods; benchmark gross margins rose from 51% to 55% as brands sold pre-tariff inventory at higher prices" next_review: 2026-09-07 change_sensitivity: high # === CONSTRAINTS === constraints: - "Revenue projections must use actual traffic data, not aspirational targets. New stores without traffic history should use conservative benchmarks: 2.5-3% conversion, category-appropriate AOV." - "COGS must include fully landed cost (product + freight-in + duties + packaging), not just supplier invoice price. Omitting landed cost components understates COGS by 15-30%." - "Return rate assumptions must be category-specific: 25-40% for apparel, 15-20% for home goods, 8-10% for electronics, 5-8% for consumables. Using a flat rate across categories produces materially wrong margins." - "Seasonal adjustment factors must be applied to both revenue and marketing spend. Q4 concentration (30-40% of annual revenue for most DTC brands) requires matching inventory and ad spend planning." - "Contribution margin must include all variable costs: COGS, shipping, returns processing, payment processing (2.9% + $0.30), and variable marketing. Omitting any component overstates profitability." # === SKIP CONDITIONS === skip_this_unit_if: - condition: "User needs a high-level business plan, not a working spreadsheet model" use_instead: "business/startup-planning/startup-idea-structuring-template/2026" - condition: "User needs SaaS financial model with MRR/churn, not ecommerce" use_instead: "finance/startup-finance/saas-financial-model-spreadsheet-template/2026" - condition: "User needs fundraising projections and pitch deck financials only" use_instead: "business/fundraising/financial-projections-for-investors/2026" # === AGENT HINTS === inputs_needed: - key: product_category question: "What product category does the ecommerce business sell?" type: choice options: ["apparel/fashion", "electronics/tech accessories", "health & beauty", "home & garden", "food & beverage", "pet supplies", "sporting goods", "mixed/multi-category"] - key: technical_skill question: "What is the user's technical skill level with spreadsheets?" type: choice options: ["basic (can fill in cells)", "intermediate (can write formulas)", "advanced (pivot tables, macros, complex models)"] - key: business_stage question: "What stage is the ecommerce business?" type: choice options: ["pre-launch (no sales data)", "early (< 6 months, some data)", "established (6+ months, full data)", "scaling (optimizing existing model)"] - key: platform question: "Which ecommerce platform is used?" type: choice options: ["Shopify", "WooCommerce", "Amazon FBA", "BigCommerce", "custom/headless", "not yet decided"] # === EXECUTION METADATA === execution: required_inputs: - name: "Product catalog with cost data" source: "user/business records" format: "spreadsheet or list with SKUs, supplier costs, weights, and retail prices" - name: "Traffic and sales data (if existing business)" source: "Shopify/Google Analytics or estimates for pre-launch" format: "monthly traffic, conversion rate, and order data" outputs: - name: "Ecommerce Financial Model Spreadsheet" format: "XLSX or Google Sheets" description: "12-month P&L with revenue forecast (traffic x conversion x AOV), COGS (landed cost), inventory planning, shipping costs, return reserves, marketing spend, and contribution margin by month and channel" - name: "Unit Economics Dashboard" format: "spreadsheet tab" description: "Per-order contribution margin, blended CAC, LTV:CAC ratio, and breakeven analysis" - name: "Scenario Analysis" format: "spreadsheet tab" description: "Base, optimistic, and pessimistic scenarios with sensitivity on conversion rate, AOV, and CAC" tools_required: - name: "Google Sheets or Excel" purpose: "Financial model construction and scenario analysis" tier: free cost: "$0" alternatives: ["LibreOffice Calc", "Notion (limited)"] - name: "Shopify Analytics or Google Analytics" purpose: "Traffic and conversion data source" tier: free cost: "$0 (included with platform)" alternatives: ["any ecommerce platform analytics"] - name: "Supplier invoices or product cost data" purpose: "Accurate COGS inputs" tier: free cost: "$0" alternatives: ["manufacturer quotes", "Alibaba pricing for estimates"] credentials_needed: - service: "Google Sheets" type: "Google account" where_to_get: "https://sheets.google.com" free_tier_limits: "Unlimited for personal use" estimated_duration: "4-8 hours for complete model build; 1-2 hours for pre-launch estimates" estimated_cost: "$0 (self-built) to $200-500 (consultant-assisted or premium template)" # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/finance/startup-finance/ecommerce-financial-model-spreadsheet/2026" suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-11)" # === RELATED UNITS === related_kos: depends_on: - id: "business/startup-planning/startup-idea-structuring-template/2026" label: "Business concept and market sizing inputs" feeds_into: - id: "business/fundraising/financial-projections-for-investors/2026" label: "How to present financial projections to investors — detail level by stage, assumptions, and common investor questions" - id: "finance/startup-finance/cash-buffer-contingency-planning/2026" label: "Cash buffer policy with a three-scenario runway model — gross/net runway, buffer sizing, tiered contingency triggers" related_to: - id: "finance/startup-finance/saas-financial-model-spreadsheet-template/2026" label: "SaaS financial model build — MRR waterfall, cohort analysis, P&L, cash flow, cap table, scenario toggles" alternative_to: [] # === SOURCES === sources: - id: src1 title: "How to use our forecast model built for ecommerce" author: Mercury url: https://mercury.com/blog/financial-forecast-model-ecommerce type: technical_blog published: 2025-06-15 reliability: high - id: src2 title: "Ecommerce Profit Benchmarks: P&L + Performance Metrics That Matter" author: Finaloop url: https://www.finaloop.com/blog/ecommerce-profit-benchmarks-performance-metrics type: industry_report published: 2025-09-01 reliability: authoritative - id: src3 title: "Cost of Goods Sold (COGS): Formula, Calculation & How to Reduce It" author: Shopify url: https://www.shopify.com/blog/cost-of-goods-sold type: official_docs published: 2026-01-10 reliability: authoritative - id: src4 title: "Ecommerce Returns: Average Return Rate and How to Reduce It" author: Shopify url: https://www.shopify.com/enterprise/blog/ecommerce-returns type: official_docs published: 2025-03-15 reliability: authoritative - id: src5 title: "eCommerce Contribution Margin: A Comprehensive Guide" author: Saras Analytics url: https://www.sarasanalytics.com/blog/ecommerce-contribution-margin type: technical_blog published: 2025-07-01 reliability: high - id: src6 title: "Ecommerce Benchmarks 2025: Key Metrics & Industry Data" author: Triple Whale url: https://www.triplewhale.com/blog/ecommerce-benchmarks type: industry_report published: 2025-06-01 reliability: high - id: src7 title: "Inventory and COGS Accounting for eCommerce Best Practices" author: LedgerGurus url: https://ledgergurus.com/inventory-and-cogs-accounting/ type: technical_blog published: 2025-04-01 reliability: high --- # 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. ## 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](https://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 10% across categories 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 your supplier lead time (typically 30-90 days for overseas, 7-14 days domestic). Failure to model this timing gap causes stockouts or cash crunches. - Payment processing fees (2.9% + $0.30 per transaction on Shopify) must be included as variable costs, not ignored. 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 ``` | Path | Tools | Cost | Time | Output Quality | |------|-------|------|------|---------------| | A: Estimate-Based | Google Sheets | $0 | 2-3 hours | Directional (+-30% accuracy) | | B: Data-Driven | Google Sheets + platform analytics | $0 | 4-6 hours | Solid (+-15% accuracy) | | C: Advanced | Excel/Sheets + analytics | $0 | 6-8 hours | High (+-10% accuracy) | | D: Multi-Channel | Excel + multi-platform data | $0-200 | 8-12 hours | Comprehensive | ## 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. ``` REVENUE FORECAST (Monthly) ═══════════════════════════════════════════════════════════ M1 M2 M3 ... M12 TOTAL ───────────────────────────────────────────────────────── TRAFFIC INPUTS Organic sessions: ____ ____ ____ ____ Paid sessions: ____ ____ ____ ____ Email sessions: ____ ____ ____ ____ Social sessions: ____ ____ ____ ____ TOTAL SESSIONS: ____ ____ ____ ____ CONVERSION Conv. rate (%): 2.5% 2.6% 2.7% 3.0% Total orders: =sessions × conv_rate ORDER VALUE Avg order value: $____ $____ $____ $____ Avg units/order: ____ ____ ____ ____ GROSS REVENUE: =orders × AOV DEDUCTIONS Discounts (%): 5% 5% 5% 8% Returns (%): ___% (category-specific, see Step 3) NET REVENUE: =gross × (1 - discount%) × (1 - return%) ``` Benchmark conversion rates by category: Food & beverage 4-6%, health & beauty 2.5-3.5%, apparel 1.5-2.5%, electronics 1-2%, luxury < 1%. [src6] Benchmark AOV: Health & beauty $60, pet supplies $59, home & garden $110, electronics $150-200, apparel $80-120. [src6] **Verify**: Revenue per month should pass the sanity check — compare total annual revenue to your market sizing. If first-year revenue exceeds $1M for a bootstrapped launch, assumptions are likely too aggressive. **If failed**: Reduce traffic growth rate or conversion rate to conservative benchmarks. Most new DTC stores generate $10K-50K/month in year one. ### Step 2: Model COGS with Fully Landed Cost **Duration**: 45-60 minutes **Tool**: Google Sheets Build the landed cost model for every SKU. COGS is the single largest expense line for ecommerce businesses. [src3] ``` LANDED COST PER UNIT ═══════════════════════════════════════════════════════════ SKU-A SKU-B SKU-C Avg ───────────────────────────────────────────────────────── Product cost (FOB): $____ $____ $____ Freight-in per unit: $____ $____ $____ Import duties (%): ____% ____% ____% Customs/brokerage: $____ $____ $____ Packaging materials: $____ $____ $____ Quality inspection: $____ $____ $____ ───────────────────────────────────────────────────────── TOTAL LANDED COST: $____ $____ $____ Retail price: $____ $____ $____ UNIT GROSS MARGIN: ____% ____% ____% MONTHLY COGS CALCULATION ═══════════════════════════════════════════════════════════ M1 M2 M3 ... M12 Units sold: ____ ____ ____ ____ Weighted avg landed: $____ $____ $____ $____ TOTAL COGS: =units × weighted_avg_landed COGS as % of revenue: ____% ____% ____% ____% ``` Target gross margin benchmarks: Health & beauty 60-70%, home & garden 50-60%, apparel 55-65%, electronics 25-40%, sporting goods 40-50%. [src2] **Verify**: COGS as percentage of net revenue should fall within category benchmarks above. If your COGS % is more than 10 points above category median, review landed cost components for errors. **If failed**: Most common error is omitting freight-in or duties. Freight typically 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 a direct offset to revenue and an additional cost line for returns processing. Overall ecommerce return rates averaged 16.9% in 2024, up from 8.1% in 2019. [src4] ``` RETURN RATE ASSUMPTIONS (by category) ═══════════════════════════════════════════════════════════ Category Return % Processing Cost/Return ───────────────────────────────────────────────────────── Apparel/Fashion: 25-40% $15-25 (inspection + repack) Footwear: 30-35% $12-20 Home goods: 15-20% $10-15 Electronics: 8-10% $8-12 Health & beauty: 5-8% $5-8 Food & beverage: 2-4% $3-5 (usually write-off) MONTHLY RETURNS IMPACT ═══════════════════════════════════════════════════════════ M1 M2 M3 ... M12 Gross orders: ____ ____ ____ ____ Return rate: ____% ____% ____% ____% Returned orders: ____ ____ ____ ____ Revenue reversed: $____ $____ $____ $____ Processing cost: $____ $____ $____ $____ Restocking salvage (%): 70% 70% 70% 70% NET RETURN COST: $____ $____ $____ $____ Seasonal return adjustments: Q1 (Jan-Mar): +5% above baseline (holiday returns) Q2 (Apr-Jun): baseline Q3 (Jul-Sep): baseline Q4 (Oct-Dec): +3% above baseline (bracketing behavior) ``` Processing a single return costs 20-65% of the original item value when accounting for all associated expenses including return shipping ($8-12), inspection ($5-8), and restocking ($2-4). [src4] **Verify**: Total annual return cost as percentage of gross revenue should be 3-8% for most categories. If above 10%, either the return rate assumption is too high or processing costs need optimization. **If failed**: Recheck category-specific return rates. If selling apparel, 25-30% is normal and must be budgeted, not ignored. ### Step 4: Build Shipping Cost Model **Duration**: 30-45 minutes **Tool**: Google Sheets Model both outbound shipping (to customer) and inbound freight (supplier to warehouse) separately. ``` SHIPPING COST MODEL ═══════════════════════════════════════════════════════════ OUTBOUND (to customer) M1 M2 M3 ... M12 Orders shipped: ____ ____ ____ ____ Avg package weight: ____ lbs Avg shipping zone: 3-4 (domestic) Cost per shipment: $____ Free shipping threshold: $____ % orders qualifying: ____% Customer-paid shipping: $____ TOTAL OUTBOUND COST: $____ $____ $____ $____ Net shipping cost (cost - revenue): $____ $____ $____ $____ INBOUND (supplier to warehouse) PO frequency: Monthly / Bi-monthly Avg PO size: ____ units Freight method: Sea / Air / Ground Cost per PO: $____ Per-unit freight-in: $____ (included in landed COGS) RETURN SHIPPING Return label cost: $____/return Returns per month: ____ TOTAL RETURN SHIP: $____ $____ $____ $____ ``` Shipping typically represents 8-15% of revenue for DTC brands. Free shipping thresholds (e.g., free over $75) increase AOV by 15-25% but increase shipping cost per order by 5-10%. **Verify**: Total shipping cost (outbound + return shipping) as percentage of net revenue should be 8-15%. Below 8% may mean you are undercharging on shipping-paid orders. Above 15% means margin erosion. **If failed**: Negotiate volume rates with carriers, increase free shipping threshold, or add shipping surcharge for oversized items. ### Step 5: Model Marketing Spend and Customer Acquisition **Duration**: 45-60 minutes **Tool**: Google Sheets Build the marketing budget with ROAS targets by channel and blended CAC calculation. ``` MARKETING SPEND MODEL ═══════════════════════════════════════════════════════════ M1 M2 M3 ... M12 BY CHANNEL Meta (FB/IG) spend: $____ $____ $____ $____ → ROAS target: 3.0x 3.0x 3.5x 4.0x → Revenue attributed: $____ $____ $____ $____ → New customers: ____ ____ ____ ____ → CAC: $____ $____ $____ $____ Google Ads spend: $____ $____ $____ $____ → ROAS target: 4.0x 4.0x 4.5x 5.0x → Revenue attributed: $____ $____ $____ $____ → New customers: ____ ____ ____ ____ → CAC: $____ $____ $____ $____ TikTok spend: $____ $____ $____ $____ Influencer spend: $____ $____ $____ $____ Email/SMS (tools): $____ $____ $____ $____ TOTAL MARKETING: $____ $____ $____ $____ Marketing as % of rev: ____% ____% ____% ____% BLENDED CAC: $____ $____ $____ $____ =Total spend / Total new customers ROAS BENCHMARKS: Good ROAS: 3-4x (breakeven with 25-33% margins) Strong ROAS: 5-8x (profitable customer acquisition) Weak ROAS: <2x (losing money on acquisition) Seasonal adjustments: Q1: -20% spend (post-holiday slowdown) Q2: baseline Q3: +10% (back-to-school, Prime Day) Q4: +40-60% (Black Friday, holiday, CPM inflation) ``` A good ROAS depends on your margin structure: 2:1 is viable with 50% margins, but you need 4:1+ with 25% margins. [src5] **Verify**: Marketing spend as percentage of net revenue should be 15-30% for growth-stage DTC, 10-20% for established brands. Blended CAC should be less than one-third of first-order AOV. **If failed**: If CAC exceeds AOV, the business model requires either higher AOV, better conversion, or repeat purchase revenue to work. Model LTV:CAC ratio before proceeding. ### Step 6: Calculate Contribution Margin and Build P&L **Duration**: 30-45 minutes **Tool**: Google Sheets Assemble all variable costs into a contribution margin waterfall, then add fixed costs for full P&L. [src5] ``` CONTRIBUTION MARGIN WATERFALL (Monthly) ═══════════════════════════════════════════════════════════ M1 M2 M3 ... M12 NET REVENUE: $____ $____ $____ $____ (after discounts + returns) VARIABLE COSTS (-) COGS (landed): $____ $____ $____ $____ (-) Outbound shipping: $____ $____ $____ $____ (-) Return processing: $____ $____ $____ $____ (-) Return shipping: $____ $____ $____ $____ (-) Payment processing: $____ $____ $____ $____ (2.9% + $0.30/txn) (-) Marketing spend: $____ $____ $____ $____ ───────────────────────────────────────────────────────── CONTRIBUTION MARGIN: $____ $____ $____ $____ CM %: ____% ____% ____% ____% FIXED COSTS (Monthly) (-) Platform fees: $____ (Shopify, tools, apps) (-) Warehouse/storage: $____ (3PL monthly minimum or lease) (-) Team/salaries: $____ (-) Software/SaaS: $____ (-) Insurance: $____ (-) Other overhead: $____ ───────────────────────────────────────────────────────── TOTAL FIXED COSTS: $____ EBITDA: $____ $____ $____ $____ EBITDA %: ____% ____% ____% ____% CM BENCHMARKS (DTC brands): Top quartile: 56% Median: 25% Bottom quartile: 3% EBITDA BENCHMARKS: Healthy: 7-10% Median: 3-5% Seasonal peak (Nov): ~11% Seasonal low (Oct): ~2% ``` DTC contribution margins typically range 30-40%, while marketplace sellers achieve 15-25% due to platform fees. [src5] Median EBITDA across 7-8 figure ecommerce brands is 3-5%, with top performers reaching 7-10%. [src2] **Verify**: Contribution margin should be positive by month 3-6 for viable businesses. If CM is negative beyond month 6, the unit economics do not work at current pricing and cost structure. **If failed**: Identify which variable cost line is the largest drag. Common fixes: raise prices (test 10-20% increase), reduce COGS (negotiate with suppliers at volume), 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 businesses see 30-40% of annual revenue concentrated in Q4. [src2] ``` SEASONAL ADJUSTMENT FACTORS ═══════════════════════════════════════════════════════════ Month Revenue Marketing Inventory Returns Factor Factor Purchase Factor ───────────────────────────────────────────────────────── Jan 0.70 0.60 0.80 1.05 (holiday returns) Feb 0.75 0.70 0.85 1.00 Mar 0.85 0.80 0.90 1.00 Apr 0.90 0.85 0.95 1.00 May 0.95 0.90 1.00 1.00 Jun 0.90 0.85 1.20* 1.00 Jul 0.85 0.80 1.30* 1.00 Aug 0.95 0.90 1.40* 1.00 Sep 1.00 0.95 1.00 1.00 Oct 1.05 1.10 0.80 1.00 Nov 1.50 1.50 0.60 1.03 Dec 1.40 1.30 0.50 1.03 ───────────────────────────────────────────────────────── * Q3 inventory purchases front-load for Q4 demand ADJUSTED MONTHLY REVENUE = Base Monthly × Seasonal Factor ADJUSTED MARKETING = Base Monthly × Marketing Factor INVENTORY ORDERS = Expected Demand(t+lead_time) × Safety Stock(1.2-1.5) ``` EBITDA follows a seasonal path with its peak in November (~11%) and its low in October (~2%). Plan cash reserves to cover low months. [src2] **Verify**: Sum of all monthly seasonal factors for revenue should approximate 12.0 (12 months). If significantly above, you are overestimating; if below, underestimating. **If failed**: Calibrate against actual prior-year data if available. For new businesses, use industry-standard seasonal curves rather than guessing. ### Step 8: Build Scenario Analysis Tab **Duration**: 20-30 minutes **Tool**: Google Sheets Create three scenarios by varying the key drivers: conversion rate, AOV, and CAC. ``` SCENARIO ANALYSIS ═══════════════════════════════════════════════════════════ Pessimistic Base Case Optimistic ───────────────────────────────────────────────────────── Conversion rate: 1.8% 2.5% 3.5% AOV: $____ (-15%) $____ $____ (+15%) Monthly traffic growth rate: 3% 5% 8% CAC: $____ (+20%) $____ $____ (-20%) Return rate: +5% above baseline -3% below category avg category avg COGS change: +5% baseline -5% RESULTING METRICS Annual revenue: $____ $____ $____ Gross margin: ____% ____% ____% Contribution margin:____% ____% ____% EBITDA: $____ $____ $____ Cash breakeven month: M____ M____ M____ 12-month cash needed: $____ $____ $____ ``` Focus scenarios on 5-8 key drivers that actually move outcomes: unit sales volume, contribution margins, customer acquisition costs, inventory turn rates, and cash conversion cycles. [src1] **Verify**: Pessimistic scenario should still show a path to profitability (even if delayed). If pessimistic scenario shows permanent cash burn, the business model has structural risk. **If failed**: Re-examine pricing power and cost structure. If even optimistic margins are thin, the category may not support a standalone DTC brand. ## Output Schema ```json { "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"} ], "expected_completeness": "All formulas linked, no hardcoded values in output tabs", "sort_order": "chronological (M1-M12)", "deduplication_key": "month" } ``` ## Quality Benchmarks | Quality Metric | Minimum Acceptable | Good | Excellent | |---------------|-------------------|------|-----------| | COGS accuracy (landed cost completeness) | Product cost only | Product + freight + packaging | Full landed (product + freight + duty + packaging + QC) | | Return rate modeling | Flat rate across categories | Category-specific rates | Category + seasonal adjustment factors | | Revenue forecast basis | Industry benchmark estimates | 3-month actual data | 12+ month actuals with cohort retention | | Scenario coverage | Base case only | Base + pessimistic | Base + pessimistic + optimistic + sensitivity | | Cost completeness | COGS + marketing | + shipping + returns | + payment processing + platform fees + all variable costs | **If below minimum**: A model with only product cost as COGS and a flat return rate will understate true costs by 20-40%. Add landed cost components and category-specific return rates before using for any business decisions. ## Error Handling | Error | Likely Cause | Recovery Action | |-------|-------------|----------------| | Gross margin negative or < 10% | Landed cost not fully captured or pricing too low | Rebuild landed cost with all components; if margin is still < 30%, reprice or find alternative suppliers | | Revenue forecast unrealistically high | Conversion rate or traffic assumptions too aggressive | Reset to industry benchmarks (2.5-3% conversion); validate traffic estimates against comparable stores | | Inventory purchases and revenue misaligned | Lead time not factored into purchase timing | Shift inventory purchase schedule earlier by supplier lead time (30-90 days for overseas) | | Contribution margin negative every month | Variable costs exceed revenue per order | Calculate per-order unit economics first; if per-order CM is negative, the business model needs restructuring before building a full model | | Cash flow shows increasing deficit despite profitable P&L | Inventory working capital not modeled | Add cash flow tab that accounts for inventory purchases timing vs. revenue collection timing | | Seasonal factors produce impossible numbers | Factors not calibrated to sum to ~12x monthly baseline | Normalize seasonal factors so annual total matches expected annual revenue | ## Cost Breakdown | Component | Free Tier | Paid Tier | At Scale | |-----------|-----------|-----------|----------| | Spreadsheet tool | Google Sheets ($0) | Excel ($7-10/mo) | N/A | | Analytics data | Shopify Analytics (free) | Triple Whale ($100/mo) | Shopify Plus ($2K+/mo) | | Accounting integration | Manual entry ($0) | A2X ($19/mo) | Finaloop ($200+/mo) | | Premium model template | Self-built ($0) | 10XSheets ($99 one-time) | Custom CFO model ($2K-5K) | | **Total for model build** | **$0** | **$99-200** | **$2K-5K** | ## 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 + ocean/air freight per unit + import duties (check HTS codes) + customs brokerage + packaging materials + quality inspection fees. This is your real cost basis. [src7] ### Wrong: Using a flat return rate across all product categories A 10% flat return rate applied to an apparel business (real rate: 25-40%) dramatically overstates net revenue. For an electronics store (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 your specific product category, then layer in seasonal spikes (January for holiday returns, Q4 for bracketing behavior). Also model the processing cost per return as a separate cost line. ### Wrong: Ignoring seasonality in both revenue and marketing spend Building a linear monthly model (annual revenue / 12) misses that Q4 may generate 30-40% of annual revenue, while January may be the slowest month. Marketing CPMs also increase 30-50% in Q4 due to auction competition. [src2] ### Correct: Apply monthly seasonal adjustment factors to revenue, marketing, and inventory Use the seasonal factor table (Step 7) as a starting point, then calibrate with your actual data after 6-12 months. Front-load inventory purchases in Q3 to avoid stockouts during 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. ## Related Units - [Startup Idea Structuring Template](/business/startup-planning/startup-idea-structuring-template/2026) — business concept inputs that feed this model - [Fundraising Financial Projections](/finance/startup-finance/fundraising-financial-projections/2026) — investor-ready version derived from this operating model - [Startup Cash Flow Runway Calculator](/finance/startup-finance/startup-cash-flow-runway-calculator/2026) — cash runway analysis using model outputs - [SaaS Unit Economics Calculator](/finance/saas-benchmarks/saas-unit-economics-calculator/2026) — equivalent model for subscription businesses