Instructions

1. Place all of the following problems in one Excel file on separate sheets (one sheet for each problem).
2. Submit only your team's final model for each problem.
3. Make sure that the solver dialogue box contains all the information for your model.
4. Make sure to answer all questions that are asked.
5. On the first sheet, put the names of all group members as well as all explanations for all of the problems.
6. Name the file with at least one group member's name in the filename.
7. Please do not zip your files.

Problem 1

During the next four months, a customer requires, respectively, 500, 650, 1000, and 700 units of a commodity, and no backlogging is allowed (that is, the customer's requirements must be met on time). Production costs are $50, $80, $40, and $70 per unit during these months. The storage cost from one month to the next is $20 per unit (assessed on ending inven- tory). It is estimated that each unit on hand at the end of month 4 can be sold for $60. Assume there is no beginning inventory.

a.Determine how to minimize the net cost incurred in meeting the demands for the next four months.

Problem 2

LG owns five production plants where 90" high definition televisions are produced. LG can sell up to 25,000 televisions per year at the price of $4000 per television. For each plant, the production capacity, the production cost per television, and the fixed cost of operating a plant for year are given below.

Plant 1,2,3,4

Production Capacity 10000, 8000, 9000, 7000, 6000

Plant Fixed Cost $9 million, $5 million, $3 million, $4 million, $1 million

Cost per Television $1,600, $2,000, $2,300, $2,100, $2,400

a. Use Solver to determine how LG can maximize its yearly profit from television production.

b. Use SolverTable to determine how the optimal plant locations vary as the number of televisions that can be sold varies from 20,000 to 30,000 (in 1000 unit increments).

Problem 3

Harry & David's Co. buys oranges and processes them into gift fruit baskets and fresh juice. The company grades the fruit it buys on a scale from 1 (lowest quality) to 5 (highest quality). The following table summarizes the company's current inventory level of fruit.

Grade

1

2

3

4

5

Supply (1000's of lbs)

90

225

300

100

75

Each pound of oranges devoted to fruit baskets results in a marginal profit of $2.50, whereas each pound devoted to fresh juice results in a marginal profit of $1.75. The company wants the fruit in its baskets to have an average quality grade of at least 3.75 and its fresh juice to have an average quality grade of at least 2.50.

Use Solver to determinehow many oranges of each grade should be used in fruit baskets and fresh juiceto maximize Harry & David's profit.