Industry Use Cases

How to Compare Supplier Quotes by Case Pack and Unit Cost in Excel

One supplier quoted $42 a case and the other quoted $54. The $42 case looks cheaper until you check how many pieces are in it. The minimum buy changes the cash decision again.

Compare delivered cost per equivalent unit, then apply case rounding, minimum order quantity, and freight at the actual purchase quantity. Keep time to receive and comparable product specs alongside price.

Which quote is cheaper for 200 units?

Both suppliers quote the same item. The actual requirement is 200 pieces. Each supplier ships whole cases.

QuoteCase costUnits per caseMinimumActual casesFreightOrder total
A$421210 cases17$40$754
B$542412 cases12$55$703

Quote A supplies 204 pieces at about $3.70 delivered per piece. Quote B supplies 288 at about $2.44. B costs $51 less in cash for this order and leaves 88 pieces above the requirement. That extra inventory is part of the buy decision.

Copy the quote inputs into Excel

Create a sheet named Quote Review and paste this tab-separated sample into A1. All prices are USD for the same approved item specification. Minimums are in whole cases. Freight is fixed per order at the quantities shown; this exercise excludes tax, duty, storage costs, price breaks and additional order multiples.

Quote	Case cost USD	Units per case	Minimum cases	Freight USD	Required units
A	42	12	10	40	200
B	54	24	12	55	200

Use G1:L1 for Actual cases, Pieces purchased, Cash outlay USD, Delivered cost per purchased piece, Excess pieces and Cash per required piece. Enter the following formulas in row 2, then fill down to row 3. They use English Excel names and comma argument separators; some regional installations use semicolons between arguments.

G2: =MAX(D2,CEILING.MATH(F2/C2))
H2: =G2*C2
I2: =G2*B2+E2
J2: =I2/H2
K2: =H2-F2
L2: =I2/F2

CEILING.MATH rounds the positive case requirement up to a whole case; MAX then applies the minimum. This formula assumes a positive requirement and pack size. Blank or zero pack sizes and missing minimums or freight need review before calculation. A confirmed zero freight charge is different from an unknown charge.

Trace the $51 cash difference to the source rows

Quote Review!B2:F2 gives Quote A’s terms. The 200-piece requirement divided by 12 needs 17 cases, above its 10-case minimum. G2 = 17, H2 = 204 and I2 = 17 × $42 + $40 = $754. K2 = 4 extra pieces.

Quote Review!B3:F3 gives Quote B’s terms. Nine cases would cover 200 pieces, but its 12-case minimum controls the order. G3 = 12, H3 = 288 and I3 = 12 × $54 + $55 = $703. K3 = 88 extra pieces. The $51 cash difference is I2 minus I3.

J2 and J3 divide the bill by all pieces purchased: about $3.70 and $2.44 respectively. L2 and L3 instead divide cash by the 200 pieces currently required: $3.770 and $3.515. Label these denominators. Cash per required piece describes this buying decision; it is not an inventory valuation policy or an assertion that surplus has no value.

Change the need to 120 pieces

In a copy of the exercise, change F2 and F3 to 120 and keep the quoted terms unchanged. Quote A now needs 10 cases: 120 pieces, $460 cash and no excess. Quote B still requires 12 cases: 288 pieces, $703 cash and 168 extra pieces. A now uses $243 less cash. Restore both requirement cells to 200 after the check.

This is why a lower per-piece rate does not automatically mean the smaller cash commitment. Confirm the extra stock can be used, the delivery date works and both quotes remain valid before choosing a supplier. If freight changes with order size or a price break applies, update those terms and rerun the comparison.

Normalize the unit before comparing price

Use the item master and current supplier quotes. Match the same spec, usable quantity, and currency. Convert packs to the same base unit and confirm whether the quoted unit is a case, inner pack, or each.

Model the actual purchase

Divide required units by pack size and round up to complete cases. Apply minimums and any order multiples before pricing. Add freight and applicable charges on the same basis. Review excess quantity, timing, and storage constraints with the buyer.

Give the buyer the quote comparison they can defend

Keep vendor quote references and price breaks where applicable. If freight is shared with other items, state how you allocated it.

Compare the supplier quotes at 200 required units. Show actual order quantity, delivered cost per unit, total cash outlay, excess units and the source rows.

Confirm the quote remains valid and the extra stock will be useful before committing to the larger pack.

NetSuite multiple units of measure · Dynamics 365 requests for quotation

The figures and identifiers are synthetic. Both quantity scenarios and their source references were checked independently. Native Excel recalculation was not performed. A live Jolene.pro result could not be verified.

Try this with your own spreadsheet at Jolene.pro. Follow Jolene.pro on LinkedIn for more spreadsheet guides.

Formula reference: Microsoft CEILING.MATH documentation. Related checks: match PO, receipt and invoice quantities · import supplier amounts with decimal commas · Workbook Studies.

1 comment

Leave a comment