Homework Assignment Six

Linear Programming

Each question is worth 1.3889 points.

Scenario # 1

A new woodworking company manufactures and sells dining room tables and chairs. The owner has assumed that his customers are interested in buying tables and chairs individually rather than having to buy them in sets, as is the case with his competitors. The owner has established the following general guidelines for the company’s production effort:

• The owner’s objective is to maximize profit during each production cycle.
• Fabrication of each table requires 10 units of wood and each chair requires 1.5 unit of wood.
• Fabrication of each table requires 20 units of fabrication labor and each chair requires 8 units of fabrication labor.
• Fabrication of each table requires 4 units of assembly labor and each chair requires 3 units of assembly labor.
• Fabrication of each table requires 12 units of finishing labor and each chair requires 4 units of finishing labor.
• Fabrication of each table requires 2 unit of packaging labor and each chair requires 1 unit of packaging labor.
• Producing partially manufactured (i.e., partially fabricated, assembled, finished and/or packaged) tables and/or chairs is acceptable since they can be completed during the subsequent production cycle.
• The company will earn a potential profit of $329 for each table sold and $73 for each chair sold.
• Profit for a given production cycle shall be calculated based only upon the number of complete tables and/or chairs produced during the production cycle (i.e., partially manufactured tables and/or chairs cannot be sold and do not contribute to profit).
• It is assumed that all complete tables and chairs manufactured during a given production cycle will be sold during the production cycle.
• For the upcoming production cycle, the owner anticipates having 2,000 units of wood, 5,000 units of fabrication labor, 2,000 units of assembly labor, 4,000 units of finishing labor and 500 units of packaging labor available.

Define the linear programming problem set-up (i.e., variables, objective function and constraints), create an Excel model for the problem set-up, and use the Excel Solver method in order to answer questions 1 through 4.

Hints:

• Do not forget to consider whether or not a non-negativity constraint would be appropriate for inclusion in your problem set-up and model.

• Do not forget to consider whether or not an integer constraint would be appropriate for inclusion in your problem set-up and model.

1. What is the optimal number of tables the company should produce during the upcoming production cycle?
2. What is the optimal number of chairs the company should produce during the upcoming production cycle?
3. What is the total amount of profit that the company will earn for producing the optimal number of tables and chairs during the upcoming production cycle, keeping in mind that partially manufactured tables and/or chairs do not contribute to profit earned?
4. Which resources will be fully used in producing the optimal number of tables and chairs? (Note: A given resource is fully consumed only if all of the resource has been used. If even a fraction of a unit of a resource remains unused, then the resource has not been fully consumed.)
   Scenario # 2
   During the production cycle the owner discovers that his prior assumption regarding customers being interested in buying tables and chairs individually rather than in sets was incorrect. The vast majority of his customers are indicating that they are only interested in purchasing table and chair in sets, with each set consisting of one table and four chairs. Without revising your LP problem set-up or Excel model for Scenario # 1 , use this additional information to answer questions 5 through 8.
5. How many complete table and chair sets can the company assemble from the optimal number of tables and chairs being produced during the production cycle?
6. If the company is only able to sell tables and chairs in complete sets, how many excess complete tables will the company have left in inventory at the end of the production cycle?
7. If the company is only able to sell tables and chairs in complete sets, how many excess complete chairs will the company have left in inventory at the end of the production cycle?
8. If the company is only able to sell tables and chairs in complete sets, what is the total amount of profit that the company will earn during the production cycle, keeping in mind that partially manufactured and/or excess tables and chairs will not contribute to profit earned?

Scenario # 3

The owner has decided to focus his production effort on manufacturing table and chair sets in response to customer feedback. Revise the LP problem set-up and Excel model you developed for Scenario # 1 to take into account the following additional general guidelines and use the Excel Solver method in order to answer questions 9 through 11:

• Tables and chairs shall only be sold in complete sets consisting of one table and four chairs (i.e., tables and chairs shall not be sold individually).
• Profit for a given production cycle shall be calculated based upon the number of complete table and chair sets produced during the production cycle. Partially manufactured and/or excess tables and chairs do not contribute to profit earned.
• Assume that all complete table and chair sets manufactured during the production cycle will be sold during the production cycle.
• All other Scenario # 1 guidelines remain the same.

