구글 시트로 재고 관리 시스템 만들기

구글 시트로 재고 관리 시스템 사진

재고 관리는 “얼마나 갖고 있고, 어디에 있고, 언제까지 써야 하며, 다시 주문해야 하는 시점이 언제인가”를 한눈에 보여주는 일입니다. 그러나 현실의 장부는 엑셀 파일이 여러 개로 갈라져 있고, 담당자마다 입력 방식이 달라 분석이 불가능해지곤 하죠. 이럴 때 구글 시트는 가볍지만 강력한 대안이 됩니다. 브라우저만 있으면 누구나 접속해 실시간으로 입·출고를 기록하고, 드롭다운·유효성으로 오타를 줄이며, QUERY/SUMIFS/피벗/차트로 재고 현황과 소진 속도를 자동으로 계산할 수 있습니다. 여기에 구글 폼과 Apps Script를 얹으면 현장에서도 폰으로 입·출고를 찍고, 재고가 재주문점에 도달하면 알림 메일이 날아가는 가벼운 WMS(창고관리시스템)로 확장됩니다.

이 가이드는 실무에 바로 쓰도록 ① 설계(마스터·입출고·스냅숏·대시보드) → ② 자동화(유효성·폼·스크립트·발주 알림) → ③ 분석/대시보드(ABC/회전율/적정재고/유통기한) 순서로 구성했습니다. 복붙 가능한 수식·스크립트, 표준 템플릿 구조, 입력 규칙, 체크리스트까지 담았으니, 오늘 바로 “재고가 보이는 시트”를 시작해 보세요.

 

1) 설계: 마스터·입출고·스냅숏·대시보드 — 역할을 분리해 데이터 품질부터 잡기

재고 시스템의 품질은 설계에서 결정됩니다. 모든 값을 한 시트에 몰아넣으면 입력은 편하지만, 시간이 갈수록 오류와 충돌이 늘어납니다. 반대로 역할 분리를 하면 입력은 단순해지고 분석은 강력해집니다. 권장 탭 구조는 네 가지입니다: ① 품목마스터(Items), ② 입출고로그(Movements), ③ 재고스냅숏(Stocks, 선택), ④ 대시보드(Dashboard). 여기에 코드표(드롭다운 목록) 탭을 더해 입력을 표준화하면 장기적으로 유지보수가 쉬워집니다.

① 품목마스터(Items) — 재고의 단일 진실(Single Source of Truth)

• 권장 열: 품목코드(고유), 품명, 규격/옵션, 카테고리, 단위, 안전재고, 재주문점(ROP), 리드타임(일), 공급사, 구매단가, 위치(랙/빈), 유통기한관리여부
• 코드/키: 품목코드는 의미 없는 일련번호(예: ITM-000123)가 좋습니다. 규칙이 단순할수록 사람의 판단 개입이 줄어듭니다.
• 색상 신호: 안전재고·ROP 열에 조건부 서식을 걸어 값이 0이거나 미입력 시 배경을 연노랑으로 표시, 누락을 즉시 발견.

팁: 카테고리, 위치, 공급사 목록은 별도의 코드표 탭에 두고 드롭다운 유효성 검사로 연결하세요. 오타를 원천 차단합니다.

② 입출고로그(Movements) — 재고의 모든 변화는 이곳에서

• 권장 열: 일시(타임스탬프), 유형(IN/OUT/조정), 품목코드, 수량(+/-), 창고/위치, 관련문서(발주/출고번호), 메모, 유통기한/로트(선택)
• 유효성: 유형은 IN/OUT/ADJ 드롭다운으로 제한, 품목코드는 Items! A:A 참조로 드롭다운. 수량은 숫자만 허용.
• 표준: 입고는 양수, 출고는 음수로 기록(혹은 별도 열로 표시). 한 규칙을 끝까지 유지하세요.

예시 행 — 2025-10-06 09:13 / IN / ITM-000123 / +50 / 메인창고-A1 / PO-2509 / 초도 입고

