Baker K.R. Optimization Modeling with Spreadsheets

Подождите немного. Документ загружается.

number of variables. A large number of outputs and inputs has a tendency to cause

nearly every unit to appear efﬁcient. Therefore, a judicious choice of outputs and

inputs retains the power of DEA but tends to limit the number of DMUs that attain

the maximum efﬁciency. The literature recommends an ideal number of DMUs of

two or three times the total number of inputs and outputs. In practice, it makes

sense to limit consideration to those inputs and outputs that are broadly considered

to be of strategic importance to the units being evaluated.

The DEA model accommodates multiple inputs and outputs and makes no

assumption about the functional form relating outputs to inputs. In other words, any

type of production function is permissible. However, the efﬁciency measure itself

and the construction of a HCU both involve some assumptions. First, the comparison

unit is deﬁned by assuming that weighted averages of efﬁcient units are feasible oper-

ating possibilities. In other words, there are no major “lumpy” relationships in the pro-

duction function. Second, the comparison unit is interpreted as the output potential

that could be achieved if the unit under consideration were to maintain its mix of

inputs and outputs. Here, DEA assumes constant returns to scale. More advanced vari-

ations of the DEA model allow for alternative assumptions about returns to scale.

SUMMARY

The DEA model represents a ﬁfth type of linear programming model, along with allocation,

covering, blending, and network models covered in Chapters 2 and 3. In a strict sense, the

DEA model is a variation on the allocation type, but because its use is so specialized, we

have given it separate treatment here.

For the purposes of spreadsheet implementation, the DEA model should be built with the

kind of ﬂexibility exempliﬁed by Figure 5.5. That is, the analysis of every DMU can be done in

the same worksheet, simply by updating a single cell. A documented analysis is likely to need a

separate location to keep a summary of the linear programming results, as illustrated in the work-

sheet of Figure 5.6. In addition, to identify and analyze the properties of an HCU, we also need

to obtain the Sensitivity Report, making use of its shadow price information as shown in

Figure 5.8.

The DEA model was introduced in the 1970s, and for many years, it was a topic known

mainly to a small group of researchers. Their work extended the theory underlying DEA,

made progress enhancing the computational aspects of the analysis, and reported on selected

applications. Over a period of many years, corporations and consultants have slowly discovered

DEA and begun to use it more frequently. As application catches up with theory, the DEA model

promises to ﬁnd more signiﬁcant use in the future.

EXERCISES

5.1. Evaluating Manufacturing Efﬁciency You have been asked to measure the relative

efﬁciency of ﬁve competing manufacturers of apple juice. Each of the ﬁrms in the

study controls the process of growing and harvesting apples, producing the juice, and deli-

vering the juice to regional distributors. You have decided that your study should

196 Chapter 5 Linear Programming: Data Envelopment Analysis

recognize one input, which is the acreage devoted to apple trees, and one output, which is

the number of gallons of juice delivered last year, in thousands.

Acres Gallons

Firm 1 100 10

Firm 2 110 15

Firm 3 122 20

Firm 4 115 23

Firm 5 96 30

(a) Calculate the productivity for each ﬁrm.

(b) Calculate the efﬁciency of each ﬁrm.

5.2. Expanding Manufacturing Efﬁciency Returning to the ﬁrms you studied in the pre-

vious exercise, you discover that they now make two products—apple juice and apple

sauce. The ﬁrms still effectively use only one important resource, which is the apples

grown in their own orchards, but they have different marketing strategies and hence

their product mixes differ. The relevant data are shown in the table below.

Acres Juice Sauce

Firm 1 100 10 31

Firm 2 110 15 25

Firm 3 122 20 30

Firm 4 115 23 23

Firm 5 96 30 20

(a) Perform a DEA evaluation for this set of ﬁrms, listing the efﬁciency for each ﬁrm.

(b) For each of the inefﬁcient units, ﬁnd the reference set and calculate the inputs and

outputs for a corresponding HCU.

What are the differences in the given data? What are the differences in results?

