이 데이터 세트에는 판매 수량(단위), 단위 판매 가격, 고정비의 구성 요소, 변동비의 구성 요소가 포함되어 있습니다.
1단계 – 다양한 비용 구성 요소 계산하기
순서:
- C7 셀을 선택하고 다음 내용을 입력합니다.
- SUM 함수를 더블클릭해 선택하거나 TAB 키를 누릅니다.
- C8:C12(고정비 범위)를 선택합니다.
- C7에 아래 수식을 입력한 뒤 ENTER 키를 누르면 총 고정비가 계산됩니다.
- C8에 아래 수식을 입력하여 총 변동비를 구합니다.
평균 변동비(제품 1단위당 변동비의 평균)는 총 변동비를 총 생산 단위 수로 나누면 구할 수 있습니다. 아래 수식을 활용하세요.
더 알아보기: 엑셀에서 수식으로 손익분기점 분석 계산하는 방법
2단계 – 손익분기점 매출 계산하기
순서:
- C10 셀을 선택하고 아래 수식을 입력한 후 ENTER 키를 누릅니다.
- C11 셀로 이동해 다음 수식을 입력하고 ENTER 키를 누릅니다.
계산 결과, 6,071개 단위를 판매했을 때 회사는 손익분기점에 도달하며, 이때의 매출액은 $30,357입니다.
더 알아보기: 엑셀에서 수식으로 손익분기점 매출 계산하는 방법
3단계 – 비용·수익·이익 표 만들기
순서:
- B4:G14 범위에 표의 기본 틀을 만듭니다.
- 판매 수량(Unit) 열에 오름차순으로 단위 값을 입력합니다.
- C5 셀로 이동해 아래 수식을 입력하고 ENTER 키를 누릅니다.
- C5 셀 우측 하단 모서리의 채우기 핸들(더하기 + 모양)을 찾아 더블클릭하면 나머지 셀이 자동으로 채워집니다.
- D5 셀을 선택하고 아래 수식을 입력합니다. 여기서는 판매 수량에 평균 변동비를 곱하며, 절대 참조($)를 사용했다는 점에 유의하세요.
- E5 셀에는 변동비에 고정비를 더해 총비용(Total Cost)을 구합니다.
- 매출(Revenue)은 B5의 판매 수량에 'Break Even' 워크시트 C5의 단위 판매 가격을 곱해 구합니다. F5 셀에 아래 수식을 입력하고 ENTER 키를 누릅니다.
- G5 셀에는 아래 수식을 입력해 이익(Profit)을 구하고 ENTER 키를 누릅니다.
4단계 – 손익분기점 차트 삽입하기
순서:
- 이익(Profit) 열을 제외한 표 전체를 선택합니다.
- 삽입 탭에서 분산형(X, Y) 또는 거품형 차트 삽입을 클릭합니다.
- 곡선과 마커가 있는 분산형(Scatter with Smooth Lines and Markers)을 선택합니다.
그러면 차트가 표시됩니다.
- 아무 계열 선의 마커 위에서 마우스 오른쪽 버튼을 클릭합니다.
- 바로 가기 메뉴에서 데이터 계열 서식…을 클릭합니다.
데이터 계열 서식 창에서:
- 채우기 및 선(Fill & Line)을 클릭합니다.
- 마커(Marker)를 선택합니다.
- 마커 옵션을 확장해 없음(None)을 선택합니다.
- 채우기(Fill) 섹션을 확장해 채우기 없음(No fill)을 선택합니다.
나머지 계열에도 동일한 방법을 적용합니다.
고정비와 변동비 선을 시각적으로 구분하려면:
- 고정비 선을 마우스 오른쪽 버튼으로 클릭합니다.
- 데이터 계열 서식…을 선택합니다.
데이터 계열 서식 창에서:
- 채우기 및 선을 클릭합니다.
- 선(Line) 항목에서 대시 종류(Dash type)를 긴 대시 점선(Long Dash Dot)으로 설정합니다.
완성된 결과물은 아래와 같습니다.
5단계 – 차트에 손익분기점 표시하기
순서:
- 플롯 영역 안에서 마우스 오른쪽 버튼을 클릭합니다.
- 데이터 선택…을 선택합니다.
- 데이터 원본 선택 대화 상자에서 추가(Add)를 클릭합니다.
계열 편집(Edit Series) 창에서:
- 계열 이름(Series name)에 BEP를 입력합니다.
- 'Break Even' 워크시트로 이동해 계열 X 값에는 C10을, 계열 Y 값에는 C11을 참조하도록 지정합니다.
- 확인을 클릭합니다.
- 확인을 한 번 더 클릭해 창을 닫습니다.
- 새로 추가된 계열의 데이터 계열 서식을 열고 마커 옵션(Marker Options)으로 이동합니다.
- 기본 제공(Built-in)을 선택하고 유형(Type)에서 원형 마커를 고릅니다.
- 마커 크기(Size)를 8로 설정합니다.
손익분기점이 강조된 최종 결과물입니다.
6단계 – 손익분기점 기준선 적용하기
순서:
- 십자(+) 모양의 차트 요소(Chart Element) 아이콘을 클릭합니다.
- 오차 막대(Error Bars) 항목 오른쪽의 화살표를 클릭합니다.
- 추가 옵션…을 선택합니다.
오차 막대 서식 작업 창에서:
- 오차 막대 옵션(Error Bar Options)을 선택합니다.
- 방향(Direction)은 음수(Minus), 끝 스타일(End Style)은 캡 없음(No Cap)으로 설정합니다.
- 오차량(Error Amount)에서 백분율(Percentage)을 선택하고 100%로 지정합니다.
- 오차 막대 옵션의 드롭다운 화살표를 클릭합니다.
- "BEP" 계열 Y 오차 막대를 선택한 뒤, 세로(Y) 오차 막대에도 동일한 절차를 반복합니다.
세로·가로 기준선이 적용된 최종 차트입니다.
목표값 찾기(Goal Seek) 도구로 손익분기점 판매 가격 계산하기
순서:
- 매출(Revenue)을 계산하려면 C9 셀에 아래 수식을 입력합니다.
- C10 셀에는 아래 수식을 사용해 이익(Profit)을 구합니다.
손익분기점이란 회사나 사업체가 이익을 내기 시작하기 직전의 상태, 즉 이익이 0이 되는 지점을 의미합니다.
- 데이터 탭으로 이동합니다.
- 예측(Forecast) 그룹에서 가상 분석(What-If Analysis)을 클릭합니다.
- 목표값 찾기(Goal Seek)를 선택합니다.
- 목표값 찾기 대화 상자에서 아래와 같이 참조 셀을 지정하고 확인을 클릭합니다.
C5 값을 변경하여 C10(이익)이 0이 되도록 설정합니다.
계산 결과, 손익분기점에서의 단위 판매 가격은 $3.90입니다.
직접 연습해 보세요
아래 예제 파일로 직접 연습해 보세요.
연습용 통합 문서 다운로드
아래의 엑셀 통합 문서를 다운로드해 학습에 활용하세요.
함께 보면 좋은 관련 글
- 엑셀에서 손익분기점 계산하는 방법
- 엑셀에서 복수 제품 손익분기점 분석하는 방법
- 엑셀에서 목표값 찾기로 손익분기점 분석하는 방법
<< 손익분기점 분석 엑셀 가이드로 돌아가기 | 금융을 위한 엑셀 | 엑셀 배우기
무료 고급 엑셀 연습 문제와 해답을 받아보세요!