Product & Customer Profitability Analyzer

Purpose and model boundary

This calculator shows where profit comes from in one summary of your sales. You enter your products, up to 50 sales summary rows that each name a product, a customer and a channel, and up to five shared cost pools. The spreadsheet works out each row's net revenue, product cost and contribution, spreads each shared pool over the rows to the cent, and sums the results by product, customer and channel, with one customer you choose as the focus.

One Calculate fills every result section. The calculator has no optimizer, no target search and no simulation, because this question needs none: every figure follows directly from what you enter. An 11 by 11 what-if table of price against direct cost for the focus customer is calculated with them.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. All sales, costs and pool amounts are your own inputs, and an allocated figure depends on the method you choose. Allocated profit is not the profit a customer adds. The shared costs do not leave when a customer does, so the page also shows what the company would earn without the focus customer. The calculator is not certified financial reporting and does not guarantee a profit.

Inputs and units

• Products, up to 10 rows:
  • a product ID: a capital letter, then up to 15 capital letters or digits, each ID used once;
  • the unit cost, from 0 to 100,000;
  • the return recovery, from 0% to 100%: the share of a returned unit's cost that comes back as stock you can sell again. Blank means 0%.
• Sales summary, up to 50 rows, filled from the top without a blank row between used rows. Rows can be typed or pasted from a spreadsheet. Each row has:
  • a row ID, in the same form as a product ID, each ID used once;
  • a product ID, which must be in the products table;
  • a customer ID and a channel, in the same form. There can be at most 10 different customers and 3 different channels;
  • units shipped, from 0 to 1,000,000; hours or days are fine;
  • units returned, in the same range, blank means 0. It may be more than units shipped, for returns of earlier sales;
  • gross sales, required, and discounts, refunds and direct costs, each from 0 to 1,000,000,000; a blank discount, refund or direct cost means 0. Direct costs are the shipping, service and channel costs that belong to this row only;
  • up to five activity counts, from 0 to 1,000,000,000, blank means 0. Activity column 1 is read only by pool row 1, column 2 by pool row 2, and so on, and only when that pool uses the Activity method.
• Shared cost pools, up to 5 rows, which may be empty: a pool ID in the same form, used once; the amount, from 0 to 1,000,000,000; and the method, Revenue, Units or Activity.
• Focus customer: one of the customer IDs in the sales table. The list follows the table as you type.
• Optional, under "Optional: what-if table":
  • the price multiplier, from 0 to 5 (1 unless you change it), which multiplies gross sales, discounts and refunds on every row, with units unchanged;
  • the direct cost multiplier, from 0 to 5 (1 unless you change it), which multiplies every unit cost, and so every return credit, and every row's direct costs. Shared pools do not change;
  • the spacing of the table's rows and columns, from 0.01 to 0.5 (0.05 unless you change it).

Money is in dollars; percentages are shares of a unit cost or of net revenue.

Governing relationships

Net revenue. Each row's net revenue is gross sales minus discounts minus refunds. Each amount is posted to the cent, rounding half away from zero, after the price multiplier. Net revenue can be below zero when refunds and discounts exceed the sales.

Product cost and the return credit. A row's product cost is the units shipped times the unit cost, less a credit for returned stock that can be sold again: the units returned times the unit cost times the product's return recovery. Because the recovery is at most 100%, the credit never exceeds what those units cost.

Contribution. Contribution is net revenue minus product cost minus direct costs. It comes before any shared cost, so the choice of allocation method never changes it.

Allocation. Each pool is spread over the sales rows by one method. Revenue spreads it by each row's net revenue, and a row with zero or negative net revenue takes no share. Units spreads it by units shipped minus units returned, never below zero. Activity spreads it by that pool's activity column. A row's exact share is the pool amount times its driver divided by the total driver.

To the cent. Each share is rounded down to the cent, and the cents left over go one at a time to the rows with the largest remainders, the lowest row ID first in a tie. Row IDs order as text, so R1 comes before R10 and R10 before R2. Every pool therefore adds up exactly, and every posted share is within one cent of its exact share.

A pool nobody drives. When a pool's driver total is zero it is reported as unallocated and never divided.

Profit. Allocated profit is contribution minus the allocated shares. Fully loaded profit also subtracts the unallocated pools, so at the company level it is contribution minus every pool. Changing an allocation method moves profit between rows and customers; the company total changes only when a pool becomes unallocated.

Margins. Contribution margin, allocated margin and fully loaded margin each divide by net revenue. A margin on zero or negative net revenue means nothing, so it is left blank.

Profit without the focus customer. This is the company's fully loaded profit minus the focus customer's contribution: what is left if its rows are removed and every shared cost pool stays the same size. A customer can show an allocated loss and still add to the company's profit. When its contribution is above zero, dropping it lowers company profit by that contribution, if the shared costs do stay.

What-if table. Rows are 11 price multipliers centred on yours and columns are 11 direct cost multipliers centred on yours, each one step apart, with quantities fixed. Each cell recalculates the focus customer's own sales rows at that price and direct cost, less its shared costs held as allocated in your own case, through the spreadsheet's own two-variable data table. The shares are not spread again at each cell, because that would make every calculation many times slower. Every price moves together, so revenue shares usually stay close, and on the worked examples the held shares give the same table as a full re-spread, to the cent. They are not always close: revenue is rounded to the cent on each row, so very small rows can change share sharply, and the difference is not limited to a cent. With two rows of gross sales $0.01 and $0.02 at no cost, a $1,000 revenue pool is allocated $333.33 and $666.67. At a price multiplier of 0.5 the two rows both round to $0.01, so a full re-spread would allocate $500 and $500. The first row's cell shows -$333.32 where a full re-spread would show -$499.99, a difference of $166.67. The middle cell equals the focus customer's allocated profit. When a step would take the lowest row or column below 0, the table is withheld with that reason and every other result still stands. A multiplier of 0 always withholds it.

