In this video of #engineeringmanagementacademy #ParetoFront is used for #RiskManagement by #DrMehrdadArashpour
🔔 Subscribe: [ Ссылка ]
❎ Excel workbook to follow along: [ Ссылка ]
🆕 New Excel Tutorials: [ Ссылка ]
⌛ TIMESTAMPS
0:00 - Introduction to the Pareto Front Optimization in Excel
0:05 - Excel’s Dynamic Template, Identifying Pareto-Optimal Solutions in Excel, Plotting the Pareto Front, & Finding the Best Solution based on Minimum Distance to the Ideal Point
0:21 - Step 1 (Identifying Pareto-Optimal Solutions in Excel)
1:33 - Step 2 (Plotting the Pareto-Optimal Line)
2:19 - Step 3 (Finding the Best Solution based on Minimum Distance to the Ideal Point)
3:00 - Concluding Remarks

✍ Request Next Tutorial Video: [ Ссылка ]

You’ll be surprised how quickly Excel can find Pareto Front in Project Risk Management. In this video, We’ll create & share a Dynamic Template in Excel with 3 easy steps: Identifying Pareto-Optimal Solutions, Plotting the Pareto Front in Excel, & Finding the Best Solution Based on Minimum Distance to the Ideal Point. You can follow along by getting the Excel workbook via the provided link in the description below. [ Ссылка ]

Step 1 is Identifying Pareto-Optimal Solutions in Excel
Our case study focuses on a project where you are evaluating 25 project risk mitigation strategies. Each strategy has two main objectives: Higher Risk Reduction & Lower implementation cost. In this multi-objective optimization, Our aim is to find the best trade-offs between risk minimization & cost of strategy. First, we must select the data from Risk Reduction & Cost columns. Then Go to the Insert tab & from the Charts section, choose Scatter Plot. We can customize the style to suit our project reports.
Our diagram provides the first impression of what strategies are Pareto optimal when hovering the mouse pointer over boundary solutions that are superior to the rest of the solutions in our search space.
For a quantitative investigation, we can duplicate our table & Sort data by Risk Reduction percentage, which is our first objective. Select the Risk Reduction range & choose Sort from Largest to Smallest & expand the selection. Begin at the top of the list & look for the lowest cost for each risk reduction level. For example, S19 & S20 on top of the list are Pareto-optimal but S5 is not. Because the cost is higher than S20 but risk reduction % is lower for S5. We can continue finding Pareto frontiers using this method.

Step 2 is Plotting the Pareto Front
In multiobjective optimization, Pareto frontiers are the set of efficient solutions where no objective can be improved without worsening others. To plot the Pareto front, In a separate part of your worksheet, create a new table containing only the Pareto-optimal strategies. Right-click on the scatter plot & choose ‘Select Data’. In the dialog box, Click Add & For Series X values, select the column with Risk Reduction %. For Series Y values, select the column with Cost & Click OK to add this new data series to our scatter plot.
Then, Right-click on the newly added data series & Choose ‘Format Data Series’. Under Line options, select Solid line & check the box for Smoothed Line. You can adjust the color & style to make the Pareto-optimal graph suitable to your risk management report.

Step 3 is Finding the Best Solution based on Minimum Distance to the Ideal Point
The ideal point is a hypothetical solution where each objective achieves its best possible value. In our example, this is the bottom-right corner of the solution space. The Euclidean distance from any solution on the Pareto front to the ideal point must be measured. An easy solution is to draw a line from the ideal point to each solution. Then calculate the distance as the square root of height squared plus width squared. distance=√(〖height〗^2+〖width〗^2 ) . You can find the height & width of the line from the Drawing Tools menu.
The 13th Strategy or S13 has the minimum distance to the ideal point, which is square root of 2.4^2 plus 2.9^2. The 2nd & 3rd best strategies are S6 & S16.
By minimizing this distance, risk analysts can identify mitigation solutions that are closest to the ideal. These solutions balance tradeoffs between conflicting objectives of risk reduction & implementation cost. The distance provides a clear, quantitative measure of how far each solution on the Pareto front is from the ideal point or "best case". This simplifies the decision-making process by ranking Pareto-optimal solutions based on their proximity to the ideal point & narrows down options.
And here it is, an easy & short implementation of Pareto Optimization using only 3 simple steps in Microsoft Excel.

If you found this video useful, please remember to like & consider subscribing. I hope to have your comments & see you in the next one…

свернуть