Prompt to build an Ecommerce Forecasting Tool
Monthly customer cohorts, repeat orders from a retention curve, contribution margin and LTV. Paste the prompt into Claude, ChatGPT or Gemini, answer its questions, and it builds the workbook with the same structure as my Ecommerce Forecasting Tool.
Or get the finished model

Ecommerce Forecasting Tool
Built and tested, for Excel and Google Sheets and in your browser. $20 suggested, pay what you want.
How to use it
You need an AI that hands back a real spreadsheet file, not a description of one. Here’s where that lives in each:
- Claude creates .xlsx files on every plan, free included, and it’s on by default. If it only describes the workbook, open Settings, then Capabilities, and turn on “Code execution and file creation” (called “Create and edit files” in newer versions). On a Team or Enterprise plan your admin controls it.
- ChatGPT has no switch to find. Ask for the workbook as an .xlsx file and it gives you a download link. A paid plan copes better with a model this size.
- Gemini builds working spreadsheets inside Google Sheets: open a blank sheet, click the Gemini icon in the side panel and paste the prompt there. That needs a Google AI Pro or Ultra plan or a Workspace plan with Gemini.
The prompt opens with a few questions about your business. Answer them, or tell it to use the defaults and see the model working before you put your own numbers in.
When it’s done, open the workbook and read the model checks before you trust a number. An AI that builds a model from a prompt still makes mistakes, and the checks are there to catch them. If you want a second pair of eyes on it, that’s what a working session is for.
The prompt
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.