Docstoc

Introduction à la modélisation

Document Sample
Introduction à la modélisation Powered By Docstoc
					2-708-04 Information Technologies in Organizations

Theme 3 : Decision Modeling
Course 11 : Implementation of a dynamic model, irregular
events and scenarios analysis
Theme 3: Tools to help decision-making


 ▪ Course 9:
    ▪ Introduction to influence diagram (static and
      repeated model)
 ▪ Course 10:
    ▪ Three-Tier architecture
    ▪ Implementation of a decision model in Excel
    ▪ Introduction to influence diagram (Temporal
      model)
 ▪ Course 11:
    ▪ Implementation of a Temporal model in Excel
    ▪ Scenario analysis
                                      Information Technologies in Organizations (2-708-04)
                                                                        Course 11, page 2
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                       Course 11, page 3
The temporal model

▪ The temporal model is a special case of the
  repeating Sub Model.
▪ The model represents a problem for a given
  period (ex: one month, one year).
  ▪ The sub model (monthly) data are accumulated into
    the full model (yearly) data for each period.
  ▪ Inventory example:
     ▪ The number of units purchased during the month are added to
       the stock level at the beginning of the month and the number
       of units sold during the month are subtracted from the
       beginning stock level. This end of month inventory becomes
       the start of month inventory for the following month. We
       sum up the number of units purchased and sold for each
       period to obtain the yearly totals for sales and purchases.

                                          Information Technologies in Organizations (2-708-04)
                                                                            Course 11, page 4
 A temporal model and the concept of period

•       Value of the result variable from one period is an
        input variable for the next period.
Each variable used in a (t-1) relationship                                               Sales
must have an initial value.
This is a parameter that we will not indicate                   Inventory                          (t - 1)          Inventory
                                                                beginning                                             end of
in the ID but must introduce in the Excel                       of period.                                            period
implementation
                                                                                     Purchases
    Variable                   July      August      Sept       Oct
Inventory                     10.000 $    9.000 $   11.000 $   7.100 $        The (t-1) influence is read as
beginning of                                                                  follows: The Inventory at the
period
Purchases                      2.000 $    4.000 $    2.500 $   5.800 $        beginning of this period is
                                                                              equal to the Inventory at the
Sales                          3.000 $    2.000 $    6.400 $   7.200 $
                                                                              end of the previous period.
Inventory end of   10.000 $    9.000 $   11.000 $    7.100 $   5.700 $
previous period
                                                                      Information Technologies in Organizations (2-708-04)
                                                                                                        Course 11, page 5
 Another example of a temporal model
                  For Each Month
▪ Identify the
  temporal
                                     Number                                               Number
  relationship.                     sold during                                             sold
                                    the month                                              during
                                                                                          the year
▪ Identify the
  variables in the
  repeating sub-
                        Beginning                          Ending
  model.                inventory                        inventory
                                          (t - 1)



                                      Number                                              Number
                                     purchased                                           purchased
                                     during the                                            during
                                       month                                              the year



                                                    Information Technologies in Organizations (2-708-04)
                                                                                      Course 11, page 6
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                       Course 11, page 7
              Case : procurement model

▪ Alan is procurement director of a distribution company. The
company imports and stores costly parts that are then
redistributed to the buyers from the local market.


▪Alan knows the demand of his clients for the next 26 weeks.


▪Alan must hold in storage at the beginning of every week at
least 110% of the demand for that week.


▪If the quantity in storage is not sufficient to respect this rule,
he must submit a purchase order to his supplier.
                                            Information Technologies in Organizations (2-708-04)
                                                                              Course 11, page 8
             Case : procurement model

▪ Alan is looking to minimise his total cost, which includes the
  storage cost and the total delivery costs.
▪ The storage cost (space and insurance) depends on the
  maximum quantity of stock storage during the 26 weeks.
  (See table)
▪ The delivery cost (transportation, administrative costs) is a
  fixed cost (1150$) for each order.
▪ When the firm orders, it orders so as to satisfy the current
  week demand and to fill its storage capacity.
