[템플릿 제공] ERP 없어도 된다? 스프레드 시트로 하는 디지털 전환 전략 ②

구매실무
공급망관리
연차무관
산업공통

165 1 2

지난번 칼럼에서는 외주사를 통하여, 하나의 반제품이 입고되기까지 각 공정을 담당하는 외주 공급망 관리를 어떻게 전산화시켜 관리할지 청사진을 그려봤습니다.

지난 시리즈 다시보기 >

🔗ERP 없어도 된다? 복잡한 외주 공정, 스프레드 시트로 하는 디지털 전환 전략

이번 칼럼에서는 해당 내용을 바탕으로 스프레드시트로 템플릿을 만들고 기준점을 협의하여 업무 효율을 개선한 경험에 대해 공유드리고자 합니다. 시행착오를 겪으며, 셋업 했던 포맷 실제 예시를 함께 공유드릴 예정이며, 스프레드시트 작성 시 활용한 엑셀 함수도 함께 공유드리니 참고하셔서 현업에 적용하시어 좋은 결과물을 산출하시면 좋겠습니다.

실전, 구글 스프레드시트 기반 외주 공정 관리 시스템 설계

지난 칼럼을 통해 하나의 반제품이 완성되어 납품되기까지 예시로 말씀드렸던 외주 공정을 기본으로 설명드리겠습니다.

공정 1 → 공정 2 → 공정 3 → 공정 4 → 반제품 입고 → 최종 생산
(공정1~4는 외주사를 통해 진행되는 공정)

① 최종 생산 계획 입력

외주 생산 계획의 근간이 되는 것은 반제품을 입고 받아서 완제품을 생산하는 ‘최종 생산’ 계획입니다. 생산 계획 일자, 수량을 기입하면 각 외주사의 공정 Lead Time을 고려하여 역으로 외주사별 입고 필요 시점이 기재되도록 설정했습니다.

※ 엑셀 수식

6행 O열에 최종 생산 계획을 입력하면, E열의 공정별 생산 일수(Lead Time)를 바탕으로 자동으로 앞 공정이 진행되어야 하는 시점에 필요 수량이 입력됨

=IF( H$1 + $E4 = INDEX($H$1:$BX$1, MATCH(H$1, $H$1:$BX$1, 0) + $E4),
INDEX($H4:$BX4, MATCH(H$1 + $E4, $H$1:$BX$1, 0)),"")

② 입/출고 RAW 입력을 통한 외주 흐름 기록

자재/반제품 입/출고 내역 기록
공정 간 이동 재고 추적 : A 외주사 → B 외주사 이동 흐름 기록

각 외주사별로 입/출고 데이터를 발생 시점 기준으로 실시간으로 입력하여, 외주사간의 자재 이동에 대해 히스토리를 남깁니다. 휴먼에러를 최대한 줄이고자 ‘드롭박스’를 활용하여 정보값을 선택할 수 있도록 구성했습니다.

여기서 중요한 포인트를 말씀드리자면, 각각을 ‘어떤 기준’으로 입력하는지 그 ‘기준이 명확+통일’되어야 한다는 것입니다. 정말 간단하고 당연해 보이지만, 실제 입력을 진행해 보면 담당자들마다 본인의 기준으로 입력하여 데이터에 혼선을 초래하는 경우가 많습니다.

ex) 입고 - 어느 업체로 출하했는지

출고 - 어느 업체로 출고했는지

폐기 - 어느 업체에서 폐기했는지

아래 구조도와 같이 자사, 경쟁사 간의 정보 노출 등 보안을 위해 각 업체별로 스프레드시트를 별도로 운영했습니다. 하지만 개별 커뮤니케이션이 아닌 Dash Board를 통한 커뮤니케이션 방식의 개선이 필요했습니다. 여기서 가장 중요한 관리 포인트로 다음 공정을 담당하는 업체에서 생산 준비를 위해 공유가 필요한 정보(자재 입고 예정일, 수량 등)의 ‘공유와 차단’의 경계를 잘 관리해야 합니다.

※ 구글스프레드시트에만 존재하는 특별한 함수 한 가지를 소개합니다.

각각의 별도 스프레드시트에 보이는 화면을 그대로 하나의 화면에 출력할 수 있도록 데이터를 보이는 그대로 끌어오는 함수입니다. 앞서 말씀드린 ‘공유와 차단’의 도구로 가장 많이 활용한 함수입니다.

importrange 함수
=importrange("URL입력","시트명!범위:범위")

③ ‘기준 값’에 대한 관리

지난 칼럼을 통해 아래와 같이, 휴먼에러를 제로에 가깝게 만들기 위해서 표준화해야 하는 Data들에 대한 기준점과 정의를 통일시키는 작업이 필요합니다.

