Optimization Assignment
TO 501
Fall 2013 Prof. Lenk

Due Lecture 4, Sept 26
Team assignment with 3 to 5 students per team
Collaboration among teams is not allowed
Each team will hand in one assignment
Answer questions in space provided

Team Names:

Comments
Copy and paste the assignment data tables to EXCEL instead of entering the numbers by hand.
Please copy and paste your Solver model after running it. I find that Paste Special, Picture (Windows Metafile) works best when the Solver model is too big to fit naturally with Paste.
The problems ask you to write the model algebraically and implement them in Solver. The reason for this requirement is that there is a one-to-one relation between the Solver model and its algebraic statement. My experience is that students who have trouble with the algebra tend to have trouble with Solver models. Students learn more by practicing both.
Unfortunately, there is a trend to cut corners and adapt existing workbooks for the problems. It may be a more efficient method to completing the assignment, but it does not help you develop your skills.
High performance students attempt the all of the problems on their own.
Problem 5 appears to be the shortest but may well be the most challenging one.

1. Allocation and Linear Programming. A company produces three products in kilos. Each product requires different amounts of time per kilo in each of four departments (A, B, C, and D). The following table gives the profit contributions per kilo, the available time (minutes) in each department, and time requires per kilo in each department for each product.

Product 1
Product 2
Product 3 Profit Contribution per Kilo
13
17
11

Amount of Time in Departments per Kilo Department
Product 1
Product 2
Product 3
Time Available
A
7
4
8
550
B
3
7
4
300
C
6
9
6
700
D
9
2
5
900

a. Formulate a linear programming model to maximize the company’s profit. State the objective function and constraints algebraically.

b. Implement the problem in Solver. Copy and paste here your Solver model after obtaining the solution.
c. Using Solver, find the production mix for the products that maximizes profits. What is the optimal mix and maximum profit?

Product 1: Product 2: Product 3:

Profit:

d. Which constraints are binding, and which ones are slack?

Department A: Department B:

Department C: Department D:

e. If the optimal amount of Product 3 is zero, how much would the contribution for Product 3 have to increase for the company to start producing Product 3? Use the Sensitivity Report.

f. Suppose that the contribution for Product 1 increases by 3 dollars. What is the new product mix? What is the new profit? Use the Sensitivity Report.

Product 1: Product 2: Product 3: Profit:

g. What is the maximum amount that the company should be willing to pay for 10 more minutes in Department A? Use the Sensitivity Report.

h. What is the maximum amount that the company should be willing to pay for 10 more minutes in Department D? Use the Sensitivity Report.

2. Fixed Costs and Mixed Programming. Consider Problem 1. The products have fixed costs and maximum demands in the following table. Include these parameters with the original Problem 1 statement.

Product 1
Product 2
Product 3
Fixed Costs
400
200
100
Maximum Demand Kilos
300
500
200

a. Formulate a linear programming model to maximize the company’s profit. State the objective function and constraints algebraically.

b. Implement the problem in Solver. Copy and paste here your Solver model after obtaining the solution.

c. What are the optimal production mix and profit?

3. Covering and Linear Programming. An IT firm staffs projects from a pool