Include Top

Case Study 3 – Six Sigma DMAIC Project Portfolio Selection

Back to Table of Contents

  1. Open the workbook DMAIC Project Selection - Optimization. DiscoverSim Input Distributions will simulate the variability in the cost savings and probability of success for each project. We will specify cost savings as a Triangular Distribution for each project in cells G14 to G22. The probability of success will be specified at cells K14 to K22 using the Bernoulli (Yes/No) Distribution.




  2. DiscoverSim Input Controls (Discrete) will be used to “select” the project (0,1) and specified in cells O14 to O22.




  3. Click on cell G14 to specify the “Cost Savings” Input Distribution for Project_A. Select DiscoverSim > Input Distribution:




  4. Select Triangular Distribution.
  5. Click the input name cell reference and specify cell F14 containing the input Name “Project_A_CS” (denoting “Project A Cost Savings”). After specifying a cell reference, the dropdown symbol changes from to .
  6. Click the Minimum parameter cell reference and specify cell C14 containing the minimum parameter value = 100 ($K).
  7. Click the Mode parameter cell reference and specify cell D14 containing the mode (most likely) parameter value = 125 ($K).
  8. Click the Maximum parameter cell reference and specify cell E14 containing the maximum parameter value = 150 ($K).
  9. Click Update Chart to view the Triangular Distribution as shown:




  10. Click OK. Hover the cursor on cell G14 to view the DiscoverSim graphical comment showing the distribution and parameter values:




  11. Click on cell G14. Click the DiscoverSim Copy Cell menu button (Do not use Excel’s Copy – it will not work!).
  12. Select cells G15:G22. Now click the DiscoverSim Paste Cell menu button (Do not use Excel’s Paste – it will not work!).
  13. Review the input comments in cells G15 to G22.
  14. Click on cell K14 to specify the “Probability of Success” Input Distribution for Project_A. Select DiscoverSim > Input Distribution:




  15. Select Discrete and Bernoulli Distribution. Bernoulli is also sometimes referred to as the “Yes/No” distribution.
  16. Click the input Name cell reference and specify cell J14 containing the input name “Project_A_Pr” (denoting “Project A Probability of Success”).
  17. Click the Event Probability parameter cell reference and specify cell I14 containing the probability parameter value = 0.9.
  18. Click Update Chart to view the Bernoulli distribution as shown:


  19. >

  20. Click OK. Hover the cursor on cell K14 to view the DiscoverSim graphical comment showing the distribution and parameter values:


  21. >

  22. Click on cell K14. Click the DiscoverSim Copy Cell menu button (Do not use Excel’s Copy – it will not work!).
  23. Select cells K15:K22. Now click the DiscoverSim Paste Cell menu button (Do not use Excel’s Paste – it will not work!).
  24. Review the input comments in cells K15 to K22.
  25. Now we will add Discrete Input Controls (also referred to as “Decision Variables”). These will be used to “select” the project (0 denotes project not selected, 1 denotes project selected). DiscoverSim will use stochastic global optimization to determine which projects to select and which not to select in order to maximize total cost savings. A constraint will also be added to ensure that the total number of resources is <= 20.
  26. Click on cell O14. Select Control:
  27. Click input Name cell reference and specify cell N14 containing the input control name “Project_A_Select”.
  28. Select Discrete (this input control setting will consider only integer values). Set the Max value to 1 as shown:




  29. Click OK. Hover the cursor on cell O14 to view the comment displaying the Input Control settings:




  30. Click on cell O14. Click the DiscoverSim Copy Cell menu button (Do not use Excel’s Copy – it will not work!).
  31. Select cells O15:O22. Click the DiscoverSim Paste Cell menu button (Do not use Excel’s Paste – it will not work!).
  32. Review the input control comments in cells O15 to O22.
  33. Now we will add the constraint. Click on cell O26. Select Constraint:
  34. Enter the Constraint Name as “Resource_Constraint”. Enter O24 in the “Left Hand Side” (LHS). Select <= . Enter 20 in the “Right Hand Side” (RHS).




  35. Note: Cell O24 contains the Excel formula: =SUMPRODUCT(O14:O22,M14:M22). This sums the product of resources*selected(0,1), in other words the sum of selected project resources.

  36. Click OK. Review the comment at cell O26.



  37. The cell display is “TRUE” since the initial value for O24 is zero which obviously satisfies the constraint.

  38. Now we will specify the output. Click on cell Q24. The cell contains the Excel formula for the sum of expected savings as the sum of: Cost Savings * Probability of Success (if project is selected) or 0 (if project is not selected).
  39. Select DiscoverSim > Output Response:




  40. Enter the output Name as “Total_Savings”. Enter the Lower Specification Limit (LSL) as 1000. The Weight and Output Goal settings are used only for multiple response optimization, so do not need to be modified in this example.




  41. Click OK.

  42. Hover the cursor on cell Q24 to view the DiscoverSim Output information.




  43. Now we are ready to perform the optimization to select projects. Select DiscoverSim > Run Optimization:




  44. Select “Maximize” for Optimization Goal and “Mean’ for Statistic. Select Seed Value and enter “123”, in order to replicate the optimization results given below. Change Replications to 1000 to reduce the optimization time. All other settings will be default as shown:



  45. Note: Maximize Mean may not produce the lowest possible standard deviation, so we will consider Maximize Ppl later.

    We will use the Hybrid optimization method which requires more time to compute, but is very powerful to solve complex optimization problems.

    See Appendix: Optimization Methods for more details.

  46. Click Run. This optimization will take approximately 5 to 6 minutes.
  47. The final optimal parameter values are given as:



  48. Projects E, F, H and I have been selected giving a predicted mean cost savings of 2004 ($K). Intuitively, these would be the projects one would select given that they have the highest expected values per resource. However, a simple “pick the winner” approach fails to take into account the variability and likelihood of meeting the minimum total cost saving requirement of 1000 $K.

  49. The constraint equation evaluated at this optimum has been satisfied (amount violated = 0):



  50. Project E requires 10 resources, F requires 2, H requires 5 and I requires 3 for a total of 20, satisfying our constraint that the total resources be less than or equal to 20.

    Tip: If the constraint condition shows an unacceptable violation amount, the constraint should be edited, increase penalty from the default of 100 and rerun the optimization.

  51. You are prompted to paste the optimal values into the spreadsheet:



  52. Click Yes. This sets the Input Control values to the optimum values as shown:



  53. Select DiscoverSim > Run Simulation:




  54. Select Seed Value and enter “123” as shown, in order to replicate the simulation results given below.



  55. Click Run.

  56. The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices:



  57. From the histogram and capability report we see that the expected mean total savings are good (2011 $K), but the standard deviation is very large (1080 $K). Looking at the Actual Performance report we see that there is an 18.9% chance of failing to meet the management requirement of 1000 $K.

  58. Now we will rerun the optimization to select projects, but this time, we will maximize the process capability index Ppl. (Ppl is used since we only have a lower specification limit). This will simultaneously maximize the mean and minimize the standard deviation:
  59. Ppl = (Mean – LSL)/(3*StDev).

    An equivalent alternative would be to minimize Calculated DPM.

    Note: A nonparametric alternative statistic would be %Ppl (percentile Ppl):

    %Ppl = (Median – LSL)/(Median - 0.135 Percentile).

    This is robust to the assumption of normality. Another robust statistic would be Actual DPM. With either of these we would recommend increasing the number of replications to 10,000 to improve the precision of the statistic, but this would require significantly more computation time for optimization.

  60. Reset the project selection by changing the Input Control values to 0:




  61. Select DiscoverSim > Run Optimization




  62. Select “Maximize” for Optimization Goal, “Weighted Sum” for Multiple Output Metric and “PpL” for Statistic. Select Seed Value and enter “123”, in order to replicate the optimization results given below. Set Replications to 1000 to reduce the optimization time. All other settings will be default as shown:


  63. Click Run. This optimization will take approximately 5 to 6 minutes.

  64. The final optimal parameter values are given as:




  65. Projects A, C, F, G, H, and I have been selected giving a predicted Ppl of 0.62.

    Note: Project E, previously selected to maximize the mean, was not selected when the goal was to maximize the process capability, Ppl.

    The constraint requirement that the total number of resources be <= 20 is satisfied with this selection.

  66. You are prompted to paste the optimal values into the spreadsheet:




  67. Click Yes. This sets the Input Control values to the optimum values as shown:




  68. Select DiscoverSim > Run Simulation:




  69. Select Seed Value and enter “123” as shown, in order to replicate the simulation results given below.



  70. Click Run.

  71. The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices:



  72. Note that the mean total savings have been reduced somewhat from 2011 to 1925 $K, however, the standard deviation has been reduced from 1080 to 484 $K and the chance of failing to meet the management requirement of 1000 $K has been reduced from 18.9% to 4.0% (actual performance).

    Considering the two selection scenarios, one can present the risks to management and make the project selection decision to go either way: take the risk and maximize the mean with potential larger savings, or choose the safer projects that result in less variation and significantly improve the chance of meeting a minimum savings of 1 million.

  73. Clearly, Project E is the key project to consider in this analysis. The impact of this project can be confirmed with a sensitivity analysis.
  74. Set all of the Input Control values to 1 as shown:




  75. Select DiscoverSim > Run Simulation:




  76. Click Report Options/Sensitivity Analysis. Check Sensitivity Regression Analysis, Sensitivity Charts - Correlation Coefficients and Regression Coefficients. Select Seed Value and enter “123” as shown, in order to replicate the simulation results given below.


  77. Click Run

  78. Click on the Sensitivity Correlations sheet:




  79. Click on the Sensitivity Regression sheet to view the R-Square percent contribution to variation:




  80. The sensitivity charts confirm that Project E is the dominant “X” factor. This is a high impact project, but is also a difficult project with a probability of success of only 0.5 and a large variation (min = 2000, max = 2500). If possible, this project might be split into smaller, lower risk (higher probability of success) projects with less variation.

Back to Table of Contents

Click here to download a Free 30-Day Trial.

Define, Measure, Analyse, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,
Realize

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Ph: 1.888.SigmaXL ( 1.888.744.6295 )

Support: Support@SigmaXL.com

Sales: Sales@SigmaXL.com

Information: Information@SigmaXL.com