회사에서 Solver를 사용하여 어떤 프로젝트를 진행해야 하는지 확인할 수 있나요?

매년 Eli Lilly와 같은 회사는 개발할 약물을 결정해야 합니다. 개발할 소프트웨어 프로그램인 Microsoft와 같은 회사; Proctor와 같은 & 새로운 소비자 제품을 개발하는 Gamble를 개발합니다. 회사에서 이러한 결정을 내리는 데 Excel 해결사 기능을 사용할 수 있습니다.

대부분의 기업은 제한된 리소스(일반적으로 자본 및 노동)에 따라 NPV(가장 큰 순 현재 가치)를 기여하는 프로젝트를 진행하기를 원합니다. 소프트웨어 개발 회사가 20개 소프트웨어 프로젝트 중 어떤 것을 실행해야 하는지 확인하려고 합니다. 각 프로젝트뿐만 아니라 각 프로젝트에서 기여한 NPV(수백만 달러)와 다음 3년 동안 필요한 프로그래머 수가 파일 Capbudget.xlsx 기본 모델 워크시트에 주어지며, 다음 페이지에 그림 30-1 에 나와 있습니다. 예를 들어 Project 2는 9억 8,000만 달러를 얻을 수 있습니다. 1년 동안 1억 5,100만 달러, 2년 동안 2억 6,900만 달러, 3년 동안 2억 4,800만 달러가 필요합니다. Project 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을 Project, C6 셀의 0은 1을 Project 나타냅니다.

  • 제약 조건. 매년 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를 계산할 수 있으며, 대상 셀은 폼(셀 변경 )*(상수)을 따르는 용어를 합산하여 계산하기 때문에 선형입니다. 비슷한 식으로 매년 사용되는 자본을 계산하고 매년 사용되는 수식 SUMPRODUCT(doit, E6:E25)를 E2에서 F2:J2로 복사하여 매년 사용한 노동을 계산합니다.

이제 그림 30-2와 같이 해결사 매개 변수 대화 상자를 입력합니다.

책 이미지

우리의 목표는 선택한 프로젝트(셀 B2)의 NPV를 최대화하는 것입니다. 변경 셀( doit라는 범위)은 각 프로젝트에 대한 이진 변경 셀입니다. 제약 조건 E2:J2<=E4:J4 는 매년 사용되는 자본 및 노동이 사용 가능한 자본 및 노동력보다 작거나 같아야 합니다. 변경 셀을 이진으로 만드는 제약 조건을 추가하려면 해결사 매개 변수 대화 상자에서 추가를 클릭한 다음 대화 상자 중간의 목록에서 Bin을 선택합니다. 제약 조건 추가 대화 상자는 그림 30-3과 같이 표시됩니다.

책 이미지

대상 셀은 폼(셀 변경 )*(상수) 이 있는 용어의 합계로 계산되는 것이고, 리소스 사용 제약 조건은 (셀 변경 )* (상수)의 합을 상수와 비교하여 계산하기 때문에 선형입니다.

해결기 매개 변수 대화 상자가 채워진 경우 해결을 클릭하고 그림 30-1의 앞부분에 표시된 결과가 표시됩니다. 이 회사는 프로젝트 2, 3, 6-10, 14-16, 19 및 20을 선택하여 최대 NPV를 9,293만 달러(9.293억 달러)까지 획득할 수 있습니다.

프로젝트 선택 모델에 다른 제약 조건이 있는 경우도 있습니다. 예를 들어 3을 선택하면 Project 4를 선택해야 Project 가정합니다. 현재 최적의 솔루션은 3 Project 4를 선택하지만 Project 4를 선택하기 때문에 현재 솔루션이 최적의 상태로 유지될 수 없습니다. 이 문제를 해결하려면 3의 이진 변경 셀이 Project 4의 이진 변경 셀보다 작거나 같아야 하는 Project 추가합니다.

그림 30-4 에 표시된 파일 Capbudget.xlsx 3개 다음 4개 워크시트에서 이 예제를 찾을 수 있습니다. 셀 L9는 Project 3과 관련된 이진 값을, 셀 L12는 4와 관련된 이진 Project 참조합니다. 제약 조건 L9<=L12를 추가하면 Project 3을 선택하면 L9가 1과 같고, 제약 조건은 L12(Project 4 이진)를 1로 강제합니다. 제약 조건은 3을 선택하지 않는 경우 Project 4의 변경 셀에 이진 Project 남겨야 합니다. 3을 선택하지 Project 경우 L9는 0과 같고 제약 조건은 4 이진 Project 0 또는 1과 같게 허용합니다. 새 최적 솔루션은 그림 30-4에 표시됩니다.

