LP, Excel, and Merit Oh My wapologies to Frank Baum

1
LP, Excel, and Merit Oh My! (w/apologies to
Frank Baum)
CIT Research/Teaching Seminar Series (Oct 4, 2007)
John Seydel
2
No, Its Not About Getting Back to Kansas!
3
Heres the Problem

• Developing merit evaluations of multiple faculty
  members
• Some are good all around
• Each is good at something
• Which somethings should be considered more/less
  important?
• How much more/less important?
• Why not borrow from Economics concept of Pareto
  efficiency?
• Identify the efficient set of faculty members
• Avoid answering the importance question
• We can use LP (linear programming) with some help
  from Excel to address this
• Hence LP, Excel, and Merit

4
Whats LP?

• Consider a production planning problem
• Livas Lumber (refer to handout)
• 3 products
• 3 constraints
• 1 objective (maximize weekly profit)
• Summary table
• Modelling LP model and Excel model

5
Now, the Merit Problem

• Typical merit criteria
• Teaching
• Research
• Service
• Consider the teaching criterion
• Our CoB evaluations have 35 dimensions associated
  with the teaching criterion
• What do we do with all those
• There are too many to weight
• So we just average them i.e., we treat them as
  if theyre all equally important!
• Follows syllabus is important as explains
  clearly
• Lets consider a smaller example (Table 1)

6
Aggregation of Results

• Humans want a single performance measures
• Typical schemes
• Simple average (see Table 2)
• Focus on overall effectiveness question (e.g.,
  8)
• Also, weighted average
• Weights determined by whom (committee,
  administrator, statute, . . . )?
• Illustrated by MBO
• So, whats wrong with a simple average?
• Obscures individual strengths and weaknesses
• Artificially values minor differences

7
DEA to the Rescue (?)

• We want to evaluate the outcomes of behaviors
  (decisions) on the basis of
• Multiple criteria to be considered for the
  outcomes
• No generally acceptable set of weights exists
  (and no one is willing to determine such)
• This is where DEA (data envelopment analysis) can
  be useful
• Consider each instructor to be a DMU
  (decision-making unit)
• Apply the concept of economic efficiency . . .

8
Efficient Set Concept

• Set of entities (DMUs) where no entity performs
  as well or better on all criteria
• Graphically convex hull
• Consider concept from finance efficient
  portfolio
• Risk
• Return
• Any entitys weighted multicriteria score will be
  the same as the others scores, if they all get
  to choose their own weights
• These entities are called efficient decision
  making units
• Consider a simple example (subset from Table 2) .
  . .

9
Bicriterion Performance Comparision
10
Graphically Identifying the Efficient Frontier
OBA
OLK
BFH
OVB
GJB
DEI
IAB
11
Some Basic Definitions

• Efficiency Output / Input
• Maximum possible efficiency is defined as 100
  (i.e., 1.00)
• Output for an instructor is her/his weighted
  average evaluation score
• Input for all instructors is theoretically the
  same (100 of time available)
• This leads to a model (recall the LP model for
  Livas Lumber) . . .

12
Efficiency Model

• Choose a set of criterion weights for a given
  instructor so as to
• Maximize Instructors Output/Input
• Subject to
• Each other instructors Output/Input lt 1
• Weight values are positive
• Which is the same as
• Maximize Instructors Weighted Average Score
• Subject to
• Each other instructors Weighted Average Score lt
  1
• Weight values are positive
• Since each instructors input is defined to be
  1.00
• Note, however, that the weighted average is now
  scaled to the 0.00 1.00 interval

13
An Example DEA Output Model for Evaluating
Faculty Teaching

• Let w1 and w2 be the weights to assign to
  impartiality and preparedness, respectively
• Then, for instructor GJB (for example, the
  objective is to
•     Maximize   3.02w1   2.83w2   (GJB
  score) 
•     ST            4.77w1   3.78w2 1.00    
  (OBA)                        3.02w1   2.83w2
  1.00     (GGB)                        2.01w1  
  2.20w2 1.00     (IAB) 
  . . . 4.58w1
    3.96w2 1.00     (IAB)
  w1, w2 gt 0.00
• We can use Excel to model and solve this, but we
  need to reformulate and solve for every
  instructor
• Thats where macro programming comes in . . .

14
Now, Lets Apply This to the Data

• Consider the model for QVA
• Then note the summary table
• Things of interest
• Size of efficient set
• Rank reversals
• Comparison with simple average approach (Figure 1)

15
Where To From Here?

• Constraining the weights
• Ranking the efficient instructors
• Expand across other criteria in the merit
  evaluations
• Other DEA applications (decsion support)
• Comparing ecommerce platforms
• Vendor selection
• Other . . . ?
• Go looking for more Lions and tigers and bears
  (oh my)!

16
Appendix
17
The LP Model for Livas Lumber

• We can model this mathematically   
• Let         x1 number of sheets of CDX to
  produce weekly         x2 number of sheets of
  form plywood to produce weekly        x3
  number of sheets of AC to produce weekly
• The objective is to
•     Maximize   5x1   7x2   
  6x3                  (Weekly profit) 
•     ST            2x1   3x2 10x3
  54000     (Cutting)                        4x1
    7x2   4x3   24000     (Gluing)              
            2x1   3x2   7x3   36000    
  (Finishing) 
• Solving is simply a matter of determining the
  best combination of x1, x2, and x3

18
Enter Excel

• Create a spreadsheet table like the summary table
• Add a few formulae
• Total profit
• Total amount of each resource consumed
• Solve by trial and error . . . ?
• Better use the Solver tool
• Find the optimal solution quickly
• Tinker with parameters and re-solve
• Even better use Solver with a macro button
• Record macro
• Call subroutine when editing onClick event for
  button

19
Table 1Example Evaluation Items
20
Table 2Example Departmental Summary
21
DEA Model for Instructor QVL
22
Results Across Instructors
23
Figure 1 DEA vs. Simple Averaging