Question

1 Problem 4 Problem 5 5,000 4.000 0.10 3 Standard Corkscrew 4 Forecast 5 Proj Available 6 MPS Lot size: 2.000 3,000 1.000 2,000 3,500 1500 4.000 4,000 Direct lobor Standard corkscres Deluxe corkscrew Machine time Standard corkscres Deluxe corkscrew 2.000 0.20 Direct labor Machine time 10 Deluxe Corkscrew 11 Forecast 12 Proj Available 13 MPS Lot size: 2.000 500 3.000 3.000 2.500 1.000 2.500 1.000 1,000 165,000 corkscrews per week? YES YES YES YES Use the spreadsheet to do some trial and error solutions for this problem. The company wants to produce exactly 5,000 corkscrews each week. When the "5,000 corkscrews per week row shows all green and there are no red colored cells you've found an answer You can use the dropdown Tow to select possible numerical values. There are multiple awwers WAI uses two resources for each product that it builds direct labor and machine time from historical records, WAl calculates the following planning factors Direct labor for standard corkscrew is 0.10 hour Direct labor for deluxe Corkscrew is 0.5 hour Machine time for standard corkscrew is 0.20 hour Machine time for deluxe corkscrew is 0.40 hour Calculate the capacity needed for the MPS developed in Problem 4 NRWER Problem 7 Wine Accessories Inc. (WA) produces two models of cortecrews, the standard model and a deluxe model W ollows a level agregate plan, producing 20,000 corkscrews per month, or 5000 corkscrews per week. The MPS is developed in weekly time periods. The forecasts for each model and the projected available are shown in the next two tables. The replenishment order quantity is 2000 units for the standard model and 1000 units for the dele model Note that you can place multiple orders angle order is insufficient to cover the forecast you can produce 4000 units of the standard model necessary, or 2000 or 3000 units of the deluxe model) Remember that total weekly production is limited to 5000 corkscrews Develop an MPs for each of the products WAI knows from historical records that approximately 40 percent of its laboris used in Department 101 and 60 percent is used in Department 102. The reverse is true of machine time, 60 percent is used in Department 101 and 40 percent is used in Department Direct lobor Dept 101 Dept 102 Machine time Dept 101 Dept 102 Calculate the labor and machine hours needed in Department 101 for each period of the MPS developed in Problem 4 Department 102 Calculate the labor and machine hours needed in Department 102 for each period of the MPS developed in Problem

Answer 1

Problem 5	(solved)
	Direct Labor (hr)	Machine time (hr)
Std	0.1	0.2
Delux	0.3	0.4
needed capacity		1	2	3	4	Totals
Direct Labor (hr)		1100	700	700	700	3200
Machine time (hr)		1600	1200	1200	1200	5200
Problem 7	(solved)
	Direct Labor	Machine time
Dept 101	40%	60%
Dept 102	60%	40%
Dept 101		1	2	3	4	Totals
Direct Labor (hr)		440	280	280	280	1280
Machine time (hr)		960	720	720	720	3120
Dept 102		1	2	3	4	Totals
Direct Labor (hr)		660	420	420	420	1920
Machine time (hr)		640	480	480	480	2080

formula used:

G M IN 1 Problem 4 Problem 5 2 B co E F (Given) week 1 2 3 4 3000 3500 5000 4000 2000 1000 1500 500 500 2000 4000 4000 4000 2000 (solved) Direct Labor (hr) Machine time (hr) 0.1 0.2 0.3 0.4 Std Delux needed capacity Direct Labor (hr) Machine time (hr) 1 2 3 4 Totals =C6*$I$3+C13*$1$4 =D6*$1$3+D13*$1$4 =E6*$1$3+E13*$I$4 =F6*$1$3+F13*$1$4 =SUM(17:M7) =C6*$J$3+C13*$J$4 =D6*$J$3+D13*$J$4 =E6*$J$3+E13*$J$4 =F6*$J$3+F13*$J$4 ESUM(J8:M8) 3 Std Corkscrew 4 forecast 5 Proj. avail. 6 MPS 7 Lot size 8 9 10 Delux Corkscrew 11 forecast 12 Proj. avail. 13 MPS 14 Lot size Problem 7 week 1 2 3 4 2000 1500 1000 3000 3000 2500 2500 500 3000 1000 1000 1000 (solved) Direct Labor 0.4 0.6 2000 Dept 101 Dept 102 Machine time 0.6 0.4 1000 Totals Dept 101 Direct Labor (hr) Machine time (hr) 1 =$I$12*17 =$J$12*18 =$I$12*K7 =$J$12*K8 =$1$12*L7 =$J$12*L8 =$1$12*M7 =$J$12*M8 =SUM(J16:M16) I-SUM(J17:M17) Dept 102 Direct Labor (hr) Machine time (hr) =$I$13*]7 =$J$13*J8 2 =$1$13*K7 =$J$13*K8 =$I$13*L7 =$J$13*L8 =$1$13*M7 =$J$13*M8 Totals =SUM(J20:M20) ESUM(121:M21)