회사에서 솔버를 사용하여 어떤 프로젝트를 수행해야 하는지 결정하려면 어떻게 해야 하나요?
매년 Eli Lilly와 같은 회사는 어떤 약물을 개발할지 결정해야 합니다. Microsoft와 같은 회사, 어떤 소프트웨어 프로그램을 개발해야 할까요? Proctor & Gamble과 같은 회사는 새로운 소비자 제품을 개발해야 합니다. Excel의 해결사 기능은 회사가 이러한 결정을 내리는 데 도움이 될 수 있습니다.
회사에서 솔버를 사용하여 어떤 프로젝트를 수행해야 하는지 결정하려면 어떻게 해야 하나요?
대부분의 기업은 제한된 자원(일반적으로 자본 및 노동)에 따라 가장 큰 순 현재 가치(NPV)에 기여하는 프로젝트를 수행하기를 원합니다. 소프트웨어 개발 회사가 20개의 소프트웨어 프로젝트 중 어떤 것을 수행해야 하는지 결정하려고 한다고 가정해 보겠습니다. 각 프로젝트에서 기여하는 NPV(백만 달러)와 자본(백만 달러) 및 향후 3년 동안 필요한 프로그래머의 수는 다음 페이지의 그림 30-1에 표시된 파일 Capbudget.xlsx의 기본 모델 워크시트에 나와 있습니다. 예를 들어, 프로젝트 2의 수익은 9억 800만 달러입니다. 1년차에는 1억 5,100만 달러, 2년차에는 2억 6,900만 달러, 3년차에는 2억 4,800만 달러가 필요합니다. 프로젝트 2에는 1년차 동안 139명의 프로그래머, 2년차 동안 86명의 프로그래머, 3년차 동안 83명의 프로그래머가 필요합니다. 셀 E4:G4는 각 3년 동안 사용 가능한 자본금(백만 달러)을 나타내고 셀 H4:J4는 사용 가능한 프로그래머 수를 나타냅니다. 예를 들어, 1년 동안 최대 25억 달러의 자본과 900명의 프로그래머를 사용할 수 있습니다.
회사는 각 프로젝트를 수행해야 하는지 여부를 결정해야 합니다. 소프트웨어 프로젝트의 일부도 수행할 수 없다고 가정해 보겠습니다. 예를 들어 필요한 리소스 중 0.5개를 할당하면 $0의 수익을 가져올 수 있는 작동하지 않는 프로그램이 생깁니다!
무언가를 하거나 하지 않는 상황을 모델링할 때 요령은 이진 변경 셀을 사용하는 것입니다. 이진 변경 셀은 항상 0 또는 1과 같습니다. 프로젝트에 해당하는 이진 변경 셀이 1이면 프로젝트를 수행합니다. 프로젝트에 해당하는 이진 변경 셀이 0이면 해당 프로젝트를 수행하지 않습니다. 제약 조건을 추가하여 이진 변경 셀 범위를 사용하도록 해 찾기를 설정합니다. 사용할 변경 셀을 선택한 다음 제약 조건 추가 대화 상자의 목록에서 Bin을 선택합니다.
이러한 배경을 바탕으로 우리는 소프트웨어 프로젝트 선택 문제를 해결할 준비가 되었습니다. 해 찾기 모델을 사용할 때와 마찬가지로 대상 셀, 변화하는 셀 및 제약 조건을 식별하는 것으로 시작합니다.
- 대상 셀입니다. 선택한 프로젝트에서 생성된 NPV를 최대화합니다.
- 셀 변경. 각 프로젝트에 대해 0 또는 1 이진 변경 셀을 찾습니다. A6:A25 범위에서 이 셀을 찾았습니다(범위 이름을 doit로 지정했습니다). 예를 들어, 셀 A6의 1은 우리가 프로젝트 1을 수행하고 있음을 나타냅니다. C6 셀의 0은 프로젝트 1을 수행하지 않음을 나타냅니다.
- 제약 조건. 각 연도 t (t=1, 2, 3)에 대해 사용 연도 t 자본이 사용 가능한 연도 t 자본보다 작거나 같고, 연도 t 노동력이 사용 가능한 연도 t 년 노동보다 작거나 같은지 확인해야 합니다.
보시다시피 워크시트는 선택한 프로젝트에 대해 NPV, 연간 사용된 자본 및 매년 사용하는 프로그래머를 계산해야 합니다. B2 셀에서는 SUMPRODUCT(doit,NPV) 수식을 사용하여 선택한 프로젝트에서 생성된 총 NPV를 계산합니다. 범위 이름 NPV 는 C6:C25 범위를 참조합니다. A 열에 1이 있는 모든 프로젝트에 대해 이 수식은 프로젝트의 NPV를 선택하고 A 열에 0인 모든 프로젝트에 대해 이 수식은 프로젝트의 NPV를 선택하지 않습니다. 따라서 모든 프로젝트의 NPV를 계산할 수 있으며, 대상 셀은 (변화하는 셀)*(상수) 형식을 따르는 항을 합산하여 계산되기 때문에 선형입니다. 비슷한 방식으로 E2에서 F2:J2로 SUMPRODUCT(doit,E6:E25) 수식을 복사하여 매년 사용되는 자본과 매년 사용되는 노동을 계산합니다.
이제 그림 30-2와 같이 해 찾기 매개 변수 대화 상자를 채웁니다.
우리의 목표는 선택한 프로젝트의 NPV를 극대화하는 것입니다(셀 B2). 변경 셀( doit이라는 범위)은 각 프로젝트의 이진 변경 셀입니다. 제약 조건 E2:J2<=E4:J4는 각 연도 동안 사용되는 자본 및 노동이 사용 가능한 자본 및 노동 이하가 되도록 합니다. 변경하는 셀을 이진으로 만드는 제약 조건을 추가하려면 해 찾기 매개 변수 대화 상자에서 추가를 클릭하고 대화 상자 가운데에 있는 목록에서 Bin을 선택합니다. 그림 30-3과 같이 제약 조건 추가 대화 상자가 나타납니다.
대상 셀이 (변화하는 셀)*(상수) 형식을 갖는 항의 합계로 계산되고 리소스 사용 제약 조건이 (변화하는 셀)*(상수) 의 합계를 상수와 비교하여 계산되기 때문에 모델은 선형입니다.
해 찾기 매개 변수 대화 상자를 채운 상태에서 해결을 클릭하면 그림 30-1에 표시된 결과가 표시됩니다. 회사는 프로젝트 2, 3, 6-10, 14-16, 19, 20을 선택하여 최대 92억 9,300만 달러(92억 9,300만 달러)의 NPV를 얻을 수 있습니다.
기타 제약 조건 처리
경우에 따라 프로젝트 선택 모델에는 다른 제약 조건이 있습니다. 예를 들어 프로젝트 3을 선택하는 경우 프로젝트 4도 선택해야 한다고 가정해 보겠습니다. 현재 최적 솔루션이 프로젝트 4가 아닌 프로젝트 3을 선택하기 때문에 현재 솔루션이 최적으로 유지할 수 없다는 것을 알고 있습니다. 이 문제를 해결하려면 프로젝트 3의 이진 변경 셀이 프로젝트 4의 이진 변경 셀보다 작거나 같다는 제약 조건을 추가하기만 하면 됩니다.
그림 30-4에 표시된 파일 Capbudget.xlsx의 If 3 then 4 워크시트에서 이 예제를 찾을 수 있습니다. 셀 L9는 프로젝트 3과 관련된 이진 값을 참조하고 셀 L12는 프로젝트 4와 관련된 이진 값을 참조합니다. 제약 조건 L9<=L12를 추가하여 프로젝트 3을 선택하면 L9는 1과 같고 제약 조건은 L12(프로젝트 4 이진)를 강제로 1과 같습니다. 제약 조건은 또한 프로젝트 3을 선택하지 않는 경우 프로젝트 4의 변경하는 셀에 있는 이진 값을 제한하지 않은 상태로 두어야 합니다. 프로젝트 3을 선택하지 않으면 L9는 0이 되고 제약 조건은 프로젝트 4 이진이 우리가 원하는 0 또는 1과 같을 수 있도록 허용합니다. 새로운 최적 솔루션은 그림 30-4에 나와 있습니다.
프로젝트 3을 선택하면 프로젝트 4도 선택해야 하는 경우 새로운 최적 솔루션이 계산됩니다. 이제 프로젝트 1부터 10까지 4개의 프로젝트만 수행할 수 있다고 가정합니다. (그림 30-5에 표시된 P1-P10의 최대 4 개 워크시트를 참조하십시오.) L8 셀에서 SUM(A6:A15) 수식을 사용하여 프로젝트 1부터 10까지 연결된 이진 값의 합계를 계산합니다. 그런 다음 제약 조건 L8<=L10을 추가하여 처음 10개 프로젝트 중 최대 4개가 선택되도록 합니다. 새로운 최적 솔루션은 그림 30-5에 나와 있습니다. NPV는 90억 1,400만 달러로 떨어졌습니다.
이진 및 정수 프로그래밍 문제 해결
변화하는 셀의 일부 또는 전부가 이진 또는 정수여야 하는 선형 해 찾기 모델은 일반적으로 모든 변화하는 셀이 분수일 수 있는 선형 모델보다 풀기가 더 어렵습니다. 이러한 이유로 우리는 종종 이항 또는 정수 프로그래밍 문제에 대한 거의 최적의 솔루션에 만족합니다. 솔버 모델을 오랫동안 실행하는 경우 솔버 옵션(Solver Options) 대화상자에서 허용 오차 설정을 조정할 수 있습니다. (그림 30-6 참조) 예를 들어 허용 오차 값을 0.5%로 설정하면 솔버가 이론적 최적 대상 셀 값(이론적 최적 대상 셀 값은 이진 및 정수 제약 조건을 생략할 때 찾은 최적 목표 값임)의 0.5% 이내인 실현 가능한 해를 처음 발견하면 해결사가 중지됨을 의미합니다. 종종 우리는 10분 안에 최적의 10% 이내에서 답을 찾거나 2주 동안 컴퓨터 시간 안에 최적의 솔루션을 찾는 것 중에서 선택해야 합니다! 기본 허용 오차 값은 0.05%입니다. 즉, 솔버는 이론적 최적 대상 셀 값의 0.05% 이내의 대상 셀 값을 찾으면 중지됩니다.
문제
- 한 회사에는 9개의 프로젝트가 고려 중입니다. 각 프로젝트에서 추가된 NPV와 향후 2년 동안 각 프로젝트에 필요한 자본은 다음 표에 나와 있습니다. (모든 숫자는 백만 단위입니다.) 예를 들어, 프로젝트 1은 NPV로 1,400만 달러를 추가하고 1년 차에 1,200만 달러, 2년 차에 300만 달러의 지출을 요구합니다. 1년 차에는 프로젝트에 5천만 달러의 자본을 사용할 수 있고, 2년차에는 2천만 달러를 사용할 수 있습니다.
| NPV | 1년차 지출 | 2년차 지출 | |
|---|---|---|---|
| 프로젝트 1 | 14 | 12 | 3 |
| 프로젝트 2 | 17 | 54 | 7 |
| 프로젝트 3 | 17 | 6 | 6 |
| 프로젝트 4 | 15 | 6 | 2 |
| 프로젝트 5 | 40 | 30 | 35 |
| 프로젝트 6 | 12 | 6 | 6 |
| 프로젝트 7 | 14 | 48 | 4 |
| 프로젝트 8 | 10 | 36 | 3 |
| 프로젝트 9 | 12 | 18 | 3 |
- 프로젝트의 일부를 수행할 수 없는데 프로젝트 전부를 수행하거나 전혀 수행하지 않아야 한다면 어떻게 NPV를 최대화할 수 있습니까?
- 프로젝트 4가 수행되면 프로젝트 5도 수행되어야 한다고 가정해 보겠습니다. 어떻게 NPV를 최대화할 수 있습니까?
한 출판사는 올해 36권의 책 중 어떤 책을 출판해야 할지 결정하려고 노력하고 있습니다. 파일 Pressdata.xlsx에는 각 책에 대한 다음 정보가 제공됩니다.
- 예상 수익 및 개발 비용(천 달러)
- 각 책의 페이지 수
- 이 책이 소프트웨어 개발자를 대상으로 하는지 여부(E열에 1로 표시)
출판사는 올해 총 8,500페이지의 책을 출판할 수 있으며 소프트웨어 개발자를 대상으로 한 책을 최소 4권 이상 출판해야 합니다. 회사는 어떻게 이익을 극대화할 수 있습니까?
문서 정보
이 문서는 Wayne L. Winston의 Microsoft Office Excel 2007 Data Analysis and Business Modeling 에서 발췌한 것입니다.
이 교실 스타일의 책은 Excel의 창의적이고 실용적인 응용 프로그램을 전문으로 하는 유명한 통계학자이자 경영학 교수인 Wayne Winston의 일련의 프레젠테이션을 통해 개발되었습니다.