■ 기준정보 관리 (Master Data Control)

공정 코드 체계화 : 공정별 순서 및 코드 표준화
외주사 DB 구축 : 업체별 담당 공정, 연락처, 위치 등 포함
자재/반제품 BOM 관리 : 공정별 소요 자재 리스트 관리

기준 정보는 모든 데이터 입력의 기반
* tip : 휴먼에러를 최대한 없애고자 드롭다운 리스트, 데이터 유효성 검사 기능을 통해 입력 오류를 방지합니다.

아래 이미지와 같이 최종 입고되는 반제품의 공정 순서, 자재별 코드, 그룹화 등 정보에 대한 명확한 기준을 설정했습니다. 수많은 담당자들이 각기 다른 정의와 기준을 담아 Data를 입력하면, 이 Raw data는 굉장한 혼란만 초래할 뿐입니다.

④ 공정별 일정 및 납기 모니터링

외주 공정 간의 이동되는 자재들은 ‘이동형 재고’로 입/출고 당시 실시간으로 자재 흐름이 기록되지 않으면 추적이 어렵습니다. 이 공정 간의 자재 흐름까지 반영하여 가용 재고를 제대로 파악할 수 있습니다.

계획 vs. 실적 일정 비교 : 공정별 예정 시작/종료일과 실제 일정 기록
공정 진행 상태 자동 색상 표시 : 조건부 서식을 활용해 진행률 시각화
납기 D-Day 자동 계산 및 경고 : 오늘 날짜 기준으로 잔여 납기일 표시

입/출고 Data를 입력하면 아래 예시 이미지와 같이, Dash Board 상에 각 업체의 공정별 자재 이동 현황이 자동 기록되도록 설정했습니다.

이 자재흐름표를 통해 각 공정별 외주사 담당자들은 납기 계획에 맞춰 자재 점검을 진행할 수 있습니다. 또한 계획 대비 실제 입고 일자, 수량 등 납기 준수에 대한 관리, 평가가 가능합니다.

⑤ 공정별 수율, 불량 원인 파악 등 KPI 수립 및 자료 활용

품질을 관리하는 부서가 별도 존재하지만, 구매팀에서도 외주사의 수율과 품질에 대한 모니터링은 함께 이뤄질 필요가 있다고 생각합니다. 낮은 수율, 높은 불량률로 인해 최종 입고 되어야 하는 반제품의 수량과 일정에 차질이 발생하는 경우가 다수 존재하기 때문입니다.

공정별 투입/산출량 기록을 통해 수율 자동 계산이 될 수 있도록 했습니다. 현재 포맷에서 더 나아가서 기준치 이하의 수율이 되면 빨간색 음영 표시가 되도록 설정하여 불량 유형 분석 내용을 작성하도록 하는 것도 좋은 아이디어라고 생각하여 반영하기 위해 준비 중입니다.

⑥ 외주사를 대상으로 스프레드시트 사용법 교육 및 시뮬레이션

저는 실제로 외주사 담당자분들을 초청하여 스프레드시트를 활용한 외주 관리 전산환 프로젝트에 대해 소개하는 행사를 진행했습니다. 담당자분들과 반나절을 함께 보내며, 용어부터 자재코드의 통일, 사용 방법을 비롯하여 작성 원칙 등 실질적으로 필요한 부분에 대하여 논의와 피드백을 주고받았습니다.

현재 이 입/출고 data를 바탕으로 전체 뭘 마감까지 진행하는 단계로 효과성을 끌어올리고 있습니다. 각 담당자들과 개선된 업무 방식과 커뮤니케이션의 효율성으로 시간을 확보했습니다. 그리고 이 시간을 또 다른 개선 활동에 사용하며 더 전문적인 구매 업무가 될 수 있도록 궁극적인 업무 고도화를 이루고 있습니다.

마치며.

ERP를 새롭게 셋업 하거나 기능을 추가하기 위해서는 검토 단계부터 굉장히 많은 시간이 소모됩니다. 여기에 비용 문제까지 더해지면 실질적인 개선 효과를 얻기까지 기약할 수 없는 시간과 비효율의 도돌이표 속에 갇히게 될 것입니다.

이번 칼럼에서 소개해드린 스프레드시트의 활용 방법은 비용이 거의 들지 않으며, 당장 실행 가능합니다. 무엇보다 필요한 업무를 위해 구성한 포맷이기에 실무자들이 쉽게 적응할 수 있습니다. 외주사를 하나의 시스템 안에서 연결하는 이 방식을 다채롭게 활용하여 여러분의 실무에서 또 다른 전산화가 여러분의 업무 효율을 높여주길 기원합니다.

👉외주 관리 전산화 템플릿 보러가기