Microsoft Excel 14.0 Answer Report Worksheet: [CoffeeBlending data.xlsx]CoffeeBlending Report Created: 1/15/2014 8:22:41 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 4 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $B$6 Maximize weekly profit: Profit Original Value $55,200.00 Final Value $55,200.00 Variable Cells Cell Name $B$3 Blend production Hotel $C$3 Blend production Restaurant $D$3 Blend production Market Original Value 10000 32500 30000 Final Value Integer 10000 Contin 32500 Contin 30000 Contin Constraints Cell Name $B$10 Robusta LHS: $B$11 Javan Arabica LHS: $B$12 Liberica LHS: $B$13 Brazillian Arabica LHS: $B$14 Processing plant: LHS: $B$16 Hotel LHS: $B$17 Restaurant LHS: $B$18 Market LHS: Cell Value Formula 16375 $B$10<=$C$10 19375 $B$11<=$C$11 20000 $B$12<=$C$12 16750 $B$13<=$C$13 72500 $B$14<=$C$14 10000 $B$16>=$C$16 32500 $B$17>=$C$17 30000 $B$18>=$C$18 Status Slack Not Binding 23625 Not Binding 5625 Binding 0 Not Binding 28250 Not Binding 27500 Binding 0 Not Binding 7500 Binding 0 Microsoft Excel 14.0 Sensitivity Report Worksheet: [CoffeeBlending data.xlsx]CoffeeBlending Report Created: 1/15/2014 8:22:41 PM Variable Cells Cell Name $B$3 Blend production Hotel $C$3 Blend production Restaurant $D$3 Blend production Market Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 10000 0 0.5525 0.085 1.0000E+030 32500 0 0.85 1.0000E+030 0.1133333333 30000 0 0.735 0.965 1.0000E+030 Constraints Cell Name $B$10 Robusta LHS: $B$11 Javan Arabica LHS: $B$12 Liberica LHS: $B$13 Brazillian Arabica LHS: $B$14 Processing plant: LHS: $B$16 Hotel LHS: $B$17 Restaurant LHS: $B$18 Market LHS: Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 16375 0 40000 1.0000E+030 23625 19375 0 25000 1E+30 5625 20000 4.25 20000 5500 1500 16750 0 45000 1.0000E+030 28250 72500 0 100000 1.0000E+030 27500 10000 -0.085 10000 10000 10000 32500 0 25000 7500 1.0000E+030 30000 -0.965 30000 3750 27500 shadow price is profit marginal Decision variables: Supplemental information: Hotel Blend production Objective Function: Maximize weekly profit: Constraints: Component availability: Robusta Javan Arabica Liberica Brazillian Arabica Processing plant: Min blend production levels: Hotel Restaurant Market Restaurant Market Component 10000 32500 30000 Robusta Javan Arabica Liberica Brazillian Arabica Wholesale Price Per Pound Profit Costs $55,200.00 $48,050.00 55200 $- Revenues $103,250.00 LHS: RHS: Purchases of components 16375 19375 20000 16750 40000 25000 20000 45000 Robusta Javan Arabica Liberica Brazillian Arabica 72500 10000 25000 30000 20% 40% 15% 25% $1.25 Hotel Restaurant 35% 15% 20% 30% $1.50 2000 4000 1500 2500 Restaurant Market 10% 35% 40% 15% $1.40 11375 4875 6500 9750 Market Cost per Max weekly pound availability (lbs) $0.60 40000 $0.80 25000 $0.55 20000 $0.70 45000 100000 10000 32500 30000 Hotel 3000 10500 12000 4500 total 16375 19375 20000 16750 how many to produce of each bland to maximize weekly process