LP, Excel, and Merit – Oh My! (w/apologies to Frank Baum)

1. CIT Research/Teaching Seminar Series (Oct 4, 2007) LP, Excel, and Merit – Oh My! (w/apologies to Frank Baum) John Seydel

2. No, It’s Not About Getting Back to Kansas!

3. Here’s 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. What’s LP? • Consider a production planning problem • Liva’s 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 they’re all equally important! • “Follows syllabus” is important as “explains clearly” • Let’s 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, what’s 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 entity’s 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 Liva’s Lumber) . . .

12. Efficiency Model • Choose a set of criterion weights for a given instructor so as to • Maximize: Instructor’s Output/Input • Subject to: • Each other instructor’s Output/Input <= 1 • Weight values are positive • Which is the same as • Maximize: Instructor’s Weighted Average Score • Subject to: • Each other instructor’s Weighted Average Score <= 1 • Weight values are positive • Since each instructor’s 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 > 0.00 • We can use Excel to model and solve this, but we need to reformulate and solve for every instructor • That’s where macro programming comes in . . .

14. Now, Let’s Apply This to the Data • Consider the model for QVA • Then note the summarytable • 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 Liva’s 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 1:Example Evaluation Items

20. Table 2:Example Departmental Summary

21. DEA Model for Instructor QVL

22. Results Across Instructors

23. Figure 1: DEA vs. Simple Averaging