help 6.38 with spreadsheet please !!!

minimized. The feed is used is e grains that contain the following us CHAPTER 6. LINEAR PROGRAMMING MODELS GRAPHICAL AND LOW pound of feed NUTRIENT ONCE POUND OF FEED A B C D 1 6- A plumbing manufacturer makes the lines of hath This madel A and model . Every tub requires Blending a certain amount of steel and in the company has available a total of 24,500 pounds of steel and 6.000 pounds of zinc. Each model A hath fub requires a mixture of 120 pounds of steel and 20 pounds of zinc, and each yields a profit of $90 Each model to produced can be sold for a profit osoit requires 100 pounds of steel and 30 pounds of nine. To maintain an adequate supply of both models, the manufacturer would like the number of model A tus made to be no more than 5 times the number of model B tubs. Find the best product mix FEED Feedmi X 3 2 Feed Y21 Feedmi 4 1 2 Food mixes X. Y. and Z. cost 53, 54, and $22. pound, respectively. The minimum requiremeni cattle per day is 4 pounds of nutrient A. S po of nutrient B. I pound of nutrient C, and pour of nutrient D. The ranch faces one additional for tion: it can only obtain so pounds of feed mix er day from the feed supplier regardless of its need Because there are usually 100 cattle at the feed ir at any given time, this means that no more than pounds of stock Z can be counted on for use in the food of each cattle per day. Formulate this problem as a linear program and solve it by using Excel. The production department for an aluminum valve plant is scheduling its work for next month. Each valve must go through three separate machines dur- ing the fabrication process. After fabrication, each valve is inspected by a human being, who spends 15 minutes per valve. There are 525 inspection hours available for the month. The time required in hours) by each machine to work on each valve is shown in the following table. Also shown are the minimum number of valves that must be produced for the month and the unit profit for each valve. of bathtubs. 6.35 A technical college department head must plan the course offerings for the next term. Student demands make it necessary to offer at least 20 core courses (each of which counts for 3 credit hours) and 20 elec- tive courses (each of which counts for 4 credit hours) in the term. Faculty contracts dictate that a total of at least 60 core and clective courses and at least 205 total credit hours be offered. Each core course taught costs the college an average of $2,600 in faculty sala- ries and each elective course costs $3,000. How many cach of core and elective courses should be scheduled so that total faculty salaries are kept to a minimum? 6-36 The size of the yield of olives in a vineyard is greatly influenced by a process of branch pruning. If olive trees are pruned, trees can be planted more densely. and output is increased. (However, olives from pruned trees are smaller in size.) Obtaining a barrel of olives in a pruned vineyard requires 5 hours of labor and 1 acre of land. Obtaining a barrel of olives by the normal process requires only 2 labor hours but takes 2 acres of land. A barrel of olives produced on pruned trees sell for $20, whereas a barrel of regular olives has a market price of $30. An olive grower has 250 hours of labor available and a total of 150 acres available to plant. He has determined that because of uncertain demand, no more than 40 barrels of pruned olives should be pro- duced. Find the combination of barrels of pruned and regular olives that will yield the maximum possible profit. Also, how many acres should the olive grower devote to each growing process? Note: Problems 6-37 to 6-43 are straightforward extensions of the two-variable problems we have seen so far and involve more than two variables. They therefore cannot be solved graphically. They are intended to give you an excellent opportunity to get familiar with formulating larger LP problems and solving them using Excel. 6-37 CHL, 6-38 CAPACITY HOURS) 700 PRODUCT Drilling Milling Lathe 890 V231 V242 7784 0.40 0.30 0.45 0.60 0.65 0.52 1.20 0.60 0.5 $16 S12 S13 200 250 600 V906 0.35 0.48 0.70 $8 450 1.200 Unit profit Minimum Cattle are sent to a feedlot to be grain-fed before being processed into beef. The owners of a feedlot seck to determine the amounts of cattle feed to buy so that minimum nutritional standards are satisfied to ensure proper weight gain, while total feed costs Determine the optimal production mix for the valve plant to make the best use of its profit potential. 6-39 The bank in Problem 6-31 now wants to add a mini size box, which will rent for $17 per year and con sume 43.2 square inches of wall space. The han still wants a total of at least 350 total boxes; at least 100 should be mini boxes and at should be large boxes. However, the be total area occupied by large and mini