한빛출판네트워크

저자: 최준선
출처: 회사에서 바로 통하는 엑셀 실무 활용 문서+기능(최준선 저, 한빛미디어 2005) 중 4부 예측 문서 5번


이번에는 일정한 흐름에서 데이터를 예측하는 작업이 아니라 주어진 조건에 맞는 값을 계산하는 비선형 툴인 해 찾기 기능을 이용해서 계산 작업을 진행해 보겠습니다. 해 찾기 기능은 앞서 잠깐 경험해 봤지만, 실제 업무를 활용할 때 내리기 어려운 결정이나 판단을 대신해 줄 수 있는 강력한 툴입니다. 이번 작업을 통해 조건을 어떻게 표로 규정해 우리가 원하는 조건을 만족하는 값을 엑셀을 통해서 계산할 수 있는지 알아보겠습니다.





작업 순서
(1) 해 찾기를 실행할 조건 정리하기
(2) 조건표 구성하기
(3) 해 찾기 실행해 최적값 계산하기
(4) 해 찾기 실행 결과 정리하기

1. 해 찾기를 실행할 조건 정리하기

해 찾기 기능을 이용할 때 가장 중요한 부분은 바로 결과값을 얻기 위해 알아야 할 많은 조건을 하나하나 정리하는 작업입니다. 중요도로 따지자면 이러한 조건을 정리하는 작업이 작업의 50%고, 정리한 조건을 해 찾기를 실행할 수 있도록 조건표로 구성하는 것이 40%며, 실행하는 것이 10% 정도입니다. 그만큼 조건들을 정리하는 작업이 중요한데, 이번 작업은 프로젝트에 투입한 개발 인력을 프로젝트 기간중의 고급, 중급, 초급 인원을 어떻게 배열해야 지불할 비용을 최소화할 수 있는지 알아보겠습니다.

Step 1 : 기간 정리하기
제일 먼저 프로젝트의 전체 작업 기간이 얼마인지 정리해야 합니다. 정리는 아무리 간단한 사항이라도 꼭 적어두는 습관을 들여야 해 찾기를 실행할 때, 발생할 수 있는 오류를 최소화할 수 있습니다. 전체 작업 기간은 7개월입니다.


Step 2 : 투입인력 총수 정리하기
프로젝트의 월별 작업량에 필요한 인원을 기록합니다. 이때 중요한 것은 고급, 중급, 초급 개발 인력 중 어느 하나를 기준으로 작성해야 한다는 것입니다. 기준 없이 정리하면 해 찾기를 실행하기가 어려워집니다. 월별 필요 인력을 월별로 입력합니다(20, 35, 50, 50, 45, 30, 20).


Step 3 : 고급 프로그래머의 비율 정리하기
프로젝트를 진행하면 초급과 중급 프로그래머가 아무리 많아도 고급 프로그래머만큼 성과를 내지 못하는 경우가 많습니다. 월별 투입 인원 중 고급 프로그래머 비율이 항상 일정하게 유지되도록 합니다. 물론 고급 프로그래머는 그만큼 비싸기 때문에 프로젝트 비용을 증대시키는 원인이 되기도 합니다. 여기서는 고급 프로그래머 비율을 20%, 15%, 10% 순으로 조정해서 가장 적당한 비율이 어느 정도인지 확인하려 합니다.


Step 4 : 중급 프로그래머의 비율 정리하기
고급 프로그래머만 있다면 고급 프로그래머가 자잘한 코딩 업무부터 잔 손질이 많이 가는 업무까지 해야 하므로 비효율적입니다. 중급 프로그래머를 이용해 고급 프로그래머가 설계해둔 프로그램의 뼈대를 만들게 하고, 초급 프로그래머에게 필요한 코딩을 하도록 해야 업무가 효율적으로 진행됩니다. 조건을 고급:중급 비율이 항상 1:2가 되도록 지정합니다.


Step 5 : 급여 정리하기
고급, 중급, 초급 프로그래머 비용을 적어놓습니다. 고급, 중급, 초급순으로 지불할 비용 450, 350, 200만원을 적습니다.


Step 6 : 업무 처리 능력을 산술화하기
2) 프로젝트 투입 인력을 초급 프로그래머를 기준으로 계산했으므로 초급을 기준으로 고급과 중급 프로그래머의 업무 처리 능력을 산술화하는 작업을 진행합니다. 즉, 초급 인원이 1만큼 일할 때, 고급과 중급 인원의 처리율을 산술화하는 것입니다(이렇게 산술화를 해 둬야 해 찾기 기능을 이용해 계산 작업을 진행할 수 있습니다). 여기서는 초급 : 중급은 1 : 1.3, 초급 : 고급은 1 : 1.5로 설정합니다.


2. 조건표 구성하기

Step 1 : 프로젝트 투입인력 계획표 구성하기
01 과정에서 정리한 조건을 필요에 따라 표로 정리하는 작업을 진행합니다. 이러한 표 구성 작업은 반복적인 훈련을 통해 효율적인 조건표를 구성할 수 있게 되는데 처음에는 조금 어렵게 느끼시는 분도 있을 것입니다. 하지만 표를 구성할 수 있어야 ‘해 찾기’를 실행할 수 있으므로 이 부분은 매우 중요합니다. 이대로 책을 덮고 직접 표를 구성해 본 다음 완성본과 비교해 보는 것도 좋은 훈련이 됩니다.