③ 재고스냅숏(Stocks) — 현재고 계산과 속도 최적화

• 간단한 방식: 현재고 = SUMIFS(Movements! 수량, Movements! 품목코드, 이 품목). 피벗/QUERY로 품목별 합계를 계산해 표시.
• 대량데이터: 로그가 수십만 행으로 커지면 매일/매주 스냅숏을 찍고, 실시간 현재고 = 스냅숏+최근 로그 합산으로 속도와 안정성을 확보.
• 유통기한: 유통기한/로트 단위로 재고를 따로 계산해야 한다면 품목코드+로트코드 복합 키를 사용하세요.

④ 대시보드(Dashboard) — 관리자는 숫자보다 결론을 본다

• 상단 KPI: 총 재고가치(수량 ×단가), 부족품목 수(ROP 이하), 유통기한 임박 수, 전월 대비 회전율.
• 핵심 표/차트: ROP 이하 리스트, ABC 분석, 월별 입출고 추세, 품목별 회전 속도, 상위 20 품목 재고가치.
• 슬라이서: 카테고리/창고/공급사로 동적 필터.

⑤ 수식/구조 샘플

• 현재고 표 (Stocks 탭):

  =QUERY(Movements!A:H,
  "select C, sum(D) 
   where C is not null 
   group by C 
   label sum(D) '현재고'", 0)

  (C=품목코드, D=수량)
• 마스터와 조인 (VLOOKUP/XLOOKUP):

  =ARRAYFORMULA(IF(A2:A="","",
   VLOOKUP(A2:A, Items!A:K, {2,5,6,7,9}, FALSE)))

  → 품명/단위/안전재고/ROP/단가를 한 번에 끌어오기
• 부족(ROP) 경고 플래그:

  =IF([현재고] <= [ROP], "REORDER", "")

⑥ 입력 규칙(데이터 유효성)과 조건부 서식

• Movements! B:B(유형) → 목록: IN, OUT, ADJ
• Movements! C:C(품목코드) → 목록 범위: Items! A:A
• 조건부 서식: ROP 이하 행 배경 주황, 유통기한 30일 이내 붉은 테두리

⑦ 설계 체크리스트

• 품목코드가 중복 없이 고유한가?
• 입출고 수량의 부호 규칙이 일관적인가?
• 재고 계산이 한 수식/피벗으로 설명 가능한가?
• 대시보드 질문(부족/가치/회전/유통기한)에 5초 안에 답하는가?

한 줄 요약 — 마스터는 표준, 로그는 단순, 스냅샷은 속도, 대시보드는 결론.

 

2) 자동화: 유효성·구글 폼·Apps Script·바코드·재주문 알림으로 시스템이 스스로 돌게

유효성·구글 폼·Apps Script·바코드·재주문 알림 시스템 사진

구조가 갖춰졌다면 “사람의 반복”을 줄일 시간입니다. 구글 시트는 유효성/조건부 서식으로 입력 품질을 끌어올리고, 구글 폼으로 현장 입력을 표준화하며, Apps Script로 알림과 정리를 자동화할 수 있습니다. 바코드/QR을 붙이면 모바일 스캔으로 품목 선택까지 단축되죠. 다음 레시피를 그대로 적용해 보세요.

① 유효성·드롭다운 칩으로 현장 오류 제로화

• Movements 시트 모든 입력열에 유효성을 설정합니다(유형/품목/창고/수량). 수량은 숫자만, 음수 입력 금지(출고는 ‘유형=OUT + 양수’ 규칙을 권장).
• 드롭다운 칩 모드를 사용하면 시각적으로 상태가 도드라져 입력 실수가 눈에 띄게 줄어듭니다.

② 구글 폼으로 입·출고 입력 채널 표준화

