Prof EXCEL 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 Nutri

Prof EXCEL 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.16×1 + 0.10×2 + 0.10×3 + 0.09×4 + 0.30×5 + 0.16×6 + 0.25×7 + 0.09×8 8. Wheat toast (slice) 65 0 1 26 3 3 0.09

s.t.

90×1 + 100×2 + 75×3 + 35×4 + 65×5 + 100×6 + 120×7 + 65×8 >= 420

2×2 + 5×3 + 3×4 + 4×6 + x8 <= 20 270x3 + 8x4 + 12x6 <= 30 6x1 + 2x2 + x3 + x5 + x8 >= 5

20×1 + 12×2 + 30×3 + 52×5 + 250×6 + 3×7 + 26×8 >= 400

3×1 + 5×2 + 7×3 + 2×4 + x5 + 9×6 + x7 + 3×8 >= 20

5×1 + 3×2 + x5 + 3×8 >= 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.