Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

What is the significance of an absolute cell reference in Excel?

The built-in Solver in Excel is found under which tab on the ribbon?

Spreadsheet modeling is an acquired skill because

Exhibit 3.3 The following questions are based on this problem and accompanying Excel windows. Jack's distillery blends scotches for local bars and saloons.One of his customers has requested a special blend of scotch targeted as a bar scotch.The customer wants the blend to involve two scotch products,call them A and B.Product A is a higher quality scotch while product B is a cheaper brand.The customer wants to make the claim the blend is closer to high quality than the alternative.The customer wants 50 1500 ml bottles of the blend.Each bottle must contain at least 48% of Product A and at least 500 ml of B.The customer also specified that the blend have an alcohol content of at least 85%.Product A contains 95% alcohol while product B contains 78%.The blend is sold for $12.50 per bottle.Product A costs $7 per liter and product B costs $3 per liter.The company wants to determine the blend that will meet the customer's requirements and maximize profit. Let ^X₁ ^{= Number of liters of product A in total blend delivered} X₂ = Number of liters of product B in total blend delivered MIN: ^{7 X}₁ ^{+ 3 X}₂ Subject to: ^X₁ ^{+ X}₂ ^{= 1.5 * 50 Total liters of mix)}X₁ ≥ 0.48 * 1.5 * 50 X₁ minimum) X₂ ≥ 0.5 * 50 X₂ minimum) .0.95 X₁ + 0.78 X₂ ≥ 0.85 * 1.5 * 50 85% alcohol minimum)X₁,X₂ ≥ 0 A 1 Jacks' Distill ery 2 3 4 Liters to use Total Cost: 5 Unit cost: 10.5 4 6 5 7 Constraints: Supplied Requirement Total Liters 1 1 75 9 A required 1 36 1 B required 1 25 0 1 85\% alcohal 0.95 0.78 63.7 1 -Refer to Exhibit 3.3.Which of the following statements could represent a constraint in this problem?

A company needs to purchase several new machines to meet its future production needs.It can purchase three different types of machines A,B,and C.Each machine A costs $80,000 and requires 2,000 square feet of floor space.Each machine B costs $50,000 and requires 3,000 square feet of floor space.Each machine C costs $40,000 and requires 5,000 square feet of floor space.The machines can produce 200,250 and 350 units per day respectively.The plant can only afford $500,000 for all the machines and has at most 20,000 square feet of room for the machines.The company wants to buy as many machines as possible to maximize daily production. Formulate the LP for this problem.

The "Objective Sense" option in the Analytic Solver Platform task pane may be used to

Models which are setup in an intuitively appealing,logical layout tend to be the most

Data Envelopment Analysis DEA)is an LP-based methodology in which weighted sums of inputs and outputs are calculated and

A hospital needs to determine how many nurses to hire to cover a 24 hour period.The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts.They are paid different wages depending on when they start their shifts.The number of nurses required per 4-hour time period and their wages are shown in the following table. Time period Required \# of Nurses Wage \ /hr) 12-4 20 15 4-8 30 16 8-12 40 13 12-4 50 13 4-8 40 14 8-12 30 15 Enter the numbers in the appropriate cells of ranges B6:G11 and B13:G13 in the Excel spreadsheet to solve this problem based on the following formulation. Let ^X_i ^{= number of nurses working in time period i;i = 1,6} MIN: ^1X₁ ^{+ 1X}₂ ^{+ 1X}₃ ^{+ 1X}₄ ^{+ 1X}₅ ^{+ 1X}₆ Subject to: ^1X₁ ^{+ 1X}₂ ^{? 30} 1X₂ + 1X₃ ? 40 1X₃ + 1X₄ ? 50 1X₄ + 1X₅ ? 40 1X₅ + 1X₆ ? 30 1X₁ + 1X₆ ? 20 X_i ? 0

You have been given the following linear programming model and Excel spreadsheet to solve this problem.What cell references would you enter in the Risk Solver Platform RSP)task pane for the following? Objective Cell: Variables Cells: Constraints Cells: 1 2 3 4 Number to make: OBJ. FN. VALUE 5 Unit profit: 8 5 6 7 Constraints: Used Available 8 1 3 5 54 9 2 1 1 14 1 0 4 10 3 1 0 12

