엑셀 BOM 관리 가능한 생산관리 생산일보 서식과 자동 계산 수식 작성 방법.

안녕하세요. 행아아빠입니다.

오늘 설명드릴 엑셀 내용으로는 엑셀로 생산관리 서식을 만들고 BOM관리까지 가능한 서식과 수식을 작성해 보고자 합니다.

 

BOM 생산관리는 사용자의 환경에 따라 굉장히 다양한 형태로 존재할수밖에 없는데 제품이 생산되기까지 자재의 종류, 소요량등이 제품마다 회사마다, 사용자마다 다를수밖에 없기 때문입니다.

 

따라서 오늘 설명드리는 예제 샘플 서식을 확인해 보고 사용자의 환경에 적합한 BOM 생산관리 서식을 어떻게 작성해야할지 고민해보고 자재 데이터 관리를 어떻게 해야 할지 고민해보고 자신만의 맞춤형 BOM 생산관리 서식을 작성하는데 도움이 될 수 있기를 바라겠습니다.

 

▼ 목차

1. BOM 관리가 가능한 생산 관리 예제와 생산 조건 요약.

2. BOM 생산 관리 서식 주요 시트 작성 내용.

3. BOM 생산 관리 및 생산 일보 자동 계산 시트별 수식 작성.

4. 사용자 맞춤형 생산집계현황 시트 서식 및 자동 계산 수식 참고 글.

 

 

BOM 생산관리 서식과 자동 계산 수식 작성 방법 썸네일 이미지.

생산관리는 보통 제조업 또는 제품을 일련의 공정과정을 거쳐 직접 생산 하는 과정에서 필요하다고 할 수 있습니다.

이렇게 제품을 일련의 공정 과정을 거쳐 생산 하는 과정에서 각 분야별 담당자가 파악해야할것으로는 제품을 생산하는데 필요한 원자재, 부자재의 생산 소요량, 그리고 제품 생산 후 남아있는 원자재/부자재의 재고파악과 제품의 생산 순서를 결정하는 생산 계획에 따라 원자재/부자재의 재고 상태, 납기 예정일, 생산 소요 시간등의 생산과 납품 그리고 원자재/부자재의 재고까지 모두 유기적으로 연결되어 자재가 입고 되어 자재 재고 수량을 파악하고 생산 계획부터 생산이 완료가 되었을때 자재의 재고, 생산 자재 소요량, 생산 시간등을 모두 파악할 수 있는 이러한 생산 흐름에 따라 이루어지는 일련의 공정 과정을 생산 bom이라고 할 수 있을것 같습니다.

 

이러한 BOM 생산 관리 서식을 작성하기 위해서 일련의 공정을 거쳐 생산되는 예제를 확인하고 예제에서 정한 생산 흐름에 따라 발생하는 생산 데이터를 생산 관리 서식으로 작성하고 생산 관리 서식에서 자동으로 계산되어 데이터 결과값이 표기 될 수 있는 수식을 작성 하는 순서로 살펴보도록 하겠습니다.

 

1. BOM 생산 관리가 필요한 예제 설정.

원자재 모형도 이미지.

특정 제품을 생산하기 위해서 이미지에 보는것과 같이 두루마리 휴지와 같은 형태의 원자재의 폭이 800mm ~ 1650mm까지 원자재 종류별로 50mm단위로 있다고 가정합니다.

 

또 원자재의 종류는 A, B, C, D, E, F로 6가지 종류가 있다고 가정할 경우 원자재 종류별로 19개씩의 원자재들이 있을 수 있습니다.

예를 들면 A원자재는 800mm, 850mm ~ 1600mm, 1650mm까지 19개, B원자재는 800mm,850mm ~ 1650mm까지 19개로 분류 되는것으로 생각해 볼 수 있습니다.

 

이러한 원자재 2종류의 원자재를 동일한 폭으로 접착한다음 폭쪽으로는 양쪽 최소 5mm씩 재단하고 원자재가 감겨있는 방향으로는 제품의 사이즈에 맞춰 재단한다고 할 경우 제품 사이즈가 800-535라고 할경우 800은 원자재가 감겨 있는 방향으로 재단할 사이즈, 535는 원자재의 폭 방향으로 재단하는 사이즈입니다.

 

