What is production planning and how do you do it? From Excel to a systematic plan

Monday morning in the workshop. On the board are twelve work orders left over from last week; three are marked “urgent”. The foreman says “the cutting machine is idle”, the planner says “the material hasn’t arrived”, sales says “the customer expects it on Friday”. All three may be right, but nobody has a single plan that shows the whole picture; for the second time that week, the planner will have to give a new delivery date. Production planning is the work of deciding in advance which product will be made, in what quantity, in what order, on which machine and by whom, with which materials and by what date; the value of a plan lies less in forecasting correctly than in showing conflicts before production starts.

Below, we use a numerical example to explain the inputs to planning, how to build the weekly plan, and where Excel is enough and where it is not.

What are the four inputs to production planning?

A plan is only as reliable as its weakest input. If capacity is known only from memory, the plan will never be realistic; if stock records are wrong, the plan shows a job with no materials as “ready to start”. The first task is to write down where each of the four inputs is kept and who holds it.

How do I plan work orders and machine load in the workshop each week?

Let’s take a hypothetical example: a small metal workshop with three machines. The cutting machine and the bending machine are each available for 40 hours a week (net of maintenance and shift patterns). This week there are three work orders:

On the cutting machine the load is 44 hours against a capacity of 40: four hours of overload. On the bending machine the load is 24 hours, so the machine is idle for more than half the time. Together these two figures say two things. First, the four hours of overload need a decision: subcontract them, schedule overtime, or push part of the work order with the most flexible due date to next week. Second, to keep the bending machine from standing idle, work has to move to bending as soon as cutting is finished; the plan looks at the machines together along the product flow, not one at a time.

1. List open work orders: product, quantity, due date and status.
2. Check materials: a work order whose materials are not in stock cannot start; mark this on the plan from the outset.
3. Enter the time for each operation: include set-up and adjustment time.
4. Convert capacity into hours: deduct shifts, maintenance and breaks to find the available hours.
5. Sequence work orders by due date: total them per machine and flag the overload.
6. Resolve the overload and record the decision: subcontracting, overtime or rescheduling; update the plan daily for any deviations during the week.

In the example, the 16 idle hours on the bending machine are an opportunity to make the plan more flexible: the bending part of a job subcontracted on the cutting side can be done in the workshop, or a work order from next week that has come in early can be pulled into the gap. The aim of planning is not only to find the overload but also to see idle capacity.

What disrupts the plan, and how do you protect it?

No plan stays as it is until the end of the week. What matters is having an answer ready for the events that disrupt it:

When the plan is disrupted, the most important thing is to record the new situation in the one place everyone can see. Having different versions of the plan circulating among the foreman, the planner and sales costs more than the disruption itself.

How do you do production planning in Excel?

For a small workshop, a four-sheet Excel file does the job well. The first sheet holds work orders (code, product, quantity, due date, status), the second the bill of materials (product, material, unit quantity), the third stock, and the fourth machine capacity and weekly load. If you total the load per machine with SUMIF and subtract it from capacity, you can see idle or overloaded hours. This set-up works with one planner, dozens of work orders and a few changes a week.

Four habits help keep the plan sound in Excel:

• Keep work order codes unique; the same code should never appear on two rows.
• Enter times based on past actual times rather than estimates, and update them every month.
• Keep the plan in a single file managed by one responsible person; everyone else should have read-only access.
• Save each week’s plan with its date, so the question “what had we planned last week?” can be answered.

When production planning in a factory reaches the limits of Excel

• Urgent jobs arrive every day and the plan exists in three different versions held by three people.
• The bill of materials, the plan and stock sit in separate files; changing one does not update the others.
• Material requirements are calculated by hand, and shortages are noticed only once the job has started.
• Actual times and scrap do not feed back into the plan; the next estimate repeats the same error.
• It is unclear at what stage a subcontracted work order is, and who has it.
• Machine load lives in one person’s head; when that person is away, there is no plan.

As these signs pile up, the problem is no longer the planning method but the scattered data that feeds the plan. Instead of speeding up Excel, you need to bring the bill of materials, stock and work orders into the same data flow. Before starting the switch, a small trial is a good yardstick: choose a single product group, run its bill of materials, stock and work orders from one place for a month, and look at where the gap between plan and actual arises. The gap usually comes from time estimates, scrap that goes unrecorded or finding out too late that material has arrived.

What does YazSis ERP offer for production planning?

The YazSis ERP page lists bill of materials, recipe, work order, subcontracting, scrap, efficiency and cost under the production heading. On the stock and warehouse side it covers transfers, stock counts, lot and serial number tracking, barcodes, RFID and locations; in purchasing, requisitions, quotes, approvals, orders, suppliers and deliveries; in management, KPIs and real-time reports. Together these make it possible to gather the inputs to planning (bill of materials, stock, delivery) in the same data flow.

To be clear about the limits: the page does not promise an automatic scheduling algorithm or capacity optimisation, and neither does this article. How the plan is produced, which rules apply and which reports are shown are matters to discuss during the needs analysis, together with your business processes. To think about moving from Excel to a system in a broader context, you can also look at the Digital Transformation page.

If you would like to talk about setting up your production planning, bill of materials and stock in a single data flow, you can request a meeting on the ERP page.