Question

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 $10 $14 $10 $12$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 Capacity 625 1000 800 f 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 Supplier Component 1 Component 2 What is the total purchase cost for the components? Round your answer to the nearest dollar.

Answer 1

	Component 1			Component 2
Supplier	1	2	3	1	2	3
Number of units	625	0	400	0	425	400

Total cost = $19350

The solution is derived using Solver function in Excel

	Component 1			Component 2
Supplier	1	2	3	1	2	3
Number of units	625	0	400	0	425	400
Price per unit	10	14	10	12	12	10
Minimiz
TOTAL COST	19350
CONSTRAINT
Total Supplier 1	625	<=	625
Total Supplier 2	425	<=	1000
Total Supplier 3	800	<=	800
Total component 1	1025	=	1025
Total component 2	825	=	825

Book1 Excel Sign in AutoSa Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share Calibn Paste +0 00 Conditional Format as Cell Insert Delete Format Sort & Find & Filter Select ormatting Table Styles Editing Clipboard Alignment Cells 81 Component 1 Component 2 Supplier Number of units 625 0 400 0 425 400 10 4. Price per unit 14 10 12 1210 Minimi SUMPRODUCT(C3:H3,C4:H4) TOTAL COST 10 CONSTRAINT Total Supplier 1 12 -C3+F3 <= 625 Total Supplier 2 D3+G3 Total Supplier 3 E3+H3 Total component 1 -SUM(C3:E3) 1000 13 14 <800 15 1025 16 Total component 2SUM(F3:H3) 825 17 Sheet1 + 100% A D ENG 3:46 PM

Book1 - Excel Solver Parameters Review View Help Tell me File Home Insert Draw Page Layout Formulas Data Set Objective: E From Text/CSV Recent Sources Queries &Connections Properties Existing ConnectionsS From Web Get From Table/Range Mip Refresh Sort | Filter AllEdit Links Ry Changing Variable Cells: Get&Transform Data Queries & Connections Sort & Filter C7 subject to the Constraints $12 <= $e$ 12 Component 1 Component 2 CS14 $I$14 Supplier Number of units 625 0 400 0 425 400 10 4. Price per unit 14 10 12 1210 Minimi -SUMPRODUCT(C3:H3,C4:H4) TOTAL COST MakeUncnnstrained variahles Non-Negative 10 Select a Solving Simplex P CONSTRAINT Total Supplier 1 625 1000 12 -C3+F3 Total Supplier 2 D3+G3 Total Supplier 3 E3+H3 Total component 1 -SUM(C3:E3) Solving Method 14 <800 Select the GRG Nonlinear engine for Soliver Problems that are smonth nonlinear. Select 1 engine for linear Solver Problems, and select the Evolutionary engine for Solver problem 15 1025 16 Total component 2SUM(F3:H3) 825 Sheet1 A D ENG 3:48 PM