Fundamentals of Forecasting Using Excel by myh14608

VIEWS: 28 PAGES: 20

Fundamentals of Forecasting Using Excel document sample

More Info
									ENGR 107: Engineering
   Fundamentals
          Lecture 7:
The Engineering Design Process:
Making Design Decisions Using
     Tradeoff Analyses


          C. Schaefer
      September 25, 2002
                            Stuff
 Reading assignment: section 2.9 in Eide, et
  al.
 Yacht design handouts
 Yacht diagram:
     – http://www.cnn.com/interactive/sailing/yacht.diagram/

 Lecture
 Meeting with test and evaluation group




September 25, 2002    Egnineering Fundamentals 107             2
            Engineering Decisions
 Engineering decision – selecting a specific
  solution to a design problem from a set of
  alternative solutions
 Trade-off analysis – an analysis technique in
  which the performance of various design
  alternatives are evaluated by varying different
  design parameters within a design space defined
  by parameter boundaries or design constraints.
 Payoff function – a mathematical model that
  relates system performance to various design
  parameters that the designer can control.


September 25, 2002   Egnineering Fundamentals 107   3
     Analyze Potential Solutions
 Narrow the ideas generated during the
  brainstorming phase to several realistic
  alternatives.
 Analytical techniques to narrow choice:
     – Estimation (“back of the envelope” calculations).
     – Common sense.
     – Economic analysis.
     – Engineering analysis.
     – Computer analysis.

September 25, 2002     Egnineering Fundamentals 107        4
           Using Models in the Decision
                 Making Process
 Mathematical models.                              Math models often
 Computer models.                                  implemented as
                                                    computer models.
     – CAD (computer aided design)
     – Simulink
   Scale models.
     – Wind tunnel models
     – Mockups
   Diagrams or graphs.
     – Free body diagrams

September 25, 2002   Egnineering Fundamentals 107                 5
                     Model Features
 Should represent the dynamics of the system being
  represented.
 Good fidelity: simple to understand and manipulate but
  good enough to generate believable results.
 Should address the most important design parameters.
  Ignore higher order terms.
 Should be repeatable.
 Should be of modest complexity: complexity often a
  function of time and budget constraints on design process.
 Easily modified.




September 25, 2002     Egnineering Fundamentals 107            6
                     Mathematical Models
   Uncovers relations between the various aspects of
    the problem that may not be apparent in verbal
    descriptions.
   Enables comparisons of many possible solutions.
   Illustrates cause-and-effect relationships.
   Defines the type of data required to solve the
    problem.
   Can be used for prediction and forecasting.
   Aids in identifying areas of risk and uncertainty.


September 25, 2002       Egnineering Fundamentals 107    7
Mathematical Model Example:
 Yacht Rating Measurement
         Formula


     L  SA   L SA
25          3       2.68
        4    12 30 D

September 25, 2002   Egnineering Fundamentals 107   8
                     Payoff Function:
Length = f (Sail Area, Displacement)



                L
                   110.72     SA                     
                            SA 
                        1       
                         33 30 D 
                                 
September 25, 2002      Egnineering Fundamentals 107       9
            Excel Spreadsheet Solution
                                                   Yacht Length (in inches)
                                                     Displacement (in pounds)
Sail Area (square inches)    4      4.5      5        5.5      6          6.5    7      7.5         8
             300            43.0   43.9     44.7     45.5     46.1       46.8   47.3   47.9        48.4
             350            40.6   41.5     42.3     43.0     43.7       44.3   44.9   45.4        45.9
             400            38.5   39.4     40.2     40.9     41.6       42.2   42.7   43.2        43.7
             450            36.7   37.6     38.4     39.1     39.7       40.3   40.8   41.3        41.8
             500            35.2   36.0     36.7     37.4     38.0       38.6   39.2   39.7        40.1
             550            33.7   34.5     35.3     35.9     36.6       37.1   37.7   38.1        38.6
             600            32.4   33.2     34.0     34.6     35.2       35.8   36.3   36.8        37.2
             650            31.3   32.0     32.8     33.4     34.0       34.5   35.0   35.5        36.0
             700            30.2   31.0     31.6     32.3     32.9       33.4   33.9   34.4        34.8
             750            29.2   30.0     30.6     31.2     31.8       32.3   32.8   33.3        33.7
             800            28.3   29.0     29.7     30.3     30.8       31.4   31.8   32.3        32.7
             850            27.4   28.1     28.8     29.4     29.9       30.4   30.9   31.4        31.8
             900            26.6   27.3     28.0     28.6     29.1       29.6   30.1   30.5        30.9




       September 25, 2002             Egnineering Fundamentals 107                            10
                                             Parametric Analysis
                                        Length vs. Sail Area (Displacement Constant)

                        50.0




                        45.0
Yacht Length (inches)




                                                                                                      4
                                                                                                      4.5
                        40.0                                                                          5
                                                                                                      5.5
                                                                                                      6
                                                                                                      6.5
                        35.0                                                                          7
                                                                                                      7.5
                                                                                                      8



                        30.0




                        25.0
                            200   300        400     500       600       700      800   900   1000

                                                    Sail Area (square inches)

          September 25, 2002                       Egnineering Fundamentals 107                      11
                                                    Parametric Analysis
                                                Length vs. Sail Area (Displacement Constant)

                        50.0


                                                                                              Invalid Design Space

                        45.0
