# Linear and Integer programming problems using Excel solver

12. Betty Mallow, owner of the Eagle Tavern in Pittsburgh, is preparing for Super Bowl Sunday, and she must determine how much beer to stock. Betty stocks three brands of beer- yodel, shotz, and rainwater. The cost per gallon (to the tavern owner) of each brand is as follows:

Brand Cost/Gallon

Yodel $1.50

Shotz $0.90

Rainwater $0.50

The tavern has a budget of $2,000 for beer for Super Bowl Sunday. Betty sells Yodel at a rate of $3.00 per gallon, Shotz at $2.50 per gallon, and Rainwater at $1.75 per gallon. Based on past football games, Betty has determined the maximum customer demand to be 400 gallons of Yodel, 500 gallons of Shotz, and 300 gallons of Rainwater. The tavern has the capacity to stock 1,000 gallons of beer; Betty wants to stock up completely. Betty wants to determine the number of gallons of each brand of beer to order so as to maximize profit.

A. Formulate a linear programming model for this problem.

B. Solve the model by using the computer.

16. The Hickory Cabinet and Furniture Company produces sofas, tables, and chairs at its plant in Greensboro, North Carolina. The plant uses three main resources to make furniture-wood, upholstery, and labor. The resource requirements for each piece of furniture and the total resources available weekly are as follows:

Resource Requirements

Wood (lb) Upholstery (yd.) Labor (hr.)

Sofa 7 12 6

Table 5 _ 9

Chair 4 7 5

Total Available Resources 2250 1000 240

The furniture is produced on a weekly basis and stored in a warehouse until the end of the week, when it is shipped out. The warehouse has a total capacity of 650 pieces of furniture. Each sofa earns $400 in profit, each table, $275, and each chair, $190. The company wants to know how many pieces of each type of furniture to make per week to maximize the profit.

A. Formulate a linear programming model for this problem.

B. Solve the model by using the computer.

26. The southfork Feed Company makes a feed mix from four ingredients-- oats, corn, soybeans, and a vitamin supplement. The company has 300 pounds of oats, 400 pounds of corn, 200 pounds of soybeans, and 100 pounds of vitamin supplement available for the mix. The company has the following requirements for the mix:

- At least 30% of the mix must be soybeans.

- At least 20% of the mix must be the vitamin supplement

- The ration of corn to oats cannot exceed 2 to 1.

- The amount of oats cannot exceed the amount of soybeans.

- The mix must be at least 500 pounds.

A pound of oats cost $.50; a pound of corn, $1.20; a pound of soybeans, $.60; and a pound of vitamin supplement, $2.00. The feed company wants to know the number of pounds of each ingredient to put in the mix in order to minimize cost.

A. Formulate a linear programming model for the problem.

B. Solve the model using the computer.

16. The athletic boosters club for Beaconville has planned a 2-day fund-raising drive to purchase uniforms for al the local high schools and to improve facilities. Donations will be solicited during the day and night by telephone and personal contact. The boosters club has arranged for local college students to donate their time to solicit donations. The average donation from each type of contact and the time for a volunteer to solicit each type of donations are as follows:

Average donation ($) Average Interview Time (min.)

Phone Personal Phone Personal

Day 16 33 6 13

Night 17 37 7 19

The boosters club has gotten several businesses and car dealers to donate gasoline and cars for the college students to use to make a maximum of 575 personal contacts daily during the fund-raising drive. The college students will donate a total of 22 hours during the day and 43 hours at night during the drive.

The president of the booster club wants to know how many different types of donor contacts to schedule during the drive to maximize the total donations. Formulate and solve an integer program between the integer and non-integer rounded-down solutions to this problem?

24. Harry and Melissa Jacobson produce handcrafted furniture in a workshop on their farm. They have obtained a load of 600 board feet of birch from a neighbor and are planning to produce round kitchen tables and ladder-back chairs during the next 3 months. Each table will require 30 hours of labor, each chair will require 18 hours, and between them they have a total of 480 hours of labor available. A table requires 40 board feet of wood to make, and a chair requires 15 board feet. A table earns the couple $575 in profit. Most people who buy a table also want four chairs to go with it, so for every table that is produced, at least four chairs must also be made, although additional chairs can also be sold separately. Formulate and solve an integer programming model to determine the number of tables and chairs the Jacobsons should make to maximize profit.

26. The Skimmer Boat Company manufactures three kinds of molded fiberglass recreational boats- a bass fishing boat, a ski boat, and a speedboat. The profit for a bass boat is $20,500, the profit for a ski boat is $12,000, and the profit for a speedboat is $22,300. The company believes it will sell more bass boats than the other two boats combined but no more than twice as many. The ski boat is its standard production model, and bass boats and speedboats are modifications. The company has production capacity to manufacture 210 standard (ski-type) boats; however, a bass boat requires 1.3 times the standard production capacity, and speedboat requires 1.5 times the normal production capacity. In addition, only 160 of the high-powered engines that are installed in the bass boats and 2 of those installed in the speedboats are available. The company wants to know how many boats of each type to produce to maximize profit. Formulate and solve an integer programming model for this problem.

Â© BrainMass Inc. brainmass.com March 4, 2021, 7:40 pm ad1c9bdddfhttps://brainmass.com/statistics/quantative-analysis-of-data/linear-and-integer-programming-problems-using-excel-solver-115279

#### Solution Summary

This posting contains solution to following LPP and IPP problems using Excel solver.