▪ Alan must decide what will be his maximum storage capacity

                                          Information Technologies in Organizations (2-708-04)
                                                                            Course 11, page 9
            Parameters : procurement model

 •The initial level of stock is 4000
 •Delivery cost is 1150$               Week        Demand      Week           Demand
 •The amount of stock at the                   1      2112              14           2713
 beginning of a week must be at
 least 110% of the current week                2      2176              15           2727
 demand                                        3      2141              16           2755

     Table Cost/Capacity                       4      2253              17           2773

The_Capacity     The_StorCost                  5      2298              18           2879

   4000               5670                     6      2350              19           2873

   6000               8340                     7      2363              20           2922

   10000             12000                     8      2453              21           2986

   20000             15800                     9      2476              22           3012

   40000             23000                    10      2465              23           3062
                                              11      2500              24           3166
                                              12                   25             3169
                                                      2585 Technologies in Organizations (2-708-04)
                                                       Information
                                                                                   Course 11, page 10
                                              13      2656              26           3227
Developing the influence diagram


 ▪ Put the decision variables on the left and the
   results variables on the right :



 Capacity
                         ?                                          TotCost




     The ? represents the model that we will develop.



                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 11
TotCost=StorCost+TotCostDeliv




                                StorCost




                                                    TotCost




                                TotCostDeliv




                                Information Technologies in Organizations (2-708-04)
                                                                Course 11, page 12
StorCost=Lookup(Capacity;The_capacity;The_StorCost)

        Capacity



                                                        Tab
                                                   Cost/Capacity




                                  StorCost




                                                       TotCost




                                  TotCostDeliv




                                   Information Technologies in Organizations (2-708-04)
                                                                   Course 11, page 13
TotCostDeliv=Sum(DelivCostWeek)

           Capacity



                                                                Tab
                                                           Cost/Capacity




                                       StorCost




                         DelivCost
                                     Sum                       TotCost
                          Week




                                       TotCostDeliv




         26 Weeks


                                           Information Technologies in Organizations (2-708-04)
                                                                           Course 11, page 14
DelivCostWeek=If(Deliv?=1;DelivCost;0)

     Capacity



                                                                Tab
                         DelivCost                         Cost/Capacity




                                       StorCost




                         DelivCost
                                     Sum                       TotCost
                          Week

                Deliv?



                                       TotCostDeliv




   26 Weeks


                                           Information Technologies in Organizations (2-708-04)
                                                                           Course 11, page 15
Deliv?= 1, if StockBegin<Stockrequir+Demand

            Capacity



                                                                                    Tab
                                             DelivCost                         Cost/Capacity




                       StockBegin                          StorCost




                                             DelivCost
                                                         Sum                       TotCost
                                              Week

                       Deliv?



                                                           TotCostDeliv
                                    StockRequir
                                        ed
           Demand




          26 Weeks


                                                               Information Technologies in Organizations (2-708-04)
                                                                                               Course 11, page 16
StockRequir=Demand(T+1)*ParaStock

            Capacity



                                                                                    Tab
                                             DelivCost                         Cost/Capacity




                       StockBegin                          StorCost




                                             DelivCost
                                                         Sum                       TotCost
                                              Week

                       Deliv?



                                                           TotCostDeliv
                                    StockRequir
                        T+1             ed
           Demand




                                                           ParaStock
          26 Weeks


                                                               Information Technologies in Organizations (2-708-04)
                                                                                               Course 11, page 17
If Deliv?= 1, StockEnd=Capacity

                 Capacity



                                                                                         Tab
                                                  DelivCost                         Cost/Capacity




                            StockBegin                          StorCost



    StockEnd



                                                  DelivCost
                                                              Sum                       TotCost
                                                   Week

                            Deliv?



                                                                TotCostDeliv
                                         StockRequir
                             T+1             ed
                Demand




                                                                ParaStock
               26 Weeks


                                                                    Information Technologies in Organizations (2-708-04)
                                                                                                    Course 11, page 18
