Finding the best combination of values using solver
Question: How do I use Excel built in solver to get the correct combination?
Objective: To purchase parts at lowest cost.
Constraints: Divvy up among 3 suppliers/out of 5 suppliers ; a total of 19 parts in some combination.
A solution could look like this:
Sup1–>P1,P4,P7,P19,P16Sup2–>P2,P5,P8,P15,P13,P17,P18Sup3–>P3,P6,P9,P10,P11,P12,P14
Input data as follows:
SupplierPart numberCostSup1P1$10.00Sup1P2$9.00Sup1P3$5.00Sup1P4$6.00Sup1P5$6.00Sup1P6$15.00Sup1P7$11.00Sup1P8$8.00Sup1P9$71.00Sup1P10$11.00Sup1P11$12.00Sup1P12$21.00Sup1P13$22.00Sup1P14$6.00Sup1P15$31.00Sup1P16$6.00Sup1P17$4.00Sup1P18$17.00Sup1P19$8.00Sup2P1$6.00Sup2P2$8.00Sup2P3$11.00Sup2P4$4.00Sup2P5$8.00Sup2P6$34.00Sup2P7$12.00Sup2P8$8.00Sup2P9$55.00Sup2P10$77.00Sup2P11$13.00Sup2P12$25.00Sup2P13$23.00Sup2P14$2.00Sup2P15$34.00Sup2P16$12.00Sup2P17$3.00Sup2P18$13.00Sup2P19$7.00Sup3P1$9.00Sup3P2$11.00Sup3P3$4.00Sup3P4$3.00Sup3P5$2.00Sup3P6$23.00Sup3P7$13.00Sup3P8$8.00Sup3P9$45.00Sup3P10$29.00Sup3P11$14.00Sup3P12$23.00Sup3P13$24.00Sup3P14$3.00Sup3P15$49.00Sup3P16$16.00Sup3P17$1.00Sup3P18$12.00Sup3P19$6.00Sup4P1$8.00Sup4P2$13.00Sup4P3$2.00Sup4P4$7.00Sup4P5$8.00Sup4P6$55.00Sup4P7$14.00Sup4P8$8.00Sup4P9$48.00Sup4P10$34.00Sup4P11$15.00Sup4P12$22.00Sup4P13$25.00Sup4P14$8.00Sup4P15$32.00Sup4P16$13.00Sup4P17$2.00Sup4P18$11.00Sup4P19$5.00Sup5P1$9.00Sup5P2$12.00Sup5P3$1.00Sup5P4$16.00Sup5P5$1.00Sup5P6$41.00Sup5P7$15.00Sup5P8$8.00Sup5P9$26.00Sup5P10$67.00Sup5P11$16.00Sup5P12$17.00Sup5P13$26.00Sup5P14$7.00Sup5P15$22.00Sup5P16$12.00Sup5P17$5.00Sup5P18$19.00Sup5P19$4.00
Question: How do I use Excel built in solver to get the correct combination?Objective: To purchase parts at lowest cost.Constraints: Divvy up among 3 suppliers/out of 5 suppliers ; a total of 19 parts in some combination.A solution could look like this:Sup1–>P1,P4,P7,P19,P16Sup2–>P2,P5,P8,P15,P13,P17,P18Sup3–>P3,P6,P9,P10,P11,P12,P14 Input data as follows:SupplierPart numberCostSup1P1$10.00Sup1P2$9.00Sup1P3$5.00Sup1P4$6.00Sup1P5$6.00Sup1P6$15.00Sup1P7$11.00Sup1P8$8.00Sup1P9$71.00Sup1P10$11.00Sup1P11$12.00Sup1P12$21.00Sup1P13$22.00Sup1P14$6.00Sup1P15$31.00Sup1P16$6.00Sup1P17$4.00Sup1P18$17.00Sup1P19$8.00Sup2P1$6.00Sup2P2$8.00Sup2P3$11.00Sup2P4$4.00Sup2P5$8.00Sup2P6$34.00Sup2P7$12.00Sup2P8$8.00Sup2P9$55.00Sup2P10$77.00Sup2P11$13.00Sup2P12$25.00Sup2P13$23.00Sup2P14$2.00Sup2P15$34.00Sup2P16$12.00Sup2P17$3.00Sup2P18$13.00Sup2P19$7.00Sup3P1$9.00Sup3P2$11.00Sup3P3$4.00Sup3P4$3.00Sup3P5$2.00Sup3P6$23.00Sup3P7$13.00Sup3P8$8.00Sup3P9$45.00Sup3P10$29.00Sup3P11$14.00Sup3P12$23.00Sup3P13$24.00Sup3P14$3.00Sup3P15$49.00Sup3P16$16.00Sup3P17$1.00Sup3P18$12.00Sup3P19$6.00Sup4P1$8.00Sup4P2$13.00Sup4P3$2.00Sup4P4$7.00Sup4P5$8.00Sup4P6$55.00Sup4P7$14.00Sup4P8$8.00Sup4P9$48.00Sup4P10$34.00Sup4P11$15.00Sup4P12$22.00Sup4P13$25.00Sup4P14$8.00Sup4P15$32.00Sup4P16$13.00Sup4P17$2.00Sup4P18$11.00Sup4P19$5.00Sup5P1$9.00Sup5P2$12.00Sup5P3$1.00Sup5P4$16.00Sup5P5$1.00Sup5P6$41.00Sup5P7$15.00Sup5P8$8.00Sup5P9$26.00Sup5P10$67.00Sup5P11$16.00Sup5P12$17.00Sup5P13$26.00Sup5P14$7.00Sup5P15$22.00Sup5P16$12.00Sup5P17$5.00Sup5P18$19.00Sup5P19$4.00 Read More