Cost-to-Serve & Minimum Profitable Order Calculator

Purpose and model boundary

This calculator shows what an order costs to serve and what it earns. A seller sells one product at one list price to up to 5 customer segments, and each segment has its own price discount. A table of up to 20 rows says how many orders of each size each segment placed in one period. Each order carries the product cost of the units kept, up to 10 order activities, a carrier cost from up to 10 shipping bands, a payment fee, and returns. Up to 3 order policies differ in their shipping terms and in what happens below a minimum order.

One Calculate fills every result section of that one model. It posts the period of the policy you choose to apply, by segment. It evaluates every order size from 1 to the largest you set, for one segment, and finds the break-even size, the smallest profitable size and every profitable range. When you enter a target contribution per order, it finds the size for the target; with no target, that section stays empty. It compares every policy on the same orders. An 11 by 11 what-if table of product cost against delivery cost is calculated with them.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. Every value is your own assumption. The calculator does not say how customers respond to a minimum order, a fee or free shipping, it is not certified financial reporting, and it does not guarantee a profit.

Inputs and units

• Product and price: the list price per unit, above 0 and up to 1,000,000, and the product cost per unit, from 0 to 1,000,000. Handling and delivery do not belong in the product cost; enter them as activities and bands.
• Customer segments, up to 5 rows, filled from the top without a blank row between used rows: a segment ID, which is a capital letter and then up to 15 capital letters or digits, each ID used once, and a price discount from 0% to 90% (0% means the list price). At least one segment is required.
• Orders in the period, up to 20 rows, filled from the top. Rows can be typed or pasted from a spreadsheet. Each row has a segment ID from the segments table, an order size from 1 to 10,000 units, and the number of orders of that size, from 0 to 10,000,000. A segment and size appear only once. Sizes above the largest size searched are allowed: the period totals include them and the search does not. At least one row is required.
• Order activities, up to 10 rows, which may be empty: an activity ID, how it is charged (per order, per unit or per shipment), the rate from 0 to 100,000, and a segment if it applies to only one. A blank segment means every segment.
• Shipping: whether the bands go by weight or by order value; the weight per unit, from 0 to 10,000, in the same unit as the bands (asked only for weight bands); and the units per shipment, from 1 to 10,000 (blank means the whole order ships as one shipment). The bands, up to 10 rows, each have the start of the band and the carrier cost per shipment, from 0 to 100,000. The first band starts at exactly 0, and each next start is higher. At least one band is required.
• Payment and returns: the payment fee as a percent of the amount collected, from 0% to 20%, and per order, from 0 to 100; the share of units returned, from 0% to 100%; and the handling cost per returned unit, from 0 to 10,000.
• Order policies, up to 3 rows. Each has a policy ID, in the same form as a segment ID; a shipping charge per order, from 0 to 10,000; and optionally an order value from which shipping is free, a minimum order from 1 to 10,000 units, what happens below the minimum (Reject or Surcharge), and the small-order surcharge, from 0 to 10,000. A minimum needs a rule, a rule needs a minimum, and the Surcharge rule needs a surcharge. At least one policy is required.
• The policy to apply and the segment to search are chosen from the IDs typed in their tables. The largest order size to search is from 1 to 1,000.
• What-if table: a product cost multiplier and a delivery cost multiplier, each from 0 to 5 (1 unless you change it), applied everywhere before anything is calculated, and the spacing of the table's rows, above 0 up to 0.2, and columns, above 0 up to 1.
• Optional, under "Optional: target, limit and overhead":
  • the target contribution per order, in whole cents (blank means no target search);
  • the most orders a policy may turn away, from 0% to 100% (blank means no limit);
  • the fixed overhead for the period, from 0 to 1,000,000,000 (blank means 0).

Money is in dollars. Percentages are shares of a price, of units or of orders.

Governing relationships

