자본 예산 책정에 해 찾기 사용

대부분의 기업은 제한된 자원(일반적으로 자본 및 노동)에 따라 가장 큰 NPV(순 현재 가치)를 기여하는 프로젝트를 수행하려고 합니다. 소프트웨어 개발 회사가 수행해야 하는 20개의 소프트웨어 프로젝트 중 어느 것을 결정하려고 한다고 가정해 보겠습니다. 각 프로젝트에서 제공하는 NPV(수백만 달러)와 자본(수백만 달러)과 향후 3년 동안 필요한 프로그래머 수는 다음 페이지의 그림 30-1에 표시된 Capbudget.xlsx 파일의 기본 모델 워크시트에 제공됩니다. 예를 들어 Project 2는 9억 8,000만 달러를 산출합니다. 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와 같이 해 찾기 매개 변수 대화 상자를 채웁니다.

목표는 선택한 프로젝트(셀 B2)의 NPV를 최대화하는 것입니다. 변경되는 셀( 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도 선택해야 한다고 가정합니다. 현재 최적 솔루션은 Project 3을 선택하지만 Project 4는 선택하지 않으므로 현재 솔루션이 최적 상태를 유지할 수 없다는 것을 알고 있습니다. 이 문제를 해결하려면 Project 3의 이진 변경 셀이 Project 4의 이진 변경 셀보다 작거나 같다는 제약 조건을 추가하기만 하면됩니다.

그림 30-4 에 표시된 파일 Capbudget.xlsx If 3, 4 워크시트에서 이 예제를 찾을 수 있습니다. L9 셀은 Project 3과 관련된 이진 값, L12 셀은 Project 4와 관련된 이진 값을 나타냅니다. 제약 조건 L9<=L12를 추가하면 Project 3을 선택하면 L9가 1이고 제약 조건으로 인해 L12(Project 4 이진)가 1로 강제됩니다. 또한 프로젝트 3을 선택하지 않는 경우 제약 조건은 Project 4의 변경 셀에 이진 값을 제한 없이 유지해야 합니다. 프로젝트 3을 선택하지 않으면 L9가 0이고 제약 조건으로 인해 Project 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만 달러로 떨어졌습니다.

일부 또는 모든 변경 셀이 이진 또는 정수여야 하는 선형 해 찾기 모델은 일반적으로 모든 변경 셀이 분수가 될 수 있는 선형 모델보다 해결하기가 어렵습니다. 이러한 이유로 이진 또는 정수 프로그래밍 문제에 대한 최적에 가까운 솔루션에 만족하는 경우가 많습니다. 해 찾기 모델이 오랫동안 실행되는 경우 해 찾기 옵션 대화 상자에서 허용 오차 설정을 조정하는 것이 좋습니다. (그림 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년 동안 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를 최대화하려면 어떻게 해야 하나요?

• Project 4가 수행되는 경우 프로젝트 5를 수행해야 한다고 가정합니다. NPV를 최대화할 수 있는 방법은 무엇입니까?

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

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

  • 각 책의 페이지

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

    한 출판사는 올해 최대 8,500페이지에 달하는 책을 출판할 수 있으며 소프트웨어 개발자를 대상으로 4권 이상의 책을 출판해야 합니다. 회사는 어떻게 수익을 극대화할 수 있을까요?

이 문서는 Wayne L. Winston의 Microsoft Office Excel 2007 데이터 분석 및 비즈니스 모델링 에서 수정되었습니다.

이 교실 스타일의 책은 Excel의 창의적이고 실용적인 응용 프로그램을 전문으로하는 잘 알려진 통계학자이자 비즈니스 교수 인 웨인 윈스턴 (Wayne Winston)의 일련의 프레젠테이션에서 개발되었습니다.