# Food factory Excel (free): recipes, batches, expiry

> Free Excel sheet for a small food factory: raw materials and lots, recipes, production batches, expiry dates, finished stock, unit cost and sales.

Source: https://softwiro.com/blog/food-factory-production-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Food factory in Excel (free): recipes, batches, cost and shelf life

Know what a good unit really costs, which batch is close to expiry, and whether your raw materials cover the next runs.

15 November 2026 6 min read

## In short

- Raw materials are deducted by formula: recipe quantity per batch × the number of batches in each production run.
- Each batch gets an expiry date from the shelf life you set for the product, and finished stock shows days left and a near-expiry alert.
- Good-unit cost spreads the whole batch cost (materials, packaging, labour, overhead) over sellable units only.
- Dispatching an expired, unapproved or oversold batch raises an alert before it reaches a customer.
- Records and calculations only: shelf life and food-safety rules follow the competent authority's requirements.

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](https://softwiro.com/templates/food-factory-production) 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

1. 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.
2. In Raw materials type each material with its cost, opening stock and minimum.
3. In Products type each product: units in a standard batch, shelf life, packaging cost, labour hours and selling price.
4. In Recipes type one line per material with the quantity per batch; material cost appears in "Products".
5. In Receipts log each delivery with its lot number and expiry date; stock rises and expired or near-expiry lots show up.
6. 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.
7. In Sales log each dispatch with the batch it came from; the balance drops in Finished stock.
8. 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](https://softwiro.com/templates/batch-expiry-inventory); for costing a restaurant recipe see the [restaurant recipe costing sheet](https://softwiro.com/templates/restaurant-recipe-costing).

## 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](https://softwiro.com/systems/meswiro.html) breaks planned orders into components (MRP) and works out shortages against stock.

## Next step

Open the [sheet page](https://softwiro.com/templates/food-factory-production) 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.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: food factory (1/3)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-1.jpg)[![Cheat sheet: food factory (2/3)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-2.jpg)[![Cheat sheet: food factory (3/3)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/food-factory-production/en-3.jpg)

0 downloads · 3 pages

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Questions this raises

### How are raw materials deducted from stock?

The sheet multiplies the material quantity per batch (from Recipes) by the number of batches in each production run (from Batches) and totals it per material in the Consumed column. Balance = opening stock + receipts - consumed.

### How is the expiry date worked out?

Expiry date = production date + the product's shelf life in days, which you type in Products. The sheet sets no shelf life; enter it for your product and according to the competent authority's requirements.

### How is unit cost calculated?

Batch cost = materials for the batches + packaging for the actual output + labour and overhead by batch hours. Good-unit cost = batch cost ÷ sellable units, so good units carry the batch's scrap.

### Does the sheet set food-safety requirements?

No. It holds records and calculations only and sets no specifications, legal limits or storage conditions; those follow the competent authority's requirements.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
