For every problem:

1. Set up the model in Excel, use Solver to determine the optimal solution.
2. Answer any other questions that are asked

Problem1

Problem2

problem 3

A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The time required to produce the tables on each machine is given in the following table. Machine Country Contemporary Total Machine Time Available per Week Router 1.5 2.0 1,000 Sander 3.0 4.5 2,000 Polisher 2.5 1.5 1,500 Country tables sell for $350, and contemporary tables sell for $450. Management has determined that at least 20% of the tables made should be country and at least 30% should be contemporary. How many of each type of table should the company produce if it wants to maximize its revenue? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. What is the optimal solution? d. How will your spreadsheet model differ if there are 25 types of tables and 15 machine processes involved in manufacturing them? c. Aire-Co produces home dehumidifiers at two different plants in Atlanta and Phoenix. The per unit cost of production in Atlanta and Phoenix is $400 and $360, respectively. Each plant can produce a maximum of 300 units per month. Inventory holding costs are assessed at $30 per unit in beginning inventory each month. Aire-Co estimates the demand for its product to be 300, 400, and 500 units, respectively, over the next three months. Aire-Co wants to be able to meet this demand at minimum cost. a. Formulate an LP model for this problem. b. Implement your model in a spreadsheet and solve it. What is the optimal solution? d. How does the solution change if each plant is required to produce at least 50 units per month? How does the solution change if each plant is required to produce at least 100 units per month? c. e. The Sentry Lock Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit, and Phoenix. The per unit cost of production at each plant is $35.50, $37.50, $39.00, and $36.25, respectively, while the annual production capacity at each plant is 18,000, 15,000, 25,000, and 20,000, respectively. Sentry's locks are sold to retailers through wholesale distributors in seven cities across the United States. The unit cost of shipping from each plant to each distributor is summarized in the following table along with the forecasted demand from each distributor for the coming year. Unit Shipping Cost to Distributor in Plants Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore Macon $2.50 $2.75 $1.75 $2.00 $2.10 $1.80 $1.65 Louisville $1.85 $1.90 $1.50 $1.60 $1.00 $1.90 $1.85 Detroit $2.30 $2.25 $1.85 $1.25 $1.50 $2.25 $2.00 Phoenix $1.90 $0.90 $1.60 $1.75 $2.00 $2.50 $2.65 Demand 8,500 14,500 13,500 12,600 18,000 15,000 9,000 A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The time required to produce the tables on each machine is given in the following table. Machine Country Contemporary Total Machine Time Available per Week Router 1.5 2.0 1,000 Sander 3.0 4.5 2,000 Polisher 2.5 1.5 1,500 Country tables sell for $350, and contemporary tables sell for $450. Management has determined that at least 20% of the tables made should be country and at least 30% should be contemporary. How many of each type of table should the company produce if it wants to maximize its revenue? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. What is the optimal solution? d. How will your spreadsheet model differ if there are 25 types of tables and 15 machine processes involved in manufacturing them? c. Aire-Co produces home dehumidifiers at two different plants in Atlanta and Phoenix. The per unit cost of production in Atlanta and Phoenix is $400 and $360, respectively. Each plant can produce a maximum of 300 units per month. Inventory holding costs are assessed at $30 per unit in beginning inventory each month. Aire-Co estimates the demand for its product to be 300, 400, and 500 units, respectively, over the next three months. Aire-Co wants to be able to meet this demand at minimum cost. a. Formulate an LP model for this problem. b. Implement your model in a spreadsheet and solve it. What is the optimal solution? d. How does the solution change if each plant is required to produce at least 50 units per month? How does the solution change if each plant is required to produce at least 100 units per month? c. e. The Sentry Lock Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit, and Phoenix. The per unit cost of production at each plant is $35.50, $37.50, $39.00, and $36.25, respectively, while the annual production capacity at each plant is 18,000, 15,000, 25,000, and 20,000, respectively. Sentry's locks are sold to retailers through wholesale distributors in seven cities across the United States. The unit cost of shipping from each plant to each distributor is summarized in the following table along with the forecasted demand from each distributor for the coming year. Unit Shipping Cost to Distributor in Plants Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore Macon $2.50 $2.75 $1.75 $2.00 $2.10 $1.80 $1.65 Louisville $1.85 $1.90 $1.50 $1.60 $1.00 $1.90 $1.85 Detroit $2.30 $2.25 $1.85 $1.25 $1.50 $2.25 $2.00 Phoenix $1.90 $0.90 $1.60 $1.75 $2.00 $2.50 $2.65 Demand 8,500 14,500 13,500 12,600 18,000 15,000 9,000