Excel을 활용한 선형계획 풀이

 

Excel을 활용한 선형계획 풀이

 

의사결정이란?

1. 개인 혹은 조직체가 무엇인가를 선택하는 과정

2. 어떠한 목표를 달성하고자 하는 의도적인 선택과정을 의미

3. 이러한 의사 결정 문제를 체계적, 계량적으로 접근하는 학문이 경영과학이라 한다.

 

경영과학

1. 시스템의 운영, 관리, 계획 및 설계에 관한 의사결정 문제를 계량적, 체계적으로 분석하여 해결하고자 하는 학문

2. 모든 의사결정 문제를 과학적, 계량적, 체계적으로 분석하여 시스템의 목표를 추구하고자 하는 학문

 

경영과학의 문제 해결 절차

경영과학의 문제 해결 절차는 아래 [그림1]과 같다.

 

[그림1] 경영과학의 문제 해결 절차 

 

수리적 모형의 종류
경영 과학에는 많은 모형이 있지만, Excel을 이용해서 풀이가 가능한 수리적 모델만 소개를 하겠다. 

1. 선형계획법(Linear Programming ; LP) : 목적함수와 제약조건식이 모두 1차식으로 표현

2. 비선형계획법(Non-Linear Programming ; NLP) : 1차식으로만 표현되지 않는 모형

3. 정수계획법(Integer Programming ; IP) :의사결정변수가 정수값만을 가지는 특수한 경우

4. 목표계획법(Goal Programming ; GP) : 목적함수가 여러개의 목표를 포함하고 있는 경우

5. 동적계획법(Dynamic Programming ; DP) : 여러 단계에 걸쳐 변수의 값을 결정하는 모형

 

Excel을 이용한 선형계획 풀이

1. 문제

경성페인트에서는 외장용 페인트와 내장용 페인트를 생산 판매하고 있다. 두 종류(외장용, 내장용)의 페인트를 생산하는데 두 종류의 원자재 A, B가 사용되고, 외장형 페인트 1톤을 생산하는데 원자재 A는 1톤, B는 2톤이 필요하고, 내장형 페인트 1톤을 생산하는데 원자재 A는 2톤, B는 1톤이 필요하다. 그런데 원자재의 하루 조달 가능량은 A, B 각각 6톤, 8톤이다. 그리고 페인트 판매가는 외장용, 내장용 각각 톤당 300만원, 200만원일 때 하루의 판매액을 최대로 하는 생산계획은 무엇일까?

 

 

2. 수리적 모형

      위 문제는 아래와 같은 수리적 모형으로 모델링 될 수 있다. 수리적 모델로 모델링이 된 문제는 Excel이나 Lindo와 같은 프로그램을 통해 우리가 구하고자 하는 최적해를 쉽게 구할 수 있다. 따라서 어떠한 의사결정 문제에 있어 가장 중요한 것은 적절한 모델로 모델링하는 작업이라고 할 수 있겠다.

 

3. 그래프에 의한 해법

      제약 조건의 식을 그래프로 표현하면 아래 [그림2]와 같다. 이 문제는 Maximize 문제이므로, [그림2]의 주황색으로 표시된 꼭지점 부분의 값만 목적함수에 대입해 목적함수가 최대가 되는 점을 구하면 될 것이다. 이렇게 결정변수가 3개 이하인 문제는 그래프에 의한 해법으로 문제를 풀 수 있지만, 결정 변수가 4개 이상이 되면 그래프로 해를 구할 수 없다.
결정 변수가 4개 이상인 문제의 풀이를 위해 Simplex법을 이용하면 최적해를 구할 수 있다. Simplex법에 대해서는 경영과학(OR, 계량경영분석, 의사결정분석) 책을 참고하기 바란다.

    

 

[그림2] 그래프에 의한 해법

 

 

Excel 해찾기 기능

