| Solving Linear Programming Applications With Excel’s Solver Tool |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Instructions: |
Use Excel’s Solver Tool to solve each application. Each problem should consist of the following |
|
|
|
|
|
|
|
|
|
|
|
|
components (outlined in the comment boxes below) and look similar to the following example. |
|
|
|
|
|
|
|
|
|
|
|
|
There are 8 problems, each one located on an individual worksheet in this file. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Problem — |
Manufacturing: Production Scheduling A furniture company has two plants |
|
|
|
|
|
|
|
|
|
|
|
|
|
that produce the lumber used in manufacturing tables and chairs. In one day |
|
|
|
|
|
|
|
|
|
|
|
|
|
of operation, plant A can produce the lumber required to manufacture 20 |
|
|
|
|
|
|
|
|
|
|
|
|
|
tables and 60 chairs, and plant B can produce the lumber required to |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
manufacture 25 tables and 50 chairs. The furniture company needs enough |
|
|
|
|
|
|
|
|
|
|
|
|
|
lumber to manufacture at least 200 tables and 500 chairs. It costs $1,000 to |
|
|
|
|
|
|
|
|
|
|
|
|
|
operate plant A for one day and $900 to operate plant B for one day. How |
|
|
|
|
|
|
|
|
|
|
|
|
|
many days should each plant be operated to produce a sufficient amount of |
|
|
|
|
|
|
|
|
|
|
|
|
|
lumber at a minimum cost? What is the minimum cost? |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Solution: |
Let A = the number of operating days for Plant A to produce the necessary lumber |
|
|
|
|
|
|
|
|
|
|
|
|
|
Let B = the number of operating days for Plant B to produce the necessary lumber |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Objective Function: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Minimum Cost, C = 1000A + 900B |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
20A + 25B >=200 |
<—Number of required tables |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
60A + 50B >= 500 |
<—Number of required chairs |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A >= 0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
B >= 0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Using the Solver tool |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Objective Function |
8600 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Decision Variables |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
number of operating days for Plant A to produce the necessary lumber (A) |
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
number of operating days for Plant B to produce the necessary lumber (B) |
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Number of required tables |
|
200 |
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Number of required chairs |
|
500 |
500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
nonnegativity for A |
|
5 |
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
nonnegativity for B |
|
4 |
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Conclusion |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Minimum costs will occur to fulfill the required amount of lumber when Plant A operates for |
|
|
|
|
|
|
|
|
|
|
|
|
5 days and Plant B operates for 4 days. The minimum cost will be $8600. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
