산업현장이나, 식당이나 제과점 등의 생산현장에서 은 특정 제품을 생산할 때 필요한 재료와 에너지 등에 대한 필요량을 원단위라는 명칭으로 관리한다. 이러한 원단위를 통해, 생산 현장의 주어진 상황하에서 최적의 생산 모드가 무엇인지를 결정하는데 중요하기 때문이다.
이처럼 생산공장 등에서, 주어진 제한조건을 고려하는 상황조건에서, 단위 생산 변화 시의 필요한 단위 소요량을 이용하여 계산하는 방식을 LP(Linear Programing)이라고 하는데, 대규모 정유공장이나 석유화학공장 등에서는 최적생산 모드에 따른 손익 변동이 크기 때문에, 널리 사용되며, 전용 프로그램을 사용하기도 한다.
그런데, 엑셀에서도 이러한 LP 계산을 지원하기 때문에, 비교적 간단한 문제들은 엑셀만으로도 최적해 계산이 가능하다. 다음과 같은 제과점의 사례를 가지고, 최적 생산 조건을 충족하는 해를 LP로 찾아보기로 하자.
A라는 제과점에서 다양한 종류의 식빵을 만드는데 사용되는 재료들의 g 당 단가는 다음과 같다.
그리고 생산하는 식빵류의 g 기준 원료 사용량은 다음과 같다.
만일 가격이 다음과 같다면, 하루 동안 20kg 10포이 내의 밀가루를 사용할 때, 각각의 식빵을 몇 개씩 생산해야 가장 최적일까?
* 참고로, 위의 가격은 임의로 정한 것이며, 개당 비용은 Sumproduct 함수에 레시피와, 재료별 단가를 넣어 계산하였다. 매출액과 이익, 매출총액 및 판매 이익 총액은 생산 개수가 정해지면, 자동으로 계산되도록 미리 수식을 넣어 두었다.
최적해를 찾는 방법은 다음과 같다.
데이터 메뉴에서 가장 오른쪽에 있는 “해 찾기”를 선택한다.
해 찾기 메뉴를 선택하면, 다음과 같은 셀지 절 화면이 나오는데, 여기에 목표로 하는 내용과 제한 조건들, 그리고 해 값을 찾을 셀들을 지정해 준다.
위의 예에서는 매출을 최대로 하는 것을 목표로 했다.
따라서, 총 판매 이익이 계산되는 셀 (T14)를 “목표 설정”에 지정하고, 이 값이 최댓값이 되도록 설정했다.
해 갚을 찾아야 하는 생산 개수는
엑셀 시트에서 R6에서 R11 사이에 지정되어 있으므로, 이들 범위를 “변수 셀 변경”이란 부분에 지정했다.
그리고, 해 갚을 찾을 때 고려해야 하는 제한 조건들을 대화창의 아랫부분의 “제한조건에 종속”이란 곳에 추가해 주었다. 위의 예에서 각 제품을 최소 5개 이상씩 생산해야 하기에, 각각의 개수인 R6부터 R11까지의 값이 5 이상이 되도록 지정했다. 단, 이들은 개별적으로 조건을 설정해 주어야 한다.
또한 작업량을 고려하여, 하루에 사용 가능한 강력분 사용량을 20kg 10포대 이내로 제한해 주었다.
M14에는 미리 각 제품별 강력분 사용량과, 찾아질 제품별 생산 개수의 셀을 SUMPRODUCT를 이용하여 계산하도록 식을 넣어두었다.
위의 지정을 마치고 난 후, 해 찾기를 누르면 다음과 같이, 조건들을 충족시키는 해를 찾았다는 메시지가 나온다.
확인을 눌러주면, 찾은 해 값이 반영되면서 수식들의 값이 갱신된다.
이 예제에서는, 20kg 강력분 10포대를 사용 시, 하루에 최대 약 89만 원 정도의 수익을 얻을 수 있는 것으로 계산되었다.
만일 이렇게 얻은 해에, 하루에 판매 가능한 바게트의 수량을 최대 20개로 추가로 제한해 주려면,
다시 “해 찾기”를 선택하고, 해당 조건을 제한조건에 추가해 주면 된다 (즉, R10<=20)
그 결과 다음과 같이, 달라진 결과를 얻게 된다.
이처럼, LP에 의한 해 찾기는 제한조건들을 잘 반영하는 것이 중요하며, 해를 찾고자 하는 목표를 명확하게 하는 것이 매우 중요하다.
다르게 설명하면 제한조건을 얼마나 정확히 설정하는가에 따라, 찾은 해의 현실감이 달라질 수도 있고,
또한 현실에서 이익의 증대를 위해 삭제할 수 있는 조건들의 우선순위와, 영향을 미리 계산해 볼 수도 있다.
참고로. 해 찾기 메뉴의 아랫부분에는 해를 찾는 수치해석적 기법을 고를 수 있는데, 이 부분은 문제의 성격에 따라 보다 효율적으로 해를 빠르게 찾기 위한 설정이다. 대개의 경우 단순 LP 또는 GRC 비선형으로 설정하기를 권한다.