• 스프레드시트에서 삽입 > 양식 → “입출고 등록” 폼을 만듭니다.
• 질문: 일시(기본), 유형(IN/OUT/ADJ), 품목코드(드롭다운), 수량(숫자), 창고/위치, 유통기한, 메모.
• 모바일 홈 화면에 폼 바로가기를 추가해 현장 담당자가 즉시 기록할 수 있게 합니다.
• 폼 응답 시트 → Movements로 옮기는 방법:
  • 간단: Movements가 응답 시트를 직접 사용(헤더 맞춤).
  • 정교: Apps Script로 응답을 검증 후 Movements로 기록(유효성 통과/보정).

③ Apps Script: 자동 정리·재주문 알림 메일

재주문점(ROP) 이하 품목 알림 — 매일 아침 공급사별로 발주 후보 메일을 보냅니다.

function mailReorderList() {
  const ss = SpreadsheetApp.getActive();
  const stocks = ss.getSheetByName('Stocks');
  const items = ss.getSheetByName('Items');
  if (!stocks || !items) throw new Error('Stocks/Items 시트를 확인하세요.');

  // Stocks: [코드, 현재고, ...], Items: VLOOKUP으로 ROP/공급사 옆열에 합쳐둔다고 가정
  const map = {}; // 코드 → {qty, rop, supplier, name}
  const sVals = stocks.getDataRange().getValues();
  const iVals = items.getDataRange().getValues();
  const headI = iVals.shift();
  const idxCode = headI.indexOf('품목코드');
  const idxName = headI.indexOf('품명');
  const idxROP  = headI.indexOf('재주문점');
  const idxSup  = headI.indexOf('공급사');

  const dictItem = {};
  iVals.forEach(r => dictItem[r[idxCode]] = {name:r[idxName], rop:Number(r[idxROP])||0, sup:r[idxSup]||''});

  const headS = sVals.shift();
  const cCode = headS.indexOf('품목코드') > -1 ? headS.indexOf('품목코드') : 0;
  const cQty  = headS.indexOf('현재고')   > -1 ? headS.indexOf('현재고')   : 1;

  sVals.forEach(r => {
    const code = r[cCode]; const qty = Number(r[cQty])||0;
    const meta = dictItem[code] || {name:'', rop:0, sup:''};
    if (code) map[code] = {qty, rop:meta.rop, supplier:meta.sup, name:meta.name};
  });

  const bySup = {};
  Object.keys(map).forEach(k => {
    const it = map[k];
    if (it.qty <= it.rop && it.rop > 0) {
      const sup = it.supplier || '미지정';
      (bySup[sup] ||= []).push({code:k, name:it.name, qty:it.qty, rop:it.rop});
    }
  });

  if (Object.keys(bySup).length === 0) return;

  const to = Session.getActiveUser().getEmail();
  let html = '

재주문 필요 품목

';
  Object.keys(bySup).forEach(sup => {
    html += `

${sup}

`; bySup [sup]. forEach(x => html += ``); html += '

코드	품명	현재고	ROP
${x.code}	${x.name}	${x.qty}	${x.rop}

';
  });
  GmailApp.sendEmail(to, '[재고] 재주문 필요 리스트', 'HTML 미리보기 필요', {htmlBody: html});
}

트리거: 앱스 스크립트 > 트리거 > 시간기반 > 매일 08:30.

폼 응답 → Movements 정리/기록 — 수량/부호/필수값을 검증하고 로깅합니다.

function onFormSubmit(e){
  const ss = SpreadsheetApp.getActive();
  const mv = ss.getSheetByName('Movements');
  const vals = e && e.values ? e.values : null;
  if (!vals) return;

  // 폼 질문 순서에 맞게 매핑 (예시)
  // 0:타임스탬프, 1:유형, 2:품목코드, 3:수량, 4:창고, 5:유통기한, 6:메모
  const type = String(vals[1]).toUpperCase();
  const code = String(vals[2]).trim();
  const qty  = Number(vals[3]) || 0;
  if (!type || !code || qty === 0) {
    GmailApp.sendEmail(Session.getActiveUser().getEmail(),'입력오류','폼 응답 누락/0 수량');
    return;
  }
  const signed = (type === 'OUT') ? -Math.abs(qty) : Math.abs(qty);
  mv.appendRow([new Date(), type, code, signed, vals[4], vals[6] || '', vals[5] || '']);
}