Yacht Length (inches)




                                                                                     Length Constraint = 36 inches               4
                                  Invalid Design Space                                                                           4.5
                        40.0                                                                                                     5
                                                                                                                                 5.5
                                                                                                                                 6
                                                                                                                                 6.5
                        35.0                                                                                                     7
                                                                                                                                 7.5
                                                                                                                                 8
                                  Invalid Design Space

                        30.0



                                                                       Valid Design Space
                        25.0
                            200           300            400     500        600       700         800       900      1000

                                                                Sail Area (square inches)

       September 25, 2002                                      Egnineering Fundamentals 107                                 12
                     Parametric Analysis




September 25, 2002       Egnineering Fundamentals 107   13
                     Payoff Function:
Sail Area = f (Length, Displacement)

                                                       2
                               
                    110.72  L 
              SA              
                   1     L 
                    3 30 D 
                        3
                                
September 25, 2002      Egnineering Fundamentals 107       14
            Excel Spreadsheet Solution
                                              Sail Area (square inches)
                                                Displacement (in pounds)
Length (inches)      4       4.5      5          5.5       6         6.5     7       7.5          8
        25           1,014   1,065    1,112      1,155    1,196      1,235   1,271   1,305        1,338
        26             942     990    1,035      1,076    1,115      1,151   1,185   1,218        1,249
        27             877     922      964      1,003    1,039      1,074   1,106   1,137        1,167
        28             816     859      899        935       970     1,003   1,034   1,063        1,091
        29             761     801      838        873       906       937     966     994        1,020
        30             709     747      783        816       847       876     904     930          955
        31             662     698      731        762       792       819     846     870          894
        32             618     652      684        713       741       767     792     815          838
        33             578     610      639        667       693       718     742     764          785
        34             540     570      598        625       649       673     695     716          736
        35             505     534      560        585       608       631     652     672          691
        36             473     500      525        548       570       591     611     630          648




       September 25, 2002            Egnineering Fundamentals 107                            15
                                                 Parametric Analysis
                                         Sail Area vs. Yacht Length (Constant Displacement)

                            1,400


                            1,300


                            1,200
Sail Area (square inches)




                            1,100
                                                                                                        4
                                                                                                        4.5
                            1,000                                                                       5
                                                                                                        5.5
                             900                                                                        6
                                                                                                        6.5
                             800                                                                        7
                                                                                                        7.5
                                                                                                        8
                             700


                             600


                             500


                             400
                                    24      26        28          30           32     34      36

                                                           Yacht Length (inches)

               September 25, 2002                      Egnineering Fundamentals 107                16
                     Payoff Function:
Displacement = f (Sail Area, Length)

                                                       3
                      L SA          
                                    
           D  332.16  3( L  SA ) 
                         30

September 25, 2002      Egnineering Fundamentals 107       17
             Excel Spreadsheet Solution
                                                     Displacement (in pounds)
                                                             Length (in inches)
Sail Area (square inches)    28    29          30        31         32          33     34     35          36
             300             0.5   0.6         0.7       0.8        0.9         1.0   1.2    1.4          1.6
             350             0.7   0.8         0.9       1.1        1.2         1.4   1.6    1.9          2.1
             400             0.9   1.0         1.2       1.4        1.6         1.8   2.1    2.4          2.8
             450             1.1   1.3         1.5       1.8        2.0         2.3   2.7    3.1          3.6
             500             1.4   1.6         1.9       2.2        2.5         2.9   3.4    3.9          4.5
             550             1.7   2.0         2.3       2.7        3.1         3.6   4.1    4.8          5.5
             600             2.0   2.4         2.8       3.2        3.7         4.3   5.0    5.8          6.7
             650             2.4   2.8         3.3       3.8        4.4         5.2   6.0    6.9          8.0
             700             2.8   3.3         3.9       4.5        5.2         6.1   7.1    8.2          9.5
             750             3.3   3.9         4.5       5.3        6.1         7.1   8.3    9.6         11.2
             800             3.8   4.5         5.2       6.1        7.1         8.3   9.7    11.2        13.0
             850             4.4   5.1         6.0       7.0        8.2         9.6   11.2   13.0        15.1
             900             5.0   5.9         6.9       8.1        9.4       11.0    12.8   15.0        17.4




        September 25, 2002               Egnineering Fundamentals 107                               18
                                         Parametric Analysis
                                    Displacement vs. Sail Area (Length Constant)

                        18.0


                        16.0


                        14.0
Displacement (pounds)




                        12.0                                                                           28
                                                                                                       29
                                                                                                       30
                        10.0                                                                           31
                                                                                                       32
                         8.0                                                                           33
                                                                                                       34
                                                                                                       35
                         6.0                                                                           36


                         4.0


                         2.0


                         0.0
                            250   350      450         550         650          750   850   950

                                                 Sail Area (square inches)

                  September 25, 2002             Egnineering Fundamentals 107                     19
         How Important are the Criteria in the Decision
                          Process?
Working               Points Avail.       #1                    #2   #3
Criteria
Cost                  20                  10                    15   18
Prod. Difficulty      15                  8                     12   14
Size, Weight,         5                   5                     4    4
Strength
Appearance            10                  7                     6    8
Convenience           5                   3                     4    4
Safety                10                  8                     7    8
Legal                 5                   4                     4    4
Reliability           15                  7                     9    11
Recyclability         5                   4                     3    4
Appeal                10                  7                     8    9
TOTAL                 100                 63                    72   84

      September 25, 2002              Egnineering Fundamentals 107        20

								
To top