5.3. Hope Valley Health Care (Revisited) Revisit Example 5.3 and revise the given data

set so that all input and output measures are indexed. That is, each value on a particular

input or output dimension is assigned its percentage relative to the highest value in the set.

Using indexed data, redo the analysis.

(a) Which nursing homes are efﬁcient?

(b) What are the efﬁciencies of the inefﬁcient nursing homes?

and outputs for a corresponding HCU. Then convert these indexed values to the orig-

inal scale.

5.4. Evaluating Hospital Efﬁciency DEA has been used to measure the relative efﬁciency

of a group of hospitals. This study involved seven teaching hospitals; data on three input

measures and four output measures are provided in Table 5.2.

Exercises

197

(a) Perform a DEA evaluation for this set of units, listing the efﬁciency for each hospital.

(b) Consider the performance of Hospital D. What is the interpretation of the efﬁciency

measure for Hospital D?

efﬁciency?

(d) What are the inputs and outputs of the HCU for Hospital D?

5.5. Evaluating Restaurant Efﬁciency Celia’s Tacqueria is a chain of ﬁve Mexican restau-

rants in Florida. A DEA evaluation is being conducted to make comparisons of the differ-

ent locations. Input measures for the restaurants include weekly hours of operation, full-

time equivalent staff, and weekly supply expenses. Output measures of performance

include average weekly contribution to proﬁt, market share, and annual growth rate.

Data for the input and output measures are shown in Table 5.3.

(a) Perform a DEA evaluation for this set of units, listing the efﬁciency and the virtual

outputs for each of restaurants.

(b) For each of the inefﬁcient units, ﬁnd the reference set and calculate the inputs and

outputs for a corresponding HCU.

5.6. Evaluating Branch Bank Efﬁciency A large national bank is interested in evaluating

the performance of several of its branches. For this purpose, it has collected data for a

Table 5.2. Inputs and Outputs for Seven Hospitals

Hospital

Input measures

Full-time Supply Bed-days

equivalent expense available

nonphysicians ($1000s) ($1000s)

A 310.0 134.60 116.00

B 278.5 114.30 106.80

C 165.6 131.30 65.52

D 250.0 316.00 94.40

E 206.4 151.20 102.10

F 384.0 217.00 153.70

G 530.1 770.80 215.00

Output Measures

Patient-days Patient days

(65 or older) (under 65) Nurses Interns

Hospital (000s) (000s) trained trained

A 55.31 49.52 291 47

B 37.64 55.63 156 3

C 32.91 25.77 141 26

D 33.53 41.99 160 21

E 32.48 55.30 157 82

F 48.78 81.92 285 92

G 58.41 119.70 111 89

198 Chapter 5 Linear Programming: Data Envelopment Analysis

DEA study (Table 5.4). Three inputs have been selected for the study: personnel hours,

operating expenses, and ﬂoor space. Three outputs have also been selected; these count

the number of transactions in different areas of the bank. In particular, transactions are

tracked in Deposits and Capital Transfers, Credit Processing, and Foreign Receipts.

(a) Perform a DEA evaluation for the branches, listing the efﬁciency for each branch.

(b) For each of the inefﬁcient units, ﬁnd the reference set.

5.7. Modifying the Efﬁciency Measure in DEA DEA does not distinguish among efﬁcient

DMUs because they are assigned efﬁciencies of 1.0 by the standard procedure. One sug-

gestion for modifying the procedure is the following. For each efﬁcient DMU, repeat the

analysis (i.e., solve the linear programming problem) without retaining the DMU being

evaluated in the set of constraints. In this form of the model, an efﬁcient DMU may

turn out to have efﬁciency greater than 1.0.

Analyze Exercise 5.4 using this approach, calculating the revised efﬁciency rating

for each of the DMUs.

(a) List the hospitals in order of their revised efﬁciencies.

(b) For each hospital, compute how much change occurs between the efﬁciency rating in

the standard procedure and the revised efﬁciency rating.

