Excel:

The initial number of workers	27
Required Production time (not using prefabs)	40	worker∙minute per unit
Required Production time (using prefabs)	30	worker∙minute per unit
Regular hours per month	200	hours
Salary	13	$ per hour
The additional material cost of using prefabs	4	$ per unit
Inventory holding cost	9.2	$ per unit per month
Backorder cost	11.9	$ per unit per month
Hiring workers	800	$ per worker
Laying off workers	1100	$ per worker
Beginning Inventory of January	200	units
Required Ending Inventory of June	500	units (at least these many units)

Period	1	2	3	4	5	6	Total
Demand	9800	11200	12400	11200	9900	12800	67300
Total number of workers							---
Number of workers (not using prefabs							---
Number of workers (using prefabs)							---
Beginning Inventory							----
Production quantity (not using prefabs)
Production quantity (using prefabs)
Ending Inventory (use negative for backorder)							----
Costs:
Hiring cost
Laying off cost
Labor cost
Additional material cost (prefabs)
Inventory holding cost
Backorder cost
Total Cost:

Use the forecasted demand and other data shown in sheet "Aggregate" to help Helen Industries Inc. to plan its production for one major products group for the next 6 months. Helen Industries Inc. used to adopt a standard production process in which it moulds and assemblies all parts by itself; the required production time of this standard process is provided in the cell "Aggregate!B2". As the quality of prefabrication parts from suppliers improves, the company now can choose to use a simplified production process in which prefabrication parts are used to shorten the requirement production time. The required production time of the simplified process is provided in the cell "Aggregate! B3". The additional material cost of using prefabrication parts are shown in the cell "Aggregate!B6". [Note that a production time of XX worker minute per unit means that it takes one worker XX minutes to produce 1 unit of product.] In order to satisfy the production requirement, the company can vary its capacity by (1) hiring or laying off workers and (2) scheduling workers to use standard production process (not using prefabrication parts) or to use a simplified production process (using the more expensive prefabrication parts). The following assumptions are made. ii. Hiring or layoff costs are incurred when the total number of workers changes. The number of workers in each month should be an integer. Once scheduled, all workers will produce at their full potential each month. The ending inventory requirement of June stated in the cell "Aggregate! B12" must be satisfied. Consider the following three different strategies. i. A level strategy without the use of prefabrications to meet the overall production requirement. That is, use a constant number of workers for these 6 months; all workers produce without the use of prefabrication parts; demand variations are absorbed by inventory and backorders. Hiring and layoff only happens at the beginning of January. A chase strategy by varying the number of workers when all workers produce with the use of prefabrication parts. That is, all scheduled workers produce with the use of prefabrication parts; use the minimum number of workers in each month to make the ending inventory of each month non-negative. Initial workers unchanged and not using prefabrications PLUS variable new workers using prefabrications. That is, schedule all initial workers to produce without prefabrication parts; this number is unchanged over these six months. Except these initial workers, new workers hired in these six months produce with the use of prefabrication parts. Vary the numbers of new workers. [Hint: input the initial number workers to the row "Number of workers (not using prefabs)", Aggregate! B18:G18; then, vary the numbers in Aggregate! B19:G19 to make your plan.] iii. . Inventory and backorder costs are calculated using ending inventory/backorder of each month. Question: Develop three plans using the above three strategies, respectively. Your plans should be cost- effective among the plans that satisfy the requirements. (For example, using one thousand workers can satisfy some of the above requirements, but it is obviously not cost-effective, i.e., not minimize the cost.)