Question

Using Excel method

Using Excel

Pl amaTech ic. a tc o al manufacturer of pharmaceutical pr ducts and medical supples cated n Zurich Switzer and uses sa es ช d opera ons pianing to set production, workforce, and inventory levels for its annual planning horizon. Among the variety of itemis the company produces one he tems used for S30P planning is a standard emergency medical kit that contains medical products that can be used to treat a variety of inures The demand for this tem fluctuates due to seasonal ilnesses and the differing ardering policies used by the various hospitals. The average reguiar pract on ate of this i กา worker at PharmaTech is 800 kits per mont The egular time production cost s $10 per kit rd $12 perkt sertine production is useu. Production of these kits can also be subcontracted to external vendors at $15 per kit inventory carrying costs are $3 per it per month verti me (s h uto o 50% of regular production, but there s no production limit if su contracting is used. The hi h sl ured in production of these kits meuns that neely hired workers require extensive training and, therefore, hiring costs are $1,200 per worker Firing costs per woker are also high at $1.0(al in U.S. dollars). PharmaTech currently has 40 tull-time workers. The forecast of demand for these medical aits for the first 6 months of next year is given as follows: May June anuaryFebruary March April Month 25,000 50,000 20,000 30,000 45,000 20,000 Forecast Deveop a 6-morth apgregate production plan for Pharma Tech using A pure chase strategy pure level strategy using a constant workforce of 40 full- time workers A mied strategy where the production rate is kept constant at 30.000 kits per month Demand during peak periodis is met using overtime ad subcontracing d Tech, Inc., a fictional manufacturer of pharmaceutical products and medical supplies located in Zurich, Swit et production, workforce, and inventory levels for its annual planning horizon. Among the varvety of itemis Pharma aims used for S&0P planning is a standard Tha demand for this item fluctuates due to seasonal illnesses and the diftering ordering policies used ty the varlous hospitals. The average n ime production rate of this kit by a worker at PharmaTech is 800 kits per month. The regular tirne production cost is $10 per kit and $12 pe r t used the company producese d emergency medical kit that contains medical products that can be used to treat a variety of res me production is used. Production of these kits can also be subcontracted to external vendors at $15 per kit. Invemtory caryng kit per month. Overtime s limited to 50% of regular product but there is pr act i s on ng oroduction of these kits worker are also high at $1,000/worker (all in U.S. dollars). PharmaTech currently has 40 tul-ime workers. The forecast of demand for these medc kits for the first 6 months of next year is given as foilows codts a means that newly hired workers require extensive training and, therefare, hiring costs are $1,200 per worker. Firig January February MarchApr 20,000 June May Month 50,000 25,000 20,000 45,000 30,000 Forecast Develop a 6-month aggregate production plan for PharmaTech using a. A pure chase strategy D A pure level strategy using a constant t workforce of 40 full-time workers ,000 kits per mornth tolowing composite demard Amiked strategy where the production rate is kept constant at 30 as needed n1l manufacturer of a variety of high-quality glazed clay roo tlis in Kerala, ndia has the to Sep Oct Nov Dec

Answer 1

Please find below the response to part a , b and c

Part a)

Solution

2 Pure Chase 3 Month 4 Forecast output 5 Regular 6 overtime 7 Subcontract 8 Output Forecast 9 Invento 10 Beginning 11 Endin 12 Average 13 Number of emplovees 14 Hired 15 Fired 16 17 Costs 18 Regular cost 19 Overtime 20 Subcontract 21 Avg Invento 22 Hiring Cost 23 Firing Cost 24 Total Feb total Mar Ма Year 0 Jan Jun 190000 24800 0 0 0 0 24800 200 200 200 200 0 100 31 0 200 200 300 200 63 32 13 31 $ 10.00 $200,000.00 $ 304,000.00 $448,000.00 $200,000.00 $248,000.00$504,000.00 S 12.00 $ 15.00 $3.00 $1,200.00 $1,000.00 $ 15,000.00 0$600.00 $ 900.00$600.00 $ 300.00$600.00 0 $ 15,600.00 $ 21,600.00 0$ 7,200.00$ 38,400.00 0 $ 31,000.00 $215,000.00 $320,200.00 $470,500.00 $231,600.00 $255,500.00 $543,000.00 $2,035,800.00