In addition to the hints previously provided for Scenario # 1, the following additional hint applies:

• You do not need to create a separate variable to represent a table and chair set. Instead, you can create a constraint that defines a set by creating a simple algebraic equation using the two variables you previously identified to represent the number of tables to be produced and the number of chairs to be produced. This algebraic equation shall describe the ratio in which tables and chairs must be manufactured in order to produce complete table and chair sets (i.e., avoid producing excess tables or excess chairs).

1. What is the optimal number of complete table and chair sets the company should produce during the upcoming production cycle?
2. What is the total amount of profit that the company will earn for producing the optimal number of complete table and chair sets, keeping in mind that partially manufactured and/or excess tables and/or chairs do not contribute to profit earned?
3. Which resource will be fully used in producing the optimal number of tables and chairs? (Note: A given resource is fully consumed only if all of the resource has been used. If even a fraction of a unit of a resource remains unused, then the resource has not been fully consumed.)

Scenario # 4

Customers have advised the owner that they would also like to a matching china hutch along with their table and chair set in order to have a matched dining room set. Accordingly, the owner has decided to focus his production effort on manufacturing dining room sets. Revise your LP problem set-up and Excel model for Scenario # 3 to take into account the following additional general guidelines and use the Excel Solver method in order to answer questions 12 through 14:

• Tables, chairs and china hutches shall only be sold as a complete dining room set consisting of one table, four chairs and one china hutch (i.e., tables, chairs and china hutches shall not be sold individually).
• Fabrication of each china hutch will require 13 units of wood, 24 units of fabrication labor, 6 units of assembly labor, 12 units of finishing labor and 2.5 units of packaging labor.
• The company will earn a unit profit of $275 for each china hutch sold.
• Producing partially manufactured china hutches is acceptable during since they can be completed during the subsequent production cycle.
• All other Scenario # 1 guidelines remain the same.
• Profit for a given production period shall be calculated based upon the number of complete dining room sets produced during the production cycle. Partially manufactured and/or excess tables, chairs and/or hutches do not contribute to profit earned.
• It is assumed that all complete dining room sets manufactured during the production cycle will be sold during the production cycle.

• The owner anticipates needing to manufacture a minimum of 50 complete dining room sets during the upcoming production cycle in order to satisfy customer demand.

In addition to the hints previously provided for Scenarios # 1 and # 3, the following additional hints apply:

• Do not forget to create a new variable to represent the number of china hutches to be manufactured.
• You do not need to create a separate variable to represent a dining room set. Instead, you can create two constraints that collectively define a set by creating two simple algebraic equations using the three variables identified to represent the number of tables to be produced, the number of chairs to be produced and the number of china hutches to be produced. These algebraic equations shall describe the ratios in which tables, chairs and china hutches must be manufactured in order to produce complete dining room sets (i.e., avoid producing excess tables, chairs or china hutches). First select one of the three variables as your reference variable and create an equation that describes the ratio in which the second variable must be manufactured relative to the reference variable in order to produce complete dining room sets. Then, create a second equation that describes the ratio in which the third variable must be manufactured relative to the reference variable in order to produce complete dining room sets.
• Do not forget to account for the new constraint requiring that you produce a specific minimum number of table and chair sets.

1. Can the company produce the required minimum number of complete dining room sets during the upcoming production cycle?
2. What is the total amount of profit that the company will earn for producing the optimal number of complete dining room sets, keeping in mind that partially manufactured and/or excess tables, chairs, and/or china hutches do not contribute to profit earned?
3. Which resource will be fully used in producing the optimal number of complete dining room sets? (Note: A given resource is fully consumed only if all of the resource has been used. If even a fraction of a unit of a resource remains unused, then the resource has not been fully consumed.)
   Use the graphical solution method to complete problem 27 on page 276 in Quantitative Analysis for Management in order to answer questions 15 through 18.
4. Which formulation has more than one optimal solution?
5. Which formulation is unbounded?
6. Which formulation has no feasible solution?
7. Which formulation is correct as is?

Price: $37.95

Solution: The downloadable solution consists of 15 pages, 2295 words and 4 charts.
Deliverable: Word Document