Optimizing Project Costs ❓ EXCEL SOLVER with 4 Easy Steps - Example of Project Portfolio Management
In this video of #engineeringmanagementacademy #solver is tutored for #portfoliooptimization by #drmehrdadarashpour
🔔 Subscribe: https://bit.ly/EngineeringManagementA...
❎ Excel workbook to follow along: https://bit.ly/Excel_Solver
▶ RELATED VIDEO: • Goal Seek to Manage Project Costs 🎯 4...
⌛ TIMESTAMPS
0:00 - Introduction to Excel Solver & Operations Research in Excel
0:26 - Excel’s Dynamic Template, Solver Parameters, GRG Nonlinear, Simplex LP, Evolutionary Method, Genetic Algorithm
1:02 - Step 1 (Identify Main Variables (Multivariate) & Set Constraints)
2:34 - Step 2 (Load Solver Add-in & create Placeholders for Optimization Results)
4:16 - Step 3 (Select a Suitable Solving Method & Solve the Multivariate Problem)
5:50 - Step 4 (View & Validate Solver Results in Excel)
6:50 - Generate & Understand Solver Reports in Excel - Answer, Sensitivity Analysis, Limits
✍ Request Next Tutorial Video: https://forms.gle/Frz9U9imCouofdoD8
🌱 Other Excel Tutorials: https://bit.ly/PM_Excel_Academy
In a previous video, we showed how Excel’s Goal Seek can be used for project cost management. The link to that video can be found in the description below↓ ( • Goal Seek to Manage Project Costs 🎯 4... ). Now the question is: Can we use Excel to optimize costs for not one project but a complex portfolio? In this video, we’ll explore the answer. Excel Solver can be used for a range of Operations Research or, in other words, OR problems. Here, we work on a quantitative optimization example related to a group of 9 CLT construction projects. We’ll create & share a dynamic Template in Excel that sets the Objective Function, Main Variables, Constraints & solution methods.
With only 4 simple steps in Microsoft Excel, we can perform cost optimization for our project portfolio management based on the project management body of knowledge or PMBOK.
Step 1 is to Identify Main Variables (Multivariate) & Set Constraints
Suppose you are a project manager responsible for coordinating a portfolio of projects. As project IDs suggest, there are 9 construction projects to build structures out of cross-laminated timber or CLT. Total cost for each project is comprised of labor, material & overhead costs. Our first constraint is to cap total portfolio cost at $800,000. Second constraint is related to material costs & our client wants them to stay below $70,000. The 3rd constraint is imposed by our company, which demands overhead costs be more than $8,000 in each project. Final constraint is related to total cost of each project, which must be less than $143,000. As you can see, we are dealing with a complex multivariate problem.
Step 2 is to Load Solver Add-in & create Placeholders for Optimization Results
By default, Solver is not listed in Excel’s menus. To load Solver add-in onto Data tab in Excel, navigate to ‘File’ tab & click on ‘Options’.
Now, we can Click on Solver to open its dialog box. Under ‘set objective’, you can cell reference portfolio cost in our spreadsheet.
Step 3 is to Select a Suitable Solving Method & Solve the Multivariate Problem
Generalized Reduced Gradient or GRG Nonlinear is one of solving methods & is best suited for nonlinear problems. In its most basic form, GRG looks at gradient or slope of objective function while input values or decision variables change. It determines that it has reached an optimum solution when partial derivatives equal zero.
The 2nd method of Simplex LP is based on the theory of linear programming & is used to find the optimal solution to problems with linear constraints.
The 3rd solving method is the Evolutionary engine, which uses a Genetic Algorithm. This method starts with a random “population” of input values, which are plugged into the model & results are evaluated relative to the target value. Input values that result in a solution that’s closest to the target value are selected to create a second population of offspring. The offspring is indeed a “mutation” of the best input values from the first population. This process continues until there is very little change in the objective function from one population to the next. The evolutionary method is useful for nonlinear & is generally slow.
Step 4 is to View & Validate Solver Results in Excel
Double-check your formulas & constraints to make sure all are correctly set up. Check if the solution meets all constraints & whether the objective function is optimized. If initial results are not satisfactory, fine-tune your model by adjusting constraints or initial values of decision variables. Compare the results from Solver with other optimization tools, including MATLAB, Gurobi & IBM CPLEX Optimizer. Finally, Perform a sensitivity analysis to understand how changes in the inputs affect the outputs.
Sensitivity Report provides information on how the solution might change if the problem parameters were to change slightly. The Limits Report provides the upper and lower limits for decision variables within the problem constraints.
