Question

Climate Control, Inc. makes expedition-quality rain gear for outdoor enthusiasts. Management prepared a forecast of sales (in suits) for next year and now must prepare a production plan. The company has traditionally maintained a level workforce strategy. All nine workers are treated like family and have been employed by the company for a number of years. Each employee can produce 2,000 suits per month. At present, finished goods inventory holds 24,000 suits. The demand forecast follows:

Month Demand 25,000 2 16,000 3 15,000 4 19,000 5 32.000 6 7 8 29,000 27.000 22.000 9 14,000 10 11 15,000 20,000 12 6,000

a. Management is willing to authorize overtime in periods for which regular production and current levels of anticipation inventory do not satisfy demand. However, overtime must be strictly limited to no more than 20 percent of regular-time capacity. Management wants to avoid stockouts and backorders and is not willing to accept a plan that calls for shortages. Is it feasible to hold the workforce constant, assuming that overtime is only used in periods for which shortages would occur?

b. Assume that management is not willing to authorize any overtime. Instead, management is willing to negotiate with customers so that backorders may be used as a supply option. However, management is not willing to carry more than 5,000 suits from one month to the next in backorder. Is it feasible to hold the workforce constant, assuming that a maximum backorder of 5,000 suits may be maintained from month to month?

c. Assume management is willing to authorize the use of overtime over the next 4 months to build additional anticipation inventory. However, overtime must be strictly limited to no more than 20 percent of regular-time capacity. Management wants to avoid stockouts and backorders and is not willing to accept a plan that calls for shortages. Is it feasible to hold the workforce constant, assuming that overtime is only used in months 1 through 4? If not, in which months would additional overtime be required?

Please use excel solution and post the table in formula auditing mode as well so that I may see how the cells were solved. Thank you!

Answer 1

The company has 9 workers and each can produce 2000 units per month. This means the regular production capability for a month is 2000*9 = 18000. The annual production capability is 18000*12 = 216000. In addition we have 24000 units in hand. This means in total we could have (only using regular production) 240,000 units.

The total annual demand is (calculated by adding all the demands) = 240,000. This means, theoretically we should not even need overtime. However, that may not hold true if we do not want to face backorder or stock out.

Let’s plot the month, demand and the three options on a table and see if the solutions are feasible or not.

A X Cut Calibri - 11 LAR General AutoSum 47 m Copy - Et Wrap Text ESSE Merge & Center FA Insert Delete Format Paste B IU. A $ . % $808 Fill - Clear Conditional Format as Cell Formatting Table Styles Styles Format Painter Sort & Find & Filter Select Editing Clipboard Font F Alignment Number Cells A1 X fit Month B с D F F - K L M N o Р 1 4 5 8 2 16000 10 H 7 27000 3 15000 6 29000 9 14000 11 200001 12 6000 25000 19000 32000 22000 15000 Problem a 18000 24000 17000 0 18000 17000 190001 18000 19000 22000 ol 18000 22000 21000 0 18000 21000 70001 0 18000 7000 -400 3600 18000 -400 -9400 18000 -94001 - 13400 18000 - 13400 -94001 18000 -9400 -6400 18000 -6400 -8400 18000 -8400 3600 0 A 1 Month 2 Demand 3 4 Regular production 5 Beginning inventory 6 Ending inventory 7 Overtime 8 9 10 Regular production 11 Beginning inventory 12 Ending inventory 13 14 15 Regular production 16 Beginning inventory 17 Ending inventory 18 Overtime 19 18000 18000 Problem b 24000 17000 18000 17000 19000 18000 19000 22000 18000 22000 21000 18000 21000 70001 18000 7000 -4000 18000 -4000 - 13000 18000 - 13000 - 17000 18000 - 17000 - 13000 18000 - 13000 -10000 -10000 - 12000 180001 - 12000 0 18000 Problem 18000 24000 20600 3600 18000 20600 26200 3600 180001 -2600 18000 26200 32800 3600 18000 35400 21400 18000 32800 35400 3600 18000 21400 10400 18000 10400 1400 4400 18000 1400 -2600 18000 1400 4400 18000 2400 14400 1400 2400 Sheet128 Sheet130 Sheet131 Sensitivity Report 8 Sheet133 Sheet135 + READY + 120%

For the first part, the overtime limit is 20% of the regular capacity. This means 18000*0.2 = 3600 units. The condition here is that we will use overtime only for the months where there are shortages. As we can see from the table, that we will face shortage on month 6 and the overtime production of 3600 units will not be sufficient to avoid backorders or stock out.

The answer is it is not feasible.

The second problem states that we will not use overtime. However we are willing to accept backorder up to a limit of 5000 units. However, in this way, we can see that in the month 7 we will face a backorder of 13000.

The answer is it is not feasible.

The third problem states that we will use overtime in the first four months. The overtime limit is again 20% so 3600 units. However, the organization also wants to avoid stock outs. This means the organization cannot have a negative ending inventory. In order to be safe we began with 3600 units of overtime production for all four months. However, we can see that in the month 8 we will face stock out issue.

The answer is it is not feasible. Additional overtime will be required for month 8.