If Deliv?= 0, StockEnd=StockBegin-Demand

                    Capacity



                                                                                            Tab
                                                     DelivCost                         Cost/Capacity




                               StockBegin                          StorCost



       StockEnd



                                                     DelivCost
                                                                 Sum                       TotCost
                                                      Week

                               Deliv?



                                                                   TotCostDeliv
                                            StockRequir
                                T+1             ed
                   Demand




                                                                   ParaStock
                  26 Weeks


                                                                       Information Technologies in Organizations (2-708-04)
                                                                                                       Course 11, page 19
StockBegin=StockEnd(T-1)

              Capacity



                                                                                      Tab
                                               DelivCost                         Cost/Capacity




                                                             StorCost
            T-1          StockBegin



 StockEnd



                                               DelivCost
                                                           Sum                       TotCost
                                                Week

                         Deliv?



                                                             TotCostDeliv
                                      StockRequir
                          T+1             ed
             Demand




                                                             ParaStock
            26 Weeks


                                                                 Information Technologies in Organizations (2-708-04)
                                                                                                 Course 11, page 20
Final Influence Diagram

             Capacity



                                                                                     Tab
                                              DelivCost                         Cost/Capacity




                                                            StorCost
           T-1          StockBegin



StockEnd



                                              DelivCost
                                                          Sum                       TotCost
                                               Week

                        Deliv?



                                                            TotCostDeliv
                                     StockRequir
                         T+1             ed
            Demand




                                                            ParaStock
           26 Weeks


                                                                Information Technologies in Organizations (2-708-04)
                                                                                                Course 11, page 21
Is the Influence Diagram complete and coherent?


   ▪   The model is complete because it respects
       the following conditions:
       – The variables with no input arrows are decision
         variables or parameters.
       – The variables with no output arrows are result
         variables.
       – All variables with at least one incoming arrow are
         described by a mathematical formula.
       – After removing the temporal relationships, there
         is no recursiveness in the diagram.


                                        Information Technologies in Organizations (2-708-04)
                                                                        Course 11, page 22
 Procurement Model – Formulas

Variable        Description                            Type / Value / Formula
Capacity        Warehouse capacity                     Decision variable
Demand          Weekly demand                          See the table on the following page
Table           Table of warehouse costs as a          Columns The_Capacity and The_StorCost
Cost/Capacity   function of the warehouse capacity     (see following page)
ParaStock       Level of stock desired                 110%
DelivCost       Delivery costs                         1150$
StockEnd        Stock at the end of the period         If(Deliv?; Capacity; StockBegin – Demand)
StockBegin      Stock at the beginning of the period   StockEnd(t-1)
StockRequired   Stock required at the end of the       Demand(t+1) * ParaStock
                period
Deliv?          Delivery indicator                     If(StockBegin<StockRequired+Demand; 1; 0)
DelivCostWeek   Weekly delivery cost                   DelivCost * Deliv?
TotCostDeliv    Total delivery costs                   Sum(DelivCostWeek)
StorCost        Warehouse costs                        Lookup(Capacity; The_Capacity;
                                                       The_StorCost)
                                                               Information Technologies in Organizations (2-708-04)
TotCost         Total costs                            TotCostDeliv + StorCost                 Course 11, page 23
Procurement Model – Tables

Week        Demand    Week        Demand
        1      2112          14       2713        Table Cost/Capacity

        2      2176          15       2727   The_Capacity            The_StorCost

        3      2141          16       2755       4000                        5670

        4      2253          17       2773       6000                        8340

        5      2298          18       2879      10000                       12000

        6      2350          19       2873      20000                       15800

        7      2363          20       2922      40000                       23000

        8      2453          21       2986
        9      2476          22       3012
       10      2465          23       3062
       11      2500          24       3166
       12      2585          25       3169
       13      2656          26       3227
                                                Information Technologies in Organizations (2-708-04)
                                                                                Course 11, page 24
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 25
Implementation of a dynamic model


 ▪ The rules for implementing a dynamic model
   are meant to facilitate its initial
   implementation and its maintenance.
 ▪ A dynamic model has a particular structure :
   The structure of each period has to be
   identical to the others. If you think that you
   have to treat an exception by introducing
   different formulas in a period, then you are
   not using the following technique properly.
                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 26