5.8. Modifying the Efﬁciency Measures in DEA DEA does not distinguish among efﬁ-

cient DMUs because they are assigned efﬁciencies of 1.0 by the standard procedure.

One suggestion for modifying the procedure is the following. For each efﬁcient DMU,

repeat the analysis (i.e., solve the linear programming problem) without retaining the

DMU being evaluated in the set of constraints. In this form of the model, an efﬁcient

DMU may turn out to have efﬁciency greater than 1.0.

Table 5.3. Inputs and Outputs for Five Restaurants

Restaurant

Input measures

Hours of

operation

FTE

staff

Supplies

($)

Jacksonville 96 16 850

Daytona 110 22 1400

Gainesville 100 18 1200

Ocala 125 25 1500

Orlando 120 24 1600

Output measures

Weekly % Market % Growth

Restaurant proﬁt ($) share rate

Jacksonville 3800 25 8.0

Daytona 4600 32 8.5

Gainesville 4400 35 8.0

Ocala 6500 30 10.0

Orlando 6000 28 9.0

Exercises

199

Analyze Exercise 5.5 using this approach, calculating the revised efﬁciency rating

for each of the DMUs.

(a) List the restaurants in order of their revised efﬁciencies.

(b) For each restaurant, compute how much change occurs between the efﬁciency rating

in the standard procedure and the revised efﬁciency rating.

5.9. DEA Efﬁciency with an Additional Constraint Review Exercise 5.4. Add constraints

that require virtual outputs of at least 2 percent.

(a) With the additional constraints, calculate the efﬁciencies of the hospitals. Comparing

the original analysis, what patterns do you see?

(b) Repeat the analysis with a minimum of 10 percent on the virtual outputs.

5.10. DEA Efﬁciency with an Additional Constraint Review Exercise 5.5. Add constraints

that require virtual outputs of at least 10 percent.

(a) With the additional constraints, calculate the efﬁciencies of the restaurants.

Comparing the original analysis, what patterns do you see?

(b) Repeat the analysis with a minimum of 20 percent on the virtual outputs.

5.11. DEA Efﬁciency with Side Constraints Review Exercise 5.5. After looking at the

analysis, your corporate client proposes a reﬁnement. Since the corporation has empha-

sized market share in other divisions, the following constraint is suggested: the virtual

Table 5.4. Inputs and Outputs for 17 Branch Banks

Branch

code

Inputs Outputs

Labor Expenses Space Deposits Credit Foreign

1 34515 6543 591 268836 9052 11242

2 49960 11830 550 475144 15697 15967

3 20652 3464 427 133020 3696 6937

4 49024 7603 478 355909 12918 16594

5 36923 8723 830 240679 4759 8087

6 28967 4606 474 211183 3188 5621

7 28452 7425 182 147364 5302 40618

8 45911 8013 790 130161 12070 115022

9 26890 14662 447 156828 15102 1336

10 47376 7576 764 297925 16797 12030

11 57913 12035 875 462603 2698 13232

12 43477 7255 1109 300976 12299 24368

13 49786 10909 405 233178 6248 4701

14 30045 4264 479 110976 8675 19796

15 56579 8895 840 363048 6370 10788

16 43824 12690 801 130219 20417 28133

17 33823 4143 381 146804 47508 21856

200 Chapter 5 Linear Programming: Data Envelopment Analysis

Table 5.5. Data on 20 Finnish Universities

Input Outputs

ID Costs Inheritances Rebates Warrants Collections

1 9.13 7.525 34.114 21.958 3.840

2 13.60 8.301 23.270 35.966 8.632

3 5.76 10.909 13.392 11.527 4.931

4 11.24 16.621 36.817 27.552 9.522

5 15.57 22.809 95.776 23.611 12.266

6 5.65 1.777 0.156 1.314 39.011

7 21.60 15.107 70.958 54.216 10.809

8 8.57 7.919 48.688 14.032 5.923

9 6.01 7.066 36.304 5.445 2.936

