John Dill on Advanced Excel for Your Systems Engineering Bag of Tricks

Abstract: There are many hidden features of Excel which are ideal for the bag of tricks needed by today’s SE challenges.  The cost of analysis tools like MatLab, MathCAD, Excel add-ons are high and in many cases not available to most engineers.  There are pains in requesting an organization to invest in tools so this course is of particular interest because it makes use of a common everyday tool like Excel to solve difficult problems. In this tutorial the student will learn how to use Excel to solve some of our difficult problems as described below in the topical outline.

Topical Outline:

  1. Simultaneous Equations and how to use Matrices and Vectors to solve N equations with N unknowns.
    • Example problems deal with factory utilization, resource allocation, material       consumption, finding equilibrium between supply and demand.
  2. Operations Research Techniques – Linear Programming and Non-Linear Optimization to solve productivity, investment, and limitation problems.
    • Example problems: Facility Location, Equipment Utilization, material unitization to minimize cost., Which Funds to invest and how much to maximum returns,  Production yield to maximize profits.
  3. Statistics – Generating Descriptive Results, Hypothesis Testing, Decisions Making Techniques to solve forecasting and analysis problems
    • Example problem: Dealing with uncertainty, estimating cost using distributions       for material and labor.
  4. Linear Regression – Curve fitting using Polynomials, Logs, Exponents and Sin functions.
    • Example problem: From data tables form an equation f(x) to compute any value within the table
  5. Data Analysis using Pivot Tables and Data Base Functions.
    • Example problem: Creating reports from table data such as bill of materials, monthly expenses, and sales figures.

This session is a “hands-on” event where the student is able to directly use the techniques being demonstrated.  Several problems are introduced for each element where the students solve the example problems during the lecture while the instructor provides over the shoulder assistance where needed.  Bring your memory stick to capture your work products.

About the Presenter: John Dill has over 30 years of experience in technology development.  Currently he is working for Cubic Defense Applications providing embedded solutions on air and ground training systems for the war fighter. John has a Bachelor of Science in mathematics (summa com laude), is a member of the Palomar Amateur Radio Club, the Mathematical Association of America, and is currently serving as Vice President of Technical Development for the San Diego chapter of the International Committee on System Engineering (INCOSE).

Course ID: 058213

Cost: $80 (donuts, coffee, and lunch will be provided)

Location: Room 116, UCSD Extension – Sorrento Mesa Center, 6925 Lusk Blvd., San Diego CA 92121

Leave a Reply