Formulas:

May 3 Month 4 Forecast o 5 Regular 6 overtime 7 Subcontract total -SUM(C4:H4 Year 0 Jan un D13 800 -F13 *800 -G13 800 -C13 800 #13+800 -H13 800 Forecast -CS+C6+C7 DS+D6+D7 -ES E6+E7 F5+F6+F7 -G5+G6+G7 -HS+H6+H7 9 Invern F11 C11 DS+D10-D4 AVERAGE(D11,010 ROUND (D4/800,0) -D13-C13 -E11 -F8+F10-F4 -D11 -E8+E10-E4 AVERAGE(E11,E10) AVERAGE(F11,F10) AVERAGE G11,G10) AVERAGE(H11,H10 ROUND(E4/800,0)ROUND(F4/800,0) ROUND(G4/800,0) ROUND(H4/800,0) -E13-D13 -G11 11 Endin 12 Average 13 Number of e 14 Hired -C8-C4 AVERAGE(C11,C10 ROUND(C4/800,0) -G8+G10-G4 -G13-F13 -H13-G13 B13-C13 E13-F13 17 Costs 18 Regular cost 19 Overtime 20 Subcontract 10 $B$18 C5 12 $B$19 C6 -SB$18*ES $B$19 E6 -$B$18 GS SB$18 D5 $B$19 D6 SB$20 D7 SB$21 D12 -D14 $B$22 D15 SBS23 -SUM(D18:D23) -SB$18*H5 $B$19 H6 -SB$20"H 7 $B$19 F6 -SB$20 FT SB$21 F12 -F14 $B$22 F15 SB$23 -SUM(F18:F23) -$B$20 G7 SB$21*G12 -G14 SB$22 G15* B$23 -SUM(G18:G23) $B$21 E12 -E14 $B$22 E15 $B$23 -SUM(E18:E23) SB$21 C12 1200-C14 SB$22 1000 C15 $B$23 B$21 H12 -H14 SB$22 H15 SB$23 -SUM(H18:H23) 22 Hiring Cost 23 Firing Cost 24 Total -SUM(C18:C23) UM(C24:H24)

b) Pure level using workforce of 40

Solution: 40 workers can produced, 40*800 = 32000 units

2 Pure level with 40 workers 3 Month 4 Forecast output 5Regular 6 overtime 7 Subcontract 8 Output Forecast 9 Invento 10 Beginnin 11 Endin 12 Average 13 Number of emplovees 14 Hired 15 Fired 16 17 Costs 18 Regular cost 19 Overtime 20 Subcontract 21 Avg Invento 22 Hiring Cost 23 Firing Cost 24 Total Feb total Mar Ma Year 0 Jan Jun 30000 190000 32000 32000 32000 32000 32000 32000 192000 0 0 0 0 32000 32000 32000 32000 32000 32000 0 14000 1000 13000 14000 1000 13000 13000 16500 11000 0 0 $ 10.00 $320,000.00 $ 320,000.00 $320,000.00 $320,000.00 $320,000.00$320,000.00 $12.00 $ 15.00 $3.00 $ $1,200.00 $1,000.00 18,000.00 39,000.00$ 22,500.00 S 21,000.00 49,500.00 33,000.00 $338,000.00 $359,000.00 $342,500.00 $341,000.00$369,500.00 $353,000.00 $ 2,103,000.00

Formulas:

