회사운영 최적화 문제, 엑셀로 해 찾기
운영 데이터분석(불확실성이 낮은 환경에서 최적화 문제)
불확실성이 낮은 환경에서 최적의 의사결정을 하는 방법을 예제로 알아보겠습니다.
소규모 화장품 회사가 있습니다. 화장품 A와 화장품 B 두 가지를 생산합니다.
화장품 A는 1개에 11만 원, 화장품 B는 1개에 18만 원에 이익이 생깁니다. 화장품 B가 A보다 이익이 높습니다. 그러나 재료가 더 많이 들어갑니다. 회사는 작고 자원은 한정 돼 있습니다. A와 B제품의 생산에 자원을 최적으로 할당해야 하는 문제에 직면해 있습니다. 화장품 A와 B를 각각 몇 개를 만들어야 이익이 최대가 될까요?
화장품 만드는데 재료 1, 재료 2, 재료 3이 들어갑니다.
화장품 A는 재료 1(20개)+재료 2(20개)+재료 3(20개)이 들어갑니다.
화장품 B는 재료 1(40개)+재료 2(30개)+재료 3(30개)이 들어갑니다.
재료 1, 2, 3은 각가 1300개, 1000개, 1200개가 있습니다.
이 문제는 불확실성이 없습니다. 제한된 재료량으로 만들 수 있는 제품 수와 그로 인한 이익이 명확합니다.
이와 같이 모든 것이 명확한 환경에서는 의사결정을 내리기가 쉽습니다. 그 숫자가 아무리 커지더라도 컴퓨터로 최적값을 찾아낼 수 있습니다.
재료와 제품의 조합을 다 계산해보면 그 답이 나옵니다. 이때 필요한 것이 엑셀입니다. 엑셀에서 최적화 문제를 풀기 위해 '해 찾기'를 이용합니다. 엑셀의 기본설정에서는 '해 찾기' 기능이 없으므로 옵션에 들어가서 '해 찾기'를 추가합니다. 엑셀, 데이터, 상단 우측에 '해 찾기'가 있습니다.
1 단계
문제에 주어진 제품에 들어가는 재료와 제한을 엑셀에 그대로 입력합니다.
엑셀에서 파란색으로 표시된 곳을 바꾸면 총이익이 계산되도록 합니다. 총이익을 최대로 만들어야 하지만 처음에는 그 값을 알 수 없기 때문에 50과 50으로 정했습니다. 그러나 재료 사용량을 보면 재료 1,2,3의 사용량이 모두 최대 공급량을 초과하고 있습니다. 실제로 생산할 수 없습니다.
2 단계
해 찾기를 이용하여 총이익이 최대치가 되도록 합니다. '해 찾기'를 누르면 세 가지를 입력해야 합니다.
1. 목표 설정:- 총이익인 F10을 클릭합니다. 이익을 최대로 만들어야 하기 때문에 최댓값을 클릭합니다.
2. 변수 셀 변경:- 화장품 A와 B의 개수를 정하는 문제이므로 C10과 D10을 클릭합니다.
3. 제한 조건에 종속:- 추가를 눌러서 제한 사항을 정합니다. 1) 재료의 최대공급량보다 재료 사용량이 적어야 하기 때문에 범위를 클릭하고 부호를 선택합니다. 2) 생산 개수인 C10과 D10의 개수는 0.1이나 음수가 될 수 없습니다. 추가를 눌러서 셀을 클릭하고 정수로 합니다.
3 단계
위의 조건으로 해' 찾기'를 클릭하면 문제가 해결됩니다. 제한된 자원으로 낼 수 있는 총이익은 595만 원입니다. 화장품 A 5개 화장품 B를 30개 만들어야 한다.라고 결론 내릴 수 있습니다. 간혹 답이 2개일 수도 있습니다. 총이익은 똑같지만 A와 B의 순서를 바꾸면 그 생산수량이 다르게 바뀔 수도 있습니다.
'해 찾기'를 쓸 때 유념해야 할 점은 '목표 설정'은 한 가지만 가능합니다. 실제 회사를 운영하려면 이익, 비용, 수수료, 인건비등 여러 가지 변수를 함께 고려해야 합니다. 언제나 한 가지를 목표로 정하고 나머지는 '제한 조건'으로 놓고 '해 찾기'를 할 수 있습니다.
이런 종류에 문제 풀기를 '선형 모델'이라고 합니다. 선형 모델인 경우 문제 풀기가 쉽습니다. 문제에 제곱, 비율등이 들어가면 '비선형 모델'이 되고 최적화하기가 더 어렵습니다.
화장품 회사를 예로 든 최적화는 비교적 간단합니다. 제한조건이 많아지고 숫자가 커지면 '해 찾기'를 사용하는 것이 매우 효율적입니다. 엑셀뿐 아니라 구글시트를 비롯한 다양한 소프트웨어에도 '해 찾기' 기능이 있습니다. 다행인 점은 어떤 소프트웨어를 사용하든 '목표 설정', '변수', '제한 조건'이 세 가지를 이용합니다.
이 글은 새태 경영컨설팅 http://saetae.co.kr에도 함께 게시됩니다.
다음 글에서는 불확실성이 높은 상황에서 의사결정을 내리는 방법에 대해 이어 씁니다.
