Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

Project 3.1 -- The Diet Problem: Ordering Meals from McDonald's 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.

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

Which function is equivalent to =SUMPRODUCT(A1:A3,B1:B3)?

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model? ​ ​ ​

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model? ​ ​ ​

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? ​

Spreadsheet modeling is an acquired skill because

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

What action is required to make Analytic Solver Platform (ASP) solve a specified problem?

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.

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. -Refer to Exhibit 3.5. What formula should be entered in cell C18 in the accompanying Excel spreadsheet to compute the quarterly carrying costs?

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 Analytic Solver Platform (ASP) task pane for the following cells for this Excel spreadsheet implementation of the formulation for this problem? Objective Cell: Variables Cells: Constraints Cells: ​

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? ​ ​ ​

A solvable problem must have: ​

Objective cell, variable cells and constraint cells are terms used in Excel solver to describe the purpose of the cells. ​

What function is used to add the contents of cells A1, A2 and A3?

Solving LP problems in Excel requires only a copy and paste operation.

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. -Refer to Exhibit 3.5. Which cells are changing cells in the accompanying Excel spreadsheet?

How many constraints are there in a transportation problem which has 5 supply points and 4 demand points? (ignore the non-negativity constraints)

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