Snacks and Beverages
Mr. George Monahan owns a chain of convenience stores that serve the East Bay area. He has a warehouse in Hayward where he stores all the products to replenish all his convenience stores. In this warehouse, Mr. Monahan has allocated 21,000 cubic feet to store snacks (including chips, cookies, and crackers) and beverages (including cola, juice and water). Mr. Monahan has good relationships with three distributors that supply him with these snacks and beverages. The following table summarizes the information about volume, cost associated with each distributor, and price on each of these products. The package of each product comes in the same size, regardless of the distributor. The selling price of each product is also the same regardless of the distributor.
|
Cost ($/unit)
|
Chips
|
Cookies
|
Crackers
|
Cola
|
Juice
|
Water
|
|
Distributor 1
|
$8
|
$6
|
$4
|
$10
|
$12
|
$8
|
|
Distributor 2
|
$7.75
|
$6.25
|
$4.25
|
$9.5
|
$12.5
|
$8.5
|
|
Distributor 3
|
$8.25
|
$5.75
|
$3.75
|
$10.5
|
$11.5
|
$7.5
|
|
Volume (cu ft/unit)
|
1
|
0.5
|
0.5
|
1.5
|
1.5
|
1.5
|
|
Price ($/unit)
|
$12
|
$10
|
$8
|
$14
|
$16
|
$12
|
To maintain his good relationship with each distributor, Mr. Monahan wants to make sure that the amount of snacks (in units) received from any distributor is between 20% and 50% of all snacks he orders and receives. Similarly, Mr. Monahan wants to ensure that the amount of beverages (in units) received from any distributor is between 20% and 50% of all beverages he orders and receives.
To ensure availability of each product and variety in each category (snacks and beverages), Mr. Monahan decides that each snack (in units) must account for at least 25%, but no more than 40% of all snacks. Similarly, each beverage (in units) must account for at least 25%, but no more than 40% of all beverages.
Notice that customers often buy snacks and beverages together. Mr. Monahan would like to make sure that the space occupied by snacks or beverages does not exceed 60% of the allocated space (21,000 cu ft).
- 1. Formulate an algebraic linear programming model for Mr. Monahan to determine how much of each product he should order from each distributor. Type your algebraic model in a Word file. The format of your linear programming model should be similar to that in the Captain Wise example (See the PPT file for details).
- 2. In an excel file, formulate your excel model based on your algebraic model and solve it with Solver. Please provide necessary comments and remarks to make your excel model readable and understandable. (See excel file for Captain Wise's Problem as an example)