Using the « cell name » feature


  ▪ Different from the static model, we don’t
    name the cells representing temporal
    variables or temporal parameters.
  ▪ But, we always name the static variables and
    the static parameters, and always name each
    column of a table.




                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 27
Implementation of periods
Temporal structure

 ▪ The structure of the Model tab containing the
   temporal model is as follows :
 ▪ Line 1 contains the number of the periods:
 ▪ Column A contains the name of each variable.
 ▪ Column B will be period 0, for the initialization of
   the model.
 ▪ Illustration: Model tab (0) of Procurement_steps.xls


                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 28
Implementation of periods
Temporal structure
 ▪ Columns C and the following columns will
   represent the periods of the model.
 ▪ The initial values, reported in the Parameters
   tab, will be transferred to the appropriate cell
   of the period 0.
    ▪ For example, the initial stocks will be transferred
      to the final stocks of period 0.
    ▪ The transfer allows us to write the appropriate
      formula in the initial stocks of period 1.
    ▪ Illustration: Model tab (1) of
      Procurement_steps.xls

                                       Information Technologies in Organizations (2-708-04)
                                                                       Course 11, page 29
Implementation of periods
Developing the model

 ▪ The entire temporal model will be developed in
   the column representing the period 1.
 ▪ Each temporal formula of your ID should be
   represented in a visually complete calculation:
    ▪ The variables used in the calculation should be
      represented first, using reference formulas.
       ▪ Calculate once, use often!
    ▪ The calculated variables are represented below the
      line. We do not name the temporal variables.
    ▪ Illustration: Model tab (2) of Procurement_steps.xls

                                      Information Technologies in Organizations (2-708-04)
                                                                      Course 11, page 30
Implementation of periods
Copying formulas

▪ Once all formulas are entered in period 1, all
  formulas from period 1 will be copied to the following
  columns.
   ▪ Select the formulas from the first cell under the period
     sub-titles (normally, cell C2)
   ▪ Copy the formulas (Ctrl-C)
   ▪ Select the destination (normally, C2:H2 for 6 periods)
   ▪ Paste the formulas (Ctrl-V)
▪ Illustration: Model tab (End) of
  Procurement_steps.xls

                                        Information Technologies in Organizations (2-708-04)
                                                                        Course 11, page 31
Implementation of periods
Static variables

 ▪ When your model includes static intermediate
   variables or static result variables, create a
   tab called « Static Model »
 ▪ Use column A for the names and column B
   for the formulas and the references.
 ▪ Illustration: Static Model tab (End) of
   Procurement_steps.xls



                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 32
Implementation of periods
Static variables

 ▪ Each formula of your ID that doesn’t use
   static variables and parameters or that
   crosses the temporal repeated block should
   be represented in a visually completed block:
    ▪ If needed, the static variables used in the
      calculations are represented first with a reference
      formula.
    ▪ The calculated variable is presented below the
      line.



                                      Information Technologies in Organizations (2-708-04)
                                                                      Course 11, page 33
Implementation of periods
Static variables

 ▪ Select the name of the calculated variable
   and the cell containing its formula (columns A
   and B) to give it a name :
   ▪ Insert / Name / Create / Column to the left
 ▪ Continue to create a block for each static
   formula of your model, separating each block
   by a blank line.



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 34
Implementation of periods
Result variables

  ▪ When your model will be completed, return to the tab
    « Decision and result ».
  ▪ In the cells to the right of the result variables names,
    complete the transfer of the corresponding result
    variables of your model, coming from the tab Model,
    or from the tab Static Model.
  ▪ Example:
     =Profit
  ▪ Test your model by modifying the values of your
    decision variables and by observing the
    corresponding results.

                                         Information Technologies in Organizations (2-708-04)
                                                                         Course 11, page 35
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 36
Modeling irregular events


  ▪ Sometimes, we’re in the situation where an
    event happens in only one period.
  ▪ At first, we might think that we could use the
    Excel function IF(), but we soon realise it is
    not easy to use without using constants and,
    therefore make the ID more complicated.
  ▪ Remember that an Influence Diagram should
    not change when the problem variable
    changes.

                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 37
