A woodworking company manufactures and sells dining room tables and chairs.The o

Question

A 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 pre-defined sets, as is the case with most furniture manufacturers.The owner has established the following general guidelines for the company’s production effort:

The company’s objective is to maximize profit during each production cycle.

Fabrication of each table requires 4 units of wood and each chair requires 1 unit of wood.

Fabrication of each table requires 20 units of fabrication labor and each chair requires 12 units of fabrication labor.

Fabrication of each table requires 2 units of assembly labor and each chair requires 4 units of assembly labor.

Fabrication of each table requires 12 units of finishing labor and each chair requires 8 units of finishing labor.

Fabrication of each table requires 1 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 during any given production period since they can be completed during the subsequent production cycle.However, potential 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, since partially manufactured tables or chairs cannot be sold.

The company will earn a potential profit of $250 for each table sold and $65 for each chair sold.

It is assumed that all complete tables and chairs manufactured during a given production period will also be sold during the same production period.

For the current production cycle, the owner anticipates having 750 units of wood, 5,000 units of fabrication labor, 1,225 units of assembly labor, 3,250 units of finishing labor and 500 units of packaging labor available. Create a linear programming model for the preceding scenario using the Excel Solver method in order to answer the following questions:

What is the optimal number of tables the company should produce during the current production cycle?

What is the optimal number of chairs the company should produce during the current production cycle?

What is the total amount of profit that the company would earn for producing the optimal number of tables and chairs during the current production cycle (keeping in mind that partially manufactured tables and/or chairs do not contribute to profit earned during the current production cycle)?

Which resources will be fully used in producing the optimal number of tables and chairs?

The following hints apply:

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

Do not forget to consider whether or not an integer constraint would be appropriate for inclusion in your model.

A given resource is fully consumed only if all of the resource has been used. If even a fraction of the resource remains unused (e.g., a fraction of a unit of wood or a fraction of a unit of labor), then the resource has not been fully consumed. The fact that the portion remaining may be insufficient to manufacture another complete or partial table or chair is irrelevant. Although you might not be able to use this surplus resource to make another complete or partial table or chair, it is nonetheless available and could be reallocated elsewhere within the company where it could possibly be put to use during the current production period to manufacture other products or could possibly be held in reserve for use in future production periods.

Explanation / Answer

If number of tables: T, number of chairs: C

The linear programming problem is:

Max Z = 250T + 65C [Profit function]

Subject to:

4T + C <= 750 (Quantity of wood)

20T + 12C <= 5,000 (Quantity of Fabrication Labor)

2T + 4C <= 1,225 (Quantity of Assembly Labor)

12T + 8C <= 3,250 (Quantity of finishing labor)

T + C <= 500 (Quantity of packaging material)

T >= 0, C >= 0 [Non-negativity]

The Solver Output report as follows:

Summarized Form:

(1)

Optimal number of tables = 143

Optimal number of chairs = 179

(2)

Total profit = $47,321

(3)

Since Wood & Fabrication Labor has 0 slack each, these 2 resources will be fully utilized.

Worksheet: [ECO Q 271 - LPP Solver.xlsx]Sheet1 Report Created: 10-10-2015 17:15:54 Target Cell (Max) Cell Name Original Value Final Value $B$4 Profit 0 47321 Adjustable Cells Cell Name Original Value Final Value $B$8 Tables 0 143 $B$9 Chairs 0 179 Constraints Cell Name Cell Value Formula Status Slack $B$13 Wood 750 $B$13<=$C$13 Binding 0 $B$14 Fabrication Labor 5000 $B$14<=$C$14 Binding 0 $B$15 Assembly Labor 1000 $B$15<=$C$15 Not Binding 225 $B$16 Finishing Labor 3143 $B$16<=$C$16 Not Binding 107.1428571 $B$17 Packaging Material 321 $B$17<=$C$17 Not Binding 178.5714286 $B$18 Non-Negativity 1 143 $B$18>=$C$18 Not Binding 143 $B$19 Non-Negativity 2 179 $B$19>=$C$19 Not Binding 179

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.