You run a small food factory (jam, halawa and juice, say) and every week you want three answers: what a good unit really costs once scrap and rejects are counted, which batch is close to expiry and still has stock, and whether the raw materials you hold will cover the next runs. The free food factory sheet answers them from tables you type: raw materials, recipes, production batches and sales.
The problem it solves
In a food plant, money leaks where the purchase ledger does not look: a batch that came out under what the recipe promised, a rejected batch that still used all its materials, finished stock ageing at the back of the shelf, a raw-material lot that expired before anyone used it. Costing on paper (materials ÷ units) looks better than reality. The sheet links recipe, batch and sale, so consumption, expiry date and real cost come from the same data.
How the sheet works, step by step
- In Settings type the cost of a labour hour, its share of overhead, today's date, the warning days before expiry and the scrap limit.
- In Raw materials type each material with its cost, opening stock and minimum.
- In Products type each product: units in a standard batch, shelf life, packaging cost, labour hours and selling price.
- In Recipes type one line per material with the quantity per batch; material cost appears in "Products".
- In Receipts log each delivery with its lot number and expiry date; stock rises and expired or near-expiry lots show up.
- In Batches log the batch number, date, product, number of dough batches, actual output, scrap % and QC result. Grey columns work out the expiry date, sellable units and cost.
- In Sales log each dispatch with the batch it came from; the balance drops in Finished stock.
- Open Summary for the indicators and charts.
What each sheet holds
- Raw materials: balance, value, status (available, order, negative) and how many lots are expired or near expiry.
- Products: batch cost, standard unit cost and margin, then actual output, scrap and actual good-unit cost.
- Batches: expected vs actual, expiry date, material, packaging and labour cost, and a scrap or QC alert.
- Finished stock: each batch's balance, days left, status and value at cost.
- Sales: total, cost and profit, with an alert when you dispatch an expired, unapproved or oversold batch.
Walk-through on the sample numbers
The figures below are sample data in the sheet, not a real factory. The example has 7 raw materials, 3 products, 10 batches and 14 sales lines; a labour hour costs 40 EGP with 25 EGP overhead, the scrap limit is 4%, and the date in Settings is 2026-11-15:
- Apricot jam 400 g: batch materials cost 6,404 (apricot 3,800, sugar 2,100, pectin 456, citric acid 48), labour 320 and overhead 200, so a batch costs 6,924. Over 400 units plus a 6.50 pack that is a standard unit cost of 23.81 against a 38 price (37.3% margin).
- Batch B-2601 (3 batches, 1,200 units, 1.5% scrap): scrap 18, sellable 1,182. Jam shelf life is 540 days in the sample (a number we typed), so it expires 2028-03-28. It cost 28,572, so a good unit costs 24.17.
- Batch B-2606 (tahini halawa): expected 900, actual 880, and 6% scrap (53 units) is over the 4% limit, so "Scrap above target" shows. Its good unit costs 52.36 against a 48.27 standard.
- Batch B-2607 (halawa): QC result "Rejected", so its 28,960 cost has no sellable units. Actual halawa cost per good unit jumps to 71.53 and its margin falls to 4.6% instead of 35.6% on paper.
- Batch B-2610 (jam): "Pending", so nothing is sellable yet. 80 units were dispatched on INV-5013 and the alert "Batch did not pass QC" appears.
- Stock: chilled mango juice has a 35-day shelf life in the sample. B-2603 expired on 2026-11-12 with 60 units left (expired); B-2605 expires on 2026-11-24, 9 days away, with 891 units (near expiry). Together they are worth 9,600.21 at cost out of 94,211.77 for all finished goods.
- Materials: lot H-12 of halawa extract expired on 2026-11-10 and mango pulp lot M-951 expires in 20 days. Apricot stock of 100 is under its minimum of 150 (50 kg needed), and citric acid is 22.6 against a minimum of 25.
- Totals: 26 dough batches made 15,030 units, scrap 278 (1.8%), sellable 13,386. Sales are 212,070 at a cost of 142,092.50, a profit of 69,977.50 (33.0% margin); the biggest customer is Al-Amal supermarket at 66,500.
Why it helps
- Consumption by formula: change a recipe line once and cost and consumption change in every batch.
- An expiry date per batch: you know what to sell first before it expires.
- Good-unit cost: the effect of scrap and rejects on margin is visible.
- Alerts at dispatch: you do not sell an expired or unapproved batch without the sheet warning you.
- Works in Excel 2016 and later, Google Sheets and LibreOffice, and holds 60 materials, 20 products, 200 batches and 500 sales lines.
Common mistakes
- An incomplete recipe: a missing material means lower cost than reality and a balance that never drops.
- Not updating today's date: days left depend on the cell in "Settings", so type today's date whenever you open the sheet.
- Leaving the QC result blank: the sheet counts sellable units only for a "Passed" batch.
- Forgetting the lot status: switch it to "Used" once a lot is finished so it stops alerting.
- Treating the sheet as an authority: it holds records and calculations only; shelf life, storage and food-safety rules follow the competent authority's requirements.
If you only need stock with batches and expiry dates, see the batch and expiry inventory sheet; for costing a restaurant recipe see the restaurant recipe costing sheet.
Who it suits and when it is not enough
It suits a small plant with dozens of materials and a few products, where one person does the logging. With several lines and shifts, or when you want planned orders broken into requirements automatically and supplier quotes compared, MESwiro breaks planned orders into components (MRP) and works out shortages against stock.
Next step
Open the sheet page and try the example or download the blank copy. Type one product's recipe and materials, log your latest batch, and check its expiry date and unit cost.