책 이미지

새 최적 솔루션은 3을 선택하면 Project 4를 선택해야 Project 계산됩니다. 이제 프로젝트 1에서 10까지의 4개의 프로젝트만 할 수 있습니다. (그림 30-5에 표시된 P1-P10 워크시트의 대부분 4개에서 참조) 셀 L8에서 SUM( A6:A15)을 사용하여 프로젝트 1에서 10과 연결된 이진 값의 합계를 계산합니다. 그런 다음 제약 조건 L8<=L10을 추가하여 처음 10개 프로젝트 중 4개가 선택되도록 합니다. 새 최적 솔루션은 그림 30-5에 표시됩니다. NPV는 9.014억 달러로 감소했다.

책 이미지

일부 또는 모든 변경 셀이 이진 또는 정수로 필요한 선형 솔버 모델은 일반적으로 모든 변경 셀이 분수로 허용되는 선형 모델보다 해결하기가 더 어렵습니다. 이러한 이유로 이진 또는 정수 프로그래밍 문제에 대한 최적의 솔루션에 종종 만족합니다. 솔버 모델이 오랫동안 실행되는 경우 해결기 옵션 대화 상자에서 허용 오차 설정을 조정하는 것이 좋습니다. (그림 30-6을 참조합니다.) 예를 들어 허용 오차 설정 0.5%는 솔버가 최초로 최적의 대상 셀 값의 0.5% 내에 있는 실현성 있는 솔루션을 찾을 때 중지됩니다(이진 및 정수 제약 조건이 생략될 때 찾을 수 있는 최적의 대상 값입니다). 종종 10분 내에 최적의 응답을 10% 이내로 찾거나 컴퓨터 시간의 2주 안에 최적의 솔루션을 찾는 선택에 직면하는 경우가 종종 있습니다. 기본 허용 오차 값은 0.05%입니다. 즉, 솔버가 0.05% 내에 있는 대상 셀 값을 찾을 때 해결사가 중지됩니다.

책 이미지

  1. 회사에는 9개의 프로젝트가 고려 중입니다. 각 프로젝트에 의해 추가된 NPV 및 다음 2년 동안 각 프로젝트에 필요한 자본은 다음 표에 나와 있습니다. (모든 숫자는 수백만입니다.) 예를 들어 Project 1은 NPV에 1,400만 달러를 추가하고 1년 동안 1,200만 달러, 2년 동안 3백만 달러의 지출이 요구됩니다. 1년 동안 프로젝트에서 5천만 달러의 자본을 사용할 수 있으며 2년 동안 2,000만 달러를 사용할 수 있습니다.

NPV

1년 지출

2년 지출

Project 1

14

12

3

Project 2

17

54

7

Project 3

17

6

6

Project 4

15

6

2

Project 5

40

30

35

Project 6

12

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

12

18

3

  • 프로젝트의 일부를 진행할 수 없지만 프로젝트의 전체 또는 전혀를 이행해야 하는 경우 NPV를 어떻게 최대화할 수 있나요?

  • 4 Project 진행하는 경우 5를 Project 해야 하다고 가정합니다. NPV를 최대화하는 방법

  • 출판 회사가 올해 발행해야 하는 36권 중 어느 책을 결정하려고 합니다. 파일 Pressdata.xlsx 각 책에 대한 다음 정보를 제공합니다.

    • 예상 수익 및 개발 비용(수천 달러)

    • 각 책의 페이지

    • 이 책이 소프트웨어 개발자의 대상을 에디트하는지 여부(E 열 1로 표시)

      게시 회사는 올해 최대 8500페이지의 책을 게시할 수 있으며 소프트웨어 개발자를 위해 최소한 4권 이상의 책을 게시해야 합니다. 회사는 어떻게 수익을 극대화할 수 있나요?

이 문서는 2007년 Microsoft Office Excel 데이터 분석 및 비즈니스 모델링에서 적용했습니다. Wayne L. Winston.

이 교실 스타일 책은 창의적이고 실용적인 애플리케이션을 전문으로 하는 잘 알려진 통계학자이자 비즈니스 교수인 Wayne Winston의 일련의 프레젠테이션에서 Excel.

추가 도움이 필요하신가요?

기술 향상
교육 살펴보기
새로운 기능 우선 가져오기
Microsoft Office 참가자 참가

이 정보가 유용한가요?

언어 품질에 얼마나 만족하시나요?
사용 경험에 어떠한 영향을 주었나요?

의견 주셔서 감사합니다!

×