포캐스트 시스템 개발 기록 (1) 계획과 실적을 한 화면에 맞대기까지

계획은 엑셀에, 실적은 ERP에 따로 있었다 MotherK에서는 영업·MD 담당자들이 매달 품목별, 채널별로 "이번 달에 얼마나 팔 것 같다"는 판매계획을 세운다. 사내에서는 이걸 포케스트(Forecast)라고 불렀다. 문제는 계획과 실적이 서로 다른 곳에 있었다는 점이다. 계획은 담당자가 사내 관리 시스템(mkware)의 포케스트 입력 화면에 넣고, 실제 판매량은 ERP에서 월말 배치로 내려받은 채널별 실적 테이블에 쌓였다. 재고는 또 다른 집계 테이블에 있었다. 그래서 "이 품목은 계획 대비 얼마나 팔렸나", "지금 재고로 몇 달을 버틸 수 있나", "어느 팀의 계획이 잘 맞나" 같은 질문에 답하려면 매번 엑셀을 여러 장 열어 VLOOKUP을 돌려야 했다. 나는 mkware의 포케스트 모듈을 맡아 입력 화면부터 손보기 시작했고, 결국 계획·실적·재고를 한 화면에서 맞대어 보는 재고 운영 관리표와 대시보드, 팀별 적중률 페이지까지 만들게 됐다. 이 시리즈는 그 과정을 세 편으로 나눠 정리한다. 첫 편은 전체 구조와, 기반 데이터가 무너졌을 때 소스를 갈아엎은 이야기다. 두 번째 편은 채널 합계가 끝까지 맞지 않던 문제, 세 번째 편은 50초 걸리던 페이지를 0.2초대로 줄인 캐시 이야기다. 해야 했던 일과 걸림돌 요구사항 자체는 단순했다. 담당자가 품목 × 채널 × 월 단위로 계획 수량을 입력하고, 자기가 맡은 품목만 볼 수 있을 것 계획 대비 판매 달성률, 남은 계획 수량, 현재고, 적정재고, 예상 품절일을 품목별로 한 줄에 보여줄 것 과잉재고·품절위험·저회전 품목을 한눈에 볼 수 있는 대시보드와 엑셀 다운로드 팀·담당자별로 계획이 얼마나 맞았는지 숫자로 볼 수 있을 것 어려웠던 건 데이터 쪽이었다. 실적 테이블은 월 마감 후에야 적재되니 진행 중인 달은 텅 비어 있었다. 계획은 채널을 8종으로 나눠 받는데 실적 테이블은 채널마다 컬럼이 따로 있어 32종(나중에 47종)이었다. 재고 테이블은 다른 배치가 채우고 있었는데, 그 배치가 언제 멈출지 아무도 알 수 없었다. 실제로 멈췄다. 구조 최종적으로 자리 잡은 구조는 이렇다. 계획 입력 : 담당자별 계획 테이블(담당자 × 품목 × 채널 × 월). 팀 설정에서 팀별 담당 품목그룹과 채널을 지정하고, 서버에서 그 범위 밖 저장을 막는다. 월 실적 : ERP에서 월 마감 후 적재되는 품목 × 채널 컬럼 테이블. BOM 전개(번들을 구성품으로 풀어낸) 기준이다. 회계서버 지표 API : 재고 스냅샷, 최근 평균출고, 월중 누적판매, 채널별 판매, 입고 수량을 JSON으로 받는다. PHP 쪽에서는 acc_metrics.php 헬퍼 하나로 감쌌다. 화면 : 품목 리스트(forecast3), 재고 운영 관리표(jego12), 재고운영 대시보드(jego13), 팀별 적중률 페이지. 각각 엑셀 내보내기를 붙였다. 새로 DB 테이블을 크게 만들기보다 이미 있는 원천을 맞대는 쪽을 택했다. 원천 데이터는 다른 팀과 배치가 관리하고 있었고, 내가 사본을 하나 더 만들면 그 사본이 또 하나의 "정체될 수 있는 테이블"이 되기 때문이다. 대신 원천마다 신뢰할 수 있는 범위를 정하고, 화면에 "이 숫자는 어디서 왔는지"를 적어 두는 방식을 썼다. 시행착오 지표가 전부 0이 됐다: 정체된 집계 테이블 처음 품목 리스트의 재고·평균출고·소진개월·회전율은 내부 집계 테이블에서 읽었다. ERP 판매 데이터를 BOM 전개해 품목별로 합쳐 둔 테이블이었다. 어느 날 리스트를 열어 보니 재고와 평균출고가 줄줄이 0으로 나왔다. 확인해 보니 그 테이블이 2025년 12월 이후 갱신되지 않고 있었다. 테이블을 채우던 배치가 멈춘 것이다. 배치를 살리는 방법도 있었지만, 그러면 같은 일이 또 생겼을 때 똑같이 모른 채 지나가게 된다. 그래서 숫자의 원천인 회계서버에 직접 묻기로 했다. 회계서버 쪽에 /api/forecast-metrics 라는 JSON API를 새로 만들고, 재고는 월말 기준 최신 스냅샷, 평균출고는 최근 3개월 월평균으로 내려주게 했다. mkware에서는 acc_fetch_forecast_metrics() 하나로 이 API를 호출하고, 리스트와 엑셀 내보내기가 같은 함수를 쓰게 했다. 같은 날 재고 관련 화면(jego2·jego3·jego5·jego6)도 순서대로 회계서버 기준으로 옮겼다. 옮기면서 보니 jego5는 접속할 때마다 테이블 전체를 DELETE 후 INSERT하고 ALTER까지 하고 있었고, jego2·jego3는 행마다 파트·리드타임·업체명을 상관 서브쿼리로 따로 조회하고 있었다. 이 부분은 품목코드를 모아 IN 조회 한 번으로 바꿨다. 회계 API 호출에는 연결 3초, 전체 8초 타임아웃과 5분 파일 캐시를 걸어, 회계서버가 느려도 페이지 전체가 같이 멈추지 않게 했다. 목록에서 사라지는 상품들 소스를 바꾸고 이틀 뒤, 계획을 입력했는데 리스트와 엑셀에서 상품이 안 보인다는 문의가 들어왔다. 원인은 두 가지였다. 리스트의 기준 테이블이 "담당자 배정 테이블" 하나였다. 담당자가 배정되지 않았지만 당월 계획은 들어가 있는 상품은 기준 테이블에 없으니 아예 목록에 오르지 못했다. 엑셀 내보내기는 품목 마스터를 INNER JOIN 하고 있었다. 마스터에 아직 등록되지 않은 신규 상품은 조인에서 떨어져 나갔다. 기준을 "담당자 배정 테이블 UNION 당월 계획 테이블"로 바꾸고, 품목 마스터는 LEFT JOIN 으로 붙였다. 그러자 이번엔 상품명이 빈 행이 생겼다. 마스터에 없는 상품이니 당연했다. 처음에는 가격표 API에서 상품명과 품목그룹을 보완했고, 나중에 재고 운영 관리표를 만들 때는 회계 재고 스냅샷의 품목 정보로 채우도록 바꿨다. 그래도 못 찾는 코드는 '품목 마스터 미등록' 배지를 달아 화면과 엑셀에 그대로 드러냈다. 빈칸으로 두면 사람들은 버그로 읽지만, 배지가 있으면 "마스터 등록을 해야 하는구나"로 읽는다. 문자열 'null' 이 품목코드로 들어온 오염 행도 이때 집계에서 빼도록 했다. "적중률"의 정의를 정하는 일 팀별 적중률 페이지를 만들 때 가장 오래 고민한 건 코드가 아니라 정의였다. 달성률(실적 ÷ 계획)은 이미 있었는데, 달성률은 과대예측과 과소예측이 서로 상쇄된다. A 채널에서 2배 팔고 B 채널에서 하나도 못 팔아도 합치면 100%가 나올 수 있다. 그래서 적중률은 품목·채널 조합마다 절대오차를 구해 계획 합계로 나누는 방식(1 − WMAPE)으로 정했다. 달성률 100%인데 적중률 50%인 예시를 화면 하단 설명 카드에 직접 넣어 둔 것도 이 차이를 설명하기 위해서였다. 두 번째 문제는 한 조합에 여러 사람이 계획을 넣는 경우였다. 같은 월 × 품목 × 채널에 두 명 이상이 계획을 넣은 비율이 월별로 최대 19%였다. 실적을 각자에게 통째로 붙이면 실적이 두 번 세어지고, 한 명에게만 붙이면 다른 사람은 실적 0이 된다. 실적을 각자의 계획 비중대로 나눠 붙이기로 했다. 계획이 아예 없는 조합의 실적은 어느 팀에도 붙이지 않고 '계획 미수립 실적'으로 따로 모았다. 이 숫자가 크면 계획을 세우지 않은 채 팔리고 있는 품목이 많다는 뜻이라, 그 자체로 쓸모가 있었다. 핵심 코드: 안분과 적중률 집계 라이브러리의 핵심 부분이다. 조합별 계획 합계를 구하고, 실적을 계획 비중대로 나눈 뒤, 나눈 실적과 각자 계획의 절대오차를 팀·담당자·월·채널 버킷에 동시에 더한다. function fa_hit(float $absErr, float $plan): ?float { if ($plan <= 0) { return null; } return max(0.0, 1 - $absErr / $plan); // 1 − WMAPE, 0 미만은 0 } foreach ($byCh as $chKey => $entries) { $planTotal = array_sum(array_column($entries, 'cnt')); if ($planTotal <= 0) continue; $actual = (float) ($actualMap[$ym][$code][$chKey] ?? 0); foreach ($entries as $e) { $share = $e['cnt'] / $planTotal; // 계획 비중 $aPart = $actual * $share; // 안분된 실적 $absErr = abs($aPart - $e['cnt']); fa_add($teams[$e['dept']], $e['cnt'], $aPart, $absErr); fa_add($people[$e['id']], $e['cnt'], $aPart, $absErr); } } 버킷에는 계획·실적·절대오차 합계만 쌓고, 비율은 마지막에 한 번만 계산한다. 비율을 미리 계산해 평균을 내면 계획이 작은 조합의 오차가 큰 조합과 같은 무게를 갖게 되기 때문이다. 이 로직은 화면과 엑셀(6개 시트)이 같이 쓰도록 라이브러리로 분리했고, 이후 매출 부서 평가 점수에 팀별 적중률을 섞을 때도 그대로 가져다 썼다. 결과 계획을 넣은 담당자는 자기 품목의 채널별 달성률, 잔여 계획, 예상 품절일, 관리 상태(부족·주의·적정·과다·과잉)를 한 줄로 본다. 재고 담당은 대시보드에서 과잉재고 TOP, 품절위험, Forecast 과대/과소, 품목그룹별 재고금액, 단종·저회전 품목을 보고 같은 조건으로 엑셀을 받는다. 팀장은 팀·담당자별 적중률과 편향(과대예측인지 과소예측인지)을 월별 추이로 본다. 담당 품목 지정에 팀 범위를 적용한 뒤로는 다른 팀 품목이 전체 선택으로 섞여 들어가는 일도 서버에서 막힌다. 회고 가장 크게 배운 건 "남이 채우는 테이블을 읽는 화면은 그 테이블이 멈추는 날 같이 멈춘다"는 점이다. 지표가 0으로 나오는 건 에러가 아니라서 아무 경고도 뜨지 않았다. 원천을 회계서버로 옮긴 뒤에도 이 걱정은 남아서, 결국 월 실적 적재를 점검하는 크론까지 만들게 됐다(3편에서 다룬다). 다시 한다면 적중률 정의를 먼저 문서로 합의하고 화면을 만들겠다. 이번에는 화면을 먼저 만들고 "이 숫자가 왜 이렇게 낮냐"는 질문을 받으면서 설명 카드를 세 장으로 늘렸다. 정의가 낯선 지표일수록 계산식과 읽는 법을 화면 안에 같이 두는 게 질문을 가장 많이 줄였다. 기간: 2025.01 ~ 2026.09 기술: PHP, MySQL, PDO, 회계서버 JSON API, PhpSpreadsheet, SimpleXLSXGen, JavaScript 관련 프로젝트: 포캐스트 시스템