Claude in Excel: How to Build a Profit-First Ecommerce Forecast (10 Copy-Paste Prompts)

Claude in Excel puts Claude in a sidebar inside your workbook, where it reads every tab, writes real formulas and shows which cells a number came from. For an ecommerce brand, the best thing to point it at is the forecast: the 12-month plan that sets how much you can spend on ads and still hit your profit.

Most forecasts are built backwards: last year's revenue plus 30%, and whatever's left after ads is "profit". A profit-first forecast starts from the net profit you need and works back to the revenue, CAC, MER and marketing spend each month has to deliver, with Claude doing the formulas and you checking them.

Prefer to watch? Karl builds a profit-first forecast step by step in this 15-minute video.

What is Claude in Excel?

Claude for Excel is Anthropic's official add-in for Microsoft Excel, sold as part of "Claude for Microsoft 365". According to Anthropic's documentation, it answers questions about a workbook with cell-level citations, changes assumptions without breaking formulas, traces errors, builds new models and works across tabs.

How to add Claude to Excel

1. Open the Claude for Microsoft 365 listing on Microsoft Marketplace and select Get it now.
2. Open Excel and activate the add-in: Home > Add-ins on Windows, Tools > Add-ins on Mac.
3. Sign in with your Claude account (Pro or above) and the sidebar opens next to your workbook.