10 8.02 8.858 43.610 13.774 4.274

11 9.93 8.999 36.852 20.661 8.151

12 7.90 8.278 45.222 6.191 5.327

13 5.15 6.763 18.704 10.620 3.540

14 6.42 8.984 13.600 12.319 3.752

15 5.94 7.686 25.906 8.242 2.483

16 8.68 7.227 16.965 17.581 6.274

17 4.86 3.356 23.672 4.298 2.482

18 10.33 8.558 30.540 17.770 8.005

19 21.97 12.234 92.020 29.530 14.763

20 9.70 7.674 41.162 13.272 4.503

21 6.34 8.168 16.613 8.264 5.047

22 7.70 7.884 15.749 14.502 3.034

23 5.99 5.666 27.546 5.243 3.410

24 5.20 6.923 12.613 4.298 3.040

25 6.36 7.352 23.510 5.744 4.207

26 8.87 6.456 38.100 9.645 3.093

27 10.71 13.642 23.862 14.631 4.631

28 6.49 7.675 17.972 8.269 2.756

29 15.32 15.341 55.415 16.361 12.530

30 7.00 8.369 14.918 9.883 4.328

31 10.50 9.608 37.910 13.493 5.035

32 10.88 10.648 36.962 14.248 4.844

33 8.52 8.967 24.672 11.841 3.753

34 7.61 6.111 31.734 7.657 2.872

35 10.91 9.778 42.725 12.169 4.657

36 9.72 7.713 5.897 14.600 9.251

37 12.63 11.082 41.586 16.420 5.647

38 11.51 9.066 28.491 16.284 5.962

39 6.22 6.627 14.667 7.703 3.083

(Continued)

Exercises 201

output for market share should be at least 150 percent of the virtual output from either

proﬁt or growth.

(a) With the side constraints added, calculate the efﬁciencies of the ﬁve restaurants.

(b) What are the advantages and disadvantages of using the side constraint?

5.12. DEA with Cross-Efﬁciencies Review Exercise 5.5. After looking at the analysis, your

corporate client proposes yet another reﬁnement to break ties in the efﬁciency ratings. The

evaluation of the k th DMU produces a set of “optimal prices” that are as favorable as poss-

ible to unit k. Suppose we call these “set k prices.” Now, to evaluate DMU k, compute the

value of its efﬁciency (output value divided by input value) under each of the price sets

(set 1 prices through set 5 prices, in this case). Then, average the ﬁve efﬁciency values.

This average value is called the cross-efﬁciency.

(a) Rank the DMUs based on cross-efﬁciency.

(b) What are the advantages and disadvantages of using the cross-efﬁciency measure?

5.13. Evaluating Universities A study of Finnish universities is aimed at determining an efﬁ-

ciency ranking. Two inputs are used: the university’s annual budget and the selectivity

Table 5.5. Continued

Input Outputs

ID Costs Inheritances Rebates Warrants Collections

40 5.29 3.958 20.416 1.961 1.835

41 8.78 6.558 31.720 8.596 4.831

42 13.50 4.769 26.469 20.877 4.170

43 12.60 6.680 30.280 9.085 19.449

44 8.10 8.103 9.708 8.534 7.502

45 9.67 6.004 19.460 10.708 8.033

46 12.37 11.253 28.500 12.528 6.741

47 9.50 8.674 23.542 8.992 3.664

48 11.47 10.300 15.576 13.740 6.458

49 11.78 12.221 14.325 10.100 5.021

50 12.57 10.432 18.306 16.387 3.924

51 50.26 32.331 150.000 45.099 19.579

52 12.70 9.500 22.391 14.900 5.803

53 13.30 7.530 21.990 14.655 8.324

54 5.60 3.727 12.208 5.388 2.837

55 11.75 5.198 13.280 13.618 7.104

56 8.47 6.149 19.453 6.505 3.300

57 8.36 5.959 17.110 4.655 3.077

58 11.07 7.247 16.338 8.686 6.620

