## Solving Linear Programming Applications with Excel’s Solver tool

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.