엑셀로 풀어보는 몬테카를로 분석

 

몬테카를로 분석 또는 시뮬레이션에 대해서 얘기해본다.

Monte Carlo simulation 워낙 유명한 분석기법이라 이해하기 쉽게 다양하게 기술된 블로그들이 많다. 여기서는 "난수를 이용한 모든 시뮬레이션" 이라고 정의해본다. 방법은 다음과 같다. 1. 모든 미래의 상황을 불확실하다고 가정한다.  2. 0과 1 사이의 난수를 뽑아서 미래의 상황이라고 가정한다. 3. 상황 내에서 평균 이익을 최대한으로 올리기 위한 의사결정을 파악한다. 비고. 평균 이익을 최대한으로 하는 것 뿐만 아니라 신뢰구간을 통해 최대의 이익을 구할 수 있도록 의사결정 변수를 선택한다. 문제를 다음과 같이 가정해본다. (첨부파일 참고)   몬테카를로 예제1.xlsx

 

(내가 근무하는 부서가 QA팀일 뿐, 사실상 QA팀에서 저런 일을 하지는 않는다. 대게~)

과거 실적을 통해 예상되는 수요량과 그에 따른 확률을 구해볼 수 있었다. (가정)

 

 

엑셀에다가 계산자료의 기본 정보를 입력한다.

대당 판매가 / 생산비용 / 재고 처리를 통한 이득 및 확률

 

 

위 문제에서 난수를 500번 생성한다고 하였으니,

엑셀에서 rand 명령어를 통해 난수를 발생시켜보자. 

 =rand() 입력하고, 드래그~

 

 

생성한 난수는 엑셀에서 다른 작업을 하면서 계속 바뀌게 된다.

일단 발생한 난수를 고정해보자.

다음과 같이 발생된 난수를 고정해볼 수 있다.

예상되는 수요량을 계산해보자.

Vlookup을 활용하여 난수에 따른 예상 수요량을 불러와 보자.

(첨부파일 참고)

 

 

 

예상 수요량에 따른 수익도 함께 고려해보자.

수요량이 생산량보다 많은 경우 생산량보다 더 팔 수가 없다. (물건이 없으니까)

또한 생산량이 많아도 수요량이 적다면 덜 팔 수 밖에 없다. (고객이 사질 않으니까)

그래서 min(생산량, 수요량)을 활용하여 수익을 다음과 같이 구해볼 수 있다.

 

 

 

다음은 생산비용을 구해본다.

생산비용의 경우 생산량에 생산비용을 곱하여 구해볼 수 있다.

(매우 단순화시킨 계산법을 이해해주기 바랍니다.)

 

 

 

다음은 이익을 구해본다.

이익의 경우, 수익에서 생산비용을 빼고, 재고처리 이득금액을 더하여 산출할 수 있다.

수익 - 생산비용 + 재고처리이득금액

 

자, 우리가 궁금했던건 이익이었다.

난수로 인해서 이익까지 뽑았으니 평균이익을 뽑아보자.

더불어 기술통계 몇개를 함께 뽑아보자.

 

신뢰구간도 함께 구해본다.

 

생산량의 변화가 평균 이익이 어떻게 달라지는지 확인하기 위해서는

위 과정을 여러번 돌려볼 필요가 있다.

물론 처음에만 작성해 놓으면 다음부터는 쉽게 구할 수는 있지만,.. 이 또한 불편하다.

엑셀의 데이터 표를 활용하여, 생산량 변화에 따른 평균이익의 변화를 체크해보자.

 

아래 표를 확인해보니, 평균이익이 최대가 되는 생산량이 3,000으로 계산되었다. 

(1 Lot를 500개로 가정)

 

 

그래프도 함께 구해보자.

 

생산량 3000개까지는 평균이익이 증가하다가 넘어서부터는 감소하는걸 확인할 수 있다.

또한 생산량 6000부터는 오히려 생산하는 것이 손해를 보고 있음을 확인하였다.

무조건 만들어서 파는게 좋은게 아니다.

위와 같이 몬테카를로 분석 또는 시뮬레이션을 활용해 볼 수 있다.

실무에서 얼만큼 활용하는지 궁금하다.

내가 있는 곳은 위의 분석 기법을 모두가 안다.

그리고 모두가 활용하지 않는다.

안다. 그러나 활용하지 않는다.

그게 자랑이 아닐텐데?

끝.