Examples: Dynamic provisioning model


 ▪ In certain weeks, parts have to be delivered
   to Alain
    ▪ -> Create a variable Deliv?
 ▪ Between weeks 20 and 23 (inclusively), some
   work will be necessary and the capacity will
   be reduced by 70%
    ▪ -> introduce new temporal parameters
 ▪ The cost of delivery changes after the 16th
   week
    ▪ -> introduce new temporal parameters
                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 38
Modeling irregular events

 Techniques :
 1. Presence / Absence indicator
    (Use 1 or 0 to indicate the presence or absence of the event in this
    period.)

 2. Distribution of a global value
    (Use a percentage to distribute a global amount over some number of
    periods)

 3. Allocation of a global value
    (Indicate a fixed amount to be allocated to some number of periods)

 4. Indicator variable
    (A 1 or 0 variable computed according to model conditions)


                                                 Information Technologies in Organizations (2-708-04)
                                                                                 Course 11, page 39
Event indicator

An Indicator is a temporal                                             For Each Month
parameter to which we give
the value 0 for the periods
                                                  Indic
where the event does not                          Loan                             Revenues
occur and the value 1 where
it does occur.
                                Amount
                                 Loan
Along with the Indicator,                                       Loan
we need a static
parameter that
represents the value to       Finally, we create a temporal intermediate variable
use when the event            that calculates, for each period, the value used in
                              the rest of the model.
occurs.
                              Example: Loan = Indic_Loan * Amount_Loan

                                                      Information Technologies in Organizations (2-708-04)
                                                                                      Course 11, page 40
 Distribution of a global value

A Distribution is a temporal                                                 For Each Month
parameter that represents the
percentage of a global value to use
                                              Dist
for each period. The sum of these            Bursary                                 Revenues
percentages must equal 100%.



                               Amount
Along with the                 Bursary                   Bursary
Distribution, we need a
static parameter that
represents the value to        Finally, we create a temporal intermediate variable
distribute over the periods.   that calculates, for each period, the value used in the
                               rest of the model.
                               Example: Bursary = Dist_Bursary * Amount_Bursary
                                                        Information Technologies in Organizations (2-708-04)
                                                                                        Course 11, page 41
Allocation of a global value

The Allocation is a temporal                                             For Each Month
parameter to which we give the
portion of a global value to use in          Tuition
each period. You must make sure               Fees
                                                                                     Expenses
that the sum of these values is
equal to the global value.


The global Value is not stored
anywhere in the model so be
careful ! It is easy to make an
error here.

                               The allocation parameter is used directly in the
                               model.


                                                       Information Technologies in Organizations (2-708-04)
                                                                                       Course 11, page 42
 Condition test (calculate the event indicator)

When the period in which an event                      Order
occurs is unknown in advance we                         Qty
create an intermediate temporal                                                 For Each Month
variable that will act like an event
                                                             Inv
indicator.
                                                             End
The value of this indicator is                                                              Ordered
the result of a test that shows                                                               Qty
whether the event occurs               Reorder
during this period.                     Point                         Reorder
Example: ReorderIndic =                                                Indic
If(InvEnd <= ReorderPoint; 1;
0).                                    Finally, we use this indicator with the parameter
                                       that represents the value to use when the event
The colored elements share             occurs.
the same logic as an event
indicator.                             Example: Ordered Qty = Order Qty * ReorderIndic

                                                          Information Technologies in Organizations (2-708-04)
                                                                                          Course 11, page 43
Examples: Parameters which vary with time

                                       Equipment Renovation
1. Presence / absence
   indicator.                      Q1 Q2 Q3 Q4 Q5 Q6
   (Use the indicator 0 / 1)
                                   0       0    0           1            0           0
2. Distribution of a                     Allocation of Renovation
   global value.                                Expense%
   (% distributed over a certain
   number of periods)              Q1      Q2    Q3            Q4            Q5            Q6
                                   10%      0   15%           60%              0          15%
