How Do I Build a Candle Raw-Material Reorder Sheet Showing Current Stock, Monthly Consumption, Safety Stock, Supplier Lead Time and Quantity to Purchase?

Nineteen columns, one status column you read every week

Every number a small candle business needs to reorder well already exists somewhere — on the shelf, in last month's sales, in your order history. The sheet puts them in one row per material and turns them into a decision. It has four groups of columns. Inputs you type: material, the SKUs that use it, stock on hand, stock already on order, monthly use, usual and worst lead time, and the pack sizes you can buy. Calculated columns: daily use, safety stock, reorder point, stock position and days of cover. Decision columns: a status that says ORDER or OK, an order-up-to level and the quantity to buy. Purchase columns: the real packs, the line price and whether the line is bought monthly or quarterly.

The design choice that makes it work is the status column. Once the formulas are in, you do not read the sheet line by line; you update the inputs (a five-minute stock count) and read one column. Lines marked ORDER go on this week's purchase order at the quantity the sheet gives; lines marked OK are left alone, however low they look by eye. That discipline is what stops both the panic order and the forgotten jar carton.

A note on the inputs that are yours alone. CSI does not publish lead times or delivery times, so the lead-time columns come from your own order and arrival dates. Monthly use comes from your sales or your forecast. The 1.2 peak factor in the safety-stock formula is an example — the ratio of your busiest recent month to an average one. Every figure in this page's worked example is a labelled example; replace them all.

The columns and their formulas

Set the sheet up once with these headers in row 1 and the formulas in row 2, then copy row 2 down for each material. Columns A to H are inputs. The formulas use spreadsheet syntax that works in Google Sheets and Excel; the logic is the same as the safety-stock post and the per-material reorder-point post.

The sheet, filled in for an eight-SKU range

The example business sells two lines. A premium line of 80 candles a month: 200 g in the White Matte Glass Jar, poured in Luxury Soy Wax Chunks, wicked with Cotton Braided Wicks. A value line of 120 a month: 180 g in the 200 ml Salsa Glass Jar (sold with a golden lug cap), poured in Natural Soy Pearl Wax, wicked with Eco Thin C1. Four oils run across both lines. Both fills are the reader's weighed figures (CSI publishes neither jar's fill); the wicks are the reader's burn-test choices (CSI publishes no wick-size chart); Natural Soy Pearl has no published fragrance ceiling, so 8% is a labelled working load. Monthly use includes 2% rejects, 3% pour loss on wax and oil and 1% breakage on glass.

Read the status column. 4 lines say ORDER: Luxury Soy Wax Chunks, Vanilla Bean, Raat Ki Rani (Night-Blooming Jasmine), White Matte Glass Jar. Notice the Salsa jars: only 60 on the shelf — about 15 days of the value line — but 100 already on order, so the position is 160 and the line says OK. Without the on-order column, that line would have been ordered twice. Notice too the Chunks line: 4.20 kg on hand is 8.1 days of cover against a reorder point of 4.33 kg, so it is ordered up to 19.80 kg, which the cheapest real packs turn into 3 × 5 kg + 1 × 1 kg.

This week's purchase order, straight from the sheet

The ORDER lines become one CSI order. The quantities are the sheet's column P rounded to the cheapest real in-stock packs, so the order covers each material to its order-up-to level — one review period plus its own lead time plus its safety stock. CSI subtotal ₹31,205.00, before labour, packaging, freight and GST.

The seven-column version for a first range

If nineteen columns is too much for a range of three scents, start with seven: material, stock on hand, on order, monthly use, lead time (worst), reorder point and status. Use a simple reorder point — monthly use ÷ 30 × (worst lead time + a labelled cushion of, say, 7 days) — and order one month's use whenever the status says ORDER. It is cruder than the full sheet, but it is the same discipline, and the extra columns can be added as the range grows.

Whichever version you use, add two columns as soon as you can: SKUs using it (so a material whose SKU count falls is visible before it becomes dead stock — the commonality post explains why) and policy (monthly or quarterly, from the return-on-cash test). Carried remainders — the extra 2 kg from a 10 kg + 5 kg wax buy, the 200 g from a 3 × 500 g oil buy — go straight into column C as stock; the 13 kg wax post shows where they come from.

Keeping the sheet honest

Three habits keep it accurate. Count on the same day every week, so stock and use line up. Recalculate monthly use whenever sales move noticeably or a season starts. And replace example lead times with your own as soon as you have three or four arrivals per material. A sheet with a wrong lead time is worse than no sheet, because it orders confidently at the wrong moment.

When a line says ORDER every single week, its order-up-to level is too low or its lead time is wrong. When a line has not said ORDER for three months, look at its SKU count and its sales — it may be on its way to dead stock. Both signals are visible in columns B, M and N without any extra work. For the minimum-pack lines (oils whose smallest size is bigger than a month's use), the MOQ post explains why they may sit at OK for a long time, and that is fine.

This page is the working tool of the procurement cluster. The 30-day purchase order and the consolidated monthly order feed its inputs; safety stock and reorder points are its formulas; the complete procurement system shows where it sits in the whole flow from BOM to CSI purchase order.

Frequently asked questions