59 10.38 7.761 16.440 6.014 3.313

60 11.83 5.347 12.410 12.238 4.567

61 12.71 6.320 13.632 8.530 5.161

62 11.19 6.578 10.900 3.523 3.456

202 Chapter 5 Linear Programming: Data Envelopment Analysis

Table 5.6. Data on 62 Municipal Departments

Input Outputs

ID Costs Inheritances Rebates Warrants Collections

1 9.13 7.525 34.114 21.958 3.840

2 13.60 8.301 23.270 35.966 8.632

3 5.76 10.909 13.392 11.527 4.931

4 11.24 16.621 36.817 27.552 9.522

5 15.57 22.809 95.776 23.611 12.266

6 5.65 1.777 0.156 1.314 39.011

7 21.60 15.107 70.958 54.216 10.809

8 8.57 7.919 48.688 14.032 5.923

9 6.01 7.066 36.304 5.445 2.936

10 8.02 8.858 43.610 13.774 4.274

11 9.93 8.999 36.852 20.661 8.151

12 7.90 8.278 45.222 6.191 5.327

13 5.15 6.763 18.704 10.620 3.540

14 6.42 8.984 13.600 12.319 3.752

15 5.94 7.686 25.906 8.242 2.483

16 8.68 7.227 16.965 17.581 6.274

17 4.86 3.356 23.672 4.298 2.482

18 10.33 8.558 30.540 17.770 8.005

19 21.97 12.234 92.020 29.530 14.763

20 9.70 7.674 41.162 13.272 4.503

21 6.34 8.168 16.613 8.264 5.047

22 7.70 7.884 15.749 14.502 3.034

23 5.99 5.666 27.546 5.243 3.410

24 5.20 6.923 12.613 4.298 3.040

25 6.36 7.352 23.510 5.744 4.207

26 8.87 6.456 38.100 9.645 3.093

27 10.71 13.642 23.862 14.631 4.631

28 6.49 7.675 17.972 8.269 2.756

29 15.32 15.341 55.415 16.361 12.530

30 7.00 8.369 14.918 9.883 4.328

31 10.50 9.608 37.910 13.493 5.035

32 10.88 10.648 36.962 14.248 4.844

33 8.52 8.967 24.672 11.841 3.753

34 7.61 6.111 31.734 7.657 2.872

35 10.91 9.778 42.725 12.169 4.657

36 9.72 7.713 5.897 14.600 9.251

37 12.63 11.082 41.586 16.420 5.647

38 11.51 9.066 28.491 16.284 5.962

39 6.22 6.627 14.667 7.703 3.083

(Continued)

Exercises 203

rating (for which higher is better). Four outputs are used: the number of graduates (receiv-

ing their primary degree), the number of advanced graduates (receiving a post-graduate

degree), a progress index (measured against a standard rate of progress toward the corre-

sponding degree), and a completion index (measuring the propensity of students to ﬁnish

their degree requirements). The data are provided in Table 5.5.

(a) Perform a DEA evaluation for the universities, listing the efﬁciency for each.

(b) How many of the 20 universities are efﬁcient?

5.14. Evaluating Municipal Departments An effort is underway to evaluate several munici-

pal departments in the UK that collect taxes related to property. One input is used: the

annual cost of operating the municipal ofﬁce. Four outputs are measured, relating to

different activities carried out in each of the departments. The data are shown in Table 5.6.

(a) Perform a DEA evaluation for the departments, listing the efﬁciency for each unit.

(b) How many of the 62 DMUs are efﬁcient?

Table 5.6. Continued

Input Outputs

ID Costs Inheritances Rebates Warrants Collections

40 5.29 3.958 20.416 1.961 1.835

41 8.78 6.558 31.720 8.596 4.831

42 13.50 4.769 26.469 20.877 4.170

43 12.60 6.680 30.280 9.085 19.449

44 8.10 8.103 9.708 8.534 7.502

45 9.67 6.004 19.460 10.708 8.033

46 12.37 11.253 28.500 12.528 6.741

