BUS 220 - Introduction to Decision Sciences Fall 2015 Assignment 2 due on Thursday, October 8, 2015 NOTE: 10% of your grade will be deducted if the work is not presented in a clear and neat manner. NOTE: The assignment could be done jointly by (at most) 2 students. (Of course, it can be done by a single person.) In any case, the cover page indicating only student(s) names and student id(s) should be attached to each assignment. I will keep this page for my recording purposes. The first page of the solutions to the assignment should also include student(s) names and student number(s), so that you can get the solutions back. ----------------------------------------------------------------------------------------------------------Problem 1 Tom's Inc. produces two salsa products. Letting W= jars of Western Foods Salsa M= jars of Mexico City Salsa leads to formulation: Max z = 1W + 1.25M s.t. 5W + 7M 4480 Whole Tomatoes 3W + 1M 2080 Tomato Sauce 2W + 2M 1600 Tomato Paste W, M 0 The Excel solution is enclosed. a. (5 points) What is the optimal objective function value, and what are the optimal values of variables? b. (5 points) Specify the range of optimality for the objective function coefficients. c. (5 points) What are the shadow prices for each constraint? Interpret each. d. (5 points) Identify the range of feasibility for each of the right hand side values (i.e. in what range the shadow price for a constraint is valid). 1 Microsoft Excel 14.0 Answer Report- Problem 1. Assig 2 Worksheet: [Excel file Problem 1.xls]Sheet1 Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.078 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constrain Objective Cell (Max) Cell $B$10 Max Name Original Value 860 Variable Cells Cell Name $C$9 Dec. variables W $D$9 Dec. variables M Constraints Cell $C$12 $C$13 $C$14 Name whole LHS sauce LHS paste LHS Final Value 860 Original Value 560 240 Final Value 560 240 Cell Value 4480 1920 1600 Formula $C$12<=$E$12 $C$13<=$E$13 $C$14<=$E$14 Integer Contin Contin Status Binding Not Binding Binding Microsoft Excel 14.0 Sensitivity ReportProblem 1, Assig 2 Worksheet: [Excel file Problem 1.xls]Sheet1 Variable Cells Cell $C$9 $D$9 Name Dec. variables W Dec. variables M Final Value 560 240 Reduced Cost 0 0 Objective Coefficient 1 1.25 Allowable Increase 0.25 0.15 Allowable Decrease 0.107142857 0.25 Name whole LHS sauce LHS paste LHS Final Value 4480 1920 1600 Shadow Price 0.125 0 0.1875 Constraint R.H. Side 4480 2080 1600 Allowable Increase 1120 1E+30 40 Allowable Decrease 160 160 320 Constraints Cell $C$12 $C$13 $C$14 2 Slack 0 160 0 Problem 2 Supersport Footballs, Inc. has to determine the best number of All-Pro (A), College (C), and High School (H) models of footballs to produce in order to maximize profits. Constraints include production capacity limitations (time available in minutes) in each of three departments (cutting and dyeing, sewing, and inspection and packaging) as well as a constraint that requires the production of at least 1000 All-Pro footballs. The linear programming model of Supersport's problem is Max z =3A + 5C +4H s.t. 12A +10C + 8 H 18,000 Cutting and dyeing 15A + 15C+ 12H 18,000 Sewing 3A + 4C+ 2H 9,000 Inspection and packaging 1A 1,000 All-Pro model A, C, H 0 Refer to the enclosed Excel computer solution and answer the following: a. (10 points) Overtime rates in the sewing department are $12 per hour. Would you recommend that the company consider using overtime in that department? Explain. b. (10 points) What is the shadow price for the fourth constraint? Interpret its value for management. c. (10 points) Suppose that the profit contribution of the College ball is increased by $1. How do you expect the solution to change? Microsoft Excel 14.0 Answer ReportProblem 2, Assig 2 Worksheet: [Excel file Problem 2.xls]Sheet1 Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constraints, Assume NonNegative 3 Objective Cell (Max) Cell Name $B$10 MAX Original Value 4000 Final Value 4000 Variable Cells Cell Name $C$9 dec. variables A $D$9 dec. variables C $E$9 dec. variables H Original Value 1000 200 0 Final Value 1000 200 0 Cell Value 14000 18000 3800 1000 Formula $C$12<=$E$12 $C$13<=$E$13 $C$14<=$E$14 $C$15>=$E$15 Constraints Cell Name $C$12 cutting LHS $C$13 sewing LHS $C$14 inspection LHS $C$15 all pro-model LHS Integer Contin Contin Contin Status Not Binding Binding Not Binding Binding Slack 4000 0 5200 0 Microsoft Excel 14.0 Sensitivity Report Problem 2, Assig 2 Worksheet: [Excel file Problem 2.xls]Sheet1 Variable Cells Cell $C$9 $D$9 $E$9 Name dec. variables A dec. variables C dec. variables H Final Value 1000 200 0 Reduced Cost 0 0 0 Objective Coefficient 3 5 4 Allowable Increase 2 1E+30 0 Allowable Decrease 1E+30 0 1E+30 Name cutting LHS sewing LHS inspection LHS all pro-model LHS Final Value 14000 18000 3800 1000 Shadow Price 0 0.333333333 0 -2 Constraint R.H. Side 18000 18000 9000 1000 Allowable Increase 1E+30 6000 1E+30 200 Allowable Decrease 4000 3000 5200 1000 Constraints Cell $C$12 $C$13 $C$14 $C$15 4 Problem 3 25 points The employee credit union at State University is planning the allocation of funds for the coming year. The credit union makes four types of loans to its members. In addition, the credit union invests in risk free securities to stabilize income. The various revenue producing investments together with annual rates of returns are as follows: Type of Loan/Investment Automobile loans Furniture loans Other Secured loans Signature loans Risk Free securities Annual Rate of Return (%) 8 10 11 12 9 The credit union has $2,000,000 available for investment during the coming year. State laws and credit union policies impose the following restrictions on the composition of the loans and investments. - Risk Free securities may not exceed 30% of the total funds available for investment. - Signature loans may not exceed 10% of the funds invested in all loans (automobile, furniture, other secured, and signature loans). - Furniture loans plus other secured loans may not exceed the automobile loans. - Other secured loans plus signature loans may not exceed the funds invested in risk free securities. How should the $2,000,000 be allocated to each of the loan/investment alternatives to maximize total return? What is the projected total annual return? Formulate the problem and solve it on a computer by using the Microsoft's Excel. You need to submit the formulation (including the definition of variables) as well as the computer output. Problem 4 (25 points) Ajax Fuels, Inc., is developing a new additive for airplane fuels. The additive is a mixture of three ingredients: A, B, and C. For the proper performance, the total amount of additive (amount of A + amount of B + amount of C) must be at least 10 ounces per gallon of fuel. However, because of safety reason, the amount of additive must not exceed 15 ounces per gallon of fuel. The mix or blend of the three ingredients is critical. At least 1 ounce of ingredient A must be used for every ounce of ingredient B, and the amount of ingredient C must be greater than or equal to one half the amount of ingredient A. If the costs per ounce for ingredients A, B, and C are $ 0.10, $0.03 and $ 0.09, respectively, find the minimum-cost mixture of A, B, and C for each gallon of airplane fuel. Formulate the problem and solve it on a computer by using the Microsoft's Excel. You need to submit the formulation (including the definition of variables), as well as the computer output. 5