Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

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 \ / h r ) 12-4 20 15 4-8 30 16 8-12 40 13 12-4 50 13 4-8 40 14 8-12 30 15 What values would you enter in the Risk Solver Platform RSP)task pane for the following cells for this Excel spreadsheet implementation of the formulation for this problem? Objective Cell: Variables Cells: Constraints Cells:

State Farm Supply has just received an order for 10,000 pounds of chicken feed.The farmer has specified certain that the feed meet minimum requirements for Protein,Carbohydrate,Fat and Vitamins.State Farm can blend four different feeds to produce the required mix.The farmer would like to pay the lowest possible price for the feed.The data for the problem is summarized in the following table. State Farm Supply Percent of Nutrient in: Minimum Nutrient Feed 1 Feed 2 Feed 3 Feed 4 Req'd Ant Pratein 15 20 30 15 18 Carbahydirate 20 10 10 15 12 Fat 20 30 15 20 20 Vitamin 1 1.50 0.75 0.50 1 Cast'1,000 lbs \ 500 \ 600 \ 550 \ 450 Formulate the LP for this problem.

The hospital administrators at New Hope,County General,and City East recently received notice of an impending state inspection of their facilities.Under new guidelines established to improve the overall health care system,state inspectors will be assessing the efficiency of each hospital.The staff at New Hope has suggested a mutual assistance program in preparation for the inspections and have proposed using DEA as a means to assess the efficiency of each facility.The data collected thus far is summarized in the following table.All data reflects averages compiled over the past six months. Hospital New Hope County General City East Input Measures Bed days unused 1000s) 83.0 105.0 104.1 Supply expense \ 1000) 123.8 162.3 154.0 Full-time staff 225.0 200.0 231.0 Output Measures Patient-days 1000s) 105.0 71.0 82.7 Nurses qualified 253.0 92.0 175.0 Assistants on staff 125.0 45.0 65.0 a.Formulate a DEA LP model to evaluate the efficiency of City East. b.Implement a spreadsheet model for this problem and compute the DEA efficiency for each facility.Which facilities are efficient?

Using Data Envelopment Analysis DEA)for an inefficient unit,a more efficient composite unit can be found by

Which type of spreadsheet cell represents the decision variables in an LP model?

A paper mill has received an order for rolls of paper.The customer wants 400 12" wide rolls,300 18" rolls and 200 24" rolls.The company has 40" wide rolls of paper which it can slit to the appropriate width.The company wants to minimize the number of rolls it must use to fill the order. Formulate the LP for this problem.

A financial planner wants to design a portfolio of investments for a client.The client has $400,000 to invest and the planner has identified four investment options for the money.The following requirements have been placed on the planner.No more than 30% of the money in any one investment,at least one half should be invested in long-term bonds which mature in six or more years,and no more than 40% of the total money should be invested in B or C since they are riskier investments.The planner has developed the following LP model based on the data in this table and the requirements of the client.The objective is to maximize the total return of the portfolio. Investment Return Years to Maturity Rating A 0.45\% 6 1 -Facellent B 8.5\% 5 3-Good C 9.00\% 8 4- Fair D 7.75\% 4 2-Very Good What formulas are required for the following cells in the Excel spreadsheet implementation of the formulation? B7 D7 F7 H7

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? MAX: ^{4 X}₁ ^{+ 3 X}₂ Subject to: ^{6 X}₁ ^{+ 7 X}₂ ^{≤ 84} X₁ ≤ 10 X₂ ≤ 8 X₁,X₂ ≥ 0 1 2 3 4 Number to make: OBJ. FN. VALUE 5 Unit profit: 6 7 Constraints: Used Available 8 1 9 2 84 10 3 10

