Slide 1: Design Optimization With Excel Terrence L. Chambers, PhD., P.E.
Slide 2: Intro to Excel  Naming Cells  Entering Formulas  Comment Boxes
Slide 3: Intro to Optimization  Modeling and Analysis  Analysis vs. Design  Defining the Optimization Problem  Concepts of the Design Space  How do Optimization Algorithms Work?  Some Cautions About Optimization  Overall View of the Design Process
Slide 4: Design of Two-bar Truss Load, P Thickness, t Diameter, d Height, h Base, b
Slide 5: Analysis Model weight   2dt ( B / 2) 2  h 2  IB / 2) 2  h 2 stress  2tdh  2 E (d 2  t 2 ) bucklingstress  8[( B / 2) 2  h 2 ] P[( B / 2) 2  h 2 ]3 / 2 deflection  2tdh 2 E
Slide 6: Analysis vs. Design Analysis Variables In Analysis Model Analysis Functions Out
Slide 7: Optimization  Minimize Weight  By Changing: height and diameter  Such that:    Stress <= 100 ksi Stress <= Buckling Stress Deflection <= 0.25 in.
Slide 8: Design Space
Slide 9: Intro to Excel Solver  Solver Parameters Dialog Box  Solver Options Box  Solver Results Box  Two-bar Truss Tutorial
Slide 10: Solver Parameters Box
Slide 11: Solver Constraint Box
Slide 12: Solver Results Box
Slide 13: Solver Answer Report
Slide 14: Solver Options Box
Slide 15: Discrete Variables  Two-bar Truss Discrete Example
Slide 16: Linear Programming  Machining Problem Example
Slide 17: Intro to Excel VBA Programming  User-Defined Function Tutorial  Recording Macros Tutorial  Contour Plot Macro Example  Jacobian Program Example
Slide 18: Summary  Review of Basic Excel Skills  Intro to Optimization  Intro to Excel Solver  Solving Problems with Discrete Variables  Solving Linear Programming Problems  Intro to Excel VBA Programming