Title: LP, Excel, and Merit Oh My wapologies to Frank Baum
1LP, Excel, and Merit Oh My! (w/apologies to
Frank Baum)
CIT Research/Teaching Seminar Series (Oct 4, 2007)
John Seydel
2No, Its Not About Getting Back to Kansas!
3Heres 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
4Whats 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
5Now, 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)
6Aggregation 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
7DEA 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 . . .
8Efficient 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) .
. .
9Bicriterion Performance Comparision
10Graphically Identifying the Efficient Frontier
OBA
OLK
BFH
OVB
GJB
DEI
IAB
11Some 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) . . .
12Efficiency 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
13An 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 . . .
14Now, 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)
15Where 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)!
16Appendix
17The 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
18Enter 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
19Table 1Example Evaluation Items
20Table 2Example Departmental Summary
21DEA Model for Instructor QVL
22Results Across Instructors
23Figure 1 DEA vs. Simple Averaging