ERP를 도입한다고 해서 이 문제가 해결되지는 않는다. ERP는 그렇게 융통성 있는 도구가 아니다. 납기 변경, 긴급 주문, 현장 변수처럼 하루에도 수차례 바뀌는 상황을 실시간으로 반영하려면 담당자가 직접 조작할 수 있는 유연한 양식이 필요하다. 엑셀이 여전히 생산현장에서 살아남는 이유가 여기에 있다.
주문의 흐름: 위에서 아래로
이 양식에서 주문은 아래 순서로 흐른다.
주문 등록 — 발주서를 접수하면 즉시 행을 추가한다
납기순 정렬 — 납기가 빠른 것이 위, 느린 것이 아래
제조계획 — 내용물(벌크)을 만드는 계획
포장계획 — 충전·포장을 진행하는 계획
제조양식과 포장양식은 행으로 이어 붙여 사용하더라도 주요 칼럼은 동일한 의미를 갖도록 설계했다. 제조 담당자와 포장 담당자가 서로의 양식을 보더라도 기본 흐름이 파악된다.
비중 입력 → 소요량 자동 계산
화장품 용량은 mL(또는 ml)로 표기된다. 그런데 이것은 부피 단위다. 포장라인에서 충전기는 무게(g)로 계량하기 때문에, mL 표기를 그대로 쓸 수 없다. 비중을 감안해 충전량(g)을 계산해야 충전기에 올바른 기준을 줄 수 있다.
Implementing an ERP system doesn't solve this. ERP is not that flexible. When delivery dates shift, urgent orders arrive, and floor conditions change multiple times a day, you need a form that the person in charge can adjust on the spot. This is why spreadsheets continue to hold their ground in production management.
The order flow: top to bottom
In this form, every order moves through the following sequence:
Order registration — a new row is added the moment a purchase order arrives
Sorted by due date — earliest deadline at the top, latest at the bottom
Manufacturing plan — producing the bulk content (filling material)
Packaging plan — filling and packaging operations
The manufacturing section and packaging section are designed so that their key columns carry the same meaning when placed side by side. A packaging team member looking at the manufacturing section — or vice versa — can still follow what's happening.
Specific gravity input → automatic quantity calculation
Cosmetics state their volume in mL (or ml) — a unit of volume. But filling machines on the packaging line work in weight (grams). Because of this, the mL figure on the label cannot be used directly as a fill target. Specific gravity must be factored in to calculate the correct fill weight in grams.
When specific gravity is entered into the form, a formula automatically calculates the required quantity in grams.
50 mL product, specific gravity 1.02 → required: 51 g per unit
Order quantity: 1,000 units → total content needed: 51,000 g = 51 kg
The calculated quantity isn't used directly as the manufacturing plan quantity. It gets adjusted based on mixer capacity and the practicality of weighing raw materials. A target quantity that produces clean decimal figures reduces measuring errors on the floor.
Which is easier to measure accurately — a raw material quantity with three decimal places, or one with a single decimal place?
Manufacturing quantity hints and existing stock
The form displays a manufacturing quantity hint derived from the required quantity. The person planning production uses this as a reference point when considering mixer capacity.
Existing stock of semi-finished or finished goods is noted in the remarks column. If there's already inventory available, the planned manufacturing quantity can be reduced accordingly. Failing to make this visible leads to duplicate production runs — exactly the kind of waste this form is designed to prevent.
Color-coding for status
Each manufacturing plan row is color-coded to show whether it's ready to proceed.
Purple — all raw materials and packaging components are ready. This row can go to the floor immediately.
Other colors or no color — a reason is written alongside. Pending component delivery, unconfirmed BOM, quality hold — whatever is blocking the job gets documented clearly.
In a production meeting, pointing at a cell is often the full answer to "why isn't this running?"
Delay reason codes
Production plans change multiple times a day. Writing "delayed" without context makes it impossible to analyze patterns afterward. Standardizing delay reasons as codes turns individual incidents into data.
Below is an example of the codes used in practice:
Code
Reason
1-11
Packaging component — insufficient quantity received
1-12
Packaging component — original defect
1-13
Packaging component — process defect
1-14
Packaging component — delayed delivery (external)
1-15
Packaging component — incoming inspection pending
1-16
Packaging component — setup delay (internal)
2-21
Pre-work suitability check
2-22
On-hold during production
2-23
Semi-finished goods shortage
2-24
Overfill
3-31
Equipment setup delay
3-32
Equipment breakdown
3-33
Equipment substitution
4-41
Staffing shortage
4-42
Previous operation delayed
5-51
New product
5-52
Production plan (existing data not used)
5-53
Packaging specification sheet
6-61
Plan change
7-71
Customer-side delay
8-81
Controlled item delay
Sub-codes can be appended with a hyphen to capture more detail. For example, 2-23-1 means "semi-finished goods shortage — stock could not be located." 2-21-1 means "pre-work check — filtration only performed." The more specific the code, the more useful the historical data becomes.
Always include a backup plan for the day
The length of this delay code list reflects a simple reality: days that run exactly to plan are the exception, not the rule.
Workers cannot sit idle. When job A gets blocked, job B needs to be ready to step in immediately. The backup plan is not a contingency — it is a required part of every daily schedule.
Keep base and color products separate
In the manufacturing plan, base (skincare) products and color cosmetics must not be interleaved.
The two product types require different equipment, different cleaning protocols, and different process sequences. Mixing them in the middle of a day's schedule increases equipment changeover time and raises the risk of cleaning steps being skipped. Group similar products together and run them consecutively.
No merged cells — ever
Production plans change constantly. When a deadline shifts, you need to cut a row out and move it up or down the sheet.
A single merged cell breaks cut-and-paste. Use color fills or borders to visually separate sections instead. Merged cells look clean on screen but become a recurring obstacle every time a plan needs adjusting.
Weekly report automation
Because all data for each order lives in one row — planned date, completion status, quantity — filtering by week and referencing those rows produces a weekly production report with minimal manual work. No separate aggregation step required.
Share it with the team via co-authoring
This form is designed to be shared live using Office co-authoring. Manufacturing staff, packaging staff, sales management, and purchasing can all work in the same file simultaneously. Each team updates their own columns as things change.
No more emailing file versions back and forth. When a plan changes, the person responsible updates it directly — and everyone sees it immediately.
In the next chapter, I'll walk through how this form is actually used day-to-day: the morning routine, mid-day adjustments, and the weekly review cycle.
댓글Comments