엑셀로 만드는 제품 단가 변동 손익 영향 분석(Sensitivity Analysis) 모델링

기업의 원가 관리나 재무팀에서 가장 자주 하는 질문 중 하나는 "원재료비가 5% 오르면 영업이익은 얼마나 깎일까?" 또는 "판매가를 3% 인상하면 목표 이익을 달성할 수 있을까?"입니다. 오늘은 복잡한 계산기 없이도 단가 변동에 따른 손익 영향을 한눈에 파악할 수 있는 시뮬레이션 시트를 만들어 보겠습니다.

1. 손익 분석 모델의 핵심 변수

정확한 시뮬레이션을 위해 다음의 4가지 요소가 필요합니다.

• 판매 수량 (Volume): 변하지 않는 고정값으로 가정
• 현재 단가 (Current Price): 현재 판매가 및 구매가
• 변동률 (Variance %): 테스트하고 싶은 변동 수치
• 공헌 이익 (Contribution Margin): 매출액 - 변동비

2. 단가 변동 영향도 계산 매크로

수많은 제품군의 단가를 일일이 수정해보기 힘들 때, 특정 카테고리의 단가를 일괄적으로 변동시켜 전체 손익에 미치는 영향을 계산해주는 매크로입니다.

Alt + F11을 눌러 모듈에 아래 코드를 복사해 보세요.

Sub SimulatePriceImpact()
    Dim ws As Worksheet
    Dim ChangeRate As Double
    Dim LastRow As Long
    Dim i As Long
    
    Set ws = ActiveSheet
    
    ' 1. 사용자로부터 변동률을 입력받습니다 (예: 0.05 입력 시 5% 인상)
    ChangeRate = InputBox("시뮬레이션할 단가 변동률을 입력하세요 (예: 0.05)", "손익 시뮬레이션")
    
    ' 2. 데이터 범위 확인 (B열: 현재 단가, C열: 시뮬레이션 결과 가정)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    ' 3. 변동률 적용 계산
    For i = 2 To LastRow
        ' 현재 단가에 (1 + 변동률)을 곱해 예상 단가를 산출합니다.
        ws.Cells(i, 3).Value = ws.Cells(i, 2).Value * (1 + ChangeRate)
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "단가 변동 시뮬레이션이 완료되었습니다.", vbInformation, "원가 관리 자동화"
End Sub

3. 실무 및 투자 활용 팁

• 실무: 원가 상승 압박이 있을 때 적정 판매가를 산출하는 근거 자료로 활용할 수 있습니다.
• 투자: 내가 투자한 기업의 주요 원재료(예: 구리, 리튬 등) 가격이 변동할 때 해당 기업의 실적을 예측해보는 도구로 사용할 수 있습니다.

4. 마치며

단순히 숫자를 기록하는 것을 넘어, 미래의 시나리오를 예측하는 것이 진정한 데이터 활용의 시작입니다. 오늘 만든 시뮬레이션 모델을 통해 업무의 전문성을 높이고 더 넓은 경제적 시야를 확보하시길 바랍니다.