Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

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

A farmer is planning his spring planting. He has 20 acres on which he can plant a combination of Corn, Pumpkins and Beans. He wants to maximize his profit but there is a limited demand for each crop. Each crop also requires fertilizer and irrigation water which are in short supply. There are only 50 acre ft of irrigation available and only 8,000 pounds/acre of fertilizer available. The following table summarizes the data for the problem. Formulate the LP for this problem.

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. ​ ​ Formulate the LP for this 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 are the key formulas in cells I12 and B12 for this Excel spreadsheet implementation of the following formulation? ​

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. -Refer to Exhibit 3.3. What formula should be entered in cell D11 in the accompanying Excel spreadsheet to compute the total liters of alcohol supplied?

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. -Refer to Exhibit 3.2. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit?

The "Analyze Without Solving" tool in Analytic Solver Platform is useful for

Exhibit 3.4 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 D7 in the accompanying Excel spreadsheet to compute the total return?

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 left hand sides (LHS) formulas in an LP model?

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. Based on the following formulation, is City East efficient? If not, what input and output values should they aspire to in order to become efficient? ​

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

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

In the Analytic Solver Platform dialog box simple upper and lower bounds for decision variables are specified by

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

A heuristic solution is

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What cell references would you enter in the Analytic Solver Platform (ASP) task pane for the following? Objective Cell: Variables Cells: Constraints Cells: ​

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.

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

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. The LP model for the problem is -Refer to Exhibit 3.1. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit?