Business Modeling with Spreadsheets
Problem Set 7
10 points each. Write out or type your answers.Be sure to define your decision variables clearly for each problem.“Formulate” means to write or type out the decision variable definitions, objective function, and constraints. Note: when asked to solve on a computer, use Excel (unless otherwise indicated) and please provide the computer printout of the output. In addition, please send your files togihan.mgtop470@gmail.com.
1. A craftsman named Barney Williams builds two kinds of birdhouses, one for wrens and a second for bluebirds. Each wren birdhouse takes 4 hours of labor and 4 units of lumber. Each bluebird house requires 2 hours of labor and 12 units of lumber. The craftsman has available 60 hours of labor and 120 units of lumber. Wren houses yield a profit of $6 each, and bluebird houses yield a profit of $15 each. Formulate and solve this problem.
2. Students who are trying to raise funds have an agreement with a local pizza chain. The chain has agreed to sell them pizzas at a discount, which the students can then resell to families in the local community for a profit. It is expected that of the 500 families in the community, at most 70% will buy pizza. Based on a survey of their personal preferences, the students believe that they should order no more than 120 cheese pizzas, no more than 150 pepperoni pizzas, and no more than 100 vegetarian pizzas. They also want to make sure that at least 20% of the total pizzas are cheese and at least 50% of the pizzas are pepperoni. They make a profit of $1.45, $1.75, and $1.98, respectively, for each cheese, pepperoni, and vegetarian pizza they resell. Formulate (but do not solve) an LP for this problem to determine the number of pizzas of each type that they should buy.
3. San Francisco’s famous Steph’s Restaurant is open 24 hours per day. Servers report for duty at 3 a.m., 7 a.m., 11 a.m., 3 p.m., 7 p.m., or 11 p.m., and each works an 8-hour shift. The following table shows the minimum number of workers needed during the 6 periods into which the day is divided:
PERIOD TIME NUMBER OF SERVERS REQUIRED
1 3 a.m.-7 a.m. 4
2 7 a.m.-11 a.m. 16
3 11 a.m.-3 p.m. 19
4 3 p.m.-7 p.m. 13
5 7 p.m.-11 p.m. 15
6 11 p.m.-3 a.m. 7
Owner Steph Curry’s scheduling problem is to determine how many servers should report for work at the start of each time period in order to minimize the total staff required for one day’s operation. Formulate (but do not solve) the appropriate LP.
4. Slick Oil Company has three warehouses from which it can ship products to any of three retail outlets. The demand in cans for the product Gunkout is 320 at retail outlet 1; 350 at outlet 2; and 255 at outlet 3. The inventory (capacity) of Gunkout at warehouse A is 270; at warehouse B is 375; and at warehouse C is 390. The cost of transporting one unit of Gunkout from each warehouse to each retail outlet follows.
RETAILER
WAREHOUSE 1 2 3
A 14 13 11
B 8 6 9
C 5 6 8
a. Formulate an LP to determine how many units should be shipped from each warehouse to each retailer so the demand at each retailer is met at minimum cost.
b. Solve this problem on a computer. (Note: feel free to use the 3 x 3 Transportation Model Template on the course website.)
5. Larry Bird’s Investments needs to develop an investment portfolio for Mrs. Jaimeson from the following list of possible investments. Exactly one of each type of investment is available, and no fractional investments are allowed.
Investment Cost Expected Return
A $6,000 $300
B $10,000 $1,200
C $5,500 $280
D $7,400 $400
E $2,600 $150
F $7,000 $390
G $7,200 $430
Mrs. Jaimeson has a total of $30,000 to invest. The following conditions must be met: (1) If investment F is chosen, then investment E must also be part of the portfolio, (2) investment A and investment G must either both be chosen or both not be chosen, and (3) of investments B and G, exactly one must be included.
Formulate this as a mathematical program to determine which stocks should be included in Mrs. Jaimeson’s portfolio to maximize expected return (write out the formulation), and solve it on a computer.
6. (5 points extra credit)
Use LINDO to solve Problem 5. Provide a printout of the LINDO formulation and the LINDO output.
======
