Softwiro
Free Excel template

Biscuit factory in Excel (free): dough, lines, packing and scrap

Know the dough yield, where line time goes, and what a carton really costs after scrap and downtime.

6 min read

You run a small biscuit factory with three lines and each week you want to know how many kilos of dough went in and how many cartons came out, where time was lost and why, what a carton really costs, and whether raw and packaging materials will last. The free biscuit factory sheet answers that from tables you type: recipes, lines and a daily production log.

The problem it solves

A biscuit plant loses money in small things that repeat every shift: a batch that comes out lighter than expected, breakage at packing, a line that stops for forty minutes and the reason is forgotten. If you cost a carton from ingredient prices alone it looks cheaper than it is, because line time and scrap were never loaded onto it. The sheet links the recipe to each line-and-shift record so all of this turns into numbers.

How the sheet works, step by step

  1. In Settings type the shift hours and the target scrap, yield and availability.
  2. In Materials type raw and packaging materials with cost, stock and minimum.
  3. In Lines type each line's capacity in kg of dough per hour and its running cost per hour.
  4. In Products type pieces per pack, packs per carton, pack weight, batch weight and carton price.
  5. In Recipes type each ingredient of the batch in kilos, and in Packaging materials what one carton uses.
  6. In Receipts log purchases; stock rises by itself.
  7. In Production log add a row per line, shift and product: batches, packed cartons, broken kilos, downtime minutes and its reason.
  8. Read Lines and Summary for availability, performance, yield and cost.

What each sheet holds

  • Products: carton weight and piece count, standard batch and carton cost, then actual yield and carton cost.
  • Lines: availability, performance, yield, scrap and an OEE-style figure per line.
  • Production log: per row, dough kg, packed weight, pieces, yield, scrap, carton cost and an alert.
  • Materials: balance, status (available, order, negative) and the suggested order cost.
  • Summary: the indicators and six charts, including downtime reasons and what a carton's cost is made of.

Walk-through on the sample numbers

The figures below are sample data in the sheet, not a real factory. The example has 13 materials, 3 lines, 3 products and 18 log rows; the scrap limit is 3%, target yield 75% and target availability 85%:

  • Plain tea biscuit 70 g: a carton holds 48 packs of 10 pieces (480 pieces) and weighs 3.36 kg. A 200 kg dough batch costs 6,310 in ingredients and, at a standard 84% yield, gives 50 cartons: 126.20 of ingredients per carton, plus 17.84 packaging for a standard cost of 144.04 against a 185 price.
  • First log row (Line 1, 7 batches, 342 cartons): dough 1,400 kg and packed weight 1,149.12 kg, so yield is 82.1%, with 18 kg broken (1.3%). The line ran 440 of 480 minutes, availability 91.7%, against a theoretical 1,466.7 kg, performance 95.5%. The row costs 54,671.28, so a carton costs 159.86.
  • Alerts in the log: a Line 2 row with 75 minutes of downtime has 84.4% availability, under 85%. A Line 3 row with 25 kg broken out of 720 (3.5%) is over the 3% limit. A Line 3 row in early November has a 72% yield, under 75%.
  • All 18 rows: 18,340 kg of dough and 4,920 cartons, packed weight 15,217.08 kg for an 83.0% yield, and 322 kg scrap (1.8%). Availability is 92.0% and performance 85.0%, so the OEE-style figure is 76.8%.
  • Downtime: 690 minutes, mostly "Mechanical fault" at 315 minutes (45.7%), then "Changeover" at 195 (28.3%).
  • Cost: 835,037.70 in total, an average of 169.72 per carton. The actual tea carton costs 159.18 (a 14.0% margin against 22.1% at standard before line time), butter biscuit 138.45 and cookies 225.95. Output at selling price is 967,790, an approximate margin of 13.7%.
  • Materials: stock is worth 134,399.80, and two materials need ordering: flour at 1,110 against a minimum of 1,500 and vegetable shortening at 246 against 400, an order worth 18,908.

Why it helps

  • Dough yield: you see where weight is lost between mixing and the carton.
  • Downtime reason: the reasons are ranked so you start with the biggest.
  • Actual carton cost: it carries scrap and lost time, not just ingredients.
  • Consumption by formula: dough and packaging materials leave stock from the log itself.
  • Works in Excel 2016 and later, Google Sheets and LibreOffice, and holds 60 materials, 10 lines, 20 products and 500 log rows.

Common mistakes

  • A wrong batch weight: if the batch weight typed is not what you really mix, every yield is wrong, so weigh it.
  • Leaving the downtime reason blank: the sheet asks for it whenever minutes are typed, because the ranking depends on it.
  • Typing the machine's catalogue capacity: type what the line really achieves, or performance will always look low.
  • Forgetting packaging materials: unless film and cartons are logged, cost drops and their stock never falls.
  • Treating the OEE-style figure as the full standard: it is a tracking indicator from availability, performance and scrap; compare it with your own past weeks.

The sheet holds records and calculations only; recipes and food-safety requirements follow the competent authority's requirements. To follow a product through stages inside the plant see the production stage tracking sheet, and for a food plant with batches and shelf life see the food factory sheet.

Who it suits and when it is not enough

It suits a small plant with a few lines where the shift supervisor writes the log. With many lines and shifts, or when you want to schedule orders onto them by drag and drop and read OEE automatically, MESwiro assigns work orders to lines and shifts and works out OEE per line.

Next step

Open the sheet page and try the example or download the blank copy. Type the recipe of your best-selling product and the capacity of one line, then log a single shift and check its yield and carton 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: biscuit factory (1/3)Cheat sheet: biscuit factory (2/3)Cheat sheet: biscuit factory (3/3)
0 downloads · 3 pages

Questions this raises

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 the OEE-style figure calculated?

Availability = (shift minutes - downtime minutes) ÷ shift minutes. Performance = dough kg ÷ (run minutes ÷ 60 × line capacity). OEE-style = availability × performance × (1 - scrap share); it is a tracking indicator, not the full standard measure.

How is the cost per carton calculated?

Row cost = batch ingredients + packaging for the cartons + (run minutes ÷ 60 × the line's hourly cost). Cost per carton = row cost ÷ cartons packed, so the packed cartons carry the row's scrap and lost time.

Does the sheet set recipes or food-safety requirements?

No. It holds records and calculations only; recipes, specifications and food-safety requirements 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