# Ecommerce Forecast Model Build an ecommerce forecast model with traffic, conversion, average order value, margin, and channel drivers in one structure. - Canonical: https://finamodel.com/templates/ecommerce-forecast-model - Excel download: https://finamodel.com/templates/ecommerce.xlsx - Category: Consumer - Model type: Operating model - Difficulty: Beginner - Audiences: Founders & operators, CFOs & FP&A, E-commerce founders, Operations directors, CFOs, Growth marketers - Tags: ecommerce, unit-economics, cac, ltv, growth ## Overview An e-commerce unit economics model projects the lifetime profitability of customer cohorts acquired at different times through different channels. The model captures the customer acquisition cost (CAC) - the paid marketing spend per customer - and traces the payback period: how many months until cumulative margin from repeat purchases covers the CAC. Key metrics are the lifetime value (LTV) - total net revenue from a customer over their entire lifetime - and the LTV-to-CAC ratio, which must exceed 3:1 to be sustainable. The workbook builds gross margin by channel and product category, accounting for different COGS (cost of goods sold) and fulfillment costs (shipping, packaging, returns). Customer retention is modeled through repeat purchase rates: what percentage of Year 1 customers buy again in Year 2, and at what frequency? Working capital is driven by the cash conversion cycle - how many days between paying suppliers and collecting payment from customers. The model shows that high-CAC customers acquired via paid ads require high repeat purchase rates to break even, while organic customers (low CAC) are profitable from the first order. This template is essential for direct-to-consumer (D2C) brands, online marketplaces, and SaaS companies evaluating the true profitability of customer acquisition programs. ## What's included - Traffic and conversion forecast - Average order value assumptions - Revenue and margin outputs - Channel planning support - Revenue by channel and customer cohort - COGS and gross margin by product category or channel - Customer acquisition cost (CAC) and payback period - Lifetime value (LTV) with repeat purchase and margin assumptions - Fulfillment, returns, and logistics costs - Unit economics contribution and break-even analysis ## Ecommerce Forecast Model: How the DTC Template Works This ecommerce forecast model is an institutional-grade DTC financial template covering 24 monthly periods. It links traffic, conversion, channel economics, cohort retention, margin, inventory, PP&E, financing and the three financial statements. The public download is a values-only preview, so what follows explains the relationships the underlying model captures rather than live formulas. ### Operating drivers behind the revenue build The model starts from a traffic and conversion funnel. Channel mix percentages direct traffic to paid search, social and organic, then a blended funnel conversion path maps that traffic into new customers. - Monthly seasonality is applied to traffic, and cohort retention is handled by a 24-by-24 power-law matrix parameterised by first-month retention and a decay rate, so repeat customers emerge from row sums rather than a single global repeat rate. - New customers and retained customers combine into total customers, which converts into orders, then average order value builds gross revenue before discounts, returns, refunds and a subscription uplift. ### Calculation flow through cost, margin and cash Orders feed both revenue and cost. Product cost is category-weighted to reflect SKU and category gross-margin mix. - Inbound freight and fulfilment costs are added, with a toggle choosing between owned pick-pack and warehouse allocation or 3PL fees per order. Payment processing, customer service, and return, refund and exchange costs are included, with a salvage offset reducing return cost. - Purchases are driven by a target closing inventory based on days inventory outstanding and safety stock, so the inventory rollforward reconciles opening stock, purchases and product cost. PP&E depreciates equipment and software over separate lives, with a cap preventing over-depreciation. Marketing, operating expenses, interest, tax and working-capital movements then flow through the P&L and cash flow statement. ### Outputs and decision metrics The model produces a linked P&L, balance sheet and cash flow statement, plus a dashboard with 18 KPI cards covering trailing-twelve-month financials, month-24 unit economics, cash position and financing usage. - Unit economics are exposed as average order value blended across channels, customer acquisition cost both blended and by paid search, social and organic, and customer lifetime value on two bases: gross-margin LTV and contribution-margin LTV, which subtracts allocated marketing spend. That supports LTV/CAC and payback measured on both definitions. - Channel CAC is computed from channel marketing spend divided by channel new customers. Eight integrity checks test the balance sheet, cohort sum, channel sum, cash tie-out, depreciation and capital expenditure matches, retained earnings rollforward and a minimum cash floor. ### Practical use of the template Practically, the template lets a DTC operator or analyst trace how a change in channel mix, funnel conversion, first-month retention or decay alters customer counts, orders, revenue and contribution margin across 24 months. - The inventory and PP&E rollforwards keep the balance sheet connected to operating decisions, while the financing module applies term-loan amortisation, revolver draws and repayments against a minimum-cash floor, and a one-time equity raise in a chosen month. A tax gate uses cumulative positive EBT as a proxy for net operating loss carryforward. - The design is deliberately revenue-side for subscriptions, without a separate subscription cohort matrix, and it omits data tables, embedded charts and multi-currency conversion. Currency is display-only in USD. ## Built for ecommerce growth planning Use this model when you need to translate traffic, conversion, and order value assumptions into revenue and margin outcomes. ## Useful for operators, founders, and finance teams An ecommerce model makes it easier to see how marketing and trading assumptions flow through to profitability. ## Better for channel-based forecasting This gives you a cleaner structure for ecommerce planning than a generic operating model, especially when acquisition channels and conversion rates matter. ## Built for ecommerce growth planning Use this model when you need to translate traffic, conversion, and order value assumptions into revenue and margin outcomes. ## Useful for operators, founders, and finance teams An ecommerce model makes it easier to see how marketing and trading assumptions flow through to profitability. ## Better for channel-based forecasting This gives you a cleaner structure for ecommerce planning than a generic operating model, especially when acquisition channels and conversion rates matter. ## Workbook structure ### Traffic Inputs This sheet sets the channel assumptions that drive site visits, sessions, or customer acquisition volume. - Channel-level traffic inputs - Marketing or acquisition assumptions - Traffic growth by period - Demand-generation starting point ### Conversion & AOV The conversion sheet models how traffic turns into orders and how average order value affects revenue. - Conversion rate assumptions - Average order value inputs - Order volume build - Revenue logic from customer behaviour ### Revenue & Margin This sheet turns commercial drivers into revenue, gross profit, and margin visibility. - Revenue output by period - Gross margin and fulfilment cost logic - Contribution view if relevant - Profitability trend from the sales build ### Channel Output The output sheet helps compare channels and understand where the commercial plan is creating or destroying value. - Channel comparison view - Revenue and margin by case - Sensitivity to traffic or conversion changes - Clear commercial planning summary ### Traffic Inputs This sheet sets the channel assumptions that drive site visits, sessions, or customer acquisition volume. - Channel-level traffic inputs - Marketing or acquisition assumptions - Traffic growth by period - Demand-generation starting point ### Conversion & AOV The conversion sheet models how traffic turns into orders and how average order value affects revenue. - Conversion rate assumptions - Average order value inputs - Order volume build - Revenue logic from customer behaviour ### Revenue & Margin This sheet turns commercial drivers into revenue, gross profit, and margin visibility. - Revenue output by period - Gross margin and fulfilment cost logic - Contribution view if relevant - Profitability trend from the sales build ### Channel Output The output sheet helps compare channels and understand where the commercial plan is creating or destroying value. - Channel comparison view - Revenue and margin by case - Sensitivity to traffic or conversion changes - Clear commercial planning summary ## Features - **Cohort-level unit economics:** Track CAC, repeat rate, and LTV separately by acquisition channel to identify profitable vs. marginal channels. - **Channel-specific margin modeling:** Differentiate COGS, platform fees, and fulfillment costs by direct, marketplace, and wholesale channels. - **Payback period and return on ad spend:** Calculate months to recover CAC and ROAS by cohort to inform budget allocation. ## Use cases - **Growth budget allocation:** Identify high-LTV, low-payback cohorts to maximize marketing spend efficiency. - **Profitability and pricing analysis:** Test margin vs. volume trade-offs and evaluate product category profitability. - **Fundraising and investor updates:** Show path to unit economics breakeven and sustainable margin expansion. ## Frequently asked questions ### What is an ecommerce forecast model? It is a model that links traffic, conversion, order value, revenue, costs, and profitability for an ecommerce business. ### Who uses ecommerce forecast models? Founders, operators, finance teams, and investors use them to understand growth and margin outcomes. ### What should an ecommerce model include? It should include traffic, conversion rate, average order value, revenue, margins, and key channel assumptions. ### Why is this useful for ecommerce planning? Because it connects commercial levers such as traffic and conversion to financial outcomes such as revenue and margin. ### Can it support budgeting? Yes. It can be used for budgeting, forecasting, and evaluating channel performance assumptions. ## Related templates - [Subscription Box Economics](https://finamodel.com/templates/subscription-box-model) - [Marketplace Economics Model](https://finamodel.com/templates/marketplace-model) - [Food Delivery Unit Economics](https://finamodel.com/templates/food-delivery-model)