3. Allocation of a global
                                       Allocation of renovation Expense
   value.
   (Amount $ allocation over a certain Q1    Q2 Q3 Q4 Q5 Q6
   number of periods)
                                   15K     0    22K 75K                        0          22K
                                                Information Technologies in Organizations (2-708-04)
                                                                                Course 11, page 44
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 45
Analysis


 ▪ Once the model is developed and tested, we
   can use it in a context of decision-making.
 ▪ 2 approaches
    ▪ Non Structured analysis
    ▪ Structured analysis




                                Information Technologies in Organizations (2-708-04)
                                                                Course 11, page 46
Non structured analysis


 ▪ The user tests a scenario by writing the value
   of the decision variables and the parameters
 ▪ He observes, and notes the values of the
   result variables.
 ▪ He repeats the experience with other values.
 ▪ The user can modify as many values as he
   wants.



                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 47
Structured analysis


 ▪ The structured analysis consists in preparing in
   advance all the interesting scenarios.
 ▪ Excel proposes a structured analysis tool, Table,
   but this tool doesn’t permit to use more than one
   or two variables at a time.




                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 48
Structured analysis : 1 variable
Principles

  ▪ When the user varies the values of a variable,
    Excel takes note of many results at a time.
  ▪ The user must prepare all the values
    corresponding to the interesting scenarios.
  ▪ He must indicate the results that he wants to
    save using transfer formulas.




                                   Information Technologies in Organizations (2-708-04)
                                                                   Course 11, page 49
Structured analysis: 1 variable
Preparation




                     In the tab Decision and Result, we
                     prepare the analysis table in a
                     clean corner of the sheet (for
                     example, D2).



                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 50
Structured analysis: 1 variable
Preparation




  We write in a column the values
  corresponding to the different
  scenarios we want to test (in D3:D8)
                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 51
Structured analysis: 1 variable
Preparation




                  We write in a line the transfers of
                  the results that we want to note
                  (E3:F3)


                                   Information Technologies in Organizations (2-708-04)
                                                                   Course 11, page 52
Structured analysis: 1 variable
Execution
                      ▪ We select all the lines and
                        columns of the table (D2:F8).
                      ▪ We execute the Data table
                        creation command:
                         ▪ Data
                         ▪ Table




                             Information Technologies in Organizations (2-708-04)
                                                             Course 11, page 53
Structured analysis: 1 variable
Execution




Our scenarios are in a column,
so we only need to specify the
entry cell for the column.
                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 54
Structured analysis: 1 variable
Execution




Excel displays the values of each
of the 6 scenarios and notes the
values of the two column headers
in the data table.
                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 55
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 56
Structured analysis: 2 variables
Principles

  ▪ When the user varies the values of two
    variables, Excel enables to take note of only
    one result at a time.
  ▪ The user must prepare all values
    corresponding to the interesting scenarios for
    each of the two variables.
  ▪ He also needs to indicate the result that he
    wants to save using a transfer formula.

                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 57
Structured analysis : 2 variables
Preparation


                             In the tab Decision and
                             result we prepare the data
                             table in a free corner (for
                             example D2).

              For the analysis, we also want to see the
              impact of varying the amount spent per
              apartment (unit cost). We have moved the
              parameter that was initially placed in the
              tab Parameters to this tab instead (use
              cut and paste instead of copy and paste
              to move this parameter).
                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 58
 Structured analysis : 2 variables
 Preparation




We place in a column the
values corresponding to the   We place in a line the
scenarios of a variable       values corresponding
(Price in this case).         to the scenarios of the
                              other variable ( the
                              parameter UnitCost in
                              this case).



                              Information Technologies in Organizations (2-708-04)
                                                              Course 11, page 59
Structured analysis : 2 variables
Preparation




                             We write, in the
                             upper left corner, the
                             transfer formula for
                             the result variable
                             the we are interested
                             in (the variable
                             Bénéfices in this
                             case).
                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 60
Structured analysis : 2 variables
Execution
                      ▪ We select all lines and
                        columns of the table
                        (D2:G15).
                      ▪ We execute the data
                        table creation
                        command:
                         ▪ Data
                         ▪ Table




                             Information Technologies in Organizations (2-708-04)
                                                             Course 11, page 61