total SUM(C4:H4 -SUM(C5:H5 3 Month 4 Forecast output Feb 20000 -F13 800 -G13 800 -C13 800 D13 800 H13 800 0 0 8 Output Forecast 9 Invento 10 Beginnin 11 Endin 12 Avera 13 Number of employees40 14 Hired 15 Fired C5+C6+C7 D5+D6+07 E5+E6+E7 F5+F6+F7 -G5 G6+67 -G11 -C11 D8+D10-D4 | -E11 F11 -G8 G10-G4 AVERAGE G11 G101 I AVERAGE(H11,H1 C8-C4 E8+E10-E4 F8+F10-F4 H8+H10-H4 AVERAGE(C11,C10) AVERAGED11,010) AVERAGE E 1 E101 I-AVERAGE FILF10) D13-C13 E13-D13 -G13-F13 H13-G13 -B13-C13 -E13-F13 -SB$18*ES $B$19 E6 -SB$20*E SB$21 E12 18 Regular cost 19 Overtime 20 Subcontract 21 Avg Invento 22 Hiring Cost 23 Firing Cost 24 Total -SB$18 D5 $B$19 D6 -SB$20 D -SB$21 D12 D14 SB$22 D15 SB$23 -SUM(D18:D23 -SB$18 F5 SB$18 G5 -$B$19 G6 SB$18*H5 $B$19 H6 SB$20 H7 -SB$21 H12 H14 $B$22 H15 SB$23 -SUM(H18:H23 2 12 B$19 C6 15 B$20 C7 SB$20 F2 -SB$21 F12 FIA" SBS22 F15 $B$23 -SUM(F18:F23 B$20 G7 SB$21 G12 G14 SB$22 G15 $B$23 -SUM(G18:G23) 1200 C14 SB$22 1000 C15 $B$23 E14 $B$22 -E15 SB$23 -SUM(E18:E23) (C24:H24) -SUM(C18:C23)

c) Mixed strategy with fixed production of 30000 kits

Solution:

2 Mixed strate 3 Month Year 0 Jan 4 Forecast output 5 Regular 6 overtime 7 Subcontract 8 Output Forecast 9 Invento 10 Beginning 11 Endin 12 Average 13 Workers 14 Costs 15 Regular $10.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 16 Overtin $12.00 Feb total Mar Ma Jun 190000 5000 5000 0 35000 0 0 35000 0 10000 5000 37.5 10000 10000 10000 10000 0 10000 5000 10000 5000 0 $ 60,000.00 0 $ 60,000.00 17 Subcon $15.00 18 Avg Inv $ 3.00 $ 15,000.00$ 30,000.00$ 15,000.00 $ 15,000.00$ 37,500.00 $ 22,500.00 19 Total 20 $315,000.00 $330,000.00 $375,000.00 $315,000.00 $337,500.00 $382,500.00 $2,055,000.00

Formulas:

2 Mixed strate 3 Month 4 Forecast o 5 Regular 6 Overtime 7 Subcontract Year 0 Jarn Feb total SUM(C4:H4 SUM(CS:H5 Jun 0 0 -F5+F6+F7 H5+H6+H7 9 Invento 10 Beginning 11 Endin 12 Avera 13 Workers 14 Costs 15 Regular cost 10 $BS15 C5 16 Overtime 17 Subcontract 18 Avg Invent 19 Total -C11 -D8+D10-D4 AVERAGE(D11,D10) AVERAGE(E11,E10 AVERAGE(F11,F10 AVERAGE(G11,G10) AVERAGE(H11,H10 -D11 -E8+E10-E4 -E11 -F8+F10-F4 -G11 -H8+H10-H4 -F11 G8+G10-G4 AVERAGE(C11,C10 -C5/800 SB$15 E5 -$B$16 E6 -SB$17"E7 -sBS18"Е 12 -SUM(E15:E18) SB$15 F5 SB$15DS -$B$16 D6 -SB$17 D7 -SB$18 D12 -SUM(D15:D18) SB$15*H5 SB$16 G6 -SB$17*G7 -SB$18 G12 SUM(G15:G18 12 SB$16 C6 15 B$17 C7 -SB$17*H7 -SB$18 H12 -SUM(H15:H18) -SB$17*F7 -SB$18*F12 -SUM(F15:F18 SB$18 C12 SUM(C15:C18) UM(C19:H19)

Note - For the mixed strategy we have 30000/800 =37.5 workers i.e. 2 workers are laid off at the start of January. If the cost is to be considered then we must add 2 * $1000 = $2000 to the total cost.