Question

Please answer questions using Microsoft excel, and please show formulas. Thank you.

School of Business BUS 476-Operations Management Dr. Enrique G. Zapatero, Professor Homework #8-Aggregate Planning Instructions: Use MS Excel to produce the computations to support your answers to ProblemsI, and I. Problem I: A production manager needs to develop a production schedule to meet the following demand Period Demand 600 600 800 1000 1200 600 2 Productions costs are as follows: (a) regular time: $3 per unit. (b) over-time: $5 per unit. (c) subcontract: $7 per unit. (d) Inventory carrying cost $2 per unit per period (e) Back-order costs = $5 per unit per period. You have to meet all the demand. You start with no inventory at the beginning of the first period, and end with no inventory at the end of the sixth period Schedule A: Suppose the manager has adopted a policy of level production (i.e., constant production rate for all periods). Assume no over-time or subcontracting is allowed Schedule B: Suppose some workers have retired, and the regular production rate can be only 700 units per period. Further, no subcontracting is allowed However, it is possible to schedule production on an over-time basis. (Hint: You normally schedule over-time production during peak demand.) Schedule C: Suppose the regular rate of production per period is 700. However you are allowed to buy 200 units from a subcontractor during each of the periods 3, 4, and 5. Develop a production schedule to meet the demand Schedule D: Suppose the regular rate of production is 700 units per period Further, you are allowed to produce 100 units during each of the periods 3,4 and 5 on an overtime basis, but the subcontractor has gone out of business. You can hire two temporary workers during the periods 3, 4, and 5. Each worker can produce 50 units per period at the regular cost. But it costs $200 to train a worker and check the quality of the product during each of the three periods

Answer 1

Schedule A can be solved as shown below (Excel Formula is shown in each cell. Kindly note this to understand):

This is for Level planning; means constant production over period to meet demand.

Its solution will be as shown below:

