Problem Statement:
Model
Processing Times
Model
Molding
Assembly
Packaging
1-quart
4
2
1
2-quart
6
3
1
4-quart
9
3
2
The PlasticMaid Corporation is a small
injection molding plastics product producer in the Houston area. Their product
line consists of three food storage container models that are similar except
for the capacity of the container. The capacities are 1 quart, 2 quart, and 4
quart. The profit contributions for the three models are: 1-quart = $0.50, 2
quart = $1.25, and 4 quart = $1.50. Each product must pass through three
departments at the plant: Molding, Assembly, and Packaging. Molding is limited
to 69200 minutes per week, Assembly is limited to 27200 minutes per week, and Packaging
is limited to 14800 minutes per week. Total plant production capacity is
limited to 9000 units. The number of minutes that each product must spend in
each department is shown in the table. PlasticMaid economists estimate that
demand for the containers will be as follows: 1-quart = 3000, 2 quart = 4,000,
and 4 quart = 2,500 (Note: it is possible that current capacity restrictions
will not allow the firm to fulfill all of the demand). PlasticMaid has a firm
order for 1000 units of the 4-quart model in the coming week.
1.
What is the
optimal product mix?
2.
How much profit
will PlasticMaid earn if they produce the optimal product mix?
Instructions:
1.
Following the
technique demonstrated in class, model the problem in Excel Solver.
2.
Place a text box
on the main page of your Excel workbook, and place your answers to questions 1
and 2 above in the text box.
3.
Submit your
completed Excel file in the assignment drop box.
Notes:
The professor
will attempt to recreate your results when he evaluates your spreadsheet. So, the
Solver dialog of your submitted file must be loaded with the model that you
used to generate your solutions.
Your spreadsheet
should be easy to read and understand, it should be well organized, and it should
be error free.
Problem Statement:Model
Processing TimesModelMoldingAssemblyPackaging1-quart4212-quart6314-quart932The PlasticMaid Corporation is a small
injection molding plastics product producer in the Houston area. Their product
line consists of three food storage container models that are similar except
for the capacity of the container. The capacities are 1 quart, 2 quart, and 4
quart. The profit contributions for the three models are: 1-quart = $0.50, 2
quart = $1.25, and 4 quart = $1.50. Each product must pass through three
departments at the plant: Molding, Assembly, and Packaging. Molding is limited
to 69200 minutes per week, Assembly is limited to 27200 minutes per week, and Packaging
is limited to 14800 minutes per week. Total plant production capacity is
limited to 9000 units. The number of minutes that each product must spend in
each department is shown in the table. PlasticMaid economists estimate that
demand for the containers will be as follows: 1-quart = 3000, 2 quart = 4,000,
and 4 quart = 2,500 (Note: it is possible that current capacity restrictions
will not allow the firm to fulfill all of the demand). PlasticMaid has a firm
order for 1000 units of the 4-quart model in the coming week. 1.
What is the
optimal product mix?2.
How much profit
will PlasticMaid earn if they produce the optimal product mix?Instructions:1.
Following the
technique demonstrated in class, model the problem in Excel Solver. 2.
Place a text box
on the main page of your Excel workbook, and place your answers to questions 1
and 2 above in the text box.3.
Submit your
completed Excel file in the assignment drop box.Notes:
The professor
will attempt to recreate your results when he evaluates your spreadsheet. So, the
Solver dialog of your submitted file must be loaded with the model that you
used to generate your solutions.
Your spreadsheet
should be easy to read and understand, it should be well organized, and it should
be error free.
