PLEASE SHOW SOLUTION ON EXCEL WITH FORMULAS

Paste BI U DA $ % ) O ab Cell Styles El Format G34 x v fx B C D D E F F H H I J K L . M N N 0 1500 30 Supplier 1 Supplier 2 Supplier3 Supplier 4 $ 10.00 $ 11.00 $ 12.00 $ 13.00 0.02 0.015 0.01 0.005 1000 1000 1000 1000 In this problem, we assume that a buyer is going to buy a single item from four suppliers to meet its demand that is 1500 units. The suppliers offer different wholesale prices that are $10, $11, $12 and $13, respectively. They also produce defective item and the percentage is 2% for the first supplier, 1.5% for the second supplier, 1% for the third supplier, and 0.5% for the forth supplier. Production capacity for all the suppliers is 1000 units. Assume that the buyer wants to minimize its purchasing cost. Use Solver to solve the problem for this case: acceptable number of defective items is less than 30 units. 1 Buyer's demand 2 Accepteble #of defective items 3 4 5 Price 6 Defective Percentage 7 Capacity 8 9 Order quantity from the supploers 9 10 11 12 13 14 15 16 17 18 19 20 24 21 22 22 23 24 25 26 27 28 29 30 31 32 In this problem, you want to: 1. minimize the purchasing cost 2. meet the demand 3. incorporate the suppliers' production capacity into your calculation 4. keep number of defective items less than 30 units