# Electronics factory Excel (free): BOM & quality

> Free Excel sheet for a small electronics plant: components, BOM, production orders, QC results, yield, good-unit cost and component shortages. No sign-up.

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

---

Free Excel template

# Electronics factory in Excel (free): BOM, production and quality

Know whether components cover the planned orders, how many units came out good, which defect repeats, and what a good unit costs.

15 November 2026 6 min read

## In short

- Components are deducted by formula: BOM quantity per unit × actual quantity of each production order.
- Planned orders are checked against stock: the sheet lists each shortage, the suggested quantity and its cost.
- QC logs passed, rework, rejected and the defect type; yield = good units ÷ units made.
- Unit cost = BOM materials + labour hours × hourly cost + labour hours × overhead share, and the good-unit cost carries the rejects.

You run a small plant that assembles electronic products (power banks, chargers and smart bulbs, say) and want to know before an order starts whether the components are enough, and after it ends how many units came out good, which defect repeats most, and what a good unit really cost. The free [electronics factory sheet](https://softwiro.com/templates/electronics-factory) answers this from three tables you type, components, a bill of materials (BOM) and production orders, plus a quality-check log.

## The problem it solves

In an assembly plant an order lives or dies at the missing component. You start the line, find the charging board is short, the workers stand idle and you buy at a rush price. After delivery you hear "300 produced" without knowing how many are good, and you cost it as if every unit sold. The sheet works out consumption from the BOM, compares need with stock, and spreads each order's cost over the good units only.

## How the sheet works, step by step

1. In الإعدادات (Settings) write the labour hour cost, the overhead per hour and the target yield.
2. In المكونات (Components) write each component with its cost, opening stock and minimum, and in المنتجات (Products) each product with labour hours per unit and its price.
3. In BOM write a row for each component in the product with its quantity per unit; the material cost per unit appears.
4. Log purchases in الوارد (Receipts) and the component balance rises by itself.
5. In أوامر الإنتاج (Production orders) write the order with date, product, planned quantity and status, and the actual quantity as work advances. Components are deducted as BOM × actual quantity.
6. In فحص الجودة (QC) log first-pass passes, rework, rejects and the defect type, and the units that passed after rework.
7. Open النواقص (Shortages) to see what to buy before the planned orders start, and الملخص (Summary) for yield, cost and defects.

## What is in each sheet

- المنتجات (Products): material, labour and overhead cost per unit and its margin, actual output, yield and good-unit cost.
- المكونات (Components): balance, requirement for planned orders, shortage, suggested quantity and status (available, order, short or negative).
- BOM: each line's cost, consumed and required quantity, and an alert for an unknown code.
- أوامر الإنتاج (Orders): good units, first-pass and final yield, order cost, and an alert when yield falls under target.
- فحص الجودة (QC): test results by defect type. الوارد (Receipts): purchases. النواقص (Shortages): the buying list. الملخص (Summary): numbers and charts.

## Walk-through on the sample numbers

The numbers below are sample data inside the sheet, not a real plant. The example has 15 components, 3 products, 18 BOM lines and 8 production orders, with labour at 45 per hour, overhead at 30 per hour and a 95% target yield:

- Power bank 10000: materials 276 (two batteries at 190, a board, a casing, ports and a box), labour 22.50 for half an hour and overhead 15, so a unit costs 313.50 and sells at 390.
- 20 W charger: cost 99.50, price 145. Smart bulb: cost 97.05, price 135.
- Order PO-101 (300 power banks): 287 passed first time, 8 sent to rework and 5 rejected; 8 passed after rework, so 295 are good and final yield is 98.3%. The order cost 94,050, so a good unit costs 318.81.
- Order PO-105 (300 smart bulbs): 250 passed, 22 to rework, 28 rejected; 15 passed after rework, so 265 are good and yield is 88.3%, under 95%, which raises "yield below target". 7 units remain unfixed.
- All orders: 1,680 units made, 1,582 good: a final yield of 94.2%, first-pass yield of 91.7% and 62 rejected units. Production cost is 268,165. The most frequent defect is "software fault" with 47 units (33.8%), then "LED or screen fault" with 38.
- Actual cost of a good unit: power bank 338.16 (not 313.50 on paper), so its margin is 13.3%, not 19.6%, because the running order is not finished. Charger 102.58 with a 29.3% margin, bulb 104.19 with 22.8%.

**Shortages:** 1,470 planned units remain (70 from the running order, 600 chargers, 500 bulbs and 300 power banks). Five components are short: the battery (need 740, balance 240), the smart control board, the power bank charging board, the charger board and the power bank casing. Four more need an order because their balance after the orders falls under the minimum. The suggested order costs 112,635, the battery being the largest at 57,000 (suggested 600 = 740 + minimum 100 − balance 240).

## Why it helps

- Consumption by formula: nobody subtracts components by hand, so stock does not drift from reality.
- Shortages before you start: you find the late component before the line stops.
- First-pass and final yield: the gap between them is what rework saves.
- Good-unit cost: the effect of rejects on cost and margin shows.
- Defect types: you see where to focus; software faults beat soldering in the example.
- Works in Excel 2016 and later, Google Sheets and LibreOffice, with room for 100 components, 30 products, 200 orders and 500 QC rows.

## Common mistakes

- An incomplete BOM: a forgotten component means a lower cost than reality and a shortage that never shows.
- Not typing the actual quantity: nothing is deducted until you do.
- Logging an unrepaired unit as passed: log "passed after rework" only for units you fixed.
- Skipping the defect type: the sheet asks for it on rework and rejects because it drives quality analysis.
- Treating overhead as fixed: recompute the hourly share now and then.

The sheet is for records and calculations only; quality requirements and specifications follow the competent authority and your product's own specs. If you also track assembly stages see the [production tracking sheet](https://softwiro.com/templates/production-tracking), and for parts the machines consume the [spare parts inventory sheet](https://softwiro.com/templates/spare-parts-inventory).

## Who it suits and when it is not enough

It suits a small plant with dozens of components and a few products, typed by one person; it always needs someone to log every receipt, order and test. With several lines and shifts, or when you need planned orders broken into components automatically and quotes from several suppliers, [MESwiro](https://softwiro.com/systems/meswiro.html) runs material requirements planning (MRP) and works out shortages against stock.

## Next step

Open the [sheet page](https://softwiro.com/templates/electronics-factory), try the example or download it. Type the BOM of one of your products with its components, then your first production order, and check "النواقص".

## 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: electronics factory (1/3)](https://softwiro.com/uploads/cheatsheets/electronics-factory/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/electronics-factory/en-1.jpg)[![Cheat sheet: electronics factory (2/3)](https://softwiro.com/uploads/cheatsheets/electronics-factory/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/electronics-factory/en-2.jpg)[![Cheat sheet: electronics factory (3/3)](https://softwiro.com/uploads/cheatsheets/electronics-factory/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/electronics-factory/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 components deducted from stock?

The sheet multiplies the component quantity per product unit (from the BOM) by the actual quantity in Production orders and totals it in the "Consumed" column. A planned order deducts nothing until you type its actual quantity.

### How is yield calculated?

Final yield = good units ÷ units made, where good = passed first time + passed after rework. First-pass yield = passed first time ÷ units tested.

### How is unit cost worked out?

Unit cost = BOM materials + labour hours × hourly cost + labour hours × overhead share. The good-unit cost spreads the whole order cost over the good units only.

### Does the sheet set quality requirements?

No. It is for records and calculations; quality requirements follow the competent authority and your product's own specifications.

## 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)
