Build a pricing model in Excel with Copilot

A pricing model is useful when a change in cost or exchange rate flows through to every price without anyone editing formulas. Copilot in Excel builds that structure in about an hour: an assumptions sheet, price formulas for each sales channel, a break-even floor and conditional formatting for prices below it. The pricing logic itself, cost-plus, market-based or a mix, is the commercial decision you bring.

Decide the logic first

Write down how prices should be set. Cost-plus: cost times a target margin. Market-based: anchored to competitor prices, with cost as a floor. Mixed: cost-plus, adjusted toward the market where you are far off. Each channel may differ; a marketplace with high commission and a direct B2B channel with volume discounts rarely share one formula. This description goes into the prompt and decides the structure Copilot proposes.

Collect the inputs: product cost, purchase currency, target margin, competitor prices with the date you collected them, and discounts for each channel. Format each range as a table, and keep the file in OneDrive or SharePoint with AutoSave on.

The prompt

Role: you are a pricing analyst. Context: products: {{list}}. Cost is in column {{column}}, purchase currency {{currency}}, target margin {{percent}}, sales channels and discounts: {{channels}}. Task: propose a model structure with an assumptions sheet, price formulas for each channel using my locale's separators, a minimum break-even price and conditional formatting for prices below the minimum. Format: a list of sheets, a table "column, formula, explanation", three scenarios for testing. No long dashes.

The rule that keeps the model alive

Every number that can change lives on the assumptions sheet: exchange rate, margin, channel discounts, commission. Formulas refer to those cells. When Copilot writes a formula with a number typed into it, such as a margin written directly as a percentage, move that number to the assumptions sheet and reference it. A model with hard-coded numbers works on the day you build it and quietly breaks the first time something changes.

Check formulas on control rows. Copilot mixes up absolute and relative references, and a price formula that is correct in the first row may point at the wrong cost further down.

Test with scenarios

Run the three scenarios the model suggests, or your own: cost up, exchange rate moves against you, a channel raises its commission. Watch the margin and the highlighted cells. The minimum price check is the practical output here: it shows which products start losing money in which channel, and by how much the price would need to move.

What to do

1. Collect cost, target margin, competitor prices with dates and channel discounts.
2. Describe the pricing logic: cost-plus, market-based or mixed.
3. Ask Copilot to build the model with the prompt above.
4. Move any hard-coded numbers in formulas to the assumptions sheet.
5. Check that the minimum break-even price is calculated and highlighted.
6. Run three scenarios of cost and exchange rate changes and watch the margin.
7. Show the model to the commercial director and record the rules for changing prices.

When it works

Each product's price is derived from the assumptions by formula, the minimum price is highlighted, and changing the exchange rate or cost recalculates everything without manual edits.

Licenses

Copilot in Excel is included in Microsoft 365 Copilot at 30 USD per seat with annual billing or in Copilot Pro at 20 USD per month for individuals with Microsoft 365 Personal or Family. If you use the =COPILOT() function to classify products, save the results as values, since it may return a different answer on recalculation. See Copilot in Excel pricing, the economist's version of this task and Copilot in Excel vs Rows AI.