How Do I Convert a 1,000-Candle Customer Order into an Exact Procurement Sheet Showing Required Quantity, Buffer Quantity, Pack Size and Total Quantity to Purchase?

The finished sheet first

Here is the complete sheet for the example order: 1,000 saleable candles of 200 g in the White Matte Glass Jar (fill not published, so 200 g is illustrative — weigh a test pour), Luxury Soy Wax Chunks with Blue Ocean at 8%, Eco Candle Wicks Thin C1, one sticker per wick. The allowances are labelled working assumptions — 6 pilot candles, 3% rejects, 4% pour loss on wax and oil, 1% damaged wicks, 2% glass breakage — and each one sits in exactly one column of exactly one row. Quantities are in grams or units; prices are the cheapest listed, in-stock CSI packs.

Read the sheet across, not down. Required is the saleable order × the per-candle quantity, with no allowance at all: 1,000 × 184 g = 184,000 g of wax. Buffer qty is everything you add on top, in the same unit. Total need is the sum. Pack sizes listed is what you can actually buy. Qty to purchase — packs, quantity and rupees — is the only column that goes to the supplier. The leftover column is there so the overshoot is visible rather than discovered later.

Column 1: required quantity

The required column is pure arithmetic from the customer's order and your candle formula, with no allowances. CSI's convention is that fragrance load is a share of the finished candle: oil = weight × load, wax = weight × (1 − load). So a 200 g candle at 8% is 16 g of oil and 184 g of wax, and the order requires 16,000 g of oil and 184,000 g of wax. Counted items are one per candle: 1,000 wicks, 1,000 stickers, 1,000 jars. If the order has several fragrances or containers, write one row per variation here and combine on the purchase columns — the BOM-structure post shows how.

Column 2: buffer quantity — each allowance on its own material

The buffer column is where most sheets go wrong, because it is tempting to put one percentage on every row. This series uses a fixed order of allowances, each applied only to the materials it describes. First, pilot and test candles, counted as whole candles. Second, rejects, by dividing the candle count by (1 − reject rate) — also whole candles, so every material. Third, pour loss, on wax and oil only. Fourth, breakage, on glass only. Fifth, rounding to packs, which happens in the purchase columns, not here.

With these rates the order pours 1,038 candles (1,000 + 6 pilot, ÷ 0.97). Wax and oil carry those candles plus 4%; wicks carry them plus 1% damaged; jars carry them ÷ 0.98. Every rate is the reader's to change. The siblings own each allowance in detail: pilot, test, reject and replacement, glass breakage, wick allowances, colour trials and a deadline remake reserve — and the buffer-logic post explains why a weighed material and a breakable one take different arithmetic.

Columns 3 and 4: total need and the packs you can actually buy

Total need is required + buffer. The pack column then lists every in-stock size, and the purchase column picks the cheapest combination that covers the need. That last step has more judgement in it than it looks, because CSI's per-unit prices are not always lowest at the largest pack. For Luxury Soy Wax Chunks, the 500 g bag is ₹598.00 a kilo, the 1 kg and 5 kg bags ₹599.00, and the 10 kg bag ₹599.90. So 198.63 kg of wax costs:

The sheet uses the 5 kg route: ₹199.00 dearer than hundreds of 500 g bags, ₹779.00 cheaper than all 10 kg bags. Oils behave similarly: Blue Ocean is ₹4.94–₹4.96 a gram from 50 g to 1 kg, so the kilos carry the row and the smallest packs top it up. Eco wicks are ₹5.90 each at every pack; White Matte jars are ₹70.80 in 5s and 10s. On CSI's listed catalogue, per-gram price breaks are rare — the reliable step is from 15 g to 50 g/100 g on oils, and Natural Soy Pearl is the only soy wax that gets cheaper from 5 kg. Larger quantities are quoted on WhatsApp; no discount figure is published.

The blank sheet to copy

Copy this into any spreadsheet. The formula column uses ordinary spreadsheet notation for row 2; fill the rate cells with your own figures and copy the formulas down. Nothing in it is specific to CSI except the pack sizes you type into column F.

The buy-when column: one sheet, two orders

A sheet is not one purchase. On this order the wax, jars and one sheet of stickers go now — they are fixed by the brief and shared by every outcome of the pilot. The fragrance kilos wait for the client's written approval if the scent is new, and the 1,000-pack of wicks waits for the pilot candles to finish a full-life burn. Splitting the order this way costs nothing but a second delivery, and it means a failed pilot or a changed fragrance strands at most the pilot materials, not ₹91,870.44 of oil and wicks.

The CSI products on this sheet

Which post in the cluster owns which column

This page is the capstone of the 500–1,000 candle cluster. Every earlier post fills one part of this sheet in depth; the table maps them to the column they own.

Frequently asked questions