이때 원자재가 감겨 있는 방향은 제품 사이즈대로 재단을 하는것으로 문제가 없지만 폭 방향으로 535를 2개씩 재단할 경우에 필요한 원자재의 폭은 535*2=1070으로 양쪽 최소 5m씩 재단해야할 여유가 필요 하기 때문에 필요한 원자재의 폭은 1080폭의 원자재가 필요하게 됩니다. 하지만 원자재의 폭 종류는 50mm단위만 존재하기 때문에 1080폭에서 가장 가까운 폭은 1100으로 1100mm폭의 원자재가 필요하게 됩니다.

 

만약 폭 방향으로 3개씩 재단할 수 있는 경우에는 535*3=1605+10=1615폭으로 1650mm의 원자재가 필요하게 되는것이죠.

 

원자재가 감겨 이는 방향으로는 최소 1개 ~ 3개까지 재단이 가능하다고 할경우 제품 사이즈의 폭이 535일 경우 원자재 폭에 따라 필요한 원자재 폭은 1개를 재단할 경우에는 535*1=535+10=545=550mm. 535*2=1070+10=1080=1100mm, 535*3=1605+10=1615=1650mm 이렇게 필요하게 됩지만 원자재의 정규격 종류는 최소 800mm부터 최대 1650mm까지만 존재 하기 때문에 535폭의 재단은 2개 또는 3개씩 재단하는것으로 생산을 해야한다고 합니다.

 

원자재의 사이즈는 원자재가 감겨 있는 방향을 "장"이라고 하고, 원자재 폭방향 재단을 "폭"이라고 하여 제품 사이즈를 장*폭으로 표기 하고 원자재는 동일한 원자재 종류를 포함해서 2개씩 접착이 가능하고, 제품 폭 사이즈에 따라 폭 방향으로 최대 재단 개수로 재단한다고 할때 원자재의 사용 폭을 결정하게 될것입니다.

 

그리고 원자재가 감겨있는 길이는 평균적으로 원자재 종류에 따라 A, B, C, D = 5,000m가 감겨 있고, E = 7,000m, F = 8,000m가 감겨 있고 원자재를 정사각형 10mm * 10mm로 잘라서 중량을 재어보면 원자재 종류별로 각각 A, B, C = 180g, D = 150g, E = 120g, F = 100g이라고 가정합니다.

 

그리고 이렇게 원자재와 원자재를 접착하여 생성할 수 있는 제품은 1번과 2번으로 나누어 생산할 수 있고 원자재를 재단하는 속도는 시간당 6000m까지 재단이 가능하고, 제품당 초기 불량(loss)는 제품 생산 수량에 약 8%입니다.

 

위와 같은 생산 흐름일때 제품을 생산할때 발생하는 생산 소요 시간, 생산 원자재 소요량, 원자재 재고등을 파악할 수 있는 생산관리 서식을 작성하고자 합니다.

 

 

굉장히 복잡해 보이는 생산 과정을 생산관리 서식을 작성하기 위한 정보 데이터로 요약을 하면 다음과 같이 요약할 수 있겠습니다.

1. 원자재 종류. A, B, C, D, E, F 6종 2. 원자재 무개와 평균 길이. A, B, C = 180g / 5,000m D = 150g / 5,000m E = 120g / 7,000m F = 100g / 8,000m 3. 원자재 종류별 지폭 분류. 원자재 종류별로 800mm ~ 1650mm까지 50mm단위로 19개 지폭으로 분류. 4. 원자재 재단 여유. 폭 방향 재단 사이즈에서 양쪽 5mm씩 최대 10mm 여유 필요하고 재단은 폭방향으로 1~3개까지 재단 가능. 5. 제품 생산 속도. 제품을 생산하는 속도는 시간당 최대 6,000m까지 생산 가능. 6. 생산 제품 종류와 초기 불량(loss) 생산 제품 종류는 1번과 2번 2종 생산이 가능하고 제품별 초기 불량은 생산 제품 수량의 8%. 7. 제품 생산 방법과 제품 표기 방법. 동일한 원자재 종류를 포함하여 원자재 + 원자재를 접착하는 방식으로 생산해야할 제품 표기는 원자재.원자재 1번 or 2번 800*535와 같이 표기. 8. 원자재 생산 소요량 원자재1.원자재2 생산시 원자재1은 *1, 원자재2는 *1.5배 소요.