Costs Per Unit Units Cost Regular Time Labor Co Overtime SUM(B28.G28 G4 H4 6 Beginning Inventor 8 Labor Standard (units/worker) 11 10 Subcontractin Inventory Holding Cost Backorders Hiring SUM(B20:G20 G6 H6 SUM(B21:G21 G7 H7 SUM(B24 G24)-G8 H8 SUM(B25:G25 G9 H9 inning Workforce 800 500 750 Total Costs -SUM(13:19 12 Period 13 Demand 14 Cumulative Demand 15 Net Cumulative Demand 600 -B14+C13 800 1000 =D14+E13 1200 -E14+F13 600 -B13 C14+D13 F14+G13 B14-Beginninglnventon C14-Beginninglnventd D14-Beginninginvento E14 nninglnventd F14-Beginninglnvetd G14-Beginninglnven 17 Production/nventory Planning 18 Production 19 Cumulative Production 20 Inventory (Excess Units 21 Backorders (Units Short 800 D19+E18 -MAX(0 E15) AXO,F19-F15 -MAX(O,E15-E19 800 -F19+G18 800 800 800 800 E19+F18 B18 MAXO,B19-B15 MAXI0,B15-819 B19+C18 MAXO,C19-C15 MAXIO,C15-C19)M C19+D18 MAXO D19-D15 MAXIO,D15-D19 E19-E MAX(0,G19-G15 -MAXI0,G15-G19 MAKO F15-F19) 23 Capacity Plann 24 Workers Hired 25 Workers Laved Off 26 Workforce Available B7+B24.B25 5E24 E25E26+F24 F25 -C26+D24-D25 -8264C24-C25 -LaborStandard"C26 E26+F24-F25 -LaborStandard"F26 MAX(O,F18-F27 -F26+G24-G25 -LaborStandard"G26 units) -LaborStandard"B26 -LaborStandard"D26 -LaborStandard"E26 28 Overtime (units 29 Subcontracting (units 30 MAX(0 B18-B27 MAX/0. B1 8-B27 B28) MAX 0,C18-C27 MAXI 0 С 18-C27-C28 MAX 0,D18-D27 M. X 0 D 18-D27-D28) MAX(0E18-E27 MAX(0.Е 18-E27-E281=MA KO F 18-F27-F28)=MAX(0. G 18-G27-G2 -27+E28 + E29 0,G18-G27 Total Production Capacity (units 27 B28+B29

Costs Reqular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Hirin Per Unit Units Cost $3.001 48001 $14.400 $5.00 $7.00 6 Beginning Invento 7 Beginning Workforce 8 Labor Standard (units/worker $0 $2.001 12001 $2.400 200 $1,000 $0 $5.00 $500.00 800 10 Total Costs $17,800 12 Periocd 13 Demand 14 Cumulative Demand 15 Net Cumulative Demand 16 17 Production/lnventory Plannin 18 Production 19 Cumulative Production 20 Inventory (Excess Units 21 Backorders (Units Short 600 600 600 600 1200 1200 800 2000 2000 1000 3000 3000 1200 4200 4200 600 4800 4800 800 800 200 800 1600 400 800 2400 400 800 3200 200 800 4000 800 4800 200 23 Capacity Plannin 24 Workers Hired 25 Workers Laved Off 26 Workforce Available 27 Reqular Time Ca 28 Overtime (units 29 Subcontracting (units 30 Total Production Capacity (units 800 800 800 800 800 800 800 800 800 800 800 800 units 800 800 800 800 800 800

Hence total cost of Production for this Schedule A is $ 17,800.00

Schedule B:

Since in this schedule, the only change is that, the # of workers have reduced and hence the production capacity. Since overtime is allowed but no subcontracting, hence its solution will be as shown below:

The Formula will remain same as shown in Schedule A. The only changes I made in Shell B7; Begining workforce is 700:

Hence the overall cost for this schedule is $ 19,000.00

Costs Reqular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Per Unit Units Cost $3.004200$12,600 600 3,000 $5.00 $7.00 6 Beginning Invento 7 Beginning Workforce 8 Labor Standard (units/worker $2.001200$2,400 200S1,000 700 $5.00 $500.00 $750.00 $0 $19,000 Total Costs 12 Period 13 Demand 14 Cumulative Demand 15 Net Cumulative Demand 600 600 600 600 1200 1200 800 2000 2000 1000 3000 3000 1200 4200 4200 600 4800 4800 17 Production/Inventory Plannin 18 Production 19 Cumulative Production 20 Inventory (Excess Units 21 Backorders (Units Short 800 800 200 800 1600 400 800 2400 400 800 3200 200 800 4000 800 4800 200 23 Capacity Plannin 24 Workers Hired 25 Workers Laved Off 26 Workforce Available 27 Reqular Time Capacity (units 28 Overtime (units 29 Subcontracting (units 30 Total Production Capacity (units 700 700 100 700 700 100 700 700 100 700 700 100 700 700 700 700 100 800 800 800 800 800 800

Schedule C:

In this case, the Formula will be changed in Row No 28 & 29. Row # 28 will be zero. But Subcontracting is allowed hence the value in Row # 29 will be as shown below:

And its solution is shown below:

Costs Per Unit Units Cost Regular Time Labor Co Overtime Subcontra Inventory Holding Cost -SUM(B27 G27)G3 H3 SUM(B28 : G28) G4.H4 -SUM B29:G29)G5H5 -SUM(B20:G20)G6H6 SUM(B21:G21)G7 H7 SUM B24 G24)G8H8 -SUM B25 G25)G9TH9 6 Beginning Invento 7 Beginning Workforce 8 Labor Standard (units/worker 700 500 Total Costs -SUM(13:19) 12 Period 13 Demand 14 Cumulative Demand 15 Net Cumulative Demand 16 17 ProductionInventory Planning 800 -C14+D13 600 -F14+G13 600 600 D14+E13 1200 -E14+F13 813 814-Beginninglnvento- C14 B14+C13 nventd-D14-BeginningInvento! 14 inningInvent F14 inningInventd G14-Beginnin 800 -C19+D18 800 D19 E18 18 Production 19 Cumulative Production 20 Inventory (Excess Units 21 Backorders (Units Short 800 800 800 800 -F19+G18 818 MAX 0,B19-B15 MAX(0,B15-B19) B19+C18 E19+F18 0,D19-D15 MAXI0 D15-D19) 0,E19-E15 MAK(O,F19-F15 0,G19-G15 AXIO C15-C19 -MAX(0.E15-E19) M ) MAX(O G15-G19 MAKIO F15-F19 23 Capacity Planning 24 Workers Hired 25 Workers L 26 Workforce Available 27 Regular Time Capacity (units 28 Overtime (units 29 Subcontracting (units 30 Total Production Capacity (units 827+B28+B29 Off B7+B24-B25 B26+C24-C25 LaborStandard"C26 MIN(0. C18-C27 MAX(0,C18-C27-C28 -C27+C28+C29 C26+D24-D25 -MIN O,D18-D27 D27+D28+D29 -D26+E24-E25 |=LaborStandard'E26 -MIN(O,E18-E27 E26+F24-F25 MIN(0,F18-F27 F27+F28+F29 -F26+G24-G25 |=LaborStandard"G26 -LaborStandard"B26 |=LaborStandard"D26 |=LaborStandard'F26 MIN(0. B18-B27 MAX(0,B18-B27-B28) 0.01 8-D27-D28)!=MAX(0E18-E27-E28|:MAKO-F 18-F27-F28)-MAX(0.G18-G27432 E27 E28+E29 -G27+G28+G29

Hence the cost in this case is $ 20,200.00

Costs Regular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Per Unit Units Cost $3.004200$12,600 $5.00 $7.00 $2.001 12001 $2.400 $5.00 $0 600 $4,200 6 Beginning Invento 7 Beginning Workforce 8 Labor Standard (units/worker 700 200$1,000 $500.00 $750.00 Total Costs $20,200 12 Period 13 Demand 14 Cumulative Demand 15 Net Cumulative Demand 600 600 600 600 1200 1200 800 2000 2000 1000 3000 3000 1200 4200 4200 600 4800 4800 17 Production/Inventory Plannin 18 Production 19 Cumulative Production 20 Inventory (Excess Units 21 Backorders (Units Short 800 800 200 800 1600 400 800 2400 400 800 3200 200 800 4000 800 4800 200 23 Capacity Plannin 24 Workers Hired 25 Workers Laved Off 26 Workforce Available 27 Reqular Time Capacity (units 28 Overtime (units 29 Subcontracting (units 30 Total Production Capacity (units 700 700 700 700 700 700 700 700 700 700 700 700 100 800 100 800 100 800 100 800 100 800 100 800