Step 2 : 조건표 작성하기
고급, 중급, 초급 프로그래머별로 정리할 수 있는 내용(처리 비율, 급여)는 바로 입력해 놓습니다. 이 때, 참여비율은 D28 셀에만 20%를 입력하고 D29 셀에는 =D28*2(중급은 고급인력과 1:2 비율을 유지)를, D30 셀에는 =1-SUM(D28:D29) 를 입력합니다.


Step 3 : 개발 인력 입력하기
초급 프로그래머를 기준으로 개발 인력을 선정했기 때문에 시작할 때는 고급과 중급 개발 인력을 0으로 설정합니다.

B8:H9 범위를 선택하고 F2 키를 눌러 0을 입력하고 Ctrl+Enter 키를 눌러 범위 전체에 0이 입력되도록 합니다. 그런 다음 B10:H10 셀에 20, 35, 50, 50, 45, 30, 20을 순서대로 입력합니다.


Step 4 : 환산합계 구하기
개발 인력은 초급 인원을 기준으로 작성했으므로 ‘중급’과 ‘고급’ 인원의 처리 비율로 환산해서 계산을 하고 그 값이 ‘필요 인원’과 일치해야 됩니다. 그러므로 B11 셀에 =SUMPRODUCT(B8:B10, $B$28:$B$30)을 입력하고 H11 셀까지 채우기 핸들을 드래그해서 수식을 복사합니다.


Step 5 : 투입인력 합계 내기
I8:I11 셀을 범위로 지정하고 자동합계 도구(
)를 클릭해 합계를 구합니다.


Step 6 : 지불 비용 계산하기
조건표의 월급을 참조해 =전체인원*월급으로 지불할 총액을 계산합니다. K8 셀에 =I8*C28를 입력하고 K10 셀까지 채우기 핸들을 드래그해서 수식을 복사합니다. K11 셀에 =SUM(K8:K10)을 입력합니다. 여기서 계산된 K11 셀 값을 최소로 하는 인원들을 구하는 것이 이번 작업의 핵심입니다.


Step 7 : 실제 인원 계산하기
초기 산정 인원은 초급 기준이므로 고급과 중급 개발 인력을 투입하면 실제 투입인원이 달라지게 됩니다. 실제 인원을 확인할 수 있도록 B13 셀에 =SUM(B8:B10)을 입력하고 H13 셀까지 채우기 핸들을 드래그해서 수식을 복사합니다. I13 셀에는 =SUM(B13:H13)을 입력해 실제 총 인원을 계산합니다.


Step 8 : 개발 필요 인원 입력하기
B19:H19 셀에 순서대로 20, 35, 50, 50, 45, 30, 20을 입력합니다. I19 셀에는 =SUM(B19:H19)를 입력합니다.


Step 9 : 현재 투입 예정 인력의 비율을 계산
투입될 인원을 계산하면 고급, 중급 개발 인력의 비율이 적당한지 확인하기 위해 비율을 계산해야 합니다. B20 셀에 =B8/B$11을 입력하고, H22 셀까지 채우기 핸들을 드래그해서 수식을 복사합니다.


Step 10 : 지정한 조건표 확인하기
모든 설정을 제대로 끝냈다면 그림과 동일한 결과가 나타납니다. 해 찾기를 실행하기 전에 모든 조건을 다시 한번 꼼꼼히 살펴 봅니다.



3. 해 찾기 실행해 최적값 계산하기

Step 1 : ‘해 찾기’ 실행하기
[도구]-[해 찾기] 메뉴를 클릭합니다. 목표 셀은 지불 총액을 담고 있는 K11 셀을 지정하고, 해의 조건은 ‘최소값’을 선택해야 합니다. 그런 다음 값을 바꿀 셀로 B8:H10을 지정해 위 조건을 만족하는 인원을 계산하도록 요청합니다. [추가] 버튼을 클릭해서 해 찾기를 실행하는데 필요한 조건들을 설정합니다.


Step 2 : 총인원 합계 일치하도록 조건 설정하기
환산합계(11행)와 필요인원(19행)이 같도록 셀 참조 영역에는 B11:H11을 제한 조건에는 B19:H19을 범위로 지정합니다. 가운데 콤보상자의 비교 연산자는 ‘=’로 선택해서 두 범위의 같도록 결과값이 나오게 합니다. [추가] 버튼을 클릭해 조건을 추가합니다.


Step 3 : 개발 인원 계산할 때 최소 0보다 크도록 설정
고급, 중급, 초급 개발 인력이 계산될 때 음수가 나오지 않도록 다음과 같이 조건을 지정하고 [추가] 버튼을 클릭해 조건을 추가합니다.