위와 같은 원자재로 생산관리 서식으로 생산 계획, 소요량, 재고등과 같은 bom 생산 관리 서식을 작성해 보도록 합니다.

 

2. 생산 관리 서식 작성.

생산관리 서식은 총 5개 시트로 참고, 원자재입고, 원자재재고, 생산현황, 생산집계현황 시트로 나눠서 작성했습니다.

샘플 서식으로 작성한 생산관리 서식에서 파악하고자 하는것은 원자재의 재고 수량 (현재 재고, 생산 소요 수량, 불량 또는 loss 소요 수량, 생산 일자별 생산 소요 수량), 생산 계획, 생산 계획 예정 소요 시간과 같은 내용을 파악 할 수 있는것에 염두해 두고 작성해 보았으며 이러한 내용을 토대로 사용자가 원하는 분석을 할 수 있는 생산집계현황 시트에서 확인할 수 있는데, 샘플 서식에서는 생산집계현황 시트는 공백으로 두고, 자신이 파악하고 분석하고자 하는 데이터를 타 시트의 데이터를 참고 하여 분석 데이터의 결과값을 표기 할 수 있는 수식을 직접 작성할 수 있다는것을 염두해 두고 작성하였으니 참고 하시길 바라겠습니다.

 

 

2-1. 참고 시트 주요 작성 내용.

참고시트 주요 내용 이미지.

참고시트에서는 원자재의 종류부터 평균 길이, 중량, 원자재단가, 그리고 재단폭에 따라 원자재를 사용해야할 폭의 구간별 범위를 표 형태로 작성하여 생산 현황에서 참조 영역으로 활용할 수 있는 내용들을 작성합니다.

 

여기서 원자재단가는 작성하지 않았는데 원자재 단가까지 작성할 수 있다면 생산단가 및 생산원가계산과 영업이익등의 집계와 분석까지도 가능할 수 있을수 있겠습니다.

 

 

2-2. 원자재입고 시트 주요 작성 내용.

원자재입고 시트 주요 작성 내용 이미지.

원자재입고 시트에서는 원자재가 입고되는 내용을 입력해 주는것으로 입고일자, 원자재 종류, 입고 지폭, 총중량을 입력하고 원자재 소요량과 생산 소요 예정 시간과 같은 데이터를 평균값으로 계산하도록 하지만 실제 입고되는 원자재의 길이로 계산할 수 있기 때문에 입고되는 내용을 기준으로 실제 원자재의 길이까지 계산 할 수 있는 수식을 작성하고 참고 원자재 입고 단가를 입력하게 된다면 참고 시트에서 원자재 단가와 입고 단가의 차이가 발생하는지 알 수 있기 때문에 원자재의 입고 거래처별로 차액이 발생되는지 또는 원가계산등과 같은 계산을 할 수 있을것입니다.

 

 

2-3. 원자재재고 시트 주요 작성 내용.

원자재재고 시트 주요 작성 내용 이미지.

원자재 재고 시트에서는 원자재의 입고와 생산시 소모되는 사용량을 계산하여 현재 재고를 파악할 수 있는 내용과 불량 또는 초기 loss와 같은 데이터를 계산하여 불량과 재고 관리와 생산에 부족한 원자재가 있는지 없는지 등을 파악할 수 있는 용도로 활용할 수 있고 일자별로 원자재의 생산 소모량을 파악할 수 있어 

 

 

2-4. 생산계획 및 생산현황 시트 주요 작성 내용.

생산계획및 생산현황 시트 주요 작성 내용 이미지.

생산현황 시트에서는 생산 계획및 작업지시서 데이터를 작성하는 내용을 주로 이루고 있고, 작업이 이루어져야할 내용들을 입력하게 되면 수식에 의한 계산으로 데이터가 표기 될 수 있도록 작업 순서를 지정하는것 자체만으로도 작업지시서가 완성될 수 있고 각각의 작업 소요 예정 시간, 원자재 소요량, loss등이 계산될 수 있는 내용들로 입력되어 있습니다.

 

 

