A certain 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 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.
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.
1. What is the optimal number of tables the company should produce during the current production cycle?
o 163.25
o 142.86
o 121.89
o 71.11
2. What is the optimal number of chairs the company should produce during the current production cycle?
o 178.57
o 191.27
o 134.96
o 89.67
3. 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)?
o $58,255
o $42,135
o $31,280
o $47,321
4. Which resources will be fully used in producing the optimal number of tables and chairs?
o Wood and fabrication labor
o Fabrication labor and assembly labor
o Assembly labor
o Wood and finishing labor
o Finishing labor
o None of the above
Shortly after beginning production and sales operations the owner discovers that his assumption regarding customers being interested in buying tables and chairs individually rather than in sets is 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.
5. Without revising the previously stated general guidelines for the company's production effort, how many complete table and chair sets can the company assemble from the optimal number of tables and chairs produced during the current production cycle?
o 36
o 44
o 51
o 64
6. If the company is only able to sell tables and chairs in complete sets during the current production cycle, how many excess tables will the company have left in inventory at the end of the current production cycle?
o 52.89
o 44.00
o 76.34
o 98.86
7. If the company is only able to sell tables and chairs in complete sets during the current production cycle, how many excess chairs will the company have left in inventory at the end of the current production cycle?
o 2.57
o 0.57
o 11.02
o 369
8. If the company is only able to sell tables and chairs in complete sets during the current production cycle, what is the total amount of profit that the company would potentially earn (keeping in mind that excess tables or chairs do not contribute to profit earned during the current production cycle)?
o $40,178
o $39,970
o $22,440
o $28,980