Production planning in Excel, and when a digital planning board works better

What goes into a production plan in Excel

A useful plan answers three questions per line: what is running, when does it start and when is it finished. Put all orders in one table with a column for the line, so you can filter and sort per line.

The columns you need at a minimum:

• Order number, item and customer, taken from the ERP
• The line or machine the order runs on
• The quantity and the speed of that line, for example bottles per hour
• The changeover time in minutes to switch to this item
• The start time and the calculated end time
• The delivery date, so you can see which order is at risk of being late
• A status: planned, running or done

An example: order PO-88213 on line 2

Order PO-88213 calls for 12,000 bottles. Line 2 fills 1,500 bottles per hour, so the order itself takes 8 hours. Before that, the line needs a 45 minute changeover. If the changeover starts on Monday at 06:00, the order is finished at 14:45.

In Excel, date and time together are one number, where 1 is a whole day. With the start in B2, the quantity in C2, the speed per hour in D2 and the changeover in minutes in E2, you calculate the end time in F2 with =B2+E2/1440+C2/D2/24. Format F2 as date and time.

The next order on the same line starts where the previous one ends: in B3 you enter =F2. If PO-88213 takes longer, the orders after it move along. With conditional formatting you colour the end time red as soon as it falls after the delivery date.

Where an Excel plan gets stuck

The arithmetic is rarely the problem. It goes wrong with everything that happens after the plan is made:

• Inserting a rush order breaks the chain of formulas: the new row stands alone and the orders after it do not move
• Shifts, breaks and the weekend are not in the formula, so on paper an order simply runs through the night
• The changeover time depends on the previous product, but the sheet knows one fixed value per row
• Whether materials arrive on time is not visible in the same sheet
• What really happens on the floor only comes in when someone types it, often the next morning
• Soon there are several versions: the planner works in one, the shift leader has a printout from yesterday
• Every change then has to be passed on by hand to the warehouse, sales and the customer

What a digital planning board adds

A digital planning board shows the same orders as a timeline per line. You drag an order to a line and a time slot, and the orders after it move along on their own. A good planning board takes capacity, changeover times and materials into account.

The big difference from Excel is that the board works both ways. Orders come from the ERP, and progress comes from the line: counts from the machine or from the operator on a tablet. The office and the floor look at the same plan, which shows what is done and what is behind.

What you gain compared with the sheet:

• One current version for the planner, the shift leader and the warehouse
• A warning when an order does not fit or materials are missing
• An end time that moves with what really happens on the line
• On every change, a new pick list for the warehouse and a new date for sales straight away

When is Excel no longer enough?

Excel is a fine start, and for a small, stable operation it can be enough for a long time. It is time for something else if you recognise these signs:

• The planner spends hours every day replanning
• Rush orders come in every week
• Lines wait for each other or share materials and people
• The shift leader walks to the office to ask what to run next
• Sales only hears about a delay when the customer calls
• When the planner is ill, nobody knows how the file works

Where Frontlink fits

Frontlink puts the orders and lines you now keep in Excel on a digital planning board that the office and the floor both see live. At the start we read in your current planning sheet, so nobody begins with an empty board. Frontlink makes suggestions, for example where a rush order fits best, and the planner decides.

Orders come from your ERP and completions go back. You can start without machine connections: operators record progress and downtime on the tablet. A Frontlink engineer builds it at your site, and the first process is usually running within a few weeks.