1. STEP1

      수리적 모형으로 모델링이 된 문제를 아래 [그림3]과 같이 Excel에 입력한다. 아래 그림에서 최적해가 출력될 Cell은 B11:C11이다. B11:C11에 어떤 값이 들어올지 모르겠지만, B11:C11에 있는 값과 B8:C8에 있는 값을 곱한 결과가 우변상수 6보다 작아야 한다. 이렇게 배열을 곱을 구하는 Excel 함수가 SUMPRODUCT함수이다. 아래 [그림3]의 좌상단 그림과 같이 SUMPRODUCT 함수를 작성하자. 그럼 그 결과는 [그림3]의 우하단과 같이 될 것이다. 

 

[그림3] 수리적 모형 입력

 

2. STEP2
STEP1에서 수리적 모형을 Excel Sheet로 입력했다면, STEP2에서는 제약 조건을 추가하는 단계이다. 아래 [그림4]에서 목표 셀은 목적함수 값이 출력될 Cell을 의미하며, 값을 바꿀 셀은 Excel이 결정변수 값을 바꿔가면서 목적함수 값이 최대가 되는 결정 변수 값을 출력할 Cell을 의미한다. 
제약 조건을 추가할 때는 [그림4]와 같이 추가버튼을 클릭한다. STEP1의 [그림3]에서 우리는 SUMPRODUCT 함수를 이용해 결정변수 값과 제약조건 값을 곱한 값을 좌변합계에 출력하도록 하였다. 수리적 모형에서 "좌변합계는 우변상수보다 작거나 같다."이다. 따라서 아래 [그림4]와 같이 제약 조건을 추가해 주면 된다. 이 문제의 경우 "제약 조건이 모두 작거나 같다." 이므로, [그림4]에서는 범위를 지정하였다. 만약 제약 조건의 부등호가 틀리다면 조건을 각각 추가해 주면 된다. 제약 조건을 모두 입력한 결과는 [그림4]의 하단과 같다.

 

[그림4] 제약조건 추가

 

3. STEP3
결정변수가 0보다 크거나 같다는 제약 조건은 [그림4]에서 추가하지 않았다. 또한 이 문제가 Maximize 문제인지 Minimize 문제인지도 Excel에 알려주지 않았는데, 이러한 조건은 옵션에서 지정해 줄 수 있다.
아래 [그림5]의 좌측 상단에 있는 그림이 옵션 버튼을 클릭했을 때, 나타나는 대화상자이다. 선형 모델 가정, 음수 아닌 것으로 가정 등의 조건은 기본값으로  Check가 되니 여러분들은 확이만 해 주면 될 것이다. Option을 다 확인했다면, 좌측상단 대화상자에서 확인을 눌러 대화상자를 닫자. 그리고 우측 상단에 있는 실행 버튼을 클릭하면 해찾기 기능이 수행된다. 해찾기 결과에 대한 민감도 분석, 한계값 보고서 등 추가적인 보고서도 자동으로 생설할 수 있다. 민감도 분석 및 한계값 보고서를 해석하는 방법에 대해서는 참고문헌을 참고하기 바란다.

 

[그림5] 옵션 설정 및 추가 보고서 출력

 

4. STEP4 : 결과확인
Excel 해찾기 기능을 이용해 선형계획 문제를 풀이한 결과는 아래 [그림6]과 같다. 그래프 해법을 통해 풀이한 결과와 동일하다.

 

[그림6] 해찾기 기능 수행 결과

 

결론

선형 계획 문제를 Excel 해찾기 기능을 이용해 풀이해 보았다. 우리가 의사 결정해야 하는 문제가 선형계획 모델 혹은 정수

계획 모델등 수리적 모델로 모델링이 되는 문제라면 Excel을 활용해 최적해를 구할 수 있음을 알수 있었다.

계산은 각종 프로그램을 이용해 컴퓨터를 이용할 수 있지만, 모델링은 불가능하다. 의사 결정 문제에 있어 가장 중요한 것은

모델링을 잘 하는 것이 아닐까 생각한다.

 

참고문헌

1. Management Science : A Self-Correcting Approach, Barry Render, Ralph M.Stair, Jr., Allyn and Bacon

2. 엑셀활용 경영과학, 김기석, 학현사

 

 

 

 

 

 

 

 

 

 

 

[출처] http://blog.naver.com/hshklee/70070987000