정산·송금·인사·업무폰·법인카드를 하나의 원천 데이터에서 파생시켜 누락을 구조적으로 차단하고, 당월 인도 실적을 끌어올리는 백오피스 운영 설계.
월 100건·영업 30명 규모에서는 "한 시트에 다 적는 파일"이 아니라, 하나의 원천에서 전부 파생되는 구조여야 합니다. 세 가지 원리로 누락을 막습니다.
계약 정보는 계약 마스터에 딱 한 번만 입력. 정산·송금·실적·장부는 전부 참조(수식)만 하므로 옮겨 적다 빠지는 누락이 원천 차단됩니다.
모든 계약은 가계약→확정→심사→인도대기→인도완료→정산완료 중 하나. "끝난 줄 안 끝난 건"이 상태로 드러납니다.
오늘 칠 일을 내가 찾는 게 아니라 To-Do 시트가 자동 추출. 빈칸·기한임박·상태불일치를 수식이 잡아냅니다.
9개 시트. 직접 입력은 4개뿐이고 나머지는 자동 파생입니다.
| # | 시트명 | 역할 | 유형 |
|---|---|---|---|
| 1 | 계약 마스터(DB) | 모든 업무의 원천. 1행=1계약 | 직접입력(유일) |
| 2 | 사원 마스터(인사) | 사번 기준 직원·직급 정보 | 직접입력 |
| 3 | 정산 | 수수료 계산·인도일자·정산상태 | 자동 |
| 4 | 송금요청 | 선납금·페이백 요청/완료 | 입력+연결 |
| 5 | 업무폰 장부 | 단말기·요금제·사번 매칭 | 직접입력 |
| 6 | 법인카드 내역 | 사용 건·증빙·계약 연결 | 직접입력 |
| 7 | 📊 대시보드 | 당월 계약/인도 실적·목표갭·파이프라인 | 자동 |
| 8 | ⚠️ To-Do / 누락감시 | 빈칸·기한·불일치 자동 추출 | 자동 |
| 9 | 설정 | 수수료율표·직급률·드롭다운·목표값 | 직접입력 |
전체 시스템은 계약ID와 사번 단 두 키로 엮입니다. 이 둘만 일관되면 30명·100건+에도 안 깨집니다.
[사원 마스터] ──사번──┐ ▼ [업무폰 장부]──사번──▶ [계약 마스터(DB)] ◀──계약ID──[송금요청] │ 계약ID ├──────────▶ [정산] └──────────▶ [법인카드] │ ▼ [대시보드] · [To-Do 감시]
2606-0001 = 연월+일련번호, 자동 채번)와 사번(S0142)은
반드시 자동/드롭다운으로만 입력 — 손타이핑 금지로 키 깨짐을 차단합니다.입력 거의 없음. 인도완료 건만 자동으로 끌어와 수수료·인도일자 표시. 인도일 없으면 "미확정". 수수료 공란·인도일 누락이 빨갛게 표시.
계약ID 선택 시 고객·금액 자동. 요청했는데 미완료, 인도됐는데 페이백 미지급 건 자동 경고. 정산 시트가 이 완료액을 합산해 차감.
단말·요금제·사번 매칭. 퇴사자(재직상태=퇴사)인데 폰 미회수면 경고.
사용일·가맹점·금액·용도·증빙유무. 증빙 없는 건·미분류 건 월말 자동 추출.
조건부 서식 + 자동 추출로 아래를 매일 잡아냅니다. 빠진 걸 내가 기억하는 게 아니라, 아침에 To-Do만 열면 다 떠 있습니다.
| 상황 | 탐지 규칙 | 결과 |
|---|---|---|
| 필수칸 공란 | 계약상태=확정인데 사번/차량가 빈칸 | 빨강 |
| 인도–정산 불일치 | 인도완료=O 인데 정산상태≠완료 | To-Do |
| 인도 기한 임박 | 인도예정일 D-3 이내 & 미완료 | 상단 경고 |
| 심사 정체 | 심사중 N일 초과 | 재촉 대상 |
| 송금 미완 | 요청 O · 완료 X · 예정일 경과 | 빨강 |
| 페이백 누락 | 인도완료인데 페이백 미지급 | 경고 |
| 폰 미회수 | 퇴사자 단말 미회수 | 경고 |
| 카드 증빙 누락 | 증빙=X | 월말 목록 |
| 키 오류 | 계약ID 중복 / 사번 오타 | 빨강 |
캐피탈사·시점·프로모션마다 달라지는 율은 율표에서 내가 직접 수정. 기준금액은 차량가, 정산은 인도 시점.
[계약 1건 · 인도완료] 캐피탈수수료 = 차량가 × 율(캐피탈사 × 인도일 적용기간 × 프로모션) ※ or 정액 부가세제외수수료 = 캐피탈수수료 ÷ 1.1 [영업사원 · 당월 합산] 수수료합 = Σ(부가세제외수수료) ← 인도완료 건만 소계 = 수수료합 − 선납금총액 − 페이백총액 실지급 = MAX(소계 × 직급률, 0) ← 음수면 0
| 캐피탈사 | 적용기간 | 프로모션 | 산정 | 율/정액 |
|---|---|---|---|---|
| A캐피탈 | 06.01~06.30 | 일반 | 율 | 3.0% |
| A캐피탈 | 06.01~06.30 | 프로모션 | 율 | 3.5% |
| B캐피탈 | 06.01~12.31 | 일반 | 율 | 2.8% |
| C캐피탈 | 06.15~07.15 | 한정 | 정액 | 700,000 |
율 변경 시 새 기간 행만 추가 → 과거 정산은 그대로 보존, 정산식은 안 건드림.
| 직급 | 직급률 |
|---|---|
| 사원 | 30% |
| 대리 | 35% |
| 과장 | 40% |
| 차장 | 45% |
차량가·캐피탈율·직급·페이백/선납금을 넣으면 실지급액이 즉시 계산됩니다.
| 차량가 (원) | 산정 | 율(%) / 정액(원) | 부가세제외 |
|---|
※ 부가세제외 = 부가세 포함 시 캐피탈수수료÷1.1, 미포함 시 그대로. 소계가 음수면 실지급 0(음수 불가 구조).
핵심 통찰: 계약 ≠ 인도(매출 인식). 계약은 당월에 해도 심사·인도가 밀리면 인도매출이 차월로 샙니다. 백오피스 최대 레버 = 계약→인도 사이 병목 제거.
| 지표 | 정의 | 백오피스 레버 |
|---|---|---|
| 계약수 | 당월 계약 건수 | 가계약→확정 전환 독려, 취소율↓ |
| 계약매출 | Σ 차량가/계약금액 | 고가·고율 캐피탈 비중 모니터링 |
| 인도수 | 당월 인도완료 건수 | 인도 병목 제거 (최대 레버) |
| 인도매출 | 당월 인도건 금액 | 월말 인도 가능 건 당겨오기 |
| 전환율 | 인도수/계약수 | 영업사원별 격차 → 코칭 근거 |
| 파이프라인 | 심사중·인도대기 건/금액 | 차월 실적 선행지표 |
영업을 안 뛰어도, 데이터로 병목을 먼저 발견해 영업이 놓친 인도를 당월에 마감시키는 게 실적 기여의 핵심입니다.