Linear Programming using excel solver

written by - Comments off

A store purchases two products (Product 1 and Product 2) that it stocks from three different vendors (Vendor A, Vendor B and Vendor C). The suppliers have limited capacity, and no one supplier can meet all of demand of Acme Mexico City. In addition, the vendors charge different prices for the products as shown in the table below:

Vendor’s Price
Product A B C
1 $12 $13 $14
2 $10 $11 $10

Each vendor has a limited capacity in terms of the total number of products it can supply. However, as long the store provides sufficiently advanced orders, each supplier can devote its capacity to product 1, product 2, or any combination of the two products, if the total number of units ordered is within its capacity. Vendor capacities are as follows.

Vendor A B C

Capacity 600 1000 800

The demand at the store is 1000 units of product 1 and 800 units of product 2 .

The purchasing manager wants to determine an optimal – ie, a lowest-cost – buying plan that would determine how many (>= 0) of each product should be bought from each vendor. To help the purchasing manager:

1) Develop an LP model for the above problem.

The following is a sample model showing a partial starting setup:

Product Unit Cost Quantity Product Cost Vendor Total Vendor Capacity
Vendor A 1 $12 $0 0 600
Vendor A 2 $10 $0
Vendor B 1 $13 $0 0 1,000
Vendor B 2 $11 $0
Vendor C 1 $14 $0 0 800
Vendor C 2 $10 $0
Product 1 Order Totals 0 Demand 1,000 Total Cost
Product 2 0 800 $0

2) Solve the developed LP model using Solver.

Note: Currently, Regular Priority times are 3-5 days. Log in to upload files with your questions. Tutorials you buy shall be emailed to your PAYPAL email. Talking about quality references: finding and referencing an (n+1)th article for your tutorial requires substantially more time than the (n)th article referenced, therefore you will see the price increasing with the number of references you require in some questions. Wanted to contact us over something related to this question? Email us: support AT

© 2010 Customized Homework help. - Powered by OM