설비(HVAC·배관) 출동 작업 원가 엑셀 계산표
청구서로는 이익이 나 보이는 출동도 이동 시간, 인건비 부담, 차량을 따지면 손해일 수 있어요. 이 통합 문서는 각 출동을 청구 방식대로(정액, 또는 시간 + 출장비 + 원가 구간별 마크업 부품) 가격을 매기고, 현장·이동 시간의 기사 부담 임금, 원가 부품, 출동당 차량비로 원가를 계산해요. 모든 작업에 매출총이익과 상태가 붙고, 한 달이 기사별·작업 유형별로 집계돼요.
- 시트:
- 7
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 원가에 따라 자동 조회되는 구간별 부품 마크업
- 같은 기록에서 정액 또는 시간·자재 가격, 재방문은 0원 청구
- 현장·이동 시간의 부담 인건비와 출동당 차량비
- 목표 대비 작업별·기사별·작업 유형별 매출총이익
구성
통합 문서에는 시트가 7개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| Start Here | 단가와 월 요약 | 청구 단가, 인건비 부담, 차량비, 출장비, 목표 이익률, 보고 월 | 작업 수, 매출, 작업 원가, 매출총이익과 %, 평균 객단가, 청구 시간, 인력 시간당 매출, 실효 부품 마크업, 목표 미달, 재방문, 최고 작업 유형 |
| Markup | 부품 마크업 구간(10) | 원가 기준과 마크업 % | 배수, 부품 이익률 |
| Techs | 기사(15) | 이름, 직급, 시급 | 시간당 부담 원가. 월 작업 수, 시간, 매출, 매출총이익, 이익률, 재방문 |
| Jobs | 출동당 한 행(300) | 날짜, 작업 번호, 고객, 기사, 작업 유형, 공종, 가격 방식, 현장 시간, 이동 시간, 부품 원가, 정액 가격, 상태 | 마크업 구간, 부품 가격, 노임, 출장비, 매출, 인건비, 차량비, 작업 원가, 매출총이익, 이익률, 이익 상태, 월 |
| Types | 작업 유형별 결과(10) | 작업 유형과 공종 | 작업 수, 매출, 매출총이익, 이익률, 평균 객단가, 평균 시간 |
| Lists | 드롭다운 | 가격 방식, 상태, 직급 | 없음 |
| Checks | 검사 6개 | 없음 | OK 또는 Review |
시작하기 전에
- 시간당 청구 단가, 출장비, 흔한 작업의 정액 가격을 알아 두세요.
- 임금에 대한 인건비 부담(급여세, 산재보험, 복리후생, 유급휴가)을 계산하세요. 25–40%가 흔해요.
- 출동당 차량비를 추정하세요. 연료, 차량 할부와 보험, 재고 손실을 월 출동 수로 나눈 값.
단계별 사용법
단가 설정
Start Here B6–B11: 시간당 청구 단가, 인건비 부담 %, 출동당 차량비, 출장비, 목표 매출총이익률, 보고 월.
마크업 구간 설정
Markup 탭: 오름차순 부품 원가 기준과 구간별 마크업. 싼 부품은 높게, 비싼 장비는 낮게.
기사 등록
Techs 탭: 이름, 직급, 시급. 시간당 부담 원가 = 시급 × (1 + 부담률).
출동 기록
Jobs 탭: 날짜, 작업 번호, 고객, 기사, 작업 유형, 가격 방식, 현장 시간, 이동 시간, 부품 원가, 정액 가격(정액 작업만), 상태. 오른쪽은 모두 계산돼요.
작업별 읽기
매출(정액, 또는 노임 + 출장비 + 마크업 부품, 재방문은 0), 작업 원가(현장·이동 부담 인건비, 부품, 차량), 매출총이익과 Loss / Below target / On target 상태.
월 읽기
Start Here에서 합계와 실효 부품 마크업, Techs 탭에서 기사별 매출·이익·재방문, Types 탭에서 어떤 작업 유형과 정액 가격이 제 몫을 하는지.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| Jobs | M5 304행까지 아래로 복사됨 | IF, LOOKUP | 마크업 구간 | J5 | 부품 가격 = 원가 × (1 + 이 값). |
| Jobs | N5 304행까지 아래로 복사됨 | IF | 부품 가격 | J5, M5 | 시간·자재 작업 매출의 일부예요. |
| Jobs | Q5 304행까지 아래로 복사됨 | IF | 매출 | L5, G5, K5, N5, O5, P5 | 매출총이익 = 이 값 − 작업 원가. |
| Jobs | R5 304행까지 아래로 복사됨 | IF, IFERROR, INDEX, MATCH | 인건비 | H5, I5, D5 | 대부분의 출동에서 가장 큰 원가예요. |
| Jobs | U5 304행까지 아래로 복사됨 | IF | 작업 원가 | R5, S5, T5 | 매출에서 빼서 매출총이익을 구해요. |
| Jobs | X5 304행까지 아래로 복사됨 | IF | 이익 상태 | V5, W5 | Start Here B24에서 세고 조건부 서식으로 색을 입혀요. |
| Techs | D5 19행까지 아래로 복사됨 | IF | 시간당 부담 원가 | C5 | 모든 작업 행이 조회해요. |
| Markup | D5 14행까지 아래로 복사됨 | IF | 부품 이익률 | B5 | 마크업 100%는 이익률 50%예요. 이 열이 둘을 혼동하지 않게 해요. |
| Start Here | B23 | IF, SUMIFS | 실효 부품 마크업 | — | 마크업이 낮은 장비 작업이 포함되어 최고 구간보다 낮아요. |
| Types | H5 14행까지 아래로 복사됨 | IF, IFERROR, AVERAGEIFS | 작업 유형별 평균 현장 시간 | — | 정액 가격을 정하거나 점검하는 기준이에요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(B5="", "", LOOKUP(J5,
Markup!$A$5:$A$14, Markup!$B$5:$B$14))근사 일치 LOOKUP: 작업의 부품 원가를 넘지 않는 가장 큰 Markup 탭 기준을 찾아 그 구간의 마크업을 돌려줘요.
부분별 설명
J5- 작업의 부품 원가.
Markup!$A$5:$A$14- 오름차순 기준.
Markup!$B$5:$B$14- 구간별 마크업 %.
예시 데이터로 보면: J5 = 125.04 → M5 결과 60.0%
어떻게 쓰이나: 부품 가격 = 원가 × (1 + 이 값).
바꾸는 방법: Markup 탭에서 구간을 고치고 A열은 작은 것부터 정렬해 두세요.
배우기: 엑셀 조회 수식: XLOOKUP, VLOOKUP, INDEX/MATCH 사용법 →=IF(B5="", "", J5*(1+M5))부품 원가 × (1 + 구간 마크업).
부분별 설명
J5- 부품 원가.
*(1+M5)- 마크업 적용.
예시 데이터로 보면: J5 = 125.04, M5 = 60.0% → N5 결과 200.06
어떻게 쓰이나: 시간·자재 작업 매출의 일부예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(B5="", "", IF(L5="Callback",
0, IF(G5="Flat rate", K5, N5+O5+P5)))재방문이면 0, 정액 작업이면 정액 가격, 그 외 마크업 부품 + 노임 + 출장비.
부분별 설명
L5="Callback"- 재방문: 무료.
G5="Flat rate" → K5- 정액 가격.
N5+O5+P5- 부품 가격 + 노임 + 출장비.
예시 데이터로 보면: L5 = Completed, G5 = Time and materials, K5 = —, N5 = 200.06, O5 = 217.5, P5 = 89 → Q5 결과 506.56
어떻게 쓰이나: 매출총이익 = 이 값 − 작업 원가.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 IF 함수 사용법 →=IF(B5="", "", (H5+I5)*IFERROR(INDEX(Techs!$D$5:$D$19, MATCH(D5, Techs!$A$5:$A$19, 0)),
0))현장 + 이동 시간 × Techs 탭에서 찾은 기사의 시간당 부담 원가.
부분별 설명
H5+I5- 현장 + 이동 시간.
MATCH(D5, Techs!$A$5:$A$19, 0)- 기사의 행.
INDEX(Techs!$D$5:$D$19, …)- 시간당 부담 원가.
IFERROR(…, 0)- Techs 탭에 없는 이름이면 0(Checks 탭이 표시).
예시 데이터로 보면: H5 = 1.5, I5 = 49.0%, D5 = R. Diaz → R5 결과 57.79
어떻게 쓰이나: 대부분의 출동에서 가장 큰 원가예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →=IF(B5="", "", R5+S5+T5)인건비 + 원가 부품 + 차량.
부분별 설명
R5- 부담 인건비.
+S5- 부품 원가.
+T5- 출동당 차량비.
예시 데이터로 보면: R5 = 57.79, S5 = 125.04, T5 = 32 → U5 결과 214.83
어떻게 쓰이나: 매출에서 빼서 매출총이익을 구해요.
바꾸는 방법: 작업별 허가·폐기 비용을 추적하면 열을 추가하세요.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(B5="", "", IF(V5<0, "Loss",
IF(W5<'Start Here'!$B$10, "Below target", "On target")))0 미만이면 Loss, 목표 이익률 미만이면 Below target, 그 외 On target.
부분별 설명
V5<0- 손해.
W5<'Start Here'!$B$10- 목표 이익률 미만.
예시 데이터로 보면: V5 = 291.73, W5 = 57.6% → X5 결과 On target
어떻게 쓰이나: Start Here B24에서 세고 조건부 서식으로 색을 입혀요.
바꾸는 방법: Start Here에서 목표를 바꾸세요.
배우기: 엑셀 IF 함수 사용법 →=IF(A5="", "", C5*(1+'Start Here'!$B$7))시급 × (1 + 인건비 부담).
부분별 설명
C5- 시급.
*(1+'Start Here'!$B$7)- 세금, 보험, 복리후생 추가.
예시 데이터로 보면: C5 = 38 → D5 결과 50.16
어떻게 쓰이나: 모든 작업 행이 조회해요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기 →=IF(A5="", "", B5/(1+B5))원가 기준 마크업을 가격 기준 이익률로: 마크업 ÷ (1 + 마크업).
부분별 설명
B5- 마크업 %.
/(1+B5)- 배수로 나눔.
예시 데이터로 보면: B5 = 1 → D5 결과 50.0%
어떻게 쓰이나: 마크업 100%는 이익률 50%예요. 이 열이 둘을 혼동하지 않게 해요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(SUMIFS(Jobs!$J$5:$J$304,
Jobs!$Y$5:$Y$304, B11)=0,
0, SUMIFS(Jobs!$N$5:$N$304,
Jobs!$Y$5:$Y$304, B11)/SUMIFS(Jobs!$J$5:$J$304,
Jobs!$Y$5:$Y$304, B11)-1)월 청구 부품 가격 ÷ 부품 원가 − 1: 모든 구간을 통틀어 실제로 달성한 마크업.
부분별 설명
SUMIFS(Jobs!$N…, Jobs!$Y…, B11)- 월 부품 가격.
SUMIFS(Jobs!$J…, Jobs!$Y…, B11)- 월 부품 원가.
…/…-1- 달성 마크업.
예시 데이터로 보면: B23 결과 38.5%
어떻게 쓰이나: 마크업이 낮은 장비 작업이 포함되어 최고 구간보다 낮아요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(A5="", "", IFERROR(AVERAGEIFS(Jobs!$H$5:$H$304, Jobs!$E$5:$E$304, A5, Jobs!$Y$5:$Y$304, 'Start Here'!$B$11),
""))보고 월 이 유형 작업의 현장 시간 평균.
부분별 설명
Jobs!$H$5:$H$304- 현장 시간.
Jobs!$E$5:$E$304, A5- 작업 유형 일치.
Jobs!$Y$5:$Y$304, 'Start Here'!$B$11- 보고 월.
IFERROR(…, "")- 작업이 없으면 빈칸.
예시 데이터로 보면: H5 결과 2.3
어떻게 쓰이나: 정액 가격을 정하거나 점검하는 기준이에요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 AVERAGEIF, AVERAGEIFS 함수 사용법 →팁과 한계
- 용량: 작업 300건, 기사 15명, 마크업 구간 10개, 작업 유형 10개. 분기마다 새 파일을 쓰거나 Ctrl+H로 범위($304)를 늘리세요.
- 정기 점검 줄을 보세요. 예시에서 $169 정액은 선임 기사, 이동 1시간, 차량을 겨우 감당해요. 정액은 시급이 아니라 부담 원가로 매겨야 해요.
- 이동 시간은 고객이 보지 않아도 실제 원가예요. 동선 계획이 가격 인상만큼 인력 시간당 매출을 올려요.
- 재방문은 0원 청구라 모두 순수한 이익 손실이에요. Techs 탭이 사람별로 세요.
- 보증 작업은 "Warranty (no charge)" 가격 방식을 쓰고, 제조사 노임 보상이 있으면 매출 수식을 추가하세요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com