One order. The net price is the list price times (1 minus the segment's discount). The item value is the order size times the net price. The seller collects the item value, the shipping charge and any surcharge, and refunds the returned share of the item value. Returned units go back into stock, so product cost is charged only on the units kept. The contribution of one order is:

item value − refunds + shipping charged + surcharge − product cost − activities − carrier cost − payment fee − return handling.

Quantities are expected values, so the share of a unit returned can be a fraction.

Shipping charge and free shipping. The shipping charge is per order, never per shipment. It is waived when the item value before refunds is at or above the free-shipping threshold. Free shipping is a step in revenue, not a saving at the carrier: the carrier still charges for the shipment.

Shipments and carrier bands. With units per shipment entered, an order of 41 with 40 per shipment ships as 2 shipments, one full and one with 1 unit, and each pays the band of its own weight or value. With it blank, the whole order is one shipment. A band runs from its start up to, but not including, the next band's start, so a shipment exactly at a start is in that band. The delivery multiplier multiplies every carrier cost.

Activities. A per order activity is charged once on an order, a per unit activity on every unit and a per shipment activity on every shipment. An activity limited to one segment applies only to that segment's orders.

Payment fee and returns. The payment fee is charged on the item value, shipping charge and surcharge of each order, plus the fixed fee, and it is not refunded on returns. Returned units cost the handling rate each.

Minimum orders. Below a policy's minimum, a Surcharge policy adds the surcharge to the order, and a Reject policy turns the order away. A turned-away order adds nothing to the period's results; what it would have contributed is shown separately, and negative means it was losing money.

Period. For each row of the orders table, each line (item value, refunds, shipping, surcharge, product cost, activities, carrier, payment fee, return handling) is multiplied by the number of orders and rounded once to the cent, half away from zero. Every total is a sum of posted lines. Contribution per order is the period contribution divided by the orders served. Margin is contribution divided by net revenue, and shipping subsidy is carrier cost less shipping charged. Period profit is contribution less the fixed overhead.

Order-size search. Every whole order size from 1 to the largest you set is evaluated for the segment to search, under the policy to apply. Each contribution per order is rounded to 8 decimals and then to the cent. The policy's Reject rule is ignored in the search, with any surcharge kept, so the search can tell you whether a minimum is worth setting. Break-even is the smallest size that earns zero or more, and the smallest profitable size is the first that earns more than zero. A profitable range is a run of consecutive profitable sizes. Sizes between ranges lose money. The size from which every order pays is the start of the last range, when that range reaches the largest size searched. Because every size is evaluated, every fee, band and threshold step is seen, and no size is skipped.

Size for the target. Only when you enter a target. The search rows above are read again: the target size is the smallest that earns at least the target, and the page also shows the order value there, the size from which every larger size meets the target, and how many sizes meet it.

Policy comparison. Every policy is evaluated on the same orders, with the same rounding. A policy can be chosen when it turns away no larger a share of orders than your limit. The best is the highest period contribution among those, and a tie goes to fewer orders turned away, then to the first row. No policy changes customer behaviour: a minimum order does not make customers order more, turned-away orders are lost, and a surcharge is assumed to lose no order.

What-if table. Rows are 11 product cost multipliers centred on yours, one row step apart; columns are 11 delivery cost multipliers centred on yours, one column step apart. Each cell is the period contribution recalculated under the policy to apply through the spreadsheet's own two-variable data table. The middle cell is your own plan. 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.

Calculation sequence

The spreadsheet first checks every segment, order row, activity, band and policy and every setting, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then works out each segment's net price and activity rates, evaluates each order row under each policy and posts its lines to the cent, and adds the totals by segment. It evaluates every order size for the segment to search and finds the sizes and ranges, reads the target when one is entered, and compares the policies. The what-if table recalculates the period at its 121 combinations. A block of consistency checks, including that the posted lines add up to the contribution, that the segments add up to the totals, and that the table's middle cell equals the contribution, runs before the status is returned.

Outputs and interpretation

The headline gives the period contribution, the smallest profitable order size and the best policy. The result sections follow.

• Period under the policy to apply: orders in the period, served and turned away, units served, each revenue and cost line, the margin, the contribution per order, the shipping subsidy, the contribution of turned-away orders, the overhead and the period profit; with the per-order cost bridge for the average served order, and the results by segment.
• Order-size search: the net price, the break-even size and what it earns, what the smallest profitable size earns and its order value, the size from which every order pays, how many profitable ranges there are and the first three, and the size that earns the most; with a table for the first 100 sizes. A component shown in that table can differ from its contribution by a cent of display rounding.
• Size for the target: the target size, its order value and contribution, the size from which every larger size meets the target, and how many sizes meet it. With no target entered this section is empty and says so.
• Policy comparison: the best policy's contribution, how much more it earns than the policy to apply, how many policies can be chosen, and every policy's orders served and turned away, share turned away, net revenue, contribution, contribution of turned-away orders, shipping subsidy and whether it can be chosen.

For nearby questions, the Quote Pricing & Deal Margin Optimizer prices one deal, the Price Volume Profit Scenario Simulator projects profit over months when demand responds to price, and the Freight Cost Calculator looks at the cost of one shipment.

Validation and status logic

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

• NOT VALID names the first problem: a segment (a blank row above a used row, a missing, malformed or repeated ID, or a discount outside its limits); no segment; an order row (a gap, a segment not in the table, a size or order count that is not a whole number in its limits, or a segment and size already entered); no order row; an activity (a gap, a missing, malformed or repeated ID, a charge that is not on the list, a rate outside its limits, or a segment not in the table); a band (a gap, a value outside its limits, a first band not at 0, or a start not above the one before it); no band; a policy (a gap, a missing, malformed or repeated ID, a value outside its limits, a minimum without a rule, a rule without a minimum, or a Surcharge rule without a surcharge); no policy; or a setting outside its limits.
• INFEASIBLE means every policy turns away more orders than the limit you entered. The period, the search and the target stay valid and shown, the best policy is withheld, and the policy table still shows every policy as one that cannot be chosen.
• NO SOLUTION FOUND means no order size from 1 to the largest searched is profitable, or a target is entered and no size meets it. Because every whole size was evaluated, within that range this is a proven result. The period and the comparison stay valid and shown, with the size that earns the most.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when the profitable sizes are not continuous, when there are more than three ranges, when free shipping lowers the contribution at its threshold, when the policy to apply turns orders away, when another policy earns more on the same orders, when policies tie, when some orders are larger than the largest size searched, when a segment has no orders, when no order is served, when every carrier cost is 0 so the delivery multiplier has no effect, and when the what-if table is withheld.

Assumptions and limitations

• Capacities are 5 segments, 20 order rows, 10 activities, 10 bands and 3 policies; the search reaches at most 1,000 units, and the size table and first chart show the first 100 sizes.
• One product and one list price; a segment differs by its discount and by any activity limited to it. Payment fees and returns apply to every segment alike.
• The orders are one period's orders, with no months or dates.
• A policy changes shipping terms and what happens below a minimum, and nothing else. The orders stay the same under every policy: the calculator does not estimate how many customers leave, order more or combine orders.
• A shipment's band is read from its own weight or value. Carrier costs are per shipment, with no surcharges by distance or zone beyond the bands you enter.
• Returned units are refunded at the net price and restocked at product cost. Payment fees and shipping are not refunded.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including a tie or a reading that no order size makes a profit.
• From about $10 trillion to $90 trillion, a line can be off by up to 5 cents and a total by more, and the best policy can change on a difference that small. Amounts within two cents below $10 trillion may show as $10 trillion.

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 ID, an order row or activity naming a segment not in the table, bands that do not start at 0 or do not rise, and a minimum and rule that do not go together are refused by the workbook. With no target the target outputs are left blank. When a table step would take the lowest row or column of the what-if table below 0, only the table is withheld.

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 costs and terms, before using it to set an order policy.

Sources and further reading

Your price, discounts, orders, activity rates, carrier bands, fees, returns and policies come from the inputs; the calculator holds no market, carrier 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 an order's net revenue less the costs that order causes (retrieved 2026-11-03).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 3.2 Calculate a Break-Even Point in Units and Dollars explains the break-even point as the level at which costs are recovered; the calculator finds it by evaluating every whole order size rather than by a formula, because fees and carrier bands make the cost of an order move in steps (retrieved 2026-11-03).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 6.4 Compare and Contrast Traditional and Activity-Based Costing Systems describes assigning costs by the activities that drive them, the idea behind charging an order activity per order, per unit or per shipment (retrieved 2026-11-03).
• Microsoft Support, Calculate multiple results by using a data table describes the two-variable data table the spreadsheet uses for the 11 by 11 what-if table (retrieved 2026-11-02).

This is an educational model of the orders you enter, not financial, accounting, pricing or legal advice. It does not estimate how customers respond to a minimum order, a fee or free shipping, it is not certified financial reporting, and it does not guarantee a profit; check a result against your own costs and terms before using it.