LibreOffice Calc · Practical projects · Intermediate
Compare Supplier Quotes with Minimum Orders in LibreOffice Calc
Compare fictional offers using pack sizes, minimum quantities, shipping and changed-demand checks.
Documentation-reviewed procedure; independently calculated example; native verification pending · Editorial standards
A low unit price can hide an expensive minimum order. Build a one-product comparison that calculates what each supplier would charge to deliver enough units, respecting its pack size and minimum quantity.
Scope: free LibreOffice Calc desktop, using English function names documented in LibreOffice 26.8 Help. No extension, online service or account is needed. Examples use decimal points and semicolon separators; adapt these to your locale. No native application execution or screenshot is claimed.
Define comparable offers
In a blank sheet, enter Required units in A1 and 73 in B1. Quantities and pack sizes must be positive whole numbers. Each supplier sells the same fictional item of equivalent quality. One supplier must cover the entire requirement; do not split the order.
Enter this table in A4:E7. Prices are fictional currency units before tax. Shipping is a flat charge per order, with no threshold.
| Supplier | Unit price | Pack size | Minimum units | Shipping |
|---|---|---|---|---|
| Alder | 2.40 | 12 | 48 | 18 |
| Birch | 2.25 | 25 | 100 | 0 |
| Cedar | 2.55 | 10 | 20 | 8 |
Minimum quantity and pack size are different rules. A minimum of 50 with packs of 12 requires at least 60 units, not 50.
Calculate quantities before costs
Enter Order units, Extra units, Delivered cost, Cost per required unit in F4:I4. Enter:
- F5:
=CEILING(MAX($B$1;D5);C5) - G5:
=F5-$B$1 - H5:
=F5*B5+E5 - I5:
=H5/$B$1
Copy F5:I5 to F6:I7. Inspect F7: demand must remain $B$1, while minimum and pack references become D7 and C7. See locking references for the underlying copying technique.
MAX chooses the larger of demand and minimum; CEILING then rounds to a permitted pack multiple. This formula assumes positive demand and pack size. Do not enter zero, negative numbers or blanks to mean “unknown”. Confirm every price, minimum and shipping input is numeric before interpreting the comparison.
| Supplier | Order units | Extra units | Delivered cost | Cost per required unit, displayed to 2 decimals |
|---|---|---|---|---|
| Alder | 84 | 11 | 219.60 | 3.01 |
| Birch | 100 | 27 | 225.00 | 3.08 |
| Cedar | 80 | 7 | 212.00 | 2.90 |
Independently check Cedar: eight packs of ten cost 80 × 2.55 = 204, plus shipping 8 = 212. Cedar wins despite Birch’s lower unit price. The last column divides by required, not purchased, units: it allocates the whole cost to this requirement instead of treating extra stock as free.
Use number formatting to display costs to two decimals. Do not round the formulas just to match the display. Read cost alongside extra units: storage capacity and future use of leftovers are not modeled.
Test a changed decision and a pack boundary
Change B1 to 100. Expected order quantities, extra units and delivered costs:
- Alder: 108, 8, 277.20.
- Birch: 100, 0, 225.00.
- Cedar: 100, 0, 263.00.
Birch now wins. Restore B1 to 73 and verify the original results.
Next change Alder’s minimum D5 from 48 to 85, leaving demand at 73. F5 must become 96, G5 23, and H5 248.40. This proves the formula rounds after applying a minimum that is not a pack multiple. Restore D5 to 48.
A zero demand makes the last-column division invalid, and a zero pack size is not a usable quote. Stop and correct invalid or missing input instead of concealing it with an error-to-zero formula.
Save a reproducible decision
Save a new ODS file such as practice-supplier-comparison.ods. In nearby cells record the currency and assumptions above. Reopen the file, inspect F5 and H5 for formulas and repeat the 73 → 100 → 73 check yourself. This is a reader acceptance test, not a reported native test.
If results differ, inspect numeric inputs, the anchored demand reference, and whether shipping was added once rather than multiplied by quantity. For real offers, separately verify tax, quality, delivery date, currency and quantity-discount rules. This bounded model does not evaluate those factors. Keep ODS as the master; another file format needs its own compatibility check.
Sources and verification scope
Reviewed 21 October 2026 against LibreOffice’s CEILING documentation and MAX documentation. The scenario and arithmetic are original and independently recomputed. No workbook execution, save/reopen or screenshot was performed. See CEILING and MAX for function detail.