Question

Matthew and Mark work for AMG, and want to do some aggregate planning using "Level Production without Backorders" for the manufacturing of brake calipers for Mercedes Benz S63 sedans. They figure the front brake calipers take 9 hours to forge and assemble while the rear calipers take 7 hours to forge and assemble. They pay their people $24.00 per hour and it costs them $900.00 to hire and $1,300 to fire each employee. They currently employ 6 people and have 129 front calipers in inventory and 113 rear calipers in inventory. Inventory carrying costs are $7.00 per labor hour. Assume 40 hours per week and 4 weeks per month. Please disregard rounding errors and select the closest answer.

The following is the 6 month demand forecast in units:

Month	1	2	3	4	5	6
Front Calipers	200	750	330	550	240	180
Rear Calipers	230	730	340	500	250	220

You will need to first figure what the level production amount will be using the following information over 6 periods:

                                                  1                2                  3                  4                 5                    6       

Cumulative Demand

Beginning Inventory (Constant using the inventory levels given)

Total Cumulative Production

Monthly Production

Use the following model to construct your analysis:

                               1              2                  3                 4               5                  6

Beginning Inventory

Demand

Production

Ending Inventory

Hire Costs

Fire Costs

Wage Costs

Inventory Costs

Total Costs

1. What is the cumulative demand for all periods in labor hours?

A. 28,970

B. 36,140

C. 27,080

D. 29,350

2. What is the level production amount in labor hours?

A. 5,089

B. 4,667

C. 5,511

D. 6,780

3. How many employees will they need to hire (or fire)?

A. Hire 37

B. Hire 32

C. Hire 26

D. Fire 2

4. What is the hiring (or firing) cost in Period 1?

A. $21,600

B. $33,300

C. $23,400

D. $26,100

5. What is the total cost for implementing this plan?

A. $917,672

B. $760,599

C. $1,125,689

D. $777,862

Answer 1

Solving this LP, we will get the following solution -

Month	Forecast	Workers	Hire	Fire	Ending Inventory	Production	Hours	Hours	Prod - (E.I - B.I)	Inventory
	Front	Rear				Front	Rear	Front	Rear	Consumed	Available	Front	Rear	labor hrs.
			3			129	113
1	200	230	32	28.81	0	507	0	578	117	5089	5089	200	230	4053
2	750	730	32	0	0	28	0	271	730	5089	5089	750	730	222
3	330	340	32	0	0	164	0	466	340	5089	5089	330	340	1311
4	550	500	32	0	0	0	0	386	500	5089	5089	550	500	0
5	240	250	32	0	0	271	0	511	250	5089	5089	240	250	2169
6	180	220	32	0	0	617	0	526	220	5089	5089	180	220	4938
Cost Estimates
Month	Prod	Hire	Fire	Inventory	Total
1	111958	25926	0	28371	166255
2	111958	0	0	1554	113512
3	111958	0	0	9177	121135
4	111958	0	0	0	111958
5	111958	0	0	15183	127141
6	111958	0	0	34566	146524
Totals	671748	25926	0	88851	786525

Note that hiring 28.81 labor is impossible. So, keeping all other things same, we change this number to 29 and get the following result which we consider final result -

Month	Forecast	Workers	Hire	Fire	Ending Inventory	Production	Hours	Hours	Prod - (E.I - B.I)	Inventory
	Front	Rear				Front	Rear	Front	Rear	Consumed	Available	Front	Rear	labor hrs.
			3			129	113
1	200	230	32	29	0	507	0	578	117	5089	5120	200	230	4053
2	750	730	32	0	0	28	0	271	730	5089	5120	750	730	222
3	330	340	32	0	0	164	0	466	340	5089	5120	330	340	1311
4	550	500	32	0	0	0	0	386	500	5089	5120	550	500	0
5	240	250	32	0	0	271	0	511	250	5089	5120	240	250	2169
6	180	220	32	0	0	617	0	526	220	5089	5120	180	220	4938
Cost Estimates
Month	Prod	Hire	Fire	Inventory	Total
1	111958	26100	0	28371	166429
2	111958	0	0	1554	113512
3	111958	0	0	9177	121135
4	111958	0	0	0	111958
5	111958	0	0	15183	127141
6	111958	0	0	34566	146524
Totals	671748	26100	0	88851	786699

Answers -

1. 27,080
2. 5,089
3. Hire 29
4. $26,100
5. $786,699

----------------------------------------

Part 1: Cumulative demand for labor hours

Month	1	2	3	4	5	6
Front Calipers	200	750	330	550	240	180
Rear Calipers	230	730	340	500	250	220
Labor hours	2520	8920	4000	6400	2920	2320
Cumulative	2520	11440	15440	21840	24760	27080

O I J K L Production Hours Hours F ront Rear Consumed Available MN Prod - (E.I-B.1) Front Rear Inventory labor hrs. M EF G H 1 Month Forecast Workers Hire Fire Ending Inventory Front Rear Front Rear 129 113 200 230 =D3+E4-F4 29 506.625 O 750 730 =D4+E5-F5 27.750 330 340 =D5+E6-F6 163.8750 550 500 =D6+E7-F7 250 =D7+E8-F8 271.125 JO 180 220 =D8+E9-F9 617.250 Cost Estimates 11 Month Prod Hire Fire Inventory Total 12 1 =K4*22 =E49900 =F4*1300 =7*04 =SUM(B12:E12) 13 2 =K5*22 =E5*900 =F5*1300 =7*05 =SUM(B13:E13) 14 3 =K6*22 =E6*900 =F6*1300 =7*06 =SUM(B14:E14) =K7*22 =E7*900 =F7*1300 =7*07 =SUM(B15:E15) 16 5 =K8 22 =E8.900 =F8*1300 =7*08 =SUM(B16:E16) =K9*22 =E9 900 =F9*1300 =7*09 =SUM(B17:E17) 18 Totals =SUM(B12:B17) =SUM(C12:017) =SUM(D12:017) -SUM(E12:E17) =SUM(F12:F17) 577.625 271.125 466.125 386.125 511.125 526.125 117 730 340 500 250 220 =8*14+4* J4 =160*D4 =14-(G4-G3) =14-(H4-H3) =G4*8+H4*4 =8*15+4*5 =160*D5 =15-(G5-G4) =)5-(H5-H4) =G5*8+H5*4 =8*16+4*6 =160*D6 =16-(G6-G5) =J6-(H6-H5) =G6*8+H6*4 =8*17+47 =160*D7 =17-(G7-G6) =J7-(H7-H6) =G7*8+H7*4 =8*18+4*J8 =160*D8 =18-(G8-G7) =38-(H8-H7) =G8*8+H8*4 =8*19+4*19 =160*D9 =19-(G9-G8) = 39-(H9-H8) =G9*8+H9*4 240 15 4

Set Objective: SF518 To: O Max Min O Value Of: By Changing Variable Cells: SE$4:$J$9 Subject to the Constraints: SE$5:SF59 = 0 SK$4:$K$9 = $L$4:$L$9 SM$4:SNS9 = $B$4:$C$9 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth.

2 6 A B C D E 1 Month 3 5 2 Front Calipers 200 750 330 550 240 180 3 Rear Calipers 230 730 340 500 250 220 4 Labor hours =B2*8+B3*4 =C2*8+C3*4 =D2*8+D3*4 =E2*8+E3*4 =F2*8+F3*4 =G2*8+G3*4 5 Cumulative =SUM($B$4:B4) =SUM($B$4:04) =SUM($B$4:04) =SUM($B$4:14) =SUM($B$4:F4) =SUM($B$4:04)