3. 생산관리및 생산일보 샘플 서식 수식 작성.

참고 시트에는 작성되는 수식은 별도로 없고 원자재 스펙, 생산 원자재를 결정하는 요소들과 같은 기본 내용들을 직접 입력해주는 시트이고, 생산집계현황 시트는 샘플 서식에서는 별도로 작성한 내용은 없지만 자신에게 맞는 맞춤형 데이터를 표기 하기 위한 수식을 직접 작성해 보셨으면 하는 마음으로 비워둔 시트입니다.

 

이전에 작성한 엑셀(Excel)의 카테고리 내용들을 확인해 보면 충분히 원하는 데이터 값을 표기 하고 생산집계 시트를 완성하실 수 있을것입니다.

 

그럼 원자재입고, 원자재재고, 생산현황 시트에 각각 작성된 수식이 어떻게 작성되어 어떤 결과값을 표기 하게 되는지 확인해 보도록 하겠습니다.

 

3-1. 원자재입고 시트 수식 작성.

원자재 입고 시트에서는 원자재 입고 스펙을 직접 입력한 데이터를 참조 하여 원자재의 실제 입고 길이, 입고월, 입고일자, 원자재 입고 단가 데이터를 계산하여 결과값이 표기 되는 수식을 작성해야 하는데, 원자재 입고 단가는 개인적으로 직접 수식을 입력하거나 단가를 입력하여 생산과 관련된 단가, 원가등의 분석데이터로 활용해 보시기를 바랍니다.

 

수식은 다음과 같이 각각의 항목에 따라 계산 할 수 있습니다.

1. F열 원자재 실제 입고 길이. 원자재 실제 입고 길이 계산은 원자재입고중량/(원자재지폭/1000)*(원자재중량/1000)=원자재 실제 입고 길이로 계산. =IF(A2="","",ROUND(($D2/((VLOOKUP($B2,참조!$B$2:$D$100,3,FALSE)*$C2)/1000000)),0)) 2. G열 입고월. =IF($A2="","",MONTH($A2)) 3. H열 일고일자. =IF($A2="","",DAY($A2))

 

 

3-2. 원자재 재고 시트 수식 작성.

원자재 재고 시트는 기초재고 + 입고 재고 - (생산 소요량 + 불량 또는 초기 loss) = 현재 재고로 계산하는것으로 원자재 기초 재고는 매월 재고 실사를 통해 실제 재고 수량을 입력해주고, 만약 재고 실사가 없을 경우에는 이전월의 마지막 재고 수량을 기초 재고로 할 수 있습니다.

 

하지만, 매월 실사 재고를 통해 실제 생산 소요량 + 불량 또는 초기 loss와 실사 재고 수량을 비교함으로써 장부상 재고와 실제 재고 수량 차이에서 발생하는 차이는 불량률 또는 불량 감소율로 체크할 수 있는 데이터를 확보할수 있을것입니다.

 

그리고 A1셀에 해당 월을 입력해 주어 해당월의 원자재 재고와 소요량, 입고수량, 불량 또는 초기 loss를 파악하는 참조 데이터 셀이 됩니다.

 

각각의 항목의 수식은 다음과 같이 작성할 수 있습니다.

