1 of 7Complete problem 15 on page 274 in the Quantitative Analysis for Management textbook using Excel Solver

method in order to answer questions 1 through 6. Problem 15 is an extension of problem 14 on page 274.

1. If Electrocomp decides that at least 20 air conditioners should be produced, but no more than 80 fans should

be produced, what would be the optimal solution?

o 25 air conditioners and 65 fans

o 35 air conditioners and 75 fans

o 40 air conditioners and 60 fans

o 20 air conditioners and 80 fans

2. If Electrocomp decides that at least 20 air conditioners should be produced, but no more than 80 fans should

be produced, how much slack is there for each of the two resources (i.e., wiring and drilling)?

o 5 wiring and 0 drilling

o 0 wiring and 0 drilling

o 0 wiring and 5 drilling

o 5 wiring and 5 drilling

3. If Electrocomp decides that at least 20 air conditioners should be produced, but no more than 80 fans should

be produced, what is the total profit Electrocomp would earn for the optimal solution?

o $2,500

o $2,350

o $1,900

o $1,765

4. If Electrocomp decides that at least 30 air conditioners should be produced, but no more than 50 fans should

be produced, what would be the optimal solution?

o 15 air conditioners and 65 fans

o 35 air conditioners and 75 fans

o 40 air conditioners and 50 fans

o 45 air conditioners and 50 fans

5. If Electrocomp decides that at least 30 air conditioners should be produced, but no more than 50 fans should

be produced, how much slack is there for each of the two resources (i.e., wiring labor and drilling labor)?

o 5 wiring and 0 drilling

o 0 wiring and 0 drilling

o 0 wiring and 5 drilling

o 5 wiring and 5 drilling

6. If Electrocomp decides that at least 30 air conditioners should be produced, but no more than 50 fans should

be produced, what is the total profit Electrocomp would earn for the optimal solution?

o $2,300

o $2,050

o $1,700

o $1,875

Woodworking Company (Original Scenario)

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

2 of 7

them in pre-defined sets, as is the case with most furniture manufacturers. The owner has established the

following general guidelines for the companys production effort:

The companys 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 questions 7 through 14.

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.

7. 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

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

o 178.57

o 191.27

3 of 7

o 134.96

o 89.67

9. 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

10. 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.

11. Without revising the previously stated general guidelines for the companys 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

12. 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

13. 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

14. 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)?

4 of 7

o

o

o

o

$40,178

$39,970

$22,440

$28,980

Woodworking Company (Revised Scenario)

Revise your linear programming model for the preceding Woodworking Company (Original Scenario) to take

into account the following additional general guidelines for production in order to answer questions 15 through

21:

Tables and chairs shall only be sold in complete sets consisting of one table and four chairs (i.e., tables

and chairs may not be sold indivually).

Profit for a given production period shall be calculated based upon the number of complete table and

chair sets produced during the production period. Tables and chairs associated with partial table and

chair sets shall not be considered when calculating profit.

Partially manufactured tables and/or chairs do not contribute to profit earned during a given production

cycle.

The company must manufacture a minimum of 50 complete table and chair sets during any given

production cycle in order to satisfy estimated customer demand.

In addition to the hints previously provided for the Woodworking Company (Original Scenario), the following

additional hints apply:

You do not need to create a separate variable to represent a table and chair set. You can create a

simple algebraic equation using only the two variables that represent the number of tables to be

produced and the number of chairs to be produced that describes the ratio in which tables and chairs

must be manufactured to produce only complete sets (i.e., no excess tables or chairs).

Do not forget to account for the constraint requiring that you produce a specific minimum number of

table and chair sets.

15. Can the company produce the required minimum number of complete table and chair sets during the current

production cycle?

o Yes

o No

16. What is the optimal number of tables the company should produce during the current production cycle in

order to produce the optimal number of complete table and chair sets?

o 62.25

o 65.18

o 68.06

o 71.11

17. What is the optimal number of chairs the company should produce during the current production cycle in

order to produce the optimal number of complete table and chair sets?

o 287.12

o 272.22

o 254.87

o 244.93

5 of 7

18. What is the total amount of profit that the company will potentially earn for producing the optimal number

of complete table and chair sets?

o $33,890

o $37,649

o $34,680

o $41,136

19. Which resource will be fully used in producing the optimal number of tables and chairs?

o Wood

o Fabrication labor

o Assembly labor

o Finishing labor

o Packaging labor

o None of the above

20. How many excess complete tables would the company have remaining in inventory at the end of the

production cycle (assuming the company sells all complete table and chairs sets produced during the

production cycle)?

o 0.81

o 0.00

o 1.12

o 4.29

21. How many excess complete chairs would Willis have remaining in inventory at the end of the production

cycle (assuming the company sells all complete table and chairs sets produced during the production cycle)?

o 0.00

o 11.29

o 2.76

o 7.12

Use the graphical solution method to complete problem 27 on page 276 in the Quantitative Analysis for

Management textbook in order to answer questions 22 through 25.

22. Which formulation has more than one optimal solution?

o Formulation 1

o Formulation 2

o Formulation 3

o Formulation 4

23. Which formulation is unbounded?

o Formulation 1

o Formulation 2

o Formulation 3

o Formulation 4

24. Which formulation has no feasible solution?

o Formulation 1

o Formulation 2

o Formulation 3

6 of 7

o Formulation 4

25. Which formulation is correct as is?

o Formulation 1

o Formulation 2

o Formulation 3

o Formulation 4