Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

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:

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:

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. What are the key formulas for this Excel spreadsheet implementation of the following formulation?

Carlton construction is supplying building materials for a new mall construction project in Kansas. Their contract calls for a total of 250,000 tons of material to be delivered over a three-week period. Carlton's supply depot has access to three modes of transportation: a trucking fleet, railway delivery, and air cargo transport. Their contract calls for 120,000 tons delivered by the end of week one, 80% of the total delivered by the end of week two, and the entire amount delivered by the end of week three. Contracts in place with the transportation companies call for at least 45% of the total delivered be delivered by trucking, at least 40% of the total delivered be delivered by railway, and up to 15% of the total delivered be delivered by air cargo. Unfortunately, competing demands limit the availability of each mode of transportation each of the three weeks to the following levels (all in thousands of tons): Formulate an LP model for this logistics problem.

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. 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:

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: The component grains have the following content characteristics: \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.

Which type of spreadsheet cell represents the objective function in an LP model?

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. 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:

An LP problem with a feasible region will have

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. What formulas are required for the following cells in the Excel spreadsheet implementation of the formulation? B7 D7 F7 H7

The following questions are based on this problem and accompanying Excel windows. A financial planner wants to design a portfolio of investments for a client. The client has $300,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 25% of the money in any one investment, at least one third should be invested in long-term bonds which mature in seven or more years, and no more than 25% of the total money should be invested in C or D 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. -Refer to Exhibit 3.4. What formula should be entered in cell B7 in the accompanying Excel spreadsheet to compute total dollars invested?

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

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. -Refer to Exhibit 3.5. What formula could be entered in cell F20 in the accompanying Excel spreadsheet to compute the Total Cost for all four quarters?

The "Objective Value of" option in the Risk Solver Platform (RSP) task pane may be used to

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

Based on: Robert A. Bosch, "Big Mac Attack: The Diet Problem revisited, Eating at McDonald's," OR/MS Today, August 1993, pp 30-31. Tina Simpson is a new fourth-grade teacher at Forest Ridge Elementary. The first teacher workshop for the upcoming school year is next Monday and by majority vote, McDonald's was selected as the food of choice. As the new person, Tina is tasked with developing the meal for the workshop. McDonald's has graciously offered to deliver whatever food Tina decides to order, along with a variety of condiments applicable to whatever is ordered. Rather than offer a menu choice, Tina has decided to simply order the same meal for each person in the workshop. To get started, Tina took a trip to McDonald's and obtained their published information on the nutritional content of their food. That data is summarized in the table below. Tina wants the meal to be nutritionally complete. The National Research Council publishes their Recommended Daily Allowances. In this publication, they contend that a diet (in this case the meal) should provide at least 100 percent of the U.S. RDA of numerous nutrients. The specific amount of the RDA depends on such factors as age, weight and gender. In addition, the council recommends daily sodium and cholesterol intakes be kept to at most 2.4 grams of sodium and 300 milligrams of cholesterol. Further, at most 30 percent of the calories consumed should come from fat, and at most 10 percent from saturated fat. Each gram of fat contains 9 calories. Based on the above information, Tina wants to design a least-cost meal that provides at least 100% of the U.S. RDA of vitamins A, C, B₁, B₂, niacin, calcium, and iron; supplies at least 55 grams of protein; contains at most 3 grams of sodium; and contains at most 30 percent of its calories from fat. Only those foods list in the table above are available for the meal. Formulate the LP model for Tina's problem. Develop a spreadsheet model of the problem and use Excel Solver to determine the least-cost meal that meets all the stated requirements. What is the recommended meal? Is this meal reasonable? If not, modify the model to obtain what you believe to be a reasonable meal that meets the stated requirements.

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. Enter the numbers in the appropriate cells of ranges B4:H6 in the Excel spreadsheet to solve this problem based on the following formulation.

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. -Refer to Exhibit 3.3. Which cells should be the constraint cells in this problem?

The "Analyze Without Solving" tool in Risk Solver Platform (RSP) is useful for

Robert Hope received a welcome surprise in this management science class; the instructor has decided to let each person define the percentage contribution to their grade for each of the graded instruments used in the class. These instruments were: homework, an individual project, a mid-term exam, and a final exam. Robert's grades on these instruments were 75, 94, 85, and 92, respectively. However, the instructor complicated Robert's task somewhat by adding the following stipulations: Formulate an LP model for Robert to maximize his numerical grade.