On a company-managed Microsoft 365 account, an admin may need to deploy it from the admin centre (Microsoft's guide).

Put your modelling rules in the add-in's Instructions field ("Always write formulas that reference the Inputs tab. Never type a calculated number into a cell."), and only use it on spreadsheets you trust: Anthropic warns downloaded files can hide instructions.

What is a profit-first forecast?

A profit-first forecast works up from the bottom of the P&L. Decide the net profit you need, subtract landed cost of goods (product, packing, shipping, payment fees, returns) and fixed operating costs, and what's left is the marketing budget you can afford. Target net sales ÷ that budget is the blended MER every month has to hit. It rests on contribution margin: plan on what each order keeps, not what it sells for.

Here's the maths on a fictional brand. It wants $3.0M of net sales and $300k of net profit. Landed COGS runs at 40% ($1.2M) and operating costs are $540k a year. That leaves $3.0M − $1.2M − $540k − $300k = $960k of allowable marketing spend, so the plan needs a blended MER of $3.0M ÷ $960k = 3.13 for the year.

If that MER is well above anything the brand has achieved, an input has to give: the sales target, the profit target or the costs. That feasibility check is the most useful thing a forecast does (more in how to forecast ad spend, MER vs ROAS and the break-even MER calculator).

Set up the workbook before you prompt

Pull last year by month first: net sales, orders, total marketing spend and the share of sales from returning customers. Shopify's reports give you sales and orders; spend comes from each ad platform. Then set up the workbook like this:

The inputs that matter most:

• Last year by month: net sales and marketing spend, which set your seasonality.
• Average order value: net AOV for the last 12 months.
• Landed COGS %: product plus packing, shipping, fees and returns (contribution margin calculator).
• Repeat share: sales from returning customers, which decides how many new customers you need.
• Target net profit: pick it before you look at revenue.

Or start from StoreHero's free ecommerce forecast template or subscription forecast template, and have Claude extend it.

10 Claude in Excel prompts for a profit-first forecast

Paste these one at a time, in order, and check each result before moving on.

1. Read last year

Read the History tab. Summarise last year by month: net sales, marketing spend, MER and orders. Flag any month that looks like a data error, and cite the cells you used.

2. Lay out the inputs

Create an Inputs tab with labelled cells for: target annual net sales, landed COGS %, annual operating expenses, target net profit, AOV, repeat share of net sales and first forecast month. Shade the input cells and don't calculate anything yet.

3. Work out the spend the plan allows

On the Inputs tab, add formulas for landed COGS, allowable marketing spend (net sales minus COGS minus operating expenses minus target profit), target MER and contribution margin after marketing. Reference input cells only, with a plain-English note beside each formula.

4. Build seasonality from history

Create a Seasonality tab with each month's share of last year's net sales, as formulas from the History tab, adding up to 100%. List any one-off months (a stockout, a viral spike) so I can decide whether to adjust them.

5. Build the 12-month forecast

Build a Forecast tab with 12 monthly columns. For each month calculate net sales (target × seasonality), landed COGS, marketing spend (net sales ÷ target MER), contribution margin, operating expenses, net profit, orders, new customers and blended new-customer CAC, plus a totals column. Every cell must be a formula referencing Inputs or Seasonality: no typed numbers.

6. Audit for hardcoded numbers

Audit the Forecast tab. List every typed number, every formula that doesn't trace back to Inputs and any circular references. Fix them and list what you changed.

7. Make Q4 realistic

Acquisition costs usually rise in November and December. Add a monthly MER adjustment row (default 0%) and rebalance so annual marketing spend still equals the allowable total. Show which months now need more spend than last year.

8. Add scenarios

Create a Scenarios tab with downside, base and upside columns: net sales −10% / base / +10%, landed COGS +3 points / base / −2 points. Show allowable marketing spend, required MER and the gap to last year's MER. Use formulas, not Excel Data Tables.

9. Add a checks tab

Create a Checks tab returning OK or FIX for: seasonality adds to 100%, forecast net sales and net profit match target, marketing spend is positive, and required MER is within 15% of last year's. Green for OK, red for FIX.

10. Explain it back to you

Walk me through how November's net profit is calculated, cell by cell, and tell me which single input it is most sensitive to.

How to check Claude's work

Anthropic says to verify audit-critical calculations and review anything final. Four checks take ten minutes:

• Read the formula bar. If November's marketing spend shows 144000 instead of a formula like =C12/Inputs!$B$13, it's hardcoded. Run prompt 6 again.
• Change one input. Moving landed COGS from 40% to 42% should drop allowable marketing from $960k to $900k and lift required MER from 3.13 to 3.33. If nothing moves, a link is broken.
• Check the anchors. Inputs used across 12 columns need absolute references ($B$13), or formulas drift when copied.
• Compare with a baseline. Ask Claude to add Excel's FORECAST.ETS as a row. If your target sits far above that trend line, that's the growth you're betting the budget on.

Here's the fictional brand's finished forecast. A flat cost base and seasonal sales mean January barely breaks even, while November makes over a quarter of the year's profit.

Use scenario tabs to stress-test the plan

Scenario analysis is where profit-first earns its keep. In the downside case (sales −10%, COGS +3 points), the same $300k profit leaves $699k of marketing and the required MER jumps to 3.86. If last year's MER was 3.30, that's a conversation for January, not November.

Test seasonality too. Shopify's guide to demand forecasting methods notes that seasonal demand is fairly predictable, so if last November had a one-off spike, strip it out in prompt 4.

Where the spreadsheet forecast breaks

Claude gets you a sound model in an afternoon. The problem is the eleven months after:

• It's stale the day after you build it, and someone has to paste in actuals every month, which is the first job skipped in Q4.
• There's no daily pacing. You find out you missed November in December.
• Margin assumptions drift. The 40% COGS you typed in January isn't true by October, and the sheet doesn't know (contribution margin formula).
• It doesn't know about stock or ad spend. It says spend $144k in November without knowing your hero has three weeks of cover (inventory alerts).

From a spreadsheet to a live profit forecast in StoreHero

StoreHero's Goals & Forecasting starts from the same inputs (net sales, COGS, operating costs, target profit) and turns them into seasonal monthly targets with the marketing spend they allow. The difference is what happens next:

• Actuals arrive on their own from Shopify, Meta, Google Ads, Klaviyo and more, so contribution margin is calculated, not typed once and forgotten.
• Every goal paces green or red, daily: net sales, contribution margin, spend, MER and CAC against target.
• An AI forecast shows the path to the goal, not just the gap.
• One set of targets for marketing, finance and the founder.

Connect the StoreHero MCP to Claude (about 2 minutes, no API key) and it can answer "What should I change to hit this month's margin target?" from this morning's numbers. Claude for Excel can use the same connectors, so you can keep modelling in Excel on live data.

Which approach should you use?

If you plan once a year and one person owns the numbers, a workbook built with these prompts is enough. If you change spend weekly, run Q4 promotions or can sell out of stock, you need a forecast that updates itself.

Either way: decide the profit first, work out the spend it allows, then ask how much revenue that buys.

Key takeaways

• Claude for Excel is Anthropic's official add-in, included on Pro, Max, Team and Enterprise plans (not Free), installed from Microsoft Marketplace in three steps.
• A profit-first forecast starts from the net profit you need and backs into the marketing spend and MER each month can afford.
• Example: $3.0M net sales − $1.2M COGS − $540k opex − $300k profit leaves $960k of marketing, a required MER of 3.13.
• Make Claude build every output as a formula off one Inputs tab, then change an input and check the whole model moves.
• Claude in Excel can't run Data Tables or macros, so build scenarios as formulas on their own tab.
• A spreadsheet forecast goes stale the day after you build it; a live forecast paces every goal daily and lets Claude answer from current numbers.

Frequently asked questions

Sources

• Anthropic: Use Claude for Excel (documentation)
• Microsoft Marketplace: Claude for Microsoft 365
• Anthropic: Claude for Microsoft 365
• Claude pricing
• Anthropic: Collaborate with Claude across Excel, PowerPoint, Word and Outlook (7 May 2026)
• Anthropic: Advancing Claude for Financial Services (27 Oct 2025)
• Anthropic: Connectors and Skills in Claude for M365
• Anthropic: Data storage and retention for Claude for M365
• Microsoft Learn: Manage add-ins in the Microsoft 365 admin center
• Microsoft Support: FORECAST.ETS function
• Microsoft Support: Switch between relative, absolute and mixed references
• Shopify Encyclopedia: Contribution margin
• Shopify: Scenario analysis
• Shopify: Demand forecasting methods
• Shopify: AI sales forecasting
• StoreHero: Goals & Forecasting