Softwiro
Free Excel template

Biscuit factory sheet: dough, lines, packing and scrap

A free Excel sheet for a small biscuit factory. You enter the dough recipe for each product in kg per batch and the capacity of each line in kg per hour, then log every day, for each line and shift, the number of batches, cartons packed, kilograms of broken biscuits and minutes of stoppage with the reason. The sheet calculates dough yield (packed weight ÷ dough weight), availability, performance and an approximate OEE, deducts raw materials and packaging from stock, calculates the cost per carton, and shows which lines and products are losing yield or time.

Excel file (.xlsx) · 226 KB · 11 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

Example from the sheet

Example from the “سجل الإنتاج” tab (Arabic), with the numbers and formulas that are in the file.
التاريخالخطالمنتجكجم العجينالعائدالإتاحةتكلفة الكرتونة
46313L1P011,40082.1%91.7%159.86
46313L2P0290087.3%93.8%139.47
46313L3P0372076.4%88.5%227.96
46314L1P011,40083.5%94.8%157.84
46314L2P0290088.7%100.0%138.46
46315L1P031,08078.9%87.5%221.08
46315L2P0290086.0%84.4%140.06
46316L3P0372076.8%95.8%228.08

Example from the “سجل الإنتاج” tab (Arabic), with the numbers and formulas that are in the file.

What is inside

  • الإعدادات (Settings): Shift hours, targets for scrap, yield and availability, the first month in the summary, and the lists of shifts, stoppage reasons and units.
  • المواد (Materials): Each raw material or packaging material with its unit, cost, opening stock, minimum level and type, with received, consumed, stock, stock value, status and reorder cost beside it. Holds 60 materials.
  • الخطوط (Lines): Each line with its capacity in kg of dough per hour and its running cost per hour, with dough kg, running minutes, availability, performance, cartons, yield, scrap and approximate OEE beside it. Holds 10 lines.
  • المنتجات (Products): Each product with pieces per pack, packs per carton, pack weight, batch weight, standard yield and carton price, with batch cost, standard carton cost and margin beside it, then the actual yield and carton cost. Holds 20 products.
  • الوصفات (Recipes): One line per ingredient in a product's batch, in kg. The line cost, its share of batch weight and the amount actually consumed are shown. Holds 120 lines.
  • مواد التعبئة (Packaging materials): What one carton uses of film, cardboard, tape and so on. The cost per carton and the amount actually consumed are shown. Holds 60 lines.
  • الوارد (Receipts): The purchase log for raw and packaging materials: date, quantity, price and supplier. It raises the material's stock automatically. Holds 200 lines.
  • سجل الإنتاج (Production log): One line per line, shift and product: batches, cartons packed, scrap and stoppage minutes with the reason. Dough kg, yield, availability, performance, record cost, carton cost, OEE and an alert are calculated. Holds 500 lines.
  • الملخص (Summary): Production and materials indicators, tables by product, carton cost components, line, stoppage reason and month, with six charts.

The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.

What makes it different

The carton cost is not an estimate. The sheet links the dough recipe to the log of each line and shift, so you know dough yield, scrap and lost time and their reasons, and it charges materials, packaging and line running cost to the cartons actually packed.

How to use it

  1. In «الإعدادات» (Settings) enter shift hours, the target scrap limit, target yield and target availability, and the first month of the summary, and review the lists of shifts and stoppage reasons.
  2. In «المواد» (Materials) enter each raw material or packaging material with its unit, cost, opening stock, minimum level and type (raw or packaging).
  3. In «الخطوط» (Lines) enter each line with its capacity in kg of dough per hour and its running cost per hour (labor, energy and overheads).
  4. In «المنتجات» (Products) enter each product: pieces per pack, packs per carton, pack weight in grams, batch weight in kg, standard yield and carton price.
  5. In «الوصفات» (Recipes) enter a line for each ingredient in the product's batch, in kg, and in «مواد التعبئة» (Packaging materials) enter what one carton uses of film, cardboard and tape.
  6. Record each delivery in «الوارد» (Receipts), and the material's stock rises by itself.
  7. In «سجل الإنتاج» (Production log) enter a line for each line, shift and product: batches, cartons packed, kilograms of scrap and stoppage minutes with the reason.
  8. Open «الخطوط» (Lines) to see each line's availability, performance and yield, and «الملخص» (Summary) for the indicators and charts.

