프로젝트 개요
10개 법인의 ERP 기초 데이터로부터 다차원 손익계산서(P&L)를 자동 산출하는 데이터 파이프라인이다. 반입처/판매처/제품군별 원가 배부, 수율 산출, GP 산정까지 9단계 SQL 쿼리로 구성되며, Python으로 Excel 기초 데이터를 자동 수집하고 Airflow가 전체 파이프라인을 오케스트레이션한다.
회사 프로젝트로 개인 레포지토리에는 소스 코드가 공개되어 있지 않습니다.
해결한 핵심 문제들
문제 1: 손익 산출 과정이 수작업 Excel에 의존한다
각 법인의 경영관리팀은 ERP에서 구매일보/판매일보/재고수불부 등을 Excel로 다운로드한 뒤, 수작업으로 원가 배부와 손익 계산을 수행하고 있었다. 법인마다 계정 구조와 원가 배부 방식이 달라 표준화가 어려웠고, 월 마감에 3~5일이 소요되었다.
해결: 9단계 SQL 파이프라인으로 표준화
각 법인의 손익 산출 로직을 PostgreSQL 쿼리로 정형화했다. 법인별로 동일한 P1~P9 단계 구조를 따르되, 법인 특성에 맞는 분류 체계와 배부 로직을 적용한다:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
P1. Input Data (기초 테이블 생성)
│ 구매일보, 판매일보, 분담금, 재고수불부 등 ERP raw 데이터
│
P2. 기초데이터 가공
│ 거래처 매핑, 반입채널/거래구분/원재료분류 태깅
│ 매핑 테이블 JOIN으로 다차원 분류 코드 부여
│
P3. 물량배부
│ 원재료 → 투입 → 생산 물량 흐름 추적
│ 수율 기반 반입처별 생산량 안분
│
P4-1. 재료비
│ 반입처별 × 원재료별 매입액 산출
│ 안분 배부 (예: 오산시청 매입액을 품목/반입구분별 안분)
│
P4-2. 비용 (운반비, 폐기물처리비, 기타경비)
│ 매입운반비, 반출운반비, 감가상각비 배부
│
P5. 수입
│ 용역수입 (반입처별 처리 수량 × 단가)
│ 제품매출, 지원금수입 (기본/추가정산)
│
P6. 다차원 GP (Gross Profit)
│ 판매처 × 제품군별 매출액 - 매출원가 = GP
│
P7. 판관비
│ 판매관리비 배부
│
P8. PL DB
│ 반입처별 다차원 손익 DB (Transposed)
│ 수율 산출, 단위경제 (UE) 지표 계산
│
P9. 손익계산서
최종 P&L 형태로 집계
매출액, 매출원가(변동/고정), CM, GP 산출
문제 2: 다차원 원가 배부 로직이 복잡하다
단순히 “이 법인의 원가는 얼마”가 아니라, 반입처 × 반입성상 × 제품소분류 조합별로 원가를 추적해야 한다. 예를 들어 “오산시청에서 반입된 혼합플라스틱이 선별 후 압축PET로 판매될 때의 톤당 원가”를 산출해야 한다.
해결: 수율 기반 물량배부 + CTE 체이닝
SQL의 CTE(Common Table Expressions)를 적극 활용하여 복잡한 배부 로직을 단계별로 분리했다:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- P3 물량배부: 반입처별 원재료 → 생산량 흐름 추적
WITH 원재료_상세 AS (
SELECT 년, 월, 반입채널, 거래처명, 반입구분, 원재료분류, SUM(수량) AS 수량
FROM 오산.erp_rm오산_구매일보_af
WHERE 품목계정 = '2.원재료'
GROUP BY ...
),
-- 수율가정: 투명플라스틱, 혼합플라스틱, Direct물품 각각의 생산수율 적용
반입처별_매입량 AS (
SELECT ...,
CASE WHEN 원재료분류 = '투명플라스틱' THEN '생산수율 (투명플라스틱)'
WHEN 원재료분류 = '혼합플라스틱' THEN '생산수율 (혼합플라스틱)'
ELSE '생산수율 (Direct물품)'
END AS 수율가정
FROM ...
)
핵심 손익 항목:
| 항목 | 산출 방식 |
|---|---|
| 매출액 | 선별판매수익 + 용역수입 + 지원금수입(기본/추가) |
| 매출원가(변동) | 재료비 + 매입운반비 + 기타운반비 + 폐기물처리비 |
| CM (공헌이익) | 매출액 - 변동 매출원가 |
| 매출원가(고정) | 기타경비 + 감가비 + 배부차이 |
| GP (매출총이익) | CM - 고정 매출원가 |
| 단위경제(UE) | 각 항목 ÷ 판매수량 (톤당 지표) |
문제 3: Excel 기초 데이터 수집이 수작업이다
각 법인은 ERP에서 추출한 Excel 파일(구매일보, 분개장, 운반비 실적 등)을 매월 전달한다. 이 파일들의 시트 구조, 헤더 위치, 데이터 범위가 법인마다 다르고, 같은 법인이라도 파일마다 다를 수 있다.
해결: openpyxl 기반 범용 Excel 파서
법인별 Excel 파일 구조에 맞는 Python 수집 함수를 구현했다. 핵심 패턴:
1
2
3
4
5
6
7
8
9
10
11
12
13
def collect_repair_costs_from_excel(folder_path, file_name, required_columns):
wb = load_workbook(file_path, read_only=True, data_only=True)
for ws in wb.worksheets[1:]: # 첫 시트 제외
header_row = [cell.value for cell in ws[1][:11]]
col_index_map = {col: idx for idx, col in enumerate(header_row)
if col in required_columns}
# 필수 컬럼 누락 시 skip (방어 로직)
if not all(col in col_index_map for col in required_columns):
continue
for row in ws.iter_rows(min_row=2, values_only=True):
if row[col_index_map['전표번호']] is None:
break # 데이터 끝 감지
...
수집 대상 데이터:
| 법인 | 수집 항목 | 처리 로직 |
|---|---|---|
| RM오산 | 구매일보 → CSV | 다차원 분류 코드 자동 태깅 |
| 하이원 | 구매일보, 생산일보 | 생산량 데이터 시트별 파싱 |
| 화성/H2 | 재무회계 데이터 | 계정과목 기반 비용 분류 |
| 청송 | 분개장(수선비), 운반비 실적 | 차변-대변 계산, 사업부문별 집계 |
| 청송 | 운반비 지원 내역 | 다중 시트(연도별) × 12개월 컬럼 파싱 |
운반비 수집의 경우, 거래처별 톤당 운반비 단가 데이터와 실중량 데이터를 조인하여 금액을 자동 산정한다:
1
2
fin_df = pd.merge(trans_df, unit_price_df, on=['년', '월', '운반업체'])
fin_df['금액'] = fin_df['실중량'] * fin_df['톤당운반비']
문제 4: 월 마감 시 10개 법인의 파이프라인을 순서대로 실행해야 한다
P1~P9 쿼리는 순서 의존성이 있다. P3(물량배부)은 P2(기초데이터)가 완료되어야 하고, P8(PL DB)은 P3~P7이 모두 완료되어야 한다. 10개 법인을 수작업으로 순서대로 실행하면 실수가 발생하기 쉽다.
해결: Airflow DAG 오케스트레이션
Apache Airflow로 각 법인의 P1~P9 단계를 DAG(Directed Acyclic Graph)로 정의하여 의존 관계를 명시하고, 월 마감 시 원클릭으로 전체 파이프라인을 실행한다.
1
2
3
4
5
[P1 Input] → [P2 기초] → [P3 물량배부] → [P4-1 재료비]
→ [P4-2 비용 ]
→ [P5 수입 ]
↓
[P6 다차원GP] → [P7 판관비] → [P8 PL DB] → [P9 손익계산서]
대상 법인 (10개)
| # | 법인 | 사업 유형 | 특이사항 |
|---|---|---|---|
| 1 | RM오산 | 재활용 선별 | 반입처 × 선별품 다차원 원가, 수율 산출 |
| 2 | 하이원 | 재활용 | 생산일보 연동 |
| 3 | 화성 | 재활용 | 재무회계 기반 비용 분류 |
| 4 | H2 | 수소 | 재무회계 연동 |
| 5 | EEK | - | - |
| 6 | 청송 | 소각/건조 | 사업부문별 수선비/운반비 배부 |
| 7 | 신진유화 | 유화 | - |
| 8 | 가나 | - | - |
| 9 | KJ | 재활용 (다지역) | 화성/의왕/정남 3개 거점 통합 P&L |
| 10 | 거단 | - | - |
프로젝트 규모
| 항목 | 수치 |
|---|---|
| SQL 쿼리 파일 | 340+ |
| Python 노트북 | 22개 |
| 대상 법인 | 10개 |
| P&L 산출 단계 | 9단계 (P1~P9) |
| 수율 분석 노트북 | 10+ (클러스터링, 이상치 제거, EDA) |
기술 스택
| 분류 | 기술 | 용도 |
|---|---|---|
| DB | PostgreSQL | 손익 산출 쿼리 실행, 결과 저장 |
| SQL | CTE, Window Function, CROSS JOIN LATERAL | 다단계 배부, 피벗, 물량 흐름 추적 |
| Python | openpyxl, pandas | Excel 데이터 자동 수집/변환 |
| Orchestration | Apache Airflow | DAG 기반 파이프라인 자동화 |
| 분석 | scikit-learn (클러스터링) | 수율 이상치 감지 및 그룹핑 |