Step 4 : 개발 인력은 정수 값으로 설정
개발 인력이 소수점이 나오지 않고 항상 정수값으로 나오도록 다음과 같이 조건을 설정하고 <추가> 버튼을 클릭해 조건을 추가합니다.



Step 5 : 고급 개발 인력 % 지정
고급 개발 인력은 최소 20% 이상이 되도록 제한 조건을 다음과 같이 설정하고 <추가> 버튼을 클릭해 조건을 추가합니다.



Step 6 : 중급 개발 인력 % 지정
중급 개발 인력이 고급 개발 인력의 2배를 넘도록 해야 하므로 다음과 같이 제한 조건을 설정하고 <추가> 버튼을 클릭해 조건을 추가합니다. 조건을 모두 설정했으므로 <취소> 버튼을 클릭해서 해 찾기 모델 설정 대화상자로 돌아갑니다.



Step 7 : 해 찾기 실행하기
설정값을 모두 확인하고 왼쪽 그림과 같으면 <실행> 버튼을 클릭해 해 찾기를 실행합니다.


Step 8 : 결과값 시나리오 저장하기
해 찾기 결과 대화상자가 나타나면 구해진 결과값을 확인한 다음 이상 없으면 <시나리오 저장> 버튼을 클릭합니다. 시나리오 저장 대화상자가 나타나면 시나리오 이름을 고급인력_20%로 지정하고 <확인> 버튼을 클릭합니다.

해 찾기 결과 대화상자로 돌아오면 <확인> 버튼을 클릭해 해 찾기 과정을 모두 종료합니다. 만약 해 찾기 결과 대화상자가 뜨지 않고 에러가 발생하면 다시 한 번 해 찾기 기능을 실행하면 됩니다.



Step 9 : 고급 개발 인력 비율 15% 변경 후 해 찾기 재실행
D28 셀에 입력되어 있는 고급 개발 인력의 투입 비율을 ‘20%’에서 ‘15%’로 조정한 다음 결과를 알고자 하면 값을 15%로 조정하고 [도구]-[해 찾기] 메뉴를 실행한 다음 해 찾기 모델 설정 대화상자가 뜨면 <실행> 버튼을 클릭해 결과를 확인합니다.

해 찾기 대화상자의 조건을 변경하거나 다른 해 찾기 작업을 진행하지 않았다면 설정해 놓은 상태가 그대로 보존되어 나타나지만 그렇지 않다면 앞의 과정을 다시 진행해 조건을 설정해줘야 합니다.


Step 10 : 중간 결과 보기 대화상자
해 찾기는 지정된 계산 횟수에 도달했는데 모든 조건을 만족하는 결과값을 찾지 못하면 중간 결과 보기 대화상자를 보여주면서 사용자의 선택을 기다립니다. 이 때, <계속> 버튼을 클릭하면 계산 횟수에 상관하지 않고 계속 조건을 만족하는 값을 계산하게 됩니다.

복잡하거나, 원하는 조건이 까다로운 경우 계산이 오래 걸리기 때문에 <계속> 버튼을 클릭하고 커피 한 잔 하시는 것도 좋은 방법입니다. 계산이 진행되는지 여부는 시트 탭 하단의 상태 표시줄을 보면 ‘계산 진행 과정’이 간략하게 표시되니 참고하기 바랍니다.






Step 11 : ‘해 찾기 결과’ 대화상자 설정
원하는 결과값이 표시되면 <시나리오 저장> 버튼을 클릭해 고급인력_15%로 입력하고 <확인> 버튼을 클릭해 저장하고, 해 찾기 결과 대화상자로 돌아오면 <확인> 버튼을 클릭해 해 찾기 대화상자를 닫습니다. 마지막으로 D28 셀의 고급 개발 인력의 투입비율을 ‘10%’로 조정한 다음 다시 한번 ‘해 찾기’ 작업을 진행하면 모든 작업을 종료됩니다.


4. 해 찾기 실행 결과 정리하기

의사 결정자를 설득할 때는 하나의 결과값보다 여러 가지 결과값을 통해 다양한 가능성에 대한 정보를 전달하는 것이 중요합니다. 실행한 해 찾기 결과 중 인원 비율과 인원 수, 총 비용 에 대한 것을 정리해서 전달합니다.

Step 1 : 시나리오 변경해 값 복사하기
해 찾기 결과를 시나리오로 저장해 둬야 할 때, [도구]-[시나리오] 메뉴를 이용해 결과값을 빠르게 확인할 수 있습니다. 메뉴를 클릭하면 시나리오 관리자 대화상자가 뜨는데 지정한 이름을 시나리오 목록에서 선택하고 <표시> 버튼을 클릭하면 해당 ‘해 찾기’ 결과가 표에 표시됩니다.

‘프로젝트투입인력계획표’ 시트에서 다음 부분을 복사해서 ‘해찾기결과’ 시트의 표에 붙여넣습니다. 시나리오에서 비율은 저장되지 않으므로 직접 입력해야 합니다.



Step 2 : ‘해 찾기’ 결과 요약표 작성하기
모든 작업을 마치면 다음 그림과 같이 해 찾기 결과를 일목요연하게 확인할 수 있습니다.