EXCEL Evolutionary Method (2 Minutes!) 🧬 Supplier Selection & Project Procurement Management- Solver
In this video of #engineeringmanagementacademy #EvolutionaryMethod and #geneticalgorithm are tutored for #ProjectProcurement by #DrMehrdadArashpour
🔔 Subscribe: https://bit.ly/EngineeringManagementA...
❎ Excel workbook to follow along: https://bit.ly/Evolutionary_Method_Su...
🎞 Other Excel Tutorials: https://bit.ly/PM_Excel_Academy
⌛ TIMESTAMPS
0:00 - Introduction to the Evolutionary Method in Excel
0:08 - Excel’s Dynamic Template, Formulating the Objective Function & Constraints, Setting up Solver Parameters, & Configuring Options of the Evolutionary Method
0:22 - Step 1 (Formulating the Objective Function & Constraints)
1:03 - Step 2 (Setting up the Excel Solver)
2:00 - Step 3 (Configuring Options of the Evolutionary Engine)
2:22 - Concluding Remarks
✍ Request Next Tutorial Video: https://forms.gle/Frz9U9imCouofdoD8
Did you know how powerful the evolutionary engine is in Excel’s Solver? In this video, an example of optimizing project procurement & Supplier selection is solved! We’ll create & share a dynamic Template in Excel with 3 easy steps: Formulating the Objective Function & Constraints, Setting up the Excel Solver, & Configuring Options of the Evolutionary Engine. You can follow along by getting the Excel workbook via the provided link in the description below. https://bit.ly/Evolutionary_Method_Su...
Step 1 is Formulating the Objective Function & Constraints
Our case study focuses on a company's need to procure raw materials from different suppliers to minimize costs, while meeting certain constraints. With a cost of $500/unit, Supplier A can supply a maximum of 100 units. Supplier B offers a lower price of $450/unit but can provide 80 units only. With a cost of $550/unit, Supplier C can supply a maximum of 120 units. Decision variables will be the number of ordered units & We can enter zeros as placeholders for now. The company needs to purchase a total of 200 units, which will be the SUM of ordered units. Our objective is to minimize total cost, which is the SUMPRODUCT of cost per unit & number of ordered units.
Step 2 is Setting up the Excel Solver
To solve the supplier selection problem, we need to activate & use Excel’s Solver. This can be done via ‘File’ Tab→Options→Excel Add-ins→Solver Add-in.
After activation, Click on ‘Data Tab’ & select ‘Solver’ from the list. ‘Set Objective’ refers to ‘Total Cost’ or the SUMPRODUCT function we defined in Step 1. Minimization is our desired task here. The 2nd dialog box of ‘by changing variable cells’ refers to ‘Number of ordered units’. Our project procurement is subject to 4 constraints: Maximum Supply capacity for supplier A is 100, for supplier B is 80 & for supplier C is 120. The final constraint refers to our total order, which is 200 units. We should select ‘Evolutionary’ from the drop-down menu as our problem-solving method. Now, we should press the solve button! Depending on complexity, solving the problem can take some time. We can achieve the minimum cost of $97,000 by ordering 100 units from Supplier A, 80 units from Supplier B & 20 units from Supplier C.
Step 3 is Configuring Options of the Evolutionary Engine
You can potentially improve the solution by adjusting the default OPTIONS of this heuristic approach based on genetic algorithms. Tuning the parameters such as convergence, mutation rate, population size & random seed is possible. One helpful tip is to increase mutation rate for the algorithm to escape local optima & find the global optimum within the solution space. You can visualize the results by any Excel Chart!
And here it is, an easy & short implementation of the evolutionary method using only 3 simple steps in Microsoft Excel.
If you found this video useful, please consider subscribing. I hope to have your comments & see you in the next one.
