The BiHye Cycle Company has three plants that supply deluxe bicycles to three main countries of USA,
Question:
The BiHye Cycle Company has three plants that supply deluxe bicycles to three main countries of USA, Canada, and Germany. The plants' daily capacities, the countries' yearly requirements, as well as the per-unit supply costs ($) are provided in the table below.
Plant 1 | Plant 2 | Plant 3 | Demand (units) | |
USA | 4.5 | 5.1 | 5.8 | 20000 |
CANADA | 5 | 5.8 | 7.2 | 15000 |
GERMANY | 14 | 11.6 | 11.1 | 24000 |
Max Capacity(units) | 25000 | 15000 | 32000 |
The production cost of each bicycle is $80 in plant 1, $85 in plant 2, and $90 in plant 3. The selling price is $140 per unit in USA and Canada, and $160 in Germany.
a. Formulate a Linear Programming Model to find the optimal fulfillment plan, i.e. the quantity supplied to different countries by each plant in order to maximize the profit. [type formulation in this Excel sheet or put a screenshot of your handwriting.]
b. Provide a spreadsheet model and use Excel solver to solve it.
c. If demand in Canada goes up by 500 units, how much additional profit can be made?
Introduction to Operations Research
ISBN: 978-1259162985
10th edition
Authors: Frederick S. Hillier, Gerald J. Lieberman