Problem 1 (12 points) You may use either a Word document or Excel for this problem

Problem 1 (12 points)

You may use either a Word document or Excel for this problem.   Name the Word or Excel file as follows:   Your last name_Problem1.   For example, your instructor would name it as follows:  Jance_Problem1 .

Suppose you are given the following mathematical model:

Maximize  15X₁ + 23X₂

5X₁ + 2X₂  ≤ 30

5X₁ + 4X₂   ≤ 32

2X₁ + 4X₂   ≤ 20

X₁, X₂ ≥ 0

1. Graph the problem (5 points) 
   1. Clearly label the horizonal axis and the vertical axis
   2. Clearly label each constraint
   3. Clearly label the corner points
   4. Clearly label the feasible solution

B) What are the corner points?  (3 points)

C) What is the optimal solution?  (2 points)

D) What is the objective function value for the optimal solution?  (2 points)

Problem 2: (12 points)

Use a separate Excel file for this problem.   Name the Excel file as follows:   Your last name_Problem2.   For example, your instructor would name it as follows:  Jance_Problem2 .

Your company manufactures five products:  A, B, C, D, and E.   The company wants to maximize its monthly profit.   The maximum possible sales of products A, B, C, D, and E are 125, 150, 175, 130, and 100 respectively.

The company has 1600 labor hours, 1000 units of Material 1, 1300 units of Material 2, 2300 units of Material 3, and 1700 units of Material 4 available.   

Your job is to determine how many units of products A, B, C, D, and E should be produced in order to maximize profits.  

1. Write out the mathematical model for the problem (2 points).
2. Setup a linear programming model in Excel and then use Solver to solve. You must show your decision model and Solver settings (4 points).

1. What is the optimal solution?
2. What is the maximum profit?

3. Run an Excel Solver Answer Report and Sensitivity Report (2 points)
4. How does the solution change if the profit of product D is increased to $22 instead of $18 (2 points)?
5. How does the solution change if one is forced to make at least 20 of Product D?     Add the constraint to the model and rerun Solver.  What is the optimal solution and maximum profit now (2 points)?

Problem 3:  12 points

Use a separate Excel file for this problem.   Name the Excel file as follows:   Your last name_Problem3.   For example, your instructor would name it as follows:  Jance_Problem3 .

You have four plants that must service customers in five major cities.  The following are the shipping costs for one item.  For example, the cost for one item is $5 from the plant in Indianapolis to the customer in Chicago. 

The following are plant capacities and amount needed by each customer.

You want to determine the minimum shipping cost in order to meet the customer demand.

1. Write out the mathematical model for the problem (5 points)
2. Setup a linear programming model in Excel to determine the minimum shipping cost. You must show your decision model and Solver settings (7 points).

Problem 4:  12 points

Use a separate Excel file for this problem.   Name the Excel file as follows:   Your last name_Problem4.   For example, your instructor would name it as follows:  Jance_Problem4 .

Suppose you have $100,000 to invest in five stocks:  A, B, C, D, and E.  You want to invest the whole $100,000 and want to invest at least $7,500 in each stock.    If you invest in Stock A, then you want to invest at least that amount in Stock D.   In addition, the sum of your investments in stocks C and E have to be less than the sum of your investments in stocks B and D. Your goal is to maximize your return.   The following are the returns for the stocks.

1. Write out the mathematical model for the problem (5 points).

2. Setup a linear programming model in Excel to determine the maximum return. You must show your decision model and Solver settings (7 points).

Problem 5: (12 points)

Use a separate Excel file for this problem.   Name the Excel file as follows:   Your last name_Problem5.   For example, your instructor would name it as follows:  Jance_Problem5 .

You are transporting items from Chicago to Phoenix.    You want to maximize the items transported (maximize the flow).   The following show the routes and capacity for each arc.

1. Setup a linear programming model in Excel and use Solver to determine the maximum flow through the system. You must show your decision model and Solver settings (8 points).

2. How much should be sent to each city.  For example, show much should be sent from Chicago to Minneapolis,  Chicago to St. Louis, etc?  (2 points)

3. What is the maximum flow through the system?  (2 points)

Problem 6: (10 points)

You may use either a Word document or Excel for this problem.   Name the Word or Excel file as follows:   Your last name_Problem6.   For example, your instructor would name it as follows:  Jance_Problem6 .

You are given the following network diagram.   Use the minimal spanning tree algorithm to determine the arcs that will connect all the nodes at the lowest cost.  Start at Node 1.

1. Node 2

   Node 3

   Node 8

   Node 4

   Node 5

   Node 6

   Node 7

   Node 1

   Draw the optimal solution that gives the lowest cost (6 points).

2. $80

   $110

   $40

   $80

   $70

   $25

   $60

   $50

   $90

   $50

   $35

   $75

   $50

   $60

   What is the lowest cost? (4 points)