# Build a Ecommerce Forecasting model

Source: https://www.hemrock.com/ecommerce-forecasting-tool/prompt

Paste everything below into Claude, ChatGPT or Gemini.

---

You are an experienced ecommerce finance modeler. Build me a monthly ecommerce forecast in a spreadsheet that tracks each month's new customers as a cohort and forecasts their repeat orders from a retention curve. Retention here means the chance a customer orders again, not subscription churn. It should forecast orders, revenue, cost of sales, marketing spend and contribution margin, plus per-order economics and customer lifetime value.

## Before you build

Ask me these questions first, in one message, and wait for my answers. If I say "use the defaults", use the defaults below.

1. What's your average order value for a first order, and for a repeat order? Is it trending up or down?
2. What's your cost of goods as a percent of revenue, and any other variable costs (payment fees, packaging, fulfillment)? Do you charge for shipping, and what does shipping cost you?
3. How many new customers do you get a month now, and how fast is that growing?
4. How often does a typical customer reorder, and what share don't come back each cycle? Do you have a retention curve from your own cohort data?
5. What does it cost to acquire a new customer, and to drive a repeat order?

## Workbook structure

- **Get Started**: every input.
- **Forecast**: one column per month, with the retention curve, the cohort grid, revenue, costs and per-order economics. A fiscal-year summary block at the top.
- **Key Reports**: charts only, plotting rows from the Forecast.

## Inputs (Get Started)

| Input | Default |
| --- | --- |
| Company name, currency symbol | Company, $ |
| First month of the forecast | Jan of next year |
| Months to forecast (12 to 120) | 48 |
| Fiscal year end month | December |
| Average order value, new customer | $50 |
| Average order value, repeat customer | $40 |
| Monthly change in order value, % | 10% |
| Monthly change in that rate, % of itself | −5% |
| Cost of sales, % of product revenue | 25% |
| Additional cost of sales, % of product revenue (fees, packaging) | 10% |
| Second additional cost of sales, % | 0% |
| Monthly change in cost of sales, and the change in that rate | 0%, 0% |
| Shipping revenue per order, shipping cost per order | $0, $0 |
| Acquisition cost per new customer | $30 |
| Acquisition cost per repeat order | $10 |
| Monthly change in acquisition costs, and the change in that rate | 0%, 0% |
| New customers in the first month | 550 |
| Monthly growth in new customers, % | 90% |
| Monthly change in that growth rate, % of itself | −15% |
| Conversion rate, sessions to orders | 2% |
| Customers at the start (existing base) | 0 |
| Retention approach (dropdown: Calculated, Manual curve) | Calculated |
| Churn per repeat cycle, % | 50% |
| Repeat cycle, months | 3 |
| Retention curve multiplier (scales every month after the first) | 1.0 |
| Change cohort performance after this many total customers | 0 (off) |
| Cohort performance multiplier once past that | 1.0 |
| Months of customer lifetime counted in LTV | 60 |
| Manual retention curve: orders per new customer by month since first order, 120 months | blank |

## Calculations (Forecast)

Rates that trend. Order values, cost of sales percents and acquisition costs each start at their input and move every month by a rate, and that rate itself changes by its own percent each month. Build each as two rows: the rate (month 1 = input, then prior × (1 + change in rate)) and the value (month 1 = input, then prior × (1 + this month's rate)). New customer growth works the same way.

Retention curve, one row by month since first order (month 0 to 120):
- Calculated: month 0 = 1 order. In every month that's a multiple of the repeat cycle, the value is (1 − churn per cycle) × the value one cycle earlier. Every other month is 0. With the defaults that gives 1, 0, 0, 0.5, 0, 0, 0.25 and so on.
- Manual: the curve I type in.
- Apply the multiplier to every month after month 0.
- Lifetime orders per customer = the sum of the curve over the LTV months.

Cohort grid. One row per monthly cohort, one column per forecast month. Each cell = that cohort's new customers × the curve value for months since the cohort started × the cohort performance multiplier (1 until cumulative new customers pass the threshold, then the multiplier input). Existing customers at the start are on average partway through their lifetime, so each places lifetime orders ÷ 48 × (48 − month number) ÷ 48 orders in forecast months 1 to 48: a straight taper to zero that adds up to about half their lifetime orders.

Orders and traffic:
- Total orders = the sum of the cohort grid for the month, plus orders from existing customers.
- New customer orders = new customers that month. Repeat orders = total orders − new customer orders.
- Website traffic = total orders ÷ conversion rate.
- New customer share of orders.

Revenue and costs:
- Product revenue = new orders × new order value + repeat orders × repeat order value.
- Shipping revenue = total orders × shipping revenue per order. Total revenue = product + shipping.
- Cost of sales = product revenue × each cost of sales percent, plus total orders × shipping cost per order.
- Marketing spend = new customers × new acquisition cost + repeat orders × repeat acquisition cost.
- Contribution (operating income) = revenue − cost of sales − marketing spend.

Per-order economics by month: average order value, cost of sales per order, gross margin per order, net shipping per order, acquisition cost per order, contribution margin per order.

Customer lifetime value = (new order value + repeat order value × (lifetime orders − 1)) × (1 − total cost of sales %) + (shipping revenue − shipping cost) × lifetime orders, using the starting inputs. Show LTV ÷ new customer acquisition cost beside it.

Fiscal-year summary with SUMIFS on the fiscal year row: orders, new customers, repeat orders, revenue, cost of sales, marketing spend, contribution and contribution margin %.

## Key Reports

Charts: orders by month split into new and repeat; the cohort grid as a stacked area (a "layer cake") so each cohort's orders are visible; revenue and contribution by month; contribution margin per order over time; the retention curve.

## Model checks

TRUE or FALSE: retention curve month 0 is 1 and no value is negative; total orders equal the sum of the cohort grid plus existing customers' orders; repeat orders are never negative; conversion rate is above 0%.

## How the workbook should look and behave

These are my house rules for spreadsheets. Follow all of them.

- Use Arial 12 everywhere, including the workbook's default style. Never Calibri.
- Inputs are blue text (#0000FF) on a light grey fill (#F2F2F2), so they read like form fields. Calculations are black text with no fill. Never use grey text anywhere.
- Every sheet leaves column A blank and narrow. The model starts in column B.
- Column B holds row labels. Column C is a narrow Notes column, about half the width of the label column, holding a short note on what the row is or where it comes from. Let notes clip rather than wrap. Values, or the first period of a timeline, start after the notes column.
- No cell comments. Notes go in the notes column.
- Turn gridlines off on every sheet. Freeze panes on the header rows only, never on columns.
- No borders at all, not even header underlines or subtotal rules. Blank rows and bold text carry the structure.
- Every calculated number is a live formula that traces back to an input. Never type a number into a calculation cell, and never mix typed values and formulas in the same row.
- Keep the number of sheets low. Sheets that share a timeline use the same columns, so a given column is the same month on every sheet.
- Format currency with thousands separators and no decimals, percentages with one decimal place, and dates as "mmm yyyy".
- Name rows in plain words a founder or investor would use, with units in the label where it helps ("Churn per contract cycle, %").

## When you're done

- Recalculate and confirm there are no errors (#REF!, #DIV/0!, #VALUE!, #N/A) and no circular references unless I asked for one.
- Show me the results of the model checks and the headline outputs.
- List every assumption you made that I didn't give you, and the three inputs I should change first.
- If you can't produce an .xlsx file, give me each sheet as a table with the exact label, input value or formula in every cell, so I can rebuild it in Excel or Google Sheets without guessing.