The constraints X₁ ≥ 0 and X₂ ≥ 0 are referred to as

Exhibit 3.2 The following questions are based on this problem and accompanying Excel windows. The Byte computer company produces two models of computers,Plain and Fancy.It wants to plan how many computers to produce next month to maximize profits.Producing these computers requires wiring,assembly and inspection time.Each computer produces a certain level of profits but faces only a limited demand.There are also a limited number of wiring,assembly and inspection hours available in each month.The data for this problem is summarized in the following table. Maximum Assembly Inspection Computer Profit per demand for Wiring Hours Hours Hours Model Model \ ) product Required Required Required Plain 30 80 .4 .5 .2 Fancy 40 90 .5 .4 .3 hours avaible 50 50 22 Let ^X₁ ^{= Number of Plain computers to produce} X₂ = Number of Fancy computers to produce MAX: ^{30 X}₁ ^{+ 40 X}₂ Subject to: ^{.4 X}₁ ^{+ .5 X}₂ ^{≤ 50 wiring hours)} .5 X₁ + .4 X₂ ≤ 50 assembly hours) .2 X₁ + .2 X₂ ≤ 22 inspection hours)X₁ ≤ 80 Plain computers demand)X₂ ≤ 90 Fancy computers demand)X₁,X₂ ≥ 0 A B C D E 1 Byte Computer Company 2 3 Plain Fancy 4 Number to make: Total Profit: 5 Unit profit: 30 40 6 7 Constraints: Used Available 8 Wiring 0.4 0.5 50 9 Assembly 0.5 0.4 50 10 Inspection 0.2 0.3 22 11 Plain Demand 1 80 12 Fancy Demand 1 90 -Refer to Exhibit 3.2.What formula should be entered in cell D8 in the accompanying Excel spreadsheet to compute the amount of wiring used?

Exhibit 3.2 The following questions are based on this problem and accompanying Excel windows. The Byte computer company produces two models of computers,Plain and Fancy.It wants to plan how many computers to produce next month to maximize profits.Producing these computers requires wiring,assembly and inspection time.Each computer produces a certain level of profits but faces only a limited demand.There are also a limited number of wiring,assembly and inspection hours available in each month.The data for this problem is summarized in the following table. Maximum Assembly Inspection Computer Profit per demand for Wiring Hours Hours Hours Model Model \ ) product Required Required Required Plain 30 80 .4 .5 .2 Fancy 40 90 .5 .4 .3 hours avaible 50 50 22 Let ^X₁ ^{= Number of Plain computers to produce} X₂ = Number of Fancy computers to produce MAX: ^{30 X}₁ ^{+ 40 X}₂ Subject to: ^{.4 X}₁ ^{+ .5 X}₂ ^{≤ 50 wiring hours)} .5 X₁ + .4 X₂ ≤ 50 assembly hours) .2 X₁ + .2 X₂ ≤ 22 inspection hours)X₁ ≤ 80 Plain computers demand)X₂ ≤ 90 Fancy computers demand)X₁,X₂ ≥ 0 A B C D E 1 Byte Computer Company 2 3 Plain Fancy 4 Number to make: Total Profit: 5 Unit profit: 30 40 6 7 Constraints: Used Available 8 Wiring 0.4 0.5 50 9 Assembly 0.5 0.4 50 10 Inspection 0.2 0.3 22 11 Plain Demand 1 80 12 Fancy Demand 1 90 -Refer to Exhibit 3.2.What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit?

You have been given the following linear programming model and Excel spreadsheet to solve this problem.What numbers should be entered into cells B5:C5 and B8:C10 to implement this model? MIN: ^{8 X}₁ ^{+ 3 X}₂ Subject to: ^X₂ ^{≥ 8} 8 X₁ + 5 X₂ ≥ 80 3 X₁ + 5 X₂ ≥ 60 X₁,X₂ ≥ 0 1 2 3 4 Number to make: OBJ. FN. VALUE 5 Unit profit: 6 7 Constraints: Used Available 8 1 8 9 2 80 10 3 60

A heuristic solution is

