For all problems please use Excel to solve the problems and give solution in Excel format.
Problem 1: Betty Malloy, 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/gal
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 using the computer
Problem 2: 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.
Resoucre Requirements
Wood(1b) Upholstery(yd) Labor(hr)
Sofa 7 12 6
Table 5 ? 9
Chair 4 7 5
Total available resources 2,250 1,000 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 tyupe of furniture to make per week to maximize profit.
a. Formulate a linear programming model for this problem.
b. Solve the model using the computer.
Problem 3: The Southfork Feed Company makes a feed mix from four ingredients-oats, corn, soybeans, and a vitamine 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 recipe for the mix.
-At least 30% of the mix must be soybeans
-At least 20% of the mix must be the vitamin supplement
-The ratio 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 costs $0.50; a pound of corn, $1.20; a pound of soybeans, $0.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 this problem
b. Solve the model using the computer.
Problem 4: The athletic boosters club for Beaconville has planned a two-day fund-raisingdrive to purchase uniforms for all the loca 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 donation 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 ollege 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 boosters club wants to know how many different types of donor contacts to schedule during the drive to maximize total donations. Formulate and solve and integer programming mdel for this problem. What is the difference in the total maximum value of donations between the integer and noninteger rounded-down solutions to this problem?
Problem 5: 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 roound kitchen tables and ladder-back chairs during the next three 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, and a chair earns $120 in profit. Most people who buy a table also want four chairs to got 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 that will determine the number of tables and chairs the Jacobsons should make to maximize profit.
Problem 6: 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 a spedboat 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 two of which are 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.