Question

Andree’s All-American manufactures fashionable tennis wear, needs help planning production for next year. Demand for tennis gear is fairly stable, but has peaks during the summer months.

Month	Demand Forecast
January	500
February	300
March	200
April	1500
May	2500
June	3500
July	4500
August	2500
September	500
October	300
November	300
December	2500

Beginning workforce 9 workers

Production per day 9 units per employee

Production cost during regular time $50 per unit

Subcontracting cost $75 per unit

Increasing production $3000 per worker

Decreasing production $6000 per worker

Inventory holding cost $1 per unit/month

Beginning inventory 300

Ending Inventory requirements: 5% of the forecasted demand

Given the above costs and demand forecasts, assume that the employee work every day of the year (i.e. 365 days per year), test these two strategies for meeting demand:

1. Chase plan (i.e. Produce to meet forecasted demand and safety stock requirements)

2. Level plan with subcontracting and inventory to meet variation in demand. (Note that employee work every day of the month and no idle time)

3. Which Aggregate Plan Strategy would you recommend? Justify.

Assumptions:

1. Since the cost of stock out or backorder is not provided thus we assume that this is not permissible.

2. Since the number of days an employee work in a month vary thus the number of units produced in a month will not be the same. In the EXAMPLES seen in class we assume that the employee work the same number of days each month ( for example 21 days/month), thus the number of unit produced each month will be the same, which is not the case for this example (i.e. February is 28 days, March is 31 days, ....).

Answer 1

1)

Spreadsheet showing the Chase plan is following:

In Increase/Decrease columns enter values to make Workforce equal to Workforce required

fx -SUMPRODUCT(D15:115, D16: 116) K18 A C D E G K Safety stock Workforce requirement required Demand Number Production Subcontract Inventory Workforce Increase Month 1 Decrease of days Forecast 2 300 9 January 3 500 31 279 0 79 1 0 25 1 February 300 28 252 0 31 1 0 15 1 March 5 200 31 279 C 110 1 10 1 April May 6 1500 30 1620 0 230 6 5 75 6 7 2500 31 2511 0 241 125 8 June 3500 30 3510 0 251 13 175 13 July 9 4500 31 4743 0 494 17 4. 225 17 10 August September 2500 31 2232 0 226 0 9 125 11 500 30 540 0 266 2 0 6 25 2 12 October 300 31 279 0 245 1 0 1 15 1 13 November 300 30 270 0 215 1 15 1 14 December 125 2500 31 2511 0 226 9 8 0 9 15 Total 19100 365 19026 2614 69 24 24 16 Unit Cost 50 75 1 3000 6000 17 1,169,914 Total Cost 18 19 20

EXCEL FORMULAS:

-SUMPRODUCT(D15:115, D16: 1 16) K19 A Demand Safety stock Number of days Workforce required 1 Month Workforce Production Subcontract Inventory Increase Decrease requirement Forecast 300 EF2-B3+D3+ E3 =F3-B4+D4+E4 EF4-B5+D5+ES F5-B6+D6+E6 EF6-87+07+E7 500 EC3 9 G3 C4*9*G4 83 5 % =84*5 % -B5 5% B6 5% 87 5% 88 5% -ROUNDUP((83-F2+ 13 ) / C3/9.0) January 4 February -G2 +H3 - 13 31 0 28 - ROUNDUP ( ( B4-F3+J4)/C4/9,0) -ROUNDUP((B5-F4+15)/C5/9,0) 300 -G3+H4- 14 0 31 March 200 1500 2500 3500 G4+H5-15 -C5 *9*G5 G5+H6-16 EG6+H7- 17 -G7+H8 - 18 30 C6*9*G6 EC7*9*G7 EC8 9 G8 =ROUNDUP( ( 86-F5+J6 ) / C6 /9,0) =ROUNDUP( ( B7-F6+ 17) /C7/9,0) =ROUNDUP( ( B8-F7+J8) /C8/9,0) Apri 0 31 Мат June 30 = F7 - B8 + D8 + E8 - F8 - B9+ D9 + E9 4 Ain 6 10 August 4500 -B9 5% 810*5 % =ROUNDUP((B9-F8+J9)/C9/9,0) C9 * 9 *G9 -G8 tH9 - 19 31 40 -G9+H10-110 9-810+010+E10 ROUNDUP(B10-F9+110)/C10/9,0) nbe =ROUNDUPUB12 511 E ROUNDUP((B13-F12+1 131 / C13/90) -ROUNDUP( (B14-F13+1 14 ) / C14 /9,0) 17 October 300 G11 F11-8124 =C12 9° 612 .. =F12-813+D13-F13 EF13 - B14 + D14+E14 - G12 + H13 - 1 1 3 8 EB13*5% 3 November 14 December C13 9°G13 300 .. 31 2500 g*G14 814 5 % =G1 14 -sUM(B3:B14) -SUM(D3:D14)-sUM(E3:E14) 15 Total -SUM(H3:H14) -SUM(13:114) 3000 SUM(C3:C14) -SUM(F3:F14) -SUM(G3:G14) 6000 1 5C 75 16 Unit Cost Total Cost-SUMPRODUCT(D15:115,D16:116) 19 20 21

Total cost of level plan = $ 1,169,914

2)

Level plan is following:

fx K18 =SUMPRODUCT(D15:115,D16:116) O P R A F G J M N Safety stock Workforce Decrease equirement required Mont Deman Numbe Produc 1 h S Solver Parameters X Subcontract Inventory Workforce Increase r of tion 2. 300 Set Objective: SK$18 3 Janua 916 500 1 1116 4 5 25 1 O Max Min Value Of: 0 4 Febru 3 300 28 1008 C 1624 4 15 To 5 March 0 200 31 1116 C 2540 4 10 By Changing Variable Cells: 4 6 April 1500 30 1080 C 2120 75 SES3:SES14,SHS3:SIS3 4 7 May 2500 31 1116 C 736 0 25 Subject to the Constraints: 4 8 June 3500 30 1080 1859 175 0 175 1 SHS3:SIS3 integer Add 9 July 3434 4500 31 1116 225 4 C 0 225 17 SFS3:SFS14 SJS3:SJS14 10 Augus 2500 4 31 1116 1284 125 C 125 9 Change 500 30 1080 0 705 C 0 25 2 11 Septe 4 12 Octob 300 31 1116 1521 15 Delete 13 Nove 300 14 Decem 2500 15 Total 19100 16 Unit Cost 0 4 30 1080 2301 15 31 1116 C 917 4 125 2 Reset All 13140 6577 13905 365 41 5 3000 50 75 6000 Make Unconstrained Variables Non-Negative 17 Total Cost-1,194,180 18 Select a Solving Method: Simplex LP Options 19 20 Solving Method 21 Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems 22 23 24 Solve Close 25

Spreadsheet model is the same as part (1)

However, increase or decrease in production is done only in the first month, i.e. January .

Total cost of level plan = $ 1,194,180

Total cost of level strategy is lower. Therefore, Level strategy is recommended.