엑셀은 앞선 글에서 설명하였듯, LP (Linear Programing) 기법에 의한 선형 해 찾기 기능을 제공하기 때문에, 다양한 생산 현장 및 판매 현장에서, 생산 계획이나, 판매 계획 등을 Simulation을 해 보는데, 다양하게 사용될 수 있다.
다음과 같은 가격으로 커피 및 음료를 판매하는 카페를 대상으로 LP 적용 사례를 소개한다.
Simulation 1. 만일 손님이 많아서, 카페에서 원하는 대로 만들어 팔 때, 1시간의 최대 이익을 달성할 수 있는 조건 및 해당 조건에서 매출액과 이익은?
엑셀 시트에서 다음과 같이 만들 개수를 가정하고, 해당 개수를 만들 때의 제조 시간 및 매출액, 해당 이익을 계산한다. 그리고 이들 각 항목의 합을 아랫부분에 계산되도록 식을 만든다.
해 찾기 메뉴에서 다음과 같이, 각 항목별 셀을 지정해 준다.
목표 설정 부분에 판매 이익 총액(U19)에 해당되는 셀을 지정하고, 변수셀변경에는 해를 찾아야 할 음료별 제조할 개수에 해당되는 셀 (R6:R15)을 지정한다. 제한조건에는 총 시간이 1시간이라는 것 외에 별도 제약이 없으므로, 총 작업시간(S19)가 60보다 작아야 한다는 제한을 설정해 준다.
이런 조건으로 설정한 후, 해 찾기를 진행하면,
다음과 같이 홍차만을 집중적으로 만들어 판매하는 것이, 이익 측면에서 가장 바람직한 것을 알 수 있었으며, 해당 조건에서 1시간에 도달 가능한 최대 매출액과 최대 이익 금액도 확인할 수 있었다.
Simulation 2. 만일 카페에서 원하는 대로 판매하되, 단 홍차의 판매 개수는 시간당 최대 10개까지만 판매하는 것으로 제한하는 경우, 1시간 동안 이익을 극대화하는 조건 및 예상 매출액과 예상 이익?
위의 Simulation에서 사용한 해 찾기 설정화면에서, 홍차의 개수만 최대 10개 이하가 되도록 추가해 준다.
이 조건에서 해 찾기를 진행하면, 다음과 같은 새로운 해를 얻게 된다.
Simulation 3. 위의 조건에 추가로 아메리카노는 반드시 20개 이상 판매된다는 가정을 추가하는 경우,
1시간 동안 이익을 극대화할 수 있는 조건은?
이를 위해 다음처럼 아메리카노 음료수(R6)에 대한 제한을 추가한다.
이 조건에서 해 찾기를 진행하면 새로 설정한 조건을 충족하는 해를 찾게 된다.
Simulation 4. 위의 조건에 추가로, 다음 조건들이 추가될 때 1시간 동안 이익을 극대화할 수 있는 조건 및 결과는 ?
* 우유를 사용하는 카페라테(R8) 및 아이스 카페라테(R9)는 각기 5개 이상씩 판매하는 조건을 추가하고
* 얼음이 사용되는 제품의 개수(R21)는 총 20개 이하로 제한하고
* 녹차라테의 개수(R14)도 1시간당 7개 이하로 제한하는 경우에
이 경우에 대한 해 찾기를 적용하는 과정에서 필요한 제한조건 중 미리 합산이 필요한 얼음 사용 음료수를 계산해 주기 위해 시트 아래에 다음과 같이 얼음 사용 음료수의 수를 계산하는 셀을 하나 추가로 만들어 식을 지정한다.
그리고, 해 찾기의 매개변수들 중 제한 조건 부분에 해당 조건들을 추가로 지정해 준다.
해 찾기 실행 결과 다음과 같이 이 모든 조건을 동시에 충족하는 해는 없는 것으로 나타났다.
해 찾기 제한 조건 중 하나의 제한을 풀어준 결과(아이스 카페라테의 수가 5 이상인 조건 R9>=5 조건을 삭제),
다음과 같이, 해를 찾을 수 있었다.
마치는 글
이처럼 엑셀의 해 차기 기능은 다양한 조건들을 다양하게 변화시키면서, 그 영향을 살펴볼 수 있는 매우 강력한 도구이다.
특히, 경제적 이익이 직결되어 있는 생산 현장이나, 판매 현장에서 각 상황별로 주어지는 여러 제한 조건들 (생산능력, 시장 가격, 원자재 가격, 최대 판매 가능량 등)이 변할 때, 어떤 조치를 취하는 것이 가장 바람직한 것인지를 찾아낼 수 있는 매우 강력한 수단이다.
다만, 많은 사람들에게 이러한 LP의 개념이 생소하고 잘 알려져 있지 않기 때문에, 실제 사용하는 사람들을 자주 볼 수는 없지만, 일단 개념을 이해하고 나면, 실제 업무에 매우 유용할 뿐 아니라, 사용방법도 매우 간편하므로 익혀서 사용해 보길 바란다.
본 글에 사용한 예제 파일을 첨부한다.
d4500787-bbc4-4c3c-a942-bb137a8404f5″950089382c1e1fab8f6f013f0bee96ef4718eb70/EFsQbrdJxrnO9txaLuoSggWaFwbSq-l_Y1wGJTkPkAQvEIK9UX4i6fi3Ce8WhWxBoyidSVvs8Nd_1eQ9DZDC8LGNUyadRyc/LP2.xlsx