Question

Please use excel and show formulas ( formula auditing mode) please:

9. 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
   1	25,000
   2	16,000
   3	15,000
   4	19,000
   5	32,000
   6	29,000
   7	27,000
   8	22,000
   9	14,000
   10	15,000
   11	20,000
   12	6,000

1. Use the Sales and Operations Planning with Spreadsheets Solver in OM Explorer or develop your own spreadsheet models to address the following questions.

   1. 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?

   2. 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?

      1. 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?

Answer 1

Demand forecast of suits (in 000’) in Company CC, for a period of 12 months, is given below:

Months	1	2	3	4	5	6	7	8	9	10	11	12
Demand	25	16	15	19	32	29	27	22	14	15	20	6

a. Draw production plan for the company with the help of given details:

Total number of employees= 9

Overtime limitation= 20 percent of regular time capacity, for periods having shortages

Hence, from the above table it is concluded that for the periods 1, 4, 5, 6, 7, 8 and 11 company faces shortages of suits. In absence of backorders, company has the only option of overtime up to 20 percent.

However, despite of the overtime by nine employees, forecasted demands can’t be fulfilled. Therefore, the idea of holding a constant workforce is not feasible in this situation.

b. Draw production plan for the company with the help of given details:

Total number of employees = 9

Backorders limitations = up to 5000 suits.

Hence, from the above table it is concluded that for the periods 1, 4, 5, 6, 7, 8 and 11 company faces shortages of suits. In absence of overtime, company can utilize backorder of maximum 5,000 suits.

However, despite of the carry forwarded backorders, company is not able to fulfill the demand. Therefore, the idea of holding a constant workforce is not feasible in this situation, also.

c.Draw production plan for the company with the help of given details:

Total number of employees = 9

Overtime limitation= 20 percent of regular time capacity, for initial 4 months

Hence, from the above table it is concluded that for the periods 1, 4, 5, 6, 7, 8 and 11 company faces shortages of suits. In absence of backorders, company has the option of overtime up to 20 percent, only for initial four months.

However, the company would not be able to satisfy the forecasted demand in this case, too. Additional overtime would be required for the month 1, 5, 6, 7, 8 and 11.