1. J3셀~O20셀 원자재 현재 재고. =IF(SUM(B3+IF(R3="",0,R3)-IF(Z3="",0,Z3))="","",SUM(B3+IF(R3="",0,R3)-IF(Z3="",0,Z3))) 2. R3셀~W20셀 원자재 입고. =IF(COUNTIFS(원자재입고!$G$2:$G$1000,원자재재고!$Q$1,원자재입고!$B$2:$B$1000,원자재재고!R$2,원자재입고!$C$2:$C$1000,원자재재고!$Q3)=0,"",COUNTIFS(원자재입고!$G$2:$G$1000,원자재재고!$Q$1,원자재입고!$B$2:$B$1000,원자재재고!R$2,원자재입고!$C$2:$C$1000,원자재재고!$Q3)) 3. Z3셀~AE20셀 원자재 생산 소요량 =IF(SUMIFS(생산현황!$U$5:$U$1000,생산현황!$E$5:$E$1000,원자재재고!Z$2,생산현황!$N$5:$N$1000,원자재재고!$Y3)=0,"", SUMIFS(생산현황!$U$5:$U$1000,생산현황!$E$5:$E$1000,원자재재고!Z$2,생산현황!$N$5:$N$1000,원자재재고!$Y3)) 4. AH3셀~AM20셀 원자재 생산 초기 불량(loss) =IF(SUMIFS(생산현황!$W$5:$W$1000,생산현황!$E$5:$E$1000,원자재재고!AH$2,생산현황!$N$5:$N$1000,원자재재고!$AG3)=0,"", SUMIFS(생산현황!$W$5:$W$1000,생산현황!$E$5:$E$1000,원자재재고!AH$2,생산현황!$N$5:$N$1000,원자재재고!$AG3)/VLOOKUP(AH$2,참조!$B$2:$E$100,2,FALSE)) 5. AP3셀~AU20셀 원자재 생산 일자별 소요량. =IF(SUM(SUMIFS(생산현황!$U$5:$U$1000,생산현황!$A$5:$A$1000,원자재재고!$AO$1,생산현황!$E$5:$E$1000,원자재재고!AU$2,생산현황!$N$5:$N$1000,원자재재고!$AO20,생산현황!$J$5:$J$1000,">=1"), SUMIFS(생산현황!$V$5:$V$1000,생산현황!$A$5:$A$1000,원자재재고!$AO$1,생산현황!$F$5:$F$1000,원자재재고!AU$2,생산현황!$N$5:$N$1000,원자재재고!$AO20,생산현황!$J$5:$J$1000,">=1"))=0,"", SUM(SUMIFS(생산현황!$U$5:$U$1000,생산현황!$A$5:$A$1000,원자재재고!$AO$1,생산현황!$E$5:$E$1000,원자재재고!AU$2,생산현황!$N$5:$N$1000,원자재재고!$AO20,생산현황!$J$5:$J$1000,">=1"), SUMIFS(생산현황!$V$5:$V$1000,생산현황!$A$5:$A$1000,원자재재고!$AO$1,생산현황!$F$5:$F$1000,원자재재고!AU$2,생산현황!$N$5:$N$1000,원자재재고!$AO20,생산현황!$J$5:$J$1000,">=1")))

 

 

3-3. 생산현황 시트 수식 작성.

생산현황 시트에서는 제품을 생산하기 위한 기초 데이터를 입력하는것으로 작업지시서, 작업계획서를 작성함과 동시에 생산에 소요 되는 모든 재원들을 파악하기 위한 데이터로 활용할 수 있습니다.

 

따라서 생산 기초 데이터 제품 재원을 각 항목에 따라 입력하고 A열의 날짜에 작업해야할 제품들의 순번을 B열에 입력하는것으로 작업계획이 기본적으로 작성되고, A2셀에 날짜를 입력하면 해당 날짜에 완료되지 않은 미작업에 대한 생산 소요 예정 시간을 확인할 수 있는 수식을 작성하여 작업 계획을 새우는데 필요한 시간을 계산 할 수 있기 때문에 해당 작업 계획을 시작하는 시점으로부터 작업 종료 시점을 파악할 수 있습니다.

 

또한 J열에는 생산이 완료된 작업은 생산완료 순서를 입력해주게 되면 미작업 소요 예정 시간은 생산 완료된 계획에 대한 작업 소요 예정 시간은 차감되므로 추가 작업 계획을 새우는데 용이할것입니다.

 

각각의 항목에 대한 수식을 작성하면 다음과 같이 작성할 수 있습니다.