Calculation sequence

The spreadsheet first checks every product, sales and pool row and every setting, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then posts each sales row's revenue and costs to the cent, spreads each pool with its leftover cents, and sums the rows by product, customer and channel in integer cents. The what-if table recalculates the focus customer's rows at its 121 combinations. A block of consistency checks, including that each pool adds up exactly, that the summaries add up to the company totals, that the steps from gross sales reach the profit figures, and that the table's middle cell equals the focus customer's allocated profit, runs before the status is returned.

Outputs and interpretation

The headline gives the company's contribution, its fully loaded profit and the focus customer's allocated profit. The result sections follow.

• Company: gross sales, discounts, refunds, net revenue, product cost, return credit, direct costs and contribution margin; the pools in total, allocated and unallocated; allocated profit and fully loaded margin; units shipped and returned; the number of sales rows and customers below zero after shared costs; and the steps from gross sales to fully loaded profit.
• Focus customer: its net revenue, contribution and margin, its allocated shared costs and allocated margin, and the company's profit without it, assuming every shared cost pool stays the same size.
• By customer, By product, By channel: units, net revenue, product cost, direct costs, contribution and margin, allocated shared costs, allocated profit and margin for each. Customers and channels appear in the order they first appear in the sales table; products in table order, including one with no sales.
• Shared cost pools: each pool's method, amount, driver total, cost per driver unit, allocated and unallocated amounts and the part that falls on the focus customer; and each sales row's share of each pool.
• Sales rows: each row's net revenue, costs, contribution, allocated shared costs and allocated profit.

The steps from gross sales to profit are drawn as signed bars that start at zero. They do not float like a waterfall chart, so read each bar on its own.

For nearby questions, the Price Volume Profit Scenario Simulator projects profit when demand responds to price, and the Quote Pricing & Deal Margin Optimizer prices one deal from its lines.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then CHECK, then OK.

• NOT VALID names the first problem, in this order: a product row (a blank row above a used row, a missing or malformed ID, a repeated ID, a unit cost or recovery outside its limits); no product; a sales row (a gap, a missing or malformed ID, an amount outside its limits, a repeated row ID, a product not in the products table); no sales row; more than 10 customers; more than 3 channels; a pool row (a gap, a missing or malformed ID, an amount outside its limits, a method not in the list, a repeated ID); a focus customer not in the sales table; a multiplier outside 0 to 5; and a table step outside its limits. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when sales rows lose money after shared costs, when a pool has no driver so it is not allocated, when a row has discounts and refunds above its gross sales, when a row returns more units than it ships, when activity is entered for a pool that does not use it, when the focus customer shows an allocated loss but a positive contribution, and when the what-if table is withheld. A warning never changes the status.

Assumptions and limitations

• Capacities are 10 products, 50 sales rows, 10 customers, 3 channels and 5 shared cost pools.
• One summary period: rows from different periods are simply different rows, and there is no dating, payment timing, tax or currency conversion. This is an accounting view, not a cash view.
• Customers and channels are the IDs typed on the sales rows; upper and lower case are different characters, and IDs use capital letters only.
• A price or direct cost multiplier applies to every row together, with quantities fixed. The calculator does not estimate how customers respond to price.
• An allocation is a way of sharing a cost, not a measure of what a customer causes. Different methods give different customer results for the same company total.
• Profit without the focus customer assumes no shared pool shrinks when the customer leaves. If a pool would shrink, the real effect is better than that line shows.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including which row takes a leftover cent.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A blank row between used rows, a repeated row ID, a product not in the products table, a focus customer not in the sales table and too many customers or channels are refused. When a table step would take the lowest row or column below 0, only the what-if table is withheld, with that reason.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and check a result against your own records, before using it to change a price or drop a customer.

Sources and further reading

Your sales, costs, returns, activity counts and cost pools come from the inputs; the calculator holds no market, industry or customer data, and every value is your own assumption. The sources below explain the ideas the model uses. They do not endorse this calculator.

• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 3.1 Explain Contribution Margin defines contribution margin as the amount by which a selling price exceeds variable cost, the amount left to cover fixed costs and profit; the calculator's contribution is net revenue less product cost and direct costs (retrieved 2026-11-03).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 6.3 Calculate Activity-Based Product Costs explains grouping overhead into cost pools and allocating each by a cost driver, the idea behind the shared cost pools and their Activity method (retrieved 2026-11-03).
• OpenStax, Contemporary Mathematics, 11.4 Apportionment Methods describes rounding shares down and giving what is left over to the largest remainders, the rule the calculator uses to post each pool to the cent (retrieved 2026-11-03).

This is an educational model of the sales summary you enter, not financial, accounting, pricing or legal advice. An allocated loss is not, by itself, a reason to drop a customer. The calculator is not certified financial reporting and does not guarantee a profit; check a result against your own records before acting on it.