Ecommerce Forecast Model

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.

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.

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.

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.