④ 바코드/QR로 품목 선택 단축

• Items 탭에 각 품목의 바코드 값(예: EAN/자체 코드) 열을 추가합니다.
• 모바일에서 바코드 스캐너 앱으로 값을 복사→폼의 “품목코드”란에 붙여 넣기.
• 고급: 앱시트(AppSheet)나 AppSheet 모바일 앱을 쓰면 카메라 스캔을 바로 폼에 연결할 수 있습니다.

⑤ 자동화 체크리스트

• 유형/품목코드/창고는 드롭다운으로 제한했는가?
• 폼 응답이 Movements에 정확히 들어오는가(부호/필수값 검증)?
• 재주문 알림 메일이 ROP 이하일 때만 발송되는가?
• 대량 처리(수만 행) 시 스냅숏/쿼리 성능을 확보했는가?

현장 예시 — 입고 담당자는 폼에서 “IN/품목/수량/창고”만 입력. 바로 Movements에 반영되고 Stocks의 현재고가 갱신. 오전 9시, 관리자 메일에 “ROP 이하 6품목” 리스트가 도착. 버튼 하나 없이 시스템이 굴러갑니다.

 

3) 분석·대시보드: ABC 분석·회전율·적정재고·유통기한 모니터링으로 ‘보이는 재고’ 만들기

입력과 자동화가 준비됐다면, 이제 숫자에서 결정을 뽑아낼 차례입니다. 관리자의 질문은 대부분 정해져 있습니다. “무엇이 핵심 품목인가(ABC)? 재고가 얼마나 빨리 돈으로 바뀌는가(회전율)? 언제 발주해야 적정한가(ROP/EOQ)? 유통기한 임박은 무엇인가?” 다음 구성을 그대로 배치해 보세요.

① ABC 분석 — 가치 중심으로 우선순위를 정한다

• 기간 매출/소비액 기준으로 품목의 누적 기여도를 계산합니다(최근 3~6개월 권장).
• 표 예시: 품목코드, 품명, 소비액, 누적비중, 등급(A=상위 70%, B=다음 20%, C=하위 10%).
• 수식:

  =LET(
   data, QUERY(Movements!A:D,"select C, -sum(D) where D<0 group by C label -sum(D) ''",0),
   sorted, SORT(data, 2, FALSE),
   total, SUM(INDEX(sorted,,2)),
   addcum, BYROW(sorted, LAMBDA(r, SUM(INDEX(sorted, SEQUENCE(ROW(r),1), 2)))),
   HSTACK(INDEX(sorted,,1), INDEX(sorted,,2), addcum/total,
    IF(addcum/total<=0.7,"A", IF(addcum/total<=0.9,"B","C"))))

  (구글 시트의 LET/BYROW가 없으면 QUERY+누적합 보조열로 구현)
• A 등급은 재고 부족/유통기한/위치 최적화 최우선 관리 대상입니다.

② 회전율·재고일 수(Days of Inventory)

• 회전율 = 기간 소비량 / 평균재고. 재고일수 = (평균재고 / 일평균 소비량).
• 평균재고는 월초/월말 또는 스냅숏 평균으로 계산합니다.
• 표 예시: 품목코드, 월소비량, 평균재고, 회전율(회/월), 재고일 수(일).

③ 재주문점(ROP)·적정재고

• ROP 기본식: 리드타임 수요(일평균 소비 ×리드타임) + 안전재고.
• 일평균 소비량 계산:

  =IFERROR(
   SUMIFS(Movements!D:D, Movements!C:C, [코드], Movements!B:B, "OUT", Movements!A:A, ">="&dateStart, Movements!A:A, "<="&dateEnd)
  / MAX(1, dateEnd - dateStart + 1)
  , 0)