Exhibit 3.5 The following questions are based on this problem and accompanying Excel windows. A company is planning production for the next 4 quarters.They want to minimize the cost of production.The production cost is stable but demand and production capacity vary from quarter to quarter.The maximum amount of inventory which can be held is 12,000 units and management wants to keep at least 3,000 units on hand.Quarterly inventory holding cost is 3% of the cost of production.The company estimates the number of units carried in inventory each month by averaging the beginning and ending inventory for each month.There are currently 5,000 units in inventory.The company wants to produce at no less than one half of its maximum capacity in any quarter. Quarter 1 2 3 4 Unit Production Cost \ 300 \ 300 \ 300 Units Demanded 2,000 9,000 12,000 11,000 Marimum Production 8,000 7,000 8,000 9,000 Let ^P_i ^{= number of units produced in quarter i,i = 1,... ,4} B_i = beginning inventory for quarter i MIN: ^{300 P}₁ ^{+ 300 P}₂ ^{+ 300 P}₃ ^{+ 300 P}₄ ⁺ 9B₁ + B₂)/2 + 9B₂ + B₃)/2 + 9B₃ + B₄)/2 + 9B₄ + B₅)/2 Subject to: ^{4000 ≤ P}₁ ^{≤ 8000} 3500 ≤ P₂ ≤ 7000 4000 ≤ P₃ ≤ 8000 4500 ≤ P₄ ≤ 9000 3000 ≤ B₁ + P₁ − 2000 ≤ 12000 3000 ≤ B₂ + P₂ − 9000 ≤ 12000 3000 ≤ B₃ + P₃ − 12000 ≤ 12000 3000 ≤ B₄ + P₄ − 11000 ≤ 12000 B₂ = B₁ + P₁ − 2000 B₃ = B₂ + P₂ − 9000 B₄ = B₃ + P₃ − 12000 B₅ = B₄ + P₄ − 11000 P_i,B_i ≥ 0 A B C D E F 1 Quarter 2 1 2 3 4 3 Beginning Inventory 5,000 11,000 9,000 5,000 4 Units Produced 8,000 7,000 8,000 9,000 5 Units Demanded 2,000 9,000 12,000 11,000 6 Ending Inventory 11,000 9,000 5,000 3,000 7 8 Minimum Production 4,000 3,500 4,000 4,500 9 Maximum Production 8,000 7,000 8,000 9,000 10 11 Minimum Inventory 3,000 3,000 3,000 3,000 12 Maximum Inventory 12,000 12,000 12,000 12,000 13 14 Unit Production Cost \ 300 \ 300 \ 300 \ 300 15 Unit Carrying Cost 3.00\% \ 9.00 \ 9.00 \ 9.00 \ 9.00 16 17 Quarterly Production Cost \ 2,400,000 \ 2,100,000 \ 2,400,000 \ 2,700,000 18 Quarterly Carrying Cost \ 72,000 \ 90,000 \ 63,000 \ 36,000 19 20 Total Cost \ 9,861,000 -Refer to Exhibit 3.5.What formula should be entered in cell C6 in the accompanying Excel spreadsheet to compute ending inventory?

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 Formulate the LP for this problem.

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 changing cells in this problem?

An LP problem with a feasible region will have

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.Which of the following statements will represent the constraint for just assembly hours?

Problems which have only integer solutions are called

A grain store has six types of grain,each varying in cost,quality,and nutritional content.Periodically,excess inventory of these grains are consolidated into two local products,Feed-M-All and Supreme-Feed.Feed-M-All sells for $6.50 for a 10-pound bag while Supreme-Feed sells for $8.50 for a 10-pound bag.These feeds are advertised as having the following nutritional content: Grain Minimum Protein Minimum Fat Maximum Carbohydrates Feed-M-All 16\% 18\% 10\% Supreme-Feed 18\% 18\% 9\% The component grains have the following content characteristics: Gran Cost/10 lbs Quality Pratein Fat Carbohydrates Paunds Avail. A \ 4.75 4 15\% 10\% 10\% 90 \ 4.00 2 20\% 20\% 8\% 120 \ 3.75 1 10\% 25\% 5\% 150 \ 4.25 3 15\% 20\% 10\% 125 \ 4.50 3 20\% 20\% 10\% 85 \ 5.00 4 25\% 15\% 12\% 165 Targets for Feed-M-All are a cost of $ 4.35 per 10-pound bag,a quality rating of 2.25,along with the minimum percentages of protein and fat,and the maximum percentage of carbohydrates.Similar targets are set for Supreme- Feed with cost set at $ 4.60 and quality at 2.45.There must be at least a 70%-30% mix among these two local feeds. Formulate an LP model for this product mix problem.