Exhibit 3.3 The following questions are based on this problem and accompanying Excel windows. Jack's distillery blends scotches for local bars and saloons.One of his customers has requested a special blend of scotch targeted as a bar scotch.The customer wants the blend to involve two scotch products,call them A and B.Product A is a higher quality scotch while product B is a cheaper brand.The customer wants to make the claim the blend is closer to high quality than the alternative.The customer wants 50 1500 ml bottles of the blend.Each bottle must contain at least 48% of Product A and at least 500 ml of B.The customer also specified that the blend have an alcohol content of at least 85%.Product A contains 95% alcohol while product B contains 78%.The blend is sold for $12.50 per bottle.Product A costs $7 per liter and product B costs $3 per liter.The company wants to determine the blend that will meet the customer's requirements and maximize profit. Let ^X₁ ^{= Number of liters of product A in total blend delivered} X₂ = Number of liters of product B in total blend delivered MIN: ^{7 X}₁ ^{+ 3 X}₂ Subject to: ^X₁ ^{+ X}₂ ^{= 1.5 * 50 Total liters of mix)}X₁ ≥ 0.48 * 1.5 * 50 X₁ minimum) X₂ ≥ 0.5 * 50 X₂ minimum) .0.95 X₁ + 0.78 X₂ ≥ 0.85 * 1.5 * 50 85% alcohol minimum)X₁,X₂ ≥ 0 A 1 Jacks' Distill ery 2 3 4 Liters to use Total Cost: 5 Unit cost: 10.5 4 6 5 7 Constraints: Supplied Requirement Total Liters 1 1 75 9 A required 1 36 1 B required 1 25 0 1 85\% alcohal 0.95 0.78 63.7 1 -Refer to Exhibit 3.3.Which cells should be the constraint cells in this problem?

Which tab in the Analytic Solver Platform task pane is used to define an optimization problem?

What does the Excel "=SUMPRODUCTA1:A5,C6;C10)" function do?

Exhibit 3.1 The following questions are based on this problem and accompanying Excel windows. Jones Furniture Company produces beds and desks for college students.The production process requires carpentry and varnishing.Each bed requires 6 hours of carpentry and 4 hour of varnishing.Each desk requires 4 hours of carpentry and 8 hours of varnishing.There are 36 hours of carpentry time and 40 hours of varnishing time available.Beds generate $30 of profit and desks generate $40 of profit.Demand for desks is limited,so at most 8 will be produced. Let ^X₁ ^{= Number of Beds to produce} X₂ = Number of Desks to produce The LP model for the problem is MAX: ^{30 X}₁ ^{+ 40 X}₂ Subject to: ^{6 X}₁ ^{+ 4 X}₂ ^{≤ 36 carpentry)} 4 X₁ + 8 X₂ ≤ 40 varnishing) X₂ ≤ 8 demand for desks) X₁,X₂ ≥ 0 -You have been given the following linear programming model. MAX: ^{8 X}₁ ^{+ 5 X}₂ Subject to: ^{3 X}₁ ^{+ 5 X}₂ ^{= 54} 11 X₁ + 10 X₂ ≤ 144 X₁ ≥12 X₂ X₁,X₂ ≥ 0 This formulation is:

Exhibit 3.1 The following questions are based on this problem and accompanying Excel windows. Jones Furniture Company produces beds and desks for college students.The production process requires carpentry and varnishing.Each bed requires 6 hours of carpentry and 4 hour of varnishing.Each desk requires 4 hours of carpentry and 8 hours of varnishing.There are 36 hours of carpentry time and 40 hours of varnishing time available.Beds generate $30 of profit and desks generate $40 of profit.Demand for desks is limited,so at most 8 will be produced. Let ^X₁ ^{= Number of Beds to produce} X₂ = Number of Desks to produce The LP model for the problem is MAX: ^{30 X}₁ ^{+ 40 X}₂ Subject to: ^{6 X}₁ ^{+ 4 X}₂ ^{≤ 36 carpentry)} 4 X₁ + 8 X₂ ≤ 40 varnishing)X₂ ≤ 8 demand for desks)X₁,X₂ ≥ 0 1 Jones Furnit 2 ure 3 4 Number to make: Total Profit: 5 Unit profit: 30 40 6 7 Constraints: Use Available 8 Carpentry 6 4 36 9 Varnishing 4 8 40 10 Desk demand 1 8 -Refer to Exhibit 3.1.What formula should be entered in cell D8 in the accompanying Excel spreadsheet to compute the amount of carpentry used?