Structured analysis : 2 variables
Execution



                      The entry cell for lines will
                      be the values 9.25, 9.50, and
                      9.75 (the parameter UnitCost
                      in this case).

                          The entry cell in
                          column will be values
                          ranging from 10 to 16
                          (the variable Price in this
                          case).
                              Information Technologies in Organizations (2-708-04)
                                                              Course 11, page 62
Structured analysis : 2 variables
Execution




Excel calculated the model 39
times: once for each value
combination between Price (13)
and UnitCost (3).
                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 63
Plan


 ▪ Temporal Model
       ▪ Temporal Model – Concepts (review)
       ▪ Temporal Model – Example and implementation
       ▪ Modeling irregular events
 ▪ Scenario Analysis
       ▪ Tables with one variable
       ▪ Tables with two variables
       ▪ Graphical analysis



                                     Information Technologies in Organizations (2-708-04)
                                                                     Course 11, page 64
Graphical analysis
Principles

 ▪ The data tables can be represented
   graphically.
 ▪ The only graphic in Excel that makes sense in
   this kind of analysis is scattered points.
 ▪ The data values from the scenarios must be
   ordered for the traits, between the points of
   the graphic, to make sense.



                                 Information Technologies in Organizations (2-708-04)
                                                                 Course 11, page 65
Graphical analysis - 1 variable
Preparation




Select the data from the
X and Y axes (D3:E8 in
this case).
                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 66
Graphical analysis - 1 variable
Execution
                         ▪ Execute the order to
                           create a graphic:
                            ▪ Insert
                            ▪ Graphic




                             Information Technologies in Organizations (2-708-04)
                                                             Course 11, page 67
Graphical analysis - 1 variable
Execution
                          ▪ Choose point plot.
                          ▪ It is also useful to
                            select the sub-type
                            attached by a line
                            (that links points
                            by segments)




                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 68
Graphical analysis - 1 variable
Execution
                         ▪ The tab data
                           selection gives you
                           a preview of the
                           graphic.




                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 69
Graphical analysis - 1 variable
Execution
                         ▪ The tab Series
                           enables you to
                           rename your data
                           series (in this case,
                           we will call it Profit)




                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 70
Graphical analysis - 1 variable
Execution

                                            The window
                                            Options of the
                                            Graphic
                                            enables you to
                                            select the way
                                            the graphic will
                                            look. Here, we
                                            entered a title
                                            for the graphic
                                            and both axes.


                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 71
Graphical analysis - 1 variable
Execution




 ▪ The window Graphic positioning enables you to
   select the location of the graphic.



                                   Information Technologies in Organizations (2-708-04)
                                                                   Course 11, page 72
Graphical analysis - 1 variable
Result




                                  Information Technologies in Organizations (2-708-04)
                                                                  Course 11, page 73
Graphical analysis – 2 variables
Preparation

Select the data zone. The
left column will be used
dimension X, the other
columns will be the series
of data. Do not select the
first line since it doesn’t
contain any data to build
the graphic.




                              Information Technologies in Organizations (2-708-04)
                                                              Course 11, page 74
Graphical analysis – 2 variables
Execution
                         ▪ Execute the
                           command Insert,
                           Graphic.
                         ▪ Choose the type
                           Point plot and sub-
                           type points
                           attached by line.




                           Information Technologies in Organizations (2-708-04)
                                                           Course 11, page 75
Graphical analysis – 2 variables
Execution
                       We will rename each
                       series with a more
                       descriptive name. To do
                       so, click on a name of
                       series to the left and enter
                       the new name in the field
                       Name to the right.




                            Information Technologies in Organizations (2-708-04)
                                                            Course 11, page 76
Graphical analysis – 2 variables
Execution
Here’s the result after
renaming each series of
data.




                               Information Technologies in Organizations (2-708-04)
                                                               Course 11, page 77
Graphical analysis – 2 variables
Execution



                               You can add elements in
                               the window Options of the
                               graphic. Here, we added
                               titles and modified the
                               position of the legend.




                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 78
Graphical analysis – 2 variables
Result

                              Here is the final result,
                              after moving and resizing
                              the graphic.




                                    Information Technologies in Organizations (2-708-04)
                                                                    Course 11, page 79

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:5/7/2012
language:English
pages:79