In: Advanced Math
Problem 4-11 (Algorithmic)
Edwards Manufacturing Company purchases two component parts from three different suppliers. The suppliers have limited capacity, and no one supplier can meet all the company’s needs. In addition, the suppliers charge different prices for the components. Component price data (in price per unit) are as follows:
Supplier | |||
---|---|---|---|
Component | 1 | 2 | 3 |
1 | $12 | $13 | $10 |
2 | $15 | $12 | $10 |
Each supplier has a limited capacity in terms of the total number of components it can supply. However, as long as Edwards provides sufficient advance orders, each supplier can devote its capacity to component 1, component 2, or any combination of the two components, if the total number of units ordered is within its capacity. Supplier capacities are as follows:
Supplier | 1 | 2 | 3 |
---|---|---|---|
Capacity | 525 | 975 | 900 |
If the Edwards production plan for the next period includes 1025 units of component 1 and 825 units of component 2, what purchases do you recommend? That is, how many units of each component should be ordered from each supplier? Round your answers to the nearest whole number. If your answer is zero, enter "0".
Supplier | |||
---|---|---|---|
1 | 2 | 3 | |
Component 1 | |||
Component 2 |
What is the total purchase cost for the components? Round your answer to the nearest dollar.
$
Put the data in excel
Now put the constraint that we want o find out
Total is in $C20
Formula $C20 = SUMPRODUCT(B4:D5,B14:D15)
Now put the constraint in the solver mode of excel as shown below.(Solver mode can be found in DATA option of excel)
Enter solve and select report as answer and we will get
The total purchase cost for the components = $20,400