Aggregate Production Plan.

The Excel worksheet name called "Aggregate".

Thanks for your help!!!

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. Inventory and backorder costs are calculated using ending inventory/backorder of each month. 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. ii. 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. iii. 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.) 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.) F 42 H B C D E 20 40 worker minute per unit 30 worker: minute per unit 200 hours 5 $ per hour 1 $ per unit 1.8 $ per unit per month 2 $ per unit per month 400 $ per worker 500 $ per worker 100 units 100 units (at least these many units) 2 9700 3 7600 5 7500 6 Total 8600 48300 6800 8100 A 1 Initial number of workers 2 Required Production time (not using prefabs) 3 Required Production time (using prefabs) 4 Regular hours per month 5 Salary 6 Additional material cost of using prefabs 7 Inventory holding cost 8 Backorder cost 9 Hiring workers 10 Laying off workers 11 Beginning Inventory of January 12 Required Ending Inventory of June 13 14 15 Period 16 Demand 17 Total number of workers 18 Number of workers (not using prefabs) 19 Number of workers (using prefabs) 20 21 Beginning Inventory 22 Production quantity (not using prefabs) 23 Production quantity (using prefabs) 24 Ending Inventory (use negative for backorder) 25 26 Hiring cost 27 Laying off cost 28 Labor cost 29 Additional material cost (prefabs) 30 Inventory holding cost 31 Backorder cost 32 33 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. Inventory and backorder costs are calculated using ending inventory/backorder of each month. 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. ii. 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. iii. 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.) 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.) F 42 H B C D E 20 40 worker minute per unit 30 worker: minute per unit 200 hours 5 $ per hour 1 $ per unit 1.8 $ per unit per month 2 $ per unit per month 400 $ per worker 500 $ per worker 100 units 100 units (at least these many units) 2 9700 3 7600 5 7500 6 Total 8600 48300 6800 8100 A 1 Initial number of workers 2 Required Production time (not using prefabs) 3 Required Production time (using prefabs) 4 Regular hours per month 5 Salary 6 Additional material cost of using prefabs 7 Inventory holding cost 8 Backorder cost 9 Hiring workers 10 Laying off workers 11 Beginning Inventory of January 12 Required Ending Inventory of June 13 14 15 Period 16 Demand 17 Total number of workers 18 Number of workers (not using prefabs) 19 Number of workers (using prefabs) 20 21 Beginning Inventory 22 Production quantity (not using prefabs) 23 Production quantity (using prefabs) 24 Ending Inventory (use negative for backorder) 25 26 Hiring cost 27 Laying off cost 28 Labor cost 29 Additional material cost (prefabs) 30 Inventory holding cost 31 Backorder cost 32 33 Total cost