1. (K열) 작업 순번. =$B5 2. (L열) 거래처명. =$C5 3. (M열) 생산 제품 종류. =$D5 4. (N열) 생산에 필요한 원자재 재단 폭. =IF(AND(H5*1<800,H5*3<1641),INDEX(참조!$I$2:$I$19,MATCH(H5*3,참조!$G$2:$G$19,1)), IF(AND(OR(H5*1<=800,H5*2>=800),H5*2<1641),INDEX(참조!$I$2:$I$19,MATCH(H5*2,참조!$G$2:$G$19,1)), IF(AND(H5*1>800,H5*2>=1641),INDEX(참조!$I$2:$I$19,MATCH(H5*1,참조!$G$2:$G$19,1))))) 5. (O열) 생산 작업 폭. (재단 개수) =ROUNDDOWN(SUM(N5/H5),0) 6. (P열) 장 / (Q열) 폭 =$G5 / =$H5 7. (R열) 실제 생산 작업 수량. =ROUNDDOWN(SUM(I5/O5),0) 8. (S열) 원자재1 소요량(단위:m) =(G5/1000)*R5 9. (T열) 원자재2 소요량(단위:m) =((G5/1000)*R5)*1.5 10. (U열) 원자재1 소요량 (단위:개) =Y5/VLOOKUP($E5,참조!$B$2:$E$7,2,FALSE) 11. (V열) 원자재2 소요량 (단위:개) =Z5/VLOOKUP($F5,참조!$B$2:$E$7,2,FALSE) 12. (W열) 초기불량 loss 원자재1 (단위:m) =ROUNDDOWN(SUM(S5*0.08),0) 13. (X열) 초기불량 loss 원자재2 (단위:m) =ROUNDDOWN(SUM(T5*0.08),0) 14. (Y열) 실제 생산 소요량 원자재1 (초기 불량 loss포함 / 단위:m) =SUM(S5,W5) 15. (Z열) 실제 생산 소요량 원자재2 (초기 불량 loss포함 / 단위:m) =SUM(T5,X5) 16. (AA열) 작업 예정 소요 시간 (00시간00분 표기) =ROUNDDOWN(SUM(Y5/6000),0)&"시간"& ROUNDDOWN((SUM(Y5/6000)-ROUNDDOWN(SUM(Y5/6000),0))*60,0)&"분" 17. (AB열) 작업 예정 소요 시간 (00표기 / 단위:시간) =ROUNDDOWN(SUM(Y5/6000),0) 18. (AC열) 작업 예정 소요 시간 (00표기 / 단위:분) =ROUNDDOWN((SUM(Y5/6000)-ROUNDDOWN(SUM(Y5/6000),0))*60,0) 19. (AD열 / AE열) 생산 월 / 생산 일자. =IF($A5="","",MONTH($A5)) / =IF($A5="","",DAY($A5))

 

 

4. 생산집계현황 서식 및 계산 수식 작성 참고 글.

생산 집계 현황 시트는 공백으로 두었는데 자신의 맞춤 데이터 분석에 따라 필요한 데이터를 참조, 원자재입고, 원자재재고, 생산현황 시트의 데이터들을 활용하여 결과값을 표기 할 수 있는 서식과 수식을 직접 작성해 보시길 바랍니다.

 

맞춤형 데이터를 결과값으로 표기 하기 위한 수식은 제가 이전에 작성한 글들을 참고해 보시길 바라며, 주요 참고 글은 링크를 걸어두도록 하겠습니다.

1. 행아아빠의 엑셀 카테고리글 전체 목록

https://hang-a-fafa.tistory.com/category/%EC%97%91%EC%85%80

2. Sumifs함수 - 분류에 따른 값의 총합계산 수식 작성 방법.

3. Sumifs함수의 충족조건을 And함수의 조건처럼 활용할 수 있는 수식 작성 방법.

4. Index함수와 Match함수 - 가로/세로 제목안에 있는 표의 데이터를 결과값으로 가져와야 할때 수식 작성 방법.

5. Vlookup 엑셀 함수 결과값 여러개일 때 순서대로 표기 하는 수식 작성 방법.

6. Countifs 함수 - 수식이 입력된 공란을 제외한 결과값이 입력된 셀의 갯수 카운팅

7. 날짜를 자동으로 입력하는 수식 작성 방법!!

 

 

※ 해당 샘플 예제 서식은 개인마다 또는 사용자마다 다른 조건과 환경이 상이하고, 생산 공정등의 정보 유출의 우려와 함께 무분별한 서식 공유가 발생할 수 있음으로 인하여 무료 서식은 제공하지 않습니다.