The help I need is just producing the excel output for the problem below (8-19) which I have already provided the answers - the problem needed to be solved is 8-19 (which references 8-18 so I have included images of both and solutions for both below). So what I need is the images from excel that I can place into the report to support the answers. Thank you
8-18. X1= number of medical patients
X2= number of surgical patients
Maximize revenue = $2,280X1 + $1,515X2
subject to
8X1 +5X2 32,850 (patient-days available = 365 days 90 new beds)
3.1X1 + 2.6X2 15,000 (lab tests)
1X1 +2X2 7,000 (x-rays)
X2 2,800 (operations/surgeries)
X1, X2 0
Problem 8-18 solved by computer results in the following solution (rounded):
X1= 2,791 medical patients
X2= 2,105 surgical patients
revenue = $9,551,659 per year
To convert X1 and X2 to number of medical versus surgical beds, find the total number of hospital days for each type of patient:
medical = (2,791 patients)(8 days/patient)
= 22,328 days
surgical = (2,105 patients)(5 days/patient)
= 10,525 days
total = 32,853 days (The rounding causes this to be slightly higher than the limit.)
This represents 68% medical days and 32% surgical days, which yields 61 medical beds and 29 surgical beds. (Note that an alternative approach would be to formulate with X1, X2 as number of beds.)
8-19.This problem, suggested by Professor C. Vertullo, is an excellent exercise in report writing. Here is a chance for students to present management science results in a management format. Basically, the following issues need to be addressed in any report:
(a)As seen in Problem 8-18, there should be 61 medical and 29 surgical beds, yielding $9,551,659 per year.
(b)There are no empty beds because the slack for constraint 1 has a value of 0.
(c)There are 876 (the slack for constraint 2) lab tests of unused capacity.
(d)The x-ray is used to its maximum (slack for constraint 3 is 0) and has a $65.45 dual price.The revenue would increase by this amount for each additional x-ray.
(e)The operating room still has 695 operations available (the slack for constraint 4).
S
334 CHAPTER 8 - LINEAR PROGRAMMING APPLICATIONS 3! 8-16 average 0f 1.5 tons of alfalfa and 2.2 tons of barlch The wheat yield is approximately 50 bushels per acre. (a) Formulate Margaret's production plan. (b) What should the crop plan be. and what [1'mt will it yield? (c) The Water Authority informs Margaret that for a special fee of $6,000 this year, her farm will qualify for an additional allotment of 600 acre- feet of water. How should she respond? ( Material blending problem) Amalgamated Prod- ucts has just received a contract to construct steel body frames for automobiles that are to be produced at a new Japanese auto factory in Tennessee. The Japanese auto manufacturer has strict quality 0011' trol standards for all of its component subcontrac- tors and has informed Amalgamated that each frame must have the following steel content: __* MINIMUM MAXIMUM MATERIAL PERCENTAGE PERCENTAGE Manganese 2. l 2.3 Silicon 4.3 4.6 Carbon 5.05 5.35 \" 35 8-17 3; 8-18 Data tor Problem 8.16 Amalgamated mixes batches of eight different available materials to produce 1 ton of steel for use in the body frames. The table on this page details these materials. Formulate and solve the LP problem, indicating how much of each of the eight materials should be blended into a l-ton load of steel so that Amalgm- ated meets its requirements while minimizing costs. Refer to Problem 8.16. Find the cause of the dif- culty and recommend how to adjust it. Then solve the problem again. (Hospital expansion problem) Mt. Sinai Hospital in New Orleans is a large, private, 600'bed facility, complete with laboratories, operating rooms, and x-ray equipment. In seeking to increase revenues. Mt. Sinai's administration has decided to build a 90-bed addition on a portion of adjacent land cur- rently used for sta' parking. The administrators feel that the labs. operating rooms, and x-ray department are not being fully utilized at present and do not anded to handle addirjm,al need to be ex? - The addition of 90 beds however: requireiihl't . w how many beds should be all\" did. 2:31;; staff for medical Palients an d '10:?!\" It, the surgical staff for surgical Paenm any,\" The hospital's accounting and medical: armlents have provrded malfolhwing sec,\" tion. The average hospital Stay for PM. is 8 days' and '1" average medical gift. generates $2,280 in revenues. The \"Vera 3 ts cal patient is in the hospital's days and \"mg\". $1,515 bill. The laboratory 15 capable of hand'i 15.000 tests per year more than It Was \"3 ' - hand- The average medical patient Tequrres 3'] l ill]; and the average surgtc .51 patient lakes 2.6 gait\PROBLEMS 335 (c) Are the laboratories being used to their capac- ity? Is it possible to perform more lab tests/year? stock, is also important. (A high value of beta indicates if so, how many more? Discuss the effect of ac- that the stock has a relatively high risk.) The expected quiring more lab space if needed. return and the beta for five stocks are as follows: (d) Is the x-ray facility being used to its maximum? Is it possible to do more x-rays/year? If so, how STOCK 1 2 3 4 5 many more? Discuss the effect of acquiring more x-ray facilities if needed. Expected return (%) 11.0 9.0 6.5 15.0 13.0 Beta 1.20 0.85 0.55 1.40 1.25 (e) Are the operating rooms being used to capacity? Is it possible to do more operations/year? If so, how many more? Discuss the effect of acquiring more Daniel would like to minimize the beta of the stock operating room facilities if needed. (Source: Pro- portfolio (calculated using a weighted average of fessor Chris Vertullo. Reprinted with permission.) the amounts put into the different stocks) while 8:8-20 In the Low Knock Oil Company blending problem maintaining an expected return of at least 11%. Since future conditions may change, Daniel has in Section 8.6. it was assumed that one barrel of decided that no more than 35% of the portfolio crude would result in one barrel of gasoline as the should be invested in any one stock. final product. In processing one barrel of crude, a typical gasoline yield is about 0.46 barrel, although (a) Formulate this as a linear program. (Hint: Define it could be higher or lower than this, depending on each variables as the proportion of the total the particular crude and processing used. However, investment that would be put in that stock. Include other products such as diesel, jet fuel, home fuel oil. a constraint that restricts the sum of these vari- and asphalt also come from that same barrel. Assum- ables to be 1.) ing that only 46% of the crude turns into gasoline, (b) Solve this problem. What are the expected return modify the Low Knock Oil Company linear pro- and beta for this portfolio? gramming example to account for this. Solve the 2:8-23 (Airline fuel problem) Coast-to-Coast Airlines is resulting LP problem using any computer software. investigating the possibility of reducing the cost of fuel 8-21 A paper mill produces rolls of paper that are 10 inches purchases by taking advantage of lower fuel costs in wide and 100 feet long. These rolls are used for creating certain cities. Since fuel purchases represent a sub- narrower rolls of paper that are used in cash registers, stantial portion of operating expenses for an airline, automatic teller machines (ATMs), and other devices. it is important that these costs be carefully monitored. The narrower widths (2.5, 3, and 3.5 inches) needed for However, fuel adds weight to an airplane, and, con- these devices are obtained by cutting the 10-inch rolls sequently, excess fuel raises the cost of getting from using prespecified cutting patterns. Cutting pattern #1 one city to another. In evaluating one particular flight will cut the 10-inch roll into four rolls that are 2.5 inches rotation, a plane begins in Atlanta, flies from Atlanta each. Cutting pattern #2 results in three rolls that are to Los Angeles, from Los Angeles to Houston, from each 3 inches wide (leaving 1 inch of waste on the end). Houston to New Orleans, and from New Orleans to Cutting pattern #3 results in one roll that is 3 inches Atlanta. When the plane arrives in Atlanta, the flight wide and two rolls that are 3.5 inches wide. Cutting pat- rotation is said to have been completed, and then it tern #4 results in one 2.5-inch roll, one 3-inch roll, and starts again. Thus, the fuel on board when the flight arrived in Atlanta must be taken into consideration one 3.5-inch roll (leaving 1 inch of waste). Cutting pat- tern #5 results in one roll that is 2.5 inches wide and two when the flight begins. Along each leg of this route, there is a minimum and a maximum amount of fuel rolls that are 3.5 inches wide (leaving 0.5 inch of waste that may be carried. This and additional information on the end). An order has been received for 2,000 of are provided in the table on the following page. the 2.5-inch rolls, 4,000 of the 3-inch rolls, and 5,000 of The regular fuel consumption is based on the the 3.5-inch rolls. How many rolls should be cut using plane carrying the minimum amount of fuel. If more each pattern if the company wants to minimize the total than this is carried, the amount of fuel consumed is number of 10-inch rolls used? How many rolls should higher. Specifically, for each 1,000 gallons of fuel be cut using each pattern if the company wants to mini- above the minimum, 5% (or 50 gallons per 1,000 gallons mize the total waste? of extra fuel) is lost due to excess fuel consump 8-22 (Portfolio selection problem) Daniel Grady is the tion. For example, if 25,000 gallons of fuel are on financial advisor for a number of professional athletes. board when the plane takes off from Atlanta, the fuel An analysis of the long-term goals for many of these consumed on this route will be 12 + 0.05 = 12.05 athletes has resulted in a recommendation to purchase thousand gallons. If 26,000 gallons are on board, the stocks with some of the income that they have set aside fuel consumed will be increased by 0.05 thousand, for investments. Five stocks have been identified as hav- for a total of 12.1 thousand gallons. ing very favorable expectations for future performance. Formulate this as an LP problem to minimize Although the expected return is important in these the cost. How many gallons should be purchased in investments, the risk, as measured by the beta of the each city? What is the total cost of this
