Fluent Essays

NEED TO BE DONE AROUND 15 HOURS AND NEED TO BE PROF IN EXCEL. WORK ON PRC XLSX FILE - ANOTHER ONE IS EXAMPLE Q1 - Instructions For the Nutrition Optimization LP problem presented in the 'Problem Formulation' worksheet, set up and solve the LP using Solver in the 'Solver Solution Q1' worksheet to find the optimal solution and generate the Answer and Sensitivity Reports. Once complete, answer the questions below and upload the complete spreadsheet with the Solver set-up and solution/sensitivity analysis intact to D2L. AFTER FINDING THE SOLUTION, USE THE ANSWER AND SENSITIVITY REPORTS TO ANSWER THE FOLLOWING QUESTIONS How low would the cost of an egg have to be before we would want to include any in our breakfast plan? Report final answer to 4 decimal places Based upon the Sensitivity Report, if we increased the calorie requirement from 420 to 520 calories, what would the new total cots? (specify to 3 decimal places) How high could the cost of oatmeal be and our current optimal solution would remain optimal? (specify to 3 decimal places) Problem Formulation CAG Seniors Healthcare, a seniors health and fitness center, operates a morning fitness program for senior citizens. The program includes aerobic exercise, either swimming or step exercise, followed by a healthy breakfast in the dining room. CAG's dietitian wants to develop a breakfast that will be high in calories, calcium, protein, and fiber, which are especially important to senior citizens, but low in fat and cholesterol. She also wants to minimize cost. She has selected the following possible food items, whose individual nutrient contributions and cost from which to develop a standard breakfast menu are shown in the Table 1 below. The dietitian wants the breakfast to include at least 420 calories, 5 milligrams of iron, 400 milligrams of calcium, 20 grams of protein, and 12 grams of fiber. Furthermore, she wants to limit fat to no more than 20 grams and cholesterol to 30 milligrams. Setup and solve the problem in the adjacent ('Solver Solution') worksheet, generate the answer and sensitivity reports, then answer the questions given on the first worksheet. Decision Variables x1 = cups Bran cereal Table 1 x2 = cups Oatmeal Breakfast Fat Cholesterol Iron Calcium Protein Fiber x3 = # Eggs Food Calories (g) (mg) (mg) (mg) (g) (g) Cost($) x4 = slice of Bacon 1. Bran cereal (cup) 90 0 6 20 3 5 0.16 x5 = # Oranges 2. Oatmeal (cup) 100 0 2 12 5 3 0.1 x6 = cups Milk - 2% 3. Egg 75 270 1 30 7 0 0.1 x7 = cups Orange juice 4. Bacon (slice) 35 8 0 0 2 0 0.09 x8 = slices of Wheat toast 5. Orange 65 0 1 52 1 1 0.3 6. Milk—2% (cup) 100 12 0 250 9 0 0.16 Objective Function 7. Orange juice (cup) 120 0 0 3 1 0 0.25 Minimize Cost = 0.16x1 + 0.10x2 + 0.10x3 + 0.09x4 + 0.30x5 + 0.16x6 + 0.25x7 + 0.09x8 8. Wheat toast (slice) 65 0 1 26 3 3 0.09 s.t. 90x1 + 100x2 + 75x3 + 35x4 + 65x5 + 100x6 + 120x7 + 65x8 >= 420 2x2 + 5x3 + 3x4 + 4x6 + x8 <= 20 270x3 + 8x4 + 12x6 <= 30 6x1 + 2x2 + x3 + x5 + x8 >= 5 20x1 + 12x2 + 30x3 + 52x5 + 250x6 + 3x7 + 26x8 >= 400 3x1 + 5x2 + 7x3 + 2x4 + x5 + 9x6 + x7 + 3x8 >= 20 5x1 + 3x2 + x5 + 3x8 >= 12 xi >= 0, i=1,...,8 Solver Solution Q1 Set-up the spreadsheet model and run Solver to find the optimal solution for LP formulated in the previous worksheet for CAG. Cleary label or identify the decision variables, objective function and constraints. Find the optimal solution and generate the Answer and Sensitivity reports, them answer the questions on the first worksheet. Q2 - Instructions The owner-operator of a truck is a member of a consortium of approved shippers. He identified (from a computerized database) 6 requests, each to have the cargo transported from Atlanta to New York City. He wants to decide which of the 6 requests to accept so as to maximize the total value of the cargo transported (in a single trip). His truck has a capacity to transport at most 12 tons of cargo. He wants to accept at most four requests. For safety reasons, he can accept at most one of requests 3 and 4. For each request, the value and weight of the cargo needing transport are given in the table below. The IP formulation for this problem is given below. Setup and solve the problem in the adjacent 'Solver Solution Q2' worksheet and generate the answer report. Request Value of cargo ($) Weight of cargo (tons) 1 7200 2.4 2 9000 4.8 3 4700 2 4 6600 3.7 5 6400 3.1 6 5500 3.5 Decision Variables yi = to accept request i or not, with 1 = accept and 0 not accept a request, i = 1,…,6 Objective Function Maximize Value ($) = 7,200 y1 + 9,000 y2 + 4,700,y3 + 6,600 y4 + 6,400 y5 + 5,500 y6 s.t. (1) 2.4y1 + 4.8y2 + 2.0y3 + 3.7y4 + 3.1y5 + 3.5y6 ≤ 12 resource (weight capacity) constraint (2) y1 + y2 + y3 + y4 + y5 + y6 ≤ 4 at most 4 requests accepted (3) y3 + y4 ≤ 1 can accept at most one of requests 3 and 4 (can still select neither) yi = 0 or 1 (binary decision variables), i = 1,…,6 Solver Solution Q2 Set-up the spreadsheet model and run Solver to find the optimal solution for the trucking cargo problem. Also generate the Answer report Be sure to cleary label or identify the decision variables, objective function and constraints in the spreadsheet. Answer Report 1 Microsoft Excel 16.0 Answer Report Worksheet: [Fixed Cost example solver solution.xlsx]solver solution Report Created: 9/3/2021 4:27:13 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 8 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 (Min) Cell Name Original Value Final Value $K$7 Objective Function: Min Costs Min Costs $0 $13,250 Variable Cells Cell Name Original Value Final Value Integer $C$5 Decision variables x1 0 6000 Integer $D$5 Decision variables x2 0 0 Integer $E$5 Decision variables x3 0 4000 Integer $F$5 Decision variables x4 0 0 Integer $G$5 Decision variables y1 0 1 Binary $H$5 Decision variables y2 0 0 Binary $I$5 Decision variables y3 0 1 Binary $J$5 Decision variables y4 0 0 Binary Constraints Cell Name Cell Value Formula Status Slack $K$11 Plant Capacity with Set-up - machine 1 LHS 0 $K$11<=$M$11 Binding 0 $K$12 Plant Capacity with Set-up - machine 2 LHS 0 $K$12<=$M$12 Binding 0 $K$13 Plant Capacity with Set-up - machine 3 LHS 0 $K$13<=$M$13 Binding 0 $K$14 Plant Capacity with Set-up - machine 4 LHS 0 $K$14<=$M$14 Binding 0 $K$15 Machine 4 and machine 1 cannot be used simultaneously LHS 1 $K$15<=$M$15 Binding 0 $K$16 Required Demand LHS 10000 $K$16>=$M$16 Binding 0 $C$5:$F$5=Integer $G$5:$J$5=Binary solver solution Fixed Cost (Westfall Garden) Solver Solution. (see formulation below). x1 x2 x3 x4 y1 y2 y3 y4 Decision variables 6000 0 4000 0 1 0 1 0 Coefficients Min Costs Objective Function: Min Costs 1.25 1.50 1.00 2.00 750 500 1000 300 $13,250 Constraints Coefficients LHS RHS Plant Capacity with Set-up - machine 1 1 -6000 0 <= 0 Plant Capacity with Set-up - machine 2 1 -7500 0 <= 0 Plant Capacity with Set-up - machine 3 1 -4000 0 <= 0 Plant Capacity with Set-up - machine 4 1 -5000 0 <= 0 Machine 4 and machine 1 cannot be used simultaneously 1 1 1 <= 1 Required Demand 1 1 1 1 10000 >= 10000 Decision Variables xi = number of hoses to produce on machine ‘i', i=1,…,4 yi = to setup machine ‘i’ or not (0=no, 1=yes), i=1,…,4 Objective Function: Min Costs = 1.25x1 + 1.50x2 + 1.00x3 + 2.00x4 + 750y1 + 500y2 + 1000y3 + 300y4 s.t. x1 - 6000y1 < 0 x2 - 7500y2 < 0 x3 - 4000y3 < 0 x4 - 5000y4 < 0 y1 + y4 < 1 x1 + x2 + x3 + x4 > 10,000 xi > 0, int., i=1,…,4 yi = 0,1 (binary decision variables), i=1,…,4