Using the data in Exhibit 19.3 prepare Excel Spreadsheets for each of the Production Plans( 1 to 4 ) shown in exhibit 19.4. In order to receive full credit you must use Excel Formulas for all calculation and you can only enter base case information once. .All additional uses of that information must be through use of a formula. I have the completed tables but just need the computations/excel formulas

Sales and Operations 497 exhibit 19.3 Aspregate Production Planning Requirements January February March April May Beginning inventory 400 450 375 275 225 275 Demand forecast 1.500 100 900 1.100 1.100 Safety stock (25 x Demand forecast 450 375 275 225 275 400 Production requirement Demand forect Safety ock - Beginning inventory 1.425 1000 150 2.750 1725 Ending inventory Beginning inventory Production requirement-Demand forecast 450 375 275 225 275 400 Before investigating alternative production plans, it is often useful to convert demand for casts into production requirements which take into account the safety rock estimates. In Exhibit 19.3, note that these requirements implicitly assume that the safety stock is never acto- ally used, so that the ending inventory each month equals the safety stock for that month. For cxample, the January safety stock of 450 (25 percent of January demand of 1.500) becomes the inventory at the end of January. The production requirement for January is demand plus safety stock minus beginning inventory (1.800 + 450 - 400 = 1850 Now we must formulate alternative production plans for the JC Company Lising a spread sheet, we investigate four different plans with the objective of finding the one with the lowest total cost Plan I. Produce to exact monthly production requirements using a regular eighbour day by varying workforce size. Plan 2. Produce to meet expected average demand over the next six months by maintain ing a constant workforce. This constant number of workers is calculated by finding the average number of workers required cach day over the horizon Take the total production requirements and multiply by the time required for each it. Then divide by the real time that one person works ver the horizon (8.000 units x 5 hours per unit) (125 days x 8 hours per day) = 40 workers). Inventory is allowed to accumulate with shortages filled from next month's production by backordering. Negative beginning inventory balances indicate that demand is backordered. In some cases, sales may be lost i demand is sex met The lost sales can be shown with a negative ending inventory balance followed by a zero beginning inventory balance in the next period. Notice that in this plan was oor safety stock in January, February March, and June to meet expected demand Plan 3. Produce to meet the minimum expected demand (April) in a cowork force on regular time. Subcontract to meet additional output requirements. The number of workers is calculated by locating the minimum monthly production quitement and deter mining how many workers would be needed for that month (50 units x 5 hours per unit) +(21 days X 8 hours per day) - 25 workers and subcontracting any monthly difference between requirements and production Plan 4. Produce to meet expected demand for all but the first two months using a con stant workforce on regular time. Use overtime to meet additional output requirements. The number of workers is more difficult to compute for this plan, but the goal is to finish June KEY IDEA with an ending inventory as close as possible to the June safety sack By trial and error it can be shown that a constant workforce of 38 workers is the closestappenimation In practice, creare often maderent The next step is to calculate the cost of cach plan. This requires the series of simple calcula types of tions shown in Exhibit 194. Note that the headings in cachow are different for each plano These because cach is a different problem requiring its own data and calculations can be due to non Corother The final step is to tabulate and graph each plan and compare their conte From factors wing to Exhibit 19.5 we can see that using subcontractors resulted in the lowest cost (Plan 3) w for Exhibit 19.6 shows the effects of the four plan. This is a cumulative graph illustrating the example expected results on the total production requirement. 498 Section 4 Supply and Demand Planning and Control exhibit 19.4 Costs of Four Production Plans April June 1,725 1.150 8.625 5.750 22 176 160 Production Plant Exact Production: Vary Worldorce January February March Production requirement from Exo 19.3) 1,850 1.000 850 Production hours required Production requirement x 5 hund 9.250 7,125 5.000 4.250 Working days per month 21 Hours per month per worker (Working days x Bruder 176 152 168 168 Workers required Production hours required Hours per month per worked 53 47 30 New workers hired suming open ing wordforce equal to first month's requirement of 53 workers) 0 0 o Hiring cost (New workers hired $200) SO SO $0 SO Workers ind of 0 6 17 4 Layoff cost (Workers isid off $250) 50 $1.500 $4250 $1.000 Straight-time cost Production hours required x 54 $37.000 528.500 $20.000 $17.000 33 54 $5.500 7 51400 0 50 21 54.200 o $0 56.750 $23.000 534500 Total.com $160.000 $172350 June 22 Production Plan 2 Constant Workforce: Vary Inventory and Stock out January February March April TO Beginning inventory 400 8 -276 412 720 Working days per month 22 19 21 21 20 Production hours available (Working days per month xhday x 40 worker 7.040 6.080 6.720 6.720 7040 6.400 Actual production Production hours available/5 1,408 1.216 1344 1.344 1.400 1.280 Demand forecast from Exhibit 19.3) 1800 1.500 1.100 900 1100 1.500 Ending inventory Beginning inventory+ Actual production - Demand forecast 8 -32 412 720 400 Shortage cost Units short x $5) 50 $1.380 3160 SO 50 $1540 Safety stock rom Eb 19.3 450 375 275 225 275 400 Units excess (Ending inventory - Safety stock only if positive amount o 187 0 Inventory cost Units excess x $1.50) 50 50 50 $281 5668 $0 594 Straight time cost Production hours available x 54 $28.160 $24.320 $26.880 $26.380 $28.160 $25.600 $160.000 Total cost $162.488 Sum of production requirement in Exhibt 193 x 5 heunity (Sum of production hours available riday = 18.00057025 x) = 40 Employee Note that we have made one other assumption in this example: The plan can start with any number of workers with no hiring or layot cost. This usually is the case because an aggregate plan draws on existing personnel, and we can start the plan that way. However, in an actual application, the availability of existing personnel transferable from other areas of the firm may Q ... Sales and Operation Planning Chapter 19 499 exhibit 19.4 Costs of Four Production Plans concluded) Production Plan Constant Low Workorce Subcontract January February March April May June Total Production requirement from Exit 192 1,850 1425 1000 850 1.150 1.725 Working days per month 22 21 21 22 20 Production hours available Working days x 8 hriday x 25 workers 3.800 4200 4.200 4.400 4,000 Actual production Production hours viable/5h perut 80 760 840 340 880 800 Units subcontracted Production requirement - Actual production 970 565 160 10 270 925 Subcontracting cost Units subcontracted 5201 $19,400 $13.300 53.200 5200 $5.400 518.500 $60.000 Straightime cost Production hours available 54 517,000 $15.200 $16.800 $16.800 $17.600 $16.000 $100.000 Total cost $160.000 "Minimum production requirement to this comple, Aprilis minum of 850 ter of work required for Aprilis 850 x 57121x8=25 Productie Plan Constant Workforce Overtime January February March April May June Total Beginning inventory 400 00177 554 792 Working days per month 22 19 21 21 22 20 Production hours available (Working days hriday x 38 workers 6.688 5.770 6.384 6.688 6.080 Regular production Production hours available/5 hun 1338 1,155 1.277 1277 1338 1.216 Demand forecast from Eshot 19,3) 1.800 1500 1.100 900 1.100 1.600 Units available before overtime (Beginning Inventory + Regular shift production Demand forecast. The number has been rounded to the nearest integer -62 554 792 408 Units overtime 62 375 0 0 0 Overtime cost (Units overtiment 56) S1360 $10.350 50 50 SO $12.210 Safety stock rom Exo 193 275 275 400 Units excess Units available before overtime - Safety stock) only if positive amount 0 0 O 329 517 3 Inventory cost (Units excessive x $1.50) $494 5776 $12 51.281 Straight-me cost Production hos av able $4 $26.952 $23.104 $25.536 $25.536 $25,752 524.320 $152.000 Total cost $165.491 "Workers determined by trial and enro See test for explanation 225 8. Each of these four plans focused on one particular cossand the fire three were simple pure exhibit 19.3 Aggregate Production Planning Requirements January February March April May June Beginning inventory 400 450 375 275 225 275 Demand forecast 1.800 1.500 1.100 900 1.100 1.600 Safety stock (25 x Demand forecast) 450 375 275 225 275 400 Production requirement (Demand forecast Safety stock - Beginning inventory 1,850 1.425 1.000 850 1.150 1.725 Ending inventory (Beginning inventory + Production requirement-Demand forecast 450 375 225 275 400 275 Before investigating alternative production plans, it is often useful to convert derhand fore- casts into production requirements, which take into account the safety stock estimates. In Exhibit 19.3, note that these requirements implicitly assume that the safety stock is never actu. ally used, so that the ending inventory each month equals the safety stock for that month. For example, the January safety stock of 450 (25 percent of January demand of 1.800) becomes the inventory at the end of January. The production requirement for January is demand plus safety stock minus beginning inventory (1,800 + 450 - 400 = 1.850). Now we must formulate alternative production plans for the JC Company. Using a spread- sheet, we investigate four different plans with the objective of finding the one with the lowest total cost. Plan 1. Produce 10 exact monthly production requirements using a regular eight-hour day by varying workforce size Plan 2. Produce to meet expected average demand over the next six months by maintain- ing a constant workforce. This constant number of workers is calculated by finding the average number of workers required each day over the horizon. Take the total production requirements and multiply by the time required for each unit. Then divide by the total time that one person works over the horizon [(8,000 units x 5 hours per unit) + (125 days x 8 hours per day) = 40 workers). Inventory is allowed to accumulate, with shortages filled from next month's production by backordering. Negative beginning inventory balances indicate that demand is backordered. In some cases, sales may be lost if demand is not met The lost sales can be shown with a negative ending inventory bulance followed by a zero beginning inventory balance in the next period. Notice that in this plan we use our safety Mock in January February March, and June to meet expected demand. Plan 3. Produce to meet the minimum expected demand (Aprily using a constant work- force on regular time. Subcontract to meet additional output requirements. The number of workers is calculated by locating the minimum monthly production requirement and deter mining how many workers would be needed for that month (850 units x 5 hours per unit) +(21 days 8 hours per day) = 25 workers and subcontracting any monthly difference between requirements and production Plan 4. Produce to meet expected demand for all hut the first two months wing a con stant workforce on regular time. Use overtime to meet additional output requirements. The number of workers is more difficult to compute for this plan, but the goal is to finish June KEY IDEA with an ending inventory as close as possible to the June safety stok By trial and errorit can be shown that a constant workforce of 38 werken is the closest approximation of many The next step is to calculate the cost of each plan. This requires the series of simple calcula- tions show in Exhibit 194. Note that the healings in a row. ure different for each plan en The because each is a different problem requiring its own data and calculations The final step is to tabulate and graph coch plan and compare their coxb. From Exhibit 19. we can see that using subcontractors resulted in the lowest cost (Plan 3). Exhihi 19.6 shows the effects of the four plans. This is a curulative graph illustrating the expected results on the total production requirement In there are of peo can be down contractore factors relating to 498 Section 4 Supply and Demand Planning and Control exhibit 19.4 Costs of Four Production Plans June Total May 1,150 1.725 5.750 22 8.625 20 176 160 Production Plant Exact Production: Vary Workforce January February March April Production requirement from Exhibit 193) 1.850 1,425 1.000 850 Production hours required Production requirement x 5 heunio 9,250 7.125 5.000 4.250 Working days per month 22 19 21 21 Hours per month per worker (Working days aheday 176 152 168 168 Workers required Production hours required Hours per month per worken 26 New workers hired (sing open ing workforce equal to first month's requirement of 63 workers) 0 0 Hiring cost New workers hired $200) 90 50 $0 Workers laid of 0 17 4 Layoff cost Workers laid off $250) $0 51,500 $4250 $1,000 Straight time cost Production hours Tequired x 54) $37.000 $28.500 $20.000 $17.000 53 47 30 33 54 0 09 $5,600 7 $1,400 0 $0 21 $4,200 o $0 $6.750 $23.000 $34,500 Total cost $160,000 5172,350 Production Plan 2. Constant Workforce Vary Inventory and Stock out January February March April May June Totat Beginning inventory 400 8 -276 -32 412 720 Working days per month 22 19 21 21 22 20 Production hours avisbie Working days per monday x40 workers 7,040 6080 6,720 6.720 7.040 6.400 Actus production Production hours www/ht 1.400 1,216 1.344 1,344 1,408 1.230 Dumand forecom EXO 193 1,800 1.500 1.100 900 1,100 1,500 Ending wory Beginning Kwentory Actus production-Demand forecast -276 -32 412 720 400 Shortage short 5) $0 51.300 5160 $0 $0 50 $1,540 Safetycom 19.3 450 175 275 275 400 cending toy - Safety shoot 0 0 0 107 horsec 5150 30 $0 5201 5660 $0 $94 Some como 28.160 $24.320 326.480 520.00 52.100 $25,000 $160.000 Totalcos $162.418 Blum of produceren 3x5mt production hours w00X625-40 Note that we have made one other asumption in this example: The plan can start with any number of an with no hiring or layoff cost. This usually is the case because an agregate plan draws on existing prel, and we can start the plan that way. However, in an actual application, the availability of existing personnel transferable from other areas of the firm may change the sumptions Plan is the cure when we chose demand by varying workforce Plan 2 has the high est average production rate the line representing cultive demand has the greatest slope Using subcontracting in an results in li having the lowest production rate Limits on the of event wilable in Plan 4 being similar to Plan 2 Sales and Operations Planning Chaper 19 499 exhibit 19.4 Costs of Four Production Plans concluded) Total May 1.150 22 June 1.725 20 4.400 4,000 Production Plan 3: Constant Low Workforce. Subcontract January February March April Production requirement from Exh 193) 1,850 1.425 1.000 850 Working days per month 22 19 21 21 Production hours avetable (Working days xhday x 25 workers 4.400 3800 4.200 4.200 Actual production Production hours avalt the per unit) 750 840 Units subcontracted Production requirement - Actual production 970 665 160 10 Subcontracting cost (Units subcontracted $20 $19.400 $13.300 $3.200 $200 Straigh-time cost Production hours available x 541 $17.600 $15,200 $16.800 $16,800 380 840 880 800 270 925 $5,400 $18.500 $60,000 $17.600 $16.000 Total cost $100,000 $160,000 1.100 "Minimum production requirement in this example, April s minimum of 50 unts. Number of workers required for Aprilis 1850 x 5121 8-25 Productio Plan & Constant Workforce Overtime January February March April May June Beginning inventory 400 0 0 177 554 792 Working days per month 22 19 29 21 22 20 Production hours available (Working days hty X 38 Work 6.688 5.776 6384 6.384 6.688 6,080 Regular production Production hours that 1.338 1.195 1,277 1.277 1.216 Demand forecast hom Exhibit 19 1.800 7.500 1.100 1.000 Unit before overre (Sagaing Regulash production Demand forecess. This number has be Founded to the neweger --52 -345 177 554 792 400 Uit Deine 52 375 0 0 Overtime comuniti overtime Shox $10,350 30 10 50 $0 5 om 19 275 225 275 400 Une before y you 0 0 COXS150 $0 30 19 1494 5770 $12 Samoooooo 26.752 24.10 $36.36 $26,252 24320 sh $12.210 $ 51:20 5133.000 5165.491 Woensinne weet for en Each of these four plans focused on one particular cos, and the first three were simple pure rategies. Otroudy, there are many other file plans, some of which would use a com bition of workforce tunes, evenine, and contrasting. The problems at the end of this chapter include examples of such med strategies. In practice, the final plan chosen would come from searching a variety of alternatives and future projections beyond the six-month planning horizon we have used Sales and Operations 497 exhibit 19.3 Aspregate Production Planning Requirements January February March April May Beginning inventory 400 450 375 275 225 275 Demand forecast 1.500 100 900 1.100 1.100 Safety stock (25 x Demand forecast 450 375 275 225 275 400 Production requirement Demand forect Safety ock - Beginning inventory 1.425 1000 150 2.750 1725 Ending inventory Beginning inventory Production requirement-Demand forecast 450 375 275 225 275 400 Before investigating alternative production plans, it is often useful to convert demand for casts into production requirements which take into account the safety rock estimates. In Exhibit 19.3, note that these requirements implicitly assume that the safety stock is never acto- ally used, so that the ending inventory each month equals the safety stock for that month. For cxample, the January safety stock of 450 (25 percent of January demand of 1.500) becomes the inventory at the end of January. The production requirement for January is demand plus safety stock minus beginning inventory (1.800 + 450 - 400 = 1850 Now we must formulate alternative production plans for the JC Company Lising a spread sheet, we investigate four different plans with the objective of finding the one with the lowest total cost Plan I. Produce to exact monthly production requirements using a regular eighbour day by varying workforce size. Plan 2. Produce to meet expected average demand over the next six months by maintain ing a constant workforce. This constant number of workers is calculated by finding the average number of workers required cach day over the horizon Take the total production requirements and multiply by the time required for each it. Then divide by the real time that one person works ver the horizon (8.000 units x 5 hours per unit) (125 days x 8 hours per day) = 40 workers). Inventory is allowed to accumulate with shortages filled from next month's production by backordering. Negative beginning inventory balances indicate that demand is backordered. In some cases, sales may be lost i demand is sex met The lost sales can be shown with a negative ending inventory balance followed by a zero beginning inventory balance in the next period. Notice that in this plan was oor safety stock in January, February March, and June to meet expected demand Plan 3. Produce to meet the minimum expected demand (April) in a cowork force on regular time. Subcontract to meet additional output requirements. The number of workers is calculated by locating the minimum monthly production quitement and deter mining how many workers would be needed for that month (50 units x 5 hours per unit) +(21 days X 8 hours per day) - 25 workers and subcontracting any monthly difference between requirements and production Plan 4. Produce to meet expected demand for all but the first two months using a con stant workforce on regular time. Use overtime to meet additional output requirements. The number of workers is more difficult to compute for this plan, but the goal is to finish June KEY IDEA with an ending inventory as close as possible to the June safety sack By trial and error it can be shown that a constant workforce of 38 workers is the closestappenimation In practice, creare often maderent The next step is to calculate the cost of cach plan. This requires the series of simple calcula types of tions shown in Exhibit 194. Note that the headings in cachow are different for each plano These because cach is a different problem requiring its own data and calculations can be due to non Corother The final step is to tabulate and graph each plan and compare their conte From factors wing to Exhibit 19.5 we can see that using subcontractors resulted in the lowest cost (Plan 3) w for Exhibit 19.6 shows the effects of the four plan. This is a cumulative graph illustrating the example expected results on the total production requirement. 498 Section 4 Supply and Demand Planning and Control exhibit 19.4 Costs of Four Production Plans April June 1,725 1.150 8.625 5.750 22 176 160 Production Plant Exact Production: Vary Worldorce January February March Production requirement from Exo 19.3) 1,850 1.000 850 Production hours required Production requirement x 5 hund 9.250 7,125 5.000 4.250 Working days per month 21 Hours per month per worker (Working days x Bruder 176 152 168 168 Workers required Production hours required Hours per month per worked 53 47 30 New workers hired suming open ing wordforce equal to first month's requirement of 53 workers) 0 0 o Hiring cost (New workers hired $200) SO SO $0 SO Workers ind of 0 6 17 4 Layoff cost (Workers isid off $250) 50 $1.500 $4250 $1.000 Straight-time cost Production hours required x 54 $37.000 528.500 $20.000 $17.000 33 54 $5.500 7 51400 0 50 21 54.200 o $0 56.750 $23.000 534500 Total.com $160.000 $172350 June 22 Production Plan 2 Constant Workforce: Vary Inventory and Stock out January February March April TO Beginning inventory 400 8 -276 412 720 Working days per month 22 19 21 21 20 Production hours available (Working days per month xhday x 40 worker 7.040 6.080 6.720 6.720 7040 6.400 Actual production Production hours available/5 1,408 1.216 1344 1.344 1.400 1.280 Demand forecast from Exhibit 19.3) 1800 1.500 1.100 900 1100 1.500 Ending inventory Beginning inventory+ Actual production - Demand forecast 8 -32 412 720 400 Shortage cost Units short x $5) 50 $1.380 3160 SO 50 $1540 Safety stock rom Eb 19.3 450 375 275 225 275 400 Units excess (Ending inventory - Safety stock only if positive amount o 187 0 Inventory cost Units excess x $1.50) 50 50 50 $281 5668 $0 594 Straight time cost Production hours available x 54 $28.160 $24.320 $26.880 $26.380 $28.160 $25.600 $160.000 Total cost $162.488 Sum of production requirement in Exhibt 193 x 5 heunity (Sum of production hours available riday = 18.00057025 x) = 40 Employee Note that we have made one other assumption in this example: The plan can start with any number of workers with no hiring or layot cost. This usually is the case because an aggregate plan draws on existing personnel, and we can start the plan that way. However, in an actual application, the availability of existing personnel transferable from other areas of the firm may Q ... Sales and Operation Planning Chapter 19 499 exhibit 19.4 Costs of Four Production Plans concluded) Production Plan Constant Low Workorce Subcontract January February March April May June Total Production requirement from Exit 192 1,850 1425 1000 850 1.150 1.725 Working days per month 22 21 21 22 20 Production hours available Working days x 8 hriday x 25 workers 3.800 4200 4.200 4.400 4,000 Actual production Production hours viable/5h perut 80 760 840 340 880 800 Units subcontracted Production requirement - Actual production 970 565 160 10 270 925 Subcontracting cost Units subcontracted 5201 $19,400 $13.300 53.200 5200 $5.400 518.500 $60.000 Straightime cost Production hours available 54 517,000 $15.200 $16.800 $16.800 $17.600 $16.000 $100.000 Total cost $160.000 "Minimum production requirement to this comple, Aprilis minum of 850 ter of work required for Aprilis 850 x 57121x8=25 Productie Plan Constant Workforce Overtime January February March April May June Total Beginning inventory 400 00177 554 792 Working days per month 22 19 21 21 22 20 Production hours available (Working days hriday x 38 workers 6.688 5.770 6.384 6.688 6.080 Regular production Production hours available/5 hun 1338 1,155 1.277 1277 1338 1.216 Demand forecast from Eshot 19,3) 1.800 1500 1.100 900 1.100 1.600 Units available before overtime (Beginning Inventory + Regular shift production Demand forecast. The number has been rounded to the nearest integer -62 554 792 408 Units overtime 62 375 0 0 0 Overtime cost (Units overtiment 56) S1360 $10.350 50 50 SO $12.210 Safety stock rom Exo 193 275 275 400 Units excess Units available before overtime - Safety stock) only if positive amount 0 0 O 329 517 3 Inventory cost (Units excessive x $1.50) $494 5776 $12 51.281 Straight-me cost Production hos av able $4 $26.952 $23.104 $25.536 $25.536 $25,752 524.320 $152.000 Total cost $165.491 "Workers determined by trial and enro See test for explanation 225 8. Each of these four plans focused on one particular cossand the fire three were simple pure exhibit 19.3 Aggregate Production Planning Requirements January February March April May June Beginning inventory 400 450 375 275 225 275 Demand forecast 1.800 1.500 1.100 900 1.100 1.600 Safety stock (25 x Demand forecast) 450 375 275 225 275 400 Production requirement (Demand forecast Safety stock - Beginning inventory 1,850 1.425 1.000 850 1.150 1.725 Ending inventory (Beginning inventory + Production requirement-Demand forecast 450 375 225 275 400 275 Before investigating alternative production plans, it is often useful to convert derhand fore- casts into production requirements, which take into account the safety stock estimates. In Exhibit 19.3, note that these requirements implicitly assume that the safety stock is never actu. ally used, so that the ending inventory each month equals the safety stock for that month. For example, the January safety stock of 450 (25 percent of January demand of 1.800) becomes the inventory at the end of January. The production requirement for January is demand plus safety stock minus beginning inventory (1,800 + 450 - 400 = 1.850). Now we must formulate alternative production plans for the JC Company. Using a spread- sheet, we investigate four different plans with the objective of finding the one with the lowest total cost. Plan 1. Produce 10 exact monthly production requirements using a regular eight-hour day by varying workforce size Plan 2. Produce to meet expected average demand over the next six months by maintain- ing a constant workforce. This constant number of workers is calculated by finding the average number of workers required each day over the horizon. Take the total production requirements and multiply by the time required for each unit. Then divide by the total time that one person works over the horizon [(8,000 units x 5 hours per unit) + (125 days x 8 hours per day) = 40 workers). Inventory is allowed to accumulate, with shortages filled from next month's production by backordering. Negative beginning inventory balances indicate that demand is backordered. In some cases, sales may be lost if demand is not met The lost sales can be shown with a negative ending inventory bulance followed by a zero beginning inventory balance in the next period. Notice that in this plan we use our safety Mock in January February March, and June to meet expected demand. Plan 3. Produce to meet the minimum expected demand (Aprily using a constant work- force on regular time. Subcontract to meet additional output requirements. The number of workers is calculated by locating the minimum monthly production requirement and deter mining how many workers would be needed for that month (850 units x 5 hours per unit) +(21 days 8 hours per day) = 25 workers and subcontracting any monthly difference between requirements and production Plan 4. Produce to meet expected demand for all hut the first two months wing a con stant workforce on regular time. Use overtime to meet additional output requirements. The number of workers is more difficult to compute for this plan, but the goal is to finish June KEY IDEA with an ending inventory as close as possible to the June safety stok By trial and errorit can be shown that a constant workforce of 38 werken is the closest approximation of many The next step is to calculate the cost of each plan. This requires the series of simple calcula- tions show in Exhibit 194. Note that the healings in a row. ure different for each plan en The because each is a different problem requiring its own data and calculations The final step is to tabulate and graph coch plan and compare their coxb. From Exhibit 19. we can see that using subcontractors resulted in the lowest cost (Plan 3). Exhihi 19.6 shows the effects of the four plans. This is a curulative graph illustrating the expected results on the total production requirement In there are of peo can be down contractore factors relating to 498 Section 4 Supply and Demand Planning and Control exhibit 19.4 Costs of Four Production Plans June Total May 1,150 1.725 5.750 22 8.625 20 176 160 Production Plant Exact Production: Vary Workforce January February March April Production requirement from Exhibit 193) 1.850 1,425 1.000 850 Production hours required Production requirement x 5 heunio 9,250 7.125 5.000 4.250 Working days per month 22 19 21 21 Hours per month per worker (Working days aheday 176 152 168 168 Workers required Production hours required Hours per month per worken 26 New workers hired (sing open ing workforce equal to first month's requirement of 63 workers) 0 0 Hiring cost New workers hired $200) 90 50 $0 Workers laid of 0 17 4 Layoff cost Workers laid off $250) $0 51,500 $4250 $1,000 Straight time cost Production hours Tequired x 54) $37.000 $28.500 $20.000 $17.000 53 47 30 33 54 0 09 $5,600 7 $1,400 0 $0 21 $4,200 o $0 $6.750 $23.000 $34,500 Total cost $160,000 5172,350 Production Plan 2. Constant Workforce Vary Inventory and Stock out January February March April May June Totat Beginning inventory 400 8 -276 -32 412 720 Working days per month 22 19 21 21 22 20 Production hours avisbie Working days per monday x40 workers 7,040 6080 6,720 6.720 7.040 6.400 Actus production Production hours www/ht 1.400 1,216 1.344 1,344 1,408 1.230 Dumand forecom EXO 193 1,800 1.500 1.100 900 1,100 1,500 Ending wory Beginning Kwentory Actus production-Demand forecast -276 -32 412 720 400 Shortage short 5) $0 51.300 5160 $0 $0 50 $1,540 Safetycom 19.3 450 175 275 275 400 cending toy - Safety shoot 0 0 0 107 horsec 5150 30 $0 5201 5660 $0 $94 Some como 28.160 $24.320 326.480 520.00 52.100 $25,000 $160.000 Totalcos $162.418 Blum of produceren 3x5mt production hours w00X625-40 Note that we have made one other asumption in this example: The plan can start with any number of an with no hiring or layoff cost. This usually is the case because an agregate plan draws on existing prel, and we can start the plan that way. However, in an actual application, the availability of existing personnel transferable from other areas of the firm may change the sumptions Plan is the cure when we chose demand by varying workforce Plan 2 has the high est average production rate the line representing cultive demand has the greatest slope Using subcontracting in an results in li having the lowest production rate Limits on the of event wilable in Plan 4 being similar to Plan 2 Sales and Operations Planning Chaper 19 499 exhibit 19.4 Costs of Four Production Plans concluded) Total May 1.150 22 June 1.725 20 4.400 4,000 Production Plan 3: Constant Low Workforce. Subcontract January February March April Production requirement from Exh 193) 1,850 1.425 1.000 850 Working days per month 22 19 21 21 Production hours avetable (Working days xhday x 25 workers 4.400 3800 4.200 4.200 Actual production Production hours avalt the per unit) 750 840 Units subcontracted Production requirement - Actual production 970 665 160 10 Subcontracting cost (Units subcontracted $20 $19.400 $13.300 $3.200 $200 Straigh-time cost Production hours available x 541 $17.600 $15,200 $16.800 $16,800 380 840 880 800 270 925 $5,400 $18.500 $60,000 $17.600 $16.000 Total cost $100,000 $160,000 1.100 "Minimum production requirement in this example, April s minimum of 50 unts. Number of workers required for Aprilis 1850 x 5121 8-25 Productio Plan & Constant Workforce Overtime January February March April May June Beginning inventory 400 0 0 177 554 792 Working days per month 22 19 29 21 22 20 Production hours available (Working days hty X 38 Work 6.688 5.776 6384 6.384 6.688 6,080 Regular production Production hours that 1.338 1.195 1,277 1.277 1.216 Demand forecast hom Exhibit 19 1.800 7.500 1.100 1.000 Unit before overre (Sagaing Regulash production Demand forecess. This number has be Founded to the neweger --52 -345 177 554 792 400 Uit Deine 52 375 0 0 Overtime comuniti overtime Shox $10,350 30 10 50 $0 5 om 19 275 225 275 400 Une before y you 0 0 COXS150 $0 30 19 1494 5770 $12 Samoooooo 26.752 24.10 $36.36 $26,252 24320 sh $12.210 $ 51:20 5133.000 5165.491 Woensinne weet for en Each of these four plans focused on one particular cos, and the first three were simple pure rategies. Otroudy, there are many other file plans, some of which would use a com bition of workforce tunes, evenine, and contrasting. The problems at the end of this chapter include examples of such med strategies. In practice, the final plan chosen would come from searching a variety of alternatives and future projections beyond the six-month planning horizon we have used