• Items 탭에 리드타임과 안전재고를 유지하고, ROP 계산 열을 만들어 Stocks와 대시보드에서 재고 대비 ROP를 비교합니다.

④ 유통기한·로트 관리

• Movements에 유통기한/로트열을 두었다면, 코드+로트별 현재고 표를 만들어 임박/지남을 색으로 표시합니다.
• 조건부 서식: 유통기한 ≤ 오늘+30 → 주황, ≤ 오늘 → 빨강.

⑤ 대시보드 레이아웃(가독성 우선)

• 상단 KPI: 총 재고가치, ROP 이하 품목 수, 유통기한 임박 수, 월 회전율.
• 좌측: ABC 도넛/막대(기여도), 우측: ROP 이하 리스트(공급사·최소발주량 표시).
• 하단: 월별 입출고 추세(선/영역), 창고별 재고가치(가로막대), 유통기한 임박 테이블.
• 색상은 4~5색 팔레트 고정, 경고색(주황/빨강)은 최소로 사용해 눈이 피로하지 않게.

⑥ 피벗/차트 예시

• 피벗 1: 행=품목코드, 값=수량 합계 → 현재고. 조건부 포맷으로 ROP 이하 강조.
• 피벗 2: 행=월, 열=유형, 값=수량 → 월별 IN/OUT 추세(영역/세로막대).
• 차트: 카테고리별 재고가치(가로막대), ABC 비중(도넛), 회전율 상위/하위(세로막대).

⑦ 운영 규칙 — 팀이 합의해야 할 7가지

1. 모든 변화는 Movements에만 기록(대시보드 직접 수정 금지).
2. 입고=양수, 출고=음수 또는 별도열 — 팀 내 하나로 통일.
3. 품목코드 드롭다운 외 입력 금지(오타 방지).
4. 유통기한 관리품은 코드+로트로 관리.
5. 월말 버전 스냅숏 저장(되돌리기 지점).
6. 대시보드 탭은 보기 전용 공유 + 보호.
7. 권한은 최소화(외부는 댓글부터), 링크 전체공개 금지.

대시보드 문구 팁 — 숫자만 보여주지 말고 결론을 문장으로 함께 써주세요. 예) “식재 A는 ROP 이하(현재 18, ROP 25) — 오늘 40 발주 권장(리드 3일).”

 

결론: ‘보이는 재고, 스스로 도는 시스템’이 정답

보이는 재고, 스스로 도는 시스템 사진

재고 관리의 핵심은 스프레드시트 기술이 아니라 일관된 규칙과 역할 분리입니다. Items에 표준을 모으고, Movements에 모든 변화를 기록하고, Stocks에서 현재고를 계산해, Dashboard에서 결론을 빠르게 보여주면 시스템은 오래갑니다. 여기에 유효성·드롭다운으로 품질을 담보하고, 폼과 Apps Script로 입력·알림을 자동화하면, 재고는 더 이상 “감(感)으로 맞추는 영역”이 아닙니다. 부족 이전에 알림이 오고, 입출고는 폰으로 10초에 기록되며, 대시보드는 재고가 돈으로 바뀌는 속도를 명확히 보여줍니다.

실행 순서는 간단합니다. Day 1: 탭 구조와 유효성, Movements 입력 규칙 확정. Day 2: Stocks 현재고/ROP 비교, ROP 이하 표와 조건부 서식. Day 3: 폼 입력 + 재주문 알림 스크립트. Day 4: ABC/회전율/유통기한 차트로 대시보드 완성. Day 5: 권한/보호/버전 스냅숏 정비. 작은 시스템이라도 꾸준한 입력과 자동화의 반복이 쌓이면, 재고는 가장 예측 가능한 자산이 됩니다. 오늘 바로 시트를 열고 품목마스터를 만들며 첫 줄을 기록하세요. 내일부터 재고는 보이고, 움직이며, 스스로 관리됩니다.