Calculate product unit cost in Excel with Copilot

A unit cost model answers one question per product: what does it really cost to make one? Copilot in Excel builds the formulas for that model from a description in about an hour, including the control that catches the most common error in cost accounting, overhead that does not add up. The allocation base is the decision that shapes every number, and it is yours to make before you ask.

Gather the inputs in one workbook

Put the building blocks on separate, clearly labeled ranges: raw material standards per unit of each product, material prices, direct labor per unit, and manufacturing overhead for the period. Format each range as a table with Ctrl+T and headers in the first row; Copilot works only with data formatted this way. Save the file in OneDrive or SharePoint with AutoSave on, since Copilot cannot see a local file.

Keep supplier prices and individual salaries out of any public service. Inside the corporate Microsoft 365 tenant, the workbook stays within company protection.

Choose the allocation base

Overhead is where cost models go wrong. Ask Copilot to allocate indirect costs without a base and it spreads them evenly across products, which undercharges the product that runs on the machines all day and overcharges the one assembled by hand. Pick the base that reflects what actually drives the overhead: machine hours for a machine-heavy plant, direct labor for manual production, volume where products are similar. Write the choice and the reason on the assumptions sheet.

The prompt

Role: you are a cost accountant at a manufacturing company. Context: products: {{list}}. Cost elements: {{elements}}. The table has columns {{columns}}. Overhead allocation base: {{base}}. Task: build a unit cost calculation for each product with Excel formulas using my locale's separators. Allocate indirect costs in proportion to the base. Add a control: total allocated costs equal the overall total. Format: the sheet structure, formulas for each column, control formulas and an explanation of how to update prices. No long dashes.

The control formula is the most valuable line in the output. If total allocated overhead differs from the period's overhead, something is off: a product missing from the base, a hard-coded number, a reference that shifted when a formula was filled down.

Check one product by hand

Pick one product and calculate its cost on paper or in a scratch sheet: materials at standard times price, labor, and its share of overhead by the base. Compare with the model. Copilot confuses absolute and relative references and occasionally the direction of a condition, and one hand-checked product catches both.

Then compare the model's cost with the book cost for the previous period. They will differ. The useful part is explaining why: price changes, a different allocation base from the one in the books, standards that no longer match actual consumption. Each explanation is either a correction to the model or a finding for the production team.

What to do

1. Collect material standards, prices, direct labor and overhead for the period in formatted tables.
2. Choose the overhead allocation base and record the reason on an assumptions sheet.
3. Ask Copilot to build the calculation with the prompt above.
4. Confirm the control formula shows total allocated overhead equals the period's total.
5. Check one product's cost by hand.
6. Compare with the previous period's book cost and explain the difference.
7. Save the model with the assumptions sheet for monthly price updates.

A model you can trust

Total allocated overhead equals the period's overhead, one product's cost is confirmed by hand, and the difference with the books is explained.

Licenses

Copilot in Excel comes with Microsoft 365 Copilot at 30 USD per seat with annual billing, where company data stays in the corporate tenant, or with Copilot Pro at 20 USD per month for individuals with Microsoft 365 Personal or Family. See Copilot in Excel pricing, the economist's version of this task and Copilot in Excel vs Gemini in Google Sheets.