Use Excel for any modelling and analysis

Consider a town with four elementary schools Alton, Beeks, Carey, and Delancey. The council has implemented a series of standards of learning (SOL) tests in reading, math, and history that all schools are required to administer to all students in the fifth grade. The average test scores are a measurable output of the school's performance. The school board has identified three key resources, or inputs, that affect a school's SOL scores the teacher-to-student ratio, supplementary funds per student (i.e., funding generated by the PTA and other private sources over and above the normal budget), and the average educational level of the parents (where 12 = high school graduate, 16 = college graduate, etc.) You were hired by the school board as their education consultant to help improve the schools efficiency.

The board has identified the following 6 measures:

measure 1 = teacher-to-student ratio

measure 2 = supplementary funds/student

measure 3 = average educational level of parents 2

measure 4 = average reading SOL score

measure 5 = average math SOL score

measure 6 = average history SOL score

The school board wants to identify the school or schools that are less efficient in converting their inputs to outputs relative to the other elementary schools in the town. You plan to construct a data envelopment analysis (DEA) model to compare one particular school with all the others. For a complete analysis, you reckon that a separate model is necessary for each school. You start by evaluating the Delancey school as compared to the other schools. Given the above school data, you are going to construct a DEA model as follows:

a) Which measures would you regard as inputs and outputs, respectively? Please provide your justification.

b) Conduct data envelopment analysis using the linear programming (LP) method. Identify the decision variables in a meaningful way and justify your answer.

c) Identify an objective function for the LP model. What is the objective of the model?

d) Develop the model constraints. How do you associate the constraints with efficiency?

e) Summarise a complete LP model for solving this problem. Use Excel Solver to solve the LP model and determine if the Delancey school is efficient.

f) Adopt a similar process to assess the efficiency of the other three schools in the town. What is your conclusion? Justify your answer with the results.

g) Suggest a method and key aspects of teaching and learning to cover for improving the performance of the school which is identified as inefficient based on your result.

The actual input and output values for each school are