문제 다음과 같이 1월~6월 까지의 월별 수요예측과 작업일수가 주어져 있다고 가정하고 향후 6개월간의 생산계획을 수립해 보자.
월 1 2 3 4 5 6 합계 7
수요예측 (unit) 1,800 1,500 1,100 900 1,100 1,600 8,000 2,000
작업일수(day) 22 19 21 21 22 20 125 22
원자재구입비용 100.00 $/unit
재고유지비용 1.50 $/unit/month 파일이름을 "중간고사(이름)"으로 변경시키시오.
품절에 따른 추가비용 5.00 $/unit/month 파일형식은 "매크로사용문서"로 하시오.
충원및 훈련비용 200.00 $/man
해고비용 260.00 $/man 완성된 답안은 본인의 USB에 저장하고 HISNET에 올리고 확인한다.
개당작업시간 5.00 hr/unit
1일 노무시간 8.00 hr/day/man
정규임금 4.50 $/hr
초과임금 7.50 $/hr
초기인력 40 명
초기재고 400.00 units
기말재고 (계획1에만) 25% 다음달 수요의 % 예: 3월의 기말재고는 900 * 25% = 225
Step1(5점): 다음과 같은 총괄 생산계획을 만들 수 있다. (아래의 표에 수식을 삽입하여 완성하시오.)
월 1 2 3 4 5 6 7
초기재고 400 375 275 225 275 400
수요예측 1,800 1,500 1,100 900 1,100 1,600 2,000
기말재고 375 275 225 275 400 500 다음달 수요의 25%
생산요구량 1,775 1,400 1,050 950 1,225 1,700 월생산요구량 = 수요예측 + 기말재고 - 초기재고
Step 2(45점): 다음 각 생산계획에 따른 비용을 계산하도록 워크시트 "Step2-계획비교"를 완성하시오.
계획 1(15점): 하루 8시간 작업기준으로 필요한 작업인력의 고용수준을 계산한 후 충원/해고할 인력의 수를 구하여 충원/해고비용을 계산하고 소요되는 정규임금 및 재고비용을 더하여 총비용을 계산한다.
계획 2(15점): 현재 인원 40명을 고정시키고 재고를 변화시켜 품절과 재고비용에 드는 비용을 계산하고 정규임금을 포함하여 총비용을 계산한다.
계획 3(15점): 계획 2에서 품절에 발생하는 달에는 품절허용 대신 초과작업을 하는 경우 발생하는초과 비용과 재고비용, 정규임금을 더하여 총비용을 계산한다.
워크시트 "Step2-계획비교" 중 계획3에서 매크로 함수 이용하기 (10점)
Step 3(10점): 세 가지 계획에 필요한 비용을 계산하여 비교하고 월별 총비용을 비교하는 그래프를 그리는 것을 매크로 녹음하여 주어진 버튼에 연결하시오.
Step4(10점): 충원비용과 해고비용이 다음과 같이 변할 때 생산계획 1에 따는 6개월간 총비용을 구하시오.
198,140.07
$ $ 225.00 $ 250.00 $ 275.00 $ 300.00 180.00
$ $ 196,659.19 $ 197,213.00 $ 197,766.81 $ 198,320.62 200.00
$ $ 197,364.74 $ 197,918.55 $ 198,472.36 $ 199,026.16 220.00
$ $ 198,070.29 $ 198,624.09 $ 199,177.90 $ 199,731.71
Step 5(5점): 계획 1의 경우 충원 및 훈련비용을 $200로 고정하고, 해고비용을 변화시켜 6개월 총비용이 $177,000이 되는 해고비용을 구하여 아래에 기록하시오.
해고비용은 다시 $250로 변경하시오.
Step 6(10점): 시나리오분석을 하여 세가지 경우에 대하여 월별 총비용이 변화하는 것을 비교하시요.
시나리오 내용을 이곳으로 복사를 하여야 진행 가능
현재값 불경기 호경기
충원및훈련비용($/man) 200 180 210 월 1 2 3 4 5 6 Total
해고비용($/man) 250 220 260 총생산비용 $42,585.23 $33,049.61 $27,811.18 $22,561.31 $29,467.97 $42,664.77 $198,140.07
정규임금 ($/hr) 4 4 4.5
초과임금($/hr) 6 5.5 7.5
Step 7(5점): D54에 월을 입력하면 D55와 D56에 해당월의 수요예측치와 작업일수가 나타나도록 HLOOKUP을 사용하시오.
월 3
수요예측 1,100
작업일수 21
해고비용
충원 및 훈련비용
계획 1(15점): 하루 8시간 작업기준으로 필요한 작업인력의 고용수준을 계산한 후 충원/해고할 인력의 수를 구하여 충원/해고비용을 계산하고 소요되는 정규임금 및 재고비용을 더하여 총비용을 계산한다.
월 1 2 3 4 5 6 총계
생산요구량 1,775 1,400 1,050 950 1,225 1,700 8,100
생산소요시간(생산요구량X작업시간) 8,875 7,000 5,250 4,750 6,125 8,500 40,500
월간 작업가능일수 22 19 21 21 22 20 125
소요인력수 (생산소요시간/일인당 월작업기능시간): 소수점 허용 50.4 46.1 31.3 28.3 34.8 53.1 244
충원인력: 소수점 허용 10.4 0.0 0.0 0.0 6.5 18.3 35
충원비용 2,085 0 0 0 1,305 3,665 7,055
해고인력: 소수점 허용 0.0 4.4 14.8 3.0 0.0 0.0 22
해고비용 0 1,137 3,849 774 0 0 5,760
정규임금 (생산소요시간 X시간당 임금) 39,938 31,500 23,625 21,375 27,563 38,250 182,250
기말재고 375 275 225 275 400 500
재고관리비용 (기말재고 x 재고유지비용) 563 413 338 413 600 750 3,075
월별 총비용 (충원비용+해고비용+정규임금+재고관리비용) 42,585 33,050 27,811 22,561 29,468 42,665 198,140
계획 2(15점): 현재 인원 40명을 고정시키고 재고를 변화시켜 품절과 재고비용에 드는 비용을 계산하고 정규임금을 포함하여 총비용을 계산한다.
월 1 2 3 4 5 6 총계
초기재고 400 8 -276 -32 412 720
월간작업가능일수 22 19 21 21 22 20 125
가용생산시간 (월간작업일수X1일작업시간X명수) 7,040 6,080 6,720 6,720 7,040 6,400 40,000
실제생산량 (가용생산시간/작업시간) 1,408.0 1,216.0 1,344.0 1,344.0 1,408.0 1,280.0 8,000
생산요구량 (필요없음) 1,775
기말재고 (기초재고 +실제생산량 - 예측수요량) 8 -276 -32 412 720 400
품절 비용 0 1,380 160 0 0 0 1,540
재고관리비용 12 0 0 618 1,080 600 2,310
정규임금 31,680 27,360 30,240 30,240 31,680 28,800 180,000
월별 총비용 (품절비용 + 재고관리비용 + 정규임금) 31,692 28,740 30,400 30,858 32,760 29,400 183,850
계획 3(15점): 계획 2에서 품절이 발생하는 달에는 품절허용 대신 초과작업을 고려한다. 품절은 허용되지 않는다. 이 경우 발생하는초과 비용과 재고비용, 정규임금을 더하여 총비용을 계산한다.
월 1 2 3 4 5 6 총계
초기재고 400 8 -276 -32 412 720 1,232
월간작업가능일수 22 19 21 21 22 20 125
가용생산시간 (월간작업일수 x 1일작업시간X명수) 7,040 6,080 6,720 6,720 7,040 6,400 40,000
정규시간 실제생산량 (가용생산시간/작업시간) 1,408 1,216 1,344 1,344 1,408 1,280 8,000
사용가능제품 (기초재고 + 정규시간생산량) 1,808 1,224 1,068 1,312 1,820 2,000 9,232
생산요구량 0
초과작업 생산량 (초기재고+정규생산량 과 예측수요량을 비교) 0 276 32 0 0 0 308
초과작업비용 0 2,070 240 0 0 0 2,310
기말재고 (기초재고+정규생산량+초과작업생산-수요) 8 0 0 412 720 400 1,540
재고관리비용 12 0 0 618 1,080 600 2,310
정규임금 31,680 27,360 30,240 30,240 31,680 28,800 180,000
월별 총비용 (초과작업비용 + 재고관리비용 + 정규임금) 31,692 29,430 30,480 30,858 32,760 29,400 184,620
매크로함수(10점): 계획 3에서 초기재고, 정규생산량, 예측수요량 세개의 값이 주어질 경우 초과작업생산량을 구하는 매크로함수를 만들어서 초과 작업생산량을 구하시오.
1 2 3 4 5 6
초과생산량