Before you start:

  • Dough kg = batches × batch weight. Packed weight = cartons × kg per carton (packs per carton × pack weight ÷ 1000). Yield = packed weight ÷ dough kg. It is below 100% because the oven drives off part of the dough's weight, and because of scrap.
  • Material consumption = recipe quantity per batch × number of batches. Packaging consumption = its quantity per carton × cartons packed. Material stock = opening stock + received − consumed.
  • The weight entered in the recipe is the ingredients purchased. The difference between it and the batch weight appears in «المنتجات» (Products) in the column «ماء وأخرى (كجم)» (Water and other, kg), and is assumed to be water and other items you do not track. If it turns negative, the recipe is heavier than the batch weight, so review it.
  • Availability = (planned shift minutes − stoppage minutes) ÷ shift minutes. Performance = dough kg ÷ (running minutes ÷ 60 × line capacity). Approximate OEE = availability × performance × (1 − scrap ratio). It is an estimate for tracking, not the full standard measurement.
  • Record cost = batch materials + carton packaging + (running minutes ÷ 60 × line cost per hour). Cost per carton = record cost ÷ cartons packed. Scrap that cannot be sold is borne by the packed cartons, because the whole cost is divided by the cartons.
  • The sheet is for records and calculations only and does not set valid recipes or food safety requirements; these are taken "according to the requirements of the competent authority".
  • It holds 60 materials, 10 lines, 20 products, 120 recipe lines, 60 packaging lines, 200 receipt lines and 500 daily production lines, and the summary shows the first 8 products, 6 lines and 6 months.

Excel file (.xlsx) · 226 KB · 11 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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: biscuit factory (1/3)Cheat sheet: biscuit factory (2/3)Cheat sheet: biscuit factory (3/3)
0 downloads · 3 pages

Still running this on Excel?

This sheet calculates requirements for the orders you type in yourself. In MESwiro, material requirements planning (MRP) breaks planned production orders down into their components and works out shortages against stock.

In this sheetIn MESwiro
You type in every order and every balance by hand.Material requirements planning (MRP) breaks planned production orders down into their components and calculates shortages against stock.
You ask suppliers for quotes by phone.It requests quotes from several suppliers and compares price, lead time and minimum order side by side.
Line scheduling in a sheet hides conflicts.It assigns work orders to lines and shifts by drag and drop, with a Gantt chart and a panel for unscheduled orders.
Efficiency and order cost are guesswork.It calculates OEE for each line automatically and shows the material, labor and machine cost of each order.

See MESwiro · Ask for a demo

Frequently asked questions

How is dough yield calculated?

Yield = packed weight ÷ dough kg. Packed weight = cartons × kg per carton (packs per carton × pack weight ÷ 1000), and dough kg = batches × batch weight. It is below 100% because the oven drives off part of the dough's weight and because of scrap.

How are availability and approximate OEE calculated?

Availability = (shift minutes − stoppage minutes) ÷ shift minutes. Performance = dough kg ÷ (running minutes ÷ 60 × line capacity). Approximate OEE = availability × performance × (1 − scrap ratio). It is a tracking indicator, not the full standard OEE measurement.

How is the cost per carton calculated?

Record cost = batch materials + packaging materials for the cartons + (running minutes ÷ 60 × line cost per hour). Cost per carton = record cost ÷ cartons packed, so the packed output carries the record's scrap and lost time.

Does the sheet set valid recipes or food safety requirements?

No. The sheet is for records and calculations only. Recipes, specifications and food safety requirements are taken "according to the requirements of the competent authority" in your area.

Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html

All the templates in this group: Factory management sheets: production, cost and quality

More free templates for: Factories and production

More free templates

All templates · Free tools