Homework answers / question archive / 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

Math

Share With

---

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.

 

 

Product	A	B	C	D	E
Profit	$ 15	$ 21	$ 17	$18	$ 24
Labor hours per unit of product	3.35	2.7	3.6	3.9	4.3
Material 1 per unit of product	3	4	0	2	0
Material 2 per unit of product	0	7	1	1	2
Material 3 per unit of product	7	1	8	11	12
Material 4 per unit of product	8	1	7	6	5

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. 

	Customers
Plants	Chicago	Boston	Denver	Nashville	New York
Indianapolis	$5	$15	$26	$7	$18
Grand Rapids	$3	$6	$36	$8	$12
Lincoln	$10	$12	$25	$24	$15
Kansas City	$13	$29	$15	$12	$16

 

 

 

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

Plants	Capacity		Customers	Demand
Indianapolis	20000		Chicago	6000
Grand Rapids	15000		Boston	7000
Lincoln	7000		Denver	10000
Kansas City	10000		Nashville	12000
			New York	9000

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.

Stock	Return
A	0.15
B	0.05
C	0.09
D	0.04
E	0.12

 

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.

Node	City		Node	City	Maximum Capacity
1	Chicago	to	2	Minneapolis	30
1	Chicago	to	3	St. Louis	45
2	Minneapolis	to	4	Lincoln	15
3	St Louis	to	5	Tulsa	25
3	St Louis	to	6	Denver	30
4	Lincoln	to	6	Denver	42
5	Tulsa	to	7	Santa Fe	55
6	Denver	to	8	Phoenix	32
7	Santa Fe	to	8	Phoenix	53

 

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)