47 9.50 8.674 23.542 8.992 3.664

48 11.47 10.300 15.576 13.740 6.458

49 11.78 12.221 14.325 10.100 5.021

50 12.57 10.432 18.306 16.387 3.924

51 50.26 32.331 150.000 45.099 19.579

52 12.70 9.500 22.391 14.900 5.803

53 13.30 7.530 21.990 14.655 8.324

54 5.60 3.727 12.208 5.388 2.837

55 11.75 5.198 13.280 13.618 7.104

56 8.47 6.149 19.453 6.505 3.300

57 8.36 5.959 17.110 4.655 3.077

58 11.07 7.247 16.338 8.686 6.620

59 10.38 7.761 16.440 6.014 3.313

60 11.83 5.347 12.410 12.238 4.567

61 12.71 6.320 13.632 8.530 5.161

62 11.19 6.578 10.900 3.523 3.456

204 Chapter 5 Linear Programming: Data Envelopment Analysis

Case: Branch Performance at Nashville National Bank

In 1993, Ann Maruchek, Chief Operating Ofﬁcer of Nashville National Bank (NNB), had been

at the helm during a heady time of rapid expansion that saw NNB grow from three to ten

branches in ﬁve years. Unfortunately, that expansion led to some personnel problems. Many

of the branch managers began complaining about discrepancies in pay, titles, and resources,

and they focused on the branch performance appraisal process. One older branch manager

who had received an unfavorable performance review threatened to sue NNB for age discrimi-

nation if he was ﬁred.

Determining some measure of bank branch performance was essential. Without some

agreed upon performance measure, varied decisions such as branch expansion/closure, man-

agerial promotion, and resource allocation would otherwise be left to the “feel” of senior man-

agement. At the time, Ann gave all branch manager performance reviews herself. Being a very

“hands-on” type of manager, she felt that she was in an informed position to pass judgment on

each branch. She based her judgments on what she felt each branch should have accomplished

during the past year, given their location, past performance, and so on, but she had no particular

benchmark for this purpose.

During most of the 1980s, when there were only three branches and she knew each manager

well, her informal style seemed to work well. With the complexity of a larger branch network

combined with the political factions that arose within NNB, it became clear that a more formal

approach was necessary. Under her informal evaluation system, many managers felt that the

negotiating and presentation skills of branch managers could be a more important factor in

their performance appraisals than the actual performance of their branch.

Branch Growth at Nashville National Bank

NNB was founded in 1970 as a largely retail bank serving upper-middle class customers in

Nashville. NNB had only three branches within Davidson County when it merged with a

failed thrift, Belle Meade S&L, in 1989 and gained three more branches. In 1991, NNB pur-

chased another failing institution, Farmer’s Bank, which added one branch. Last year NNB

and People’s Bank merged, bringing the total branches in the NNB system to 10.

Each of the acquisitions was made because the banks were considered “good buys,” rather

than for strategic considerations. Outwardly, few changes were made for the new branches. The

employees of the purchased bank were kept on at their current pay scale and title. Few procedural

changes were implemented to make them conform to NNB’s methods. For instance, loan appli-

cation and review were different from branch to branch. At the extreme, only the former

Farmer’s Bank branch made agricultural loans.

The major changes NNB made were to the backroom operations. The most signiﬁcant

change was to the computer systems. The disparate systems were integrated to ensure that

accounts could be accessed in real time from any branch in the NNB system. This was greatly

appreciated by their customers, as many customers preferred to process transactions at a variety

of branches in the NNB system, not just the particular branch that opened their account.

The acquired branches catered to different market segments than NNB traditionally

embraced. Belle Meade S&L focused on retail banking for the same upper-middle income cus-

tomers. Farmer’s Bank was established to provide both retail and commercial services for agri-

cultural purposes. As agriculture declined in importance in the local economy, the market share

This case was written by Professor Richard Metters of Emory University and is used with permission.

Case: Branch Performance at Nashville National Bank 205