QMT 2001 Management Science Case Study Preparation Guide

November 30, 2011: Due date of the submission of the first report.

December 7, 2011: First presentation.

December 16, 2011: Due date of the submission of the main report.

December 28, 2011: Main presentation.

- Your grade will depend on how much you follow the instructions in this guide, so read it

carefully.

- Download the report templates for the first and main reports from

http://kisi.deu.edu.tr/ayhan.celik/ms.html or http://businessfaculty.freewebclass.com

- If you need to contact me via email use only this address:

- You need to submit both hard and soft copies of your reports as well as your Excel files.

You can learn your group numbers from my website as well. Name your files as

Group##CaseName.docx, Group##CaseName.xlsx

(e.g. Group03AssigningStudentsToSchools.docx)

- Use Word/Excel 2007 or later. DO NOT USE Word/Excel 2003.

- If you need assistance make an appointment in advance.

- Use the equation editor of Word for variables and formulas.

1

1. Problem Statement

In the problem statement part, you need to summarize the case study. While summarizing,

instead of the irrelevant story behind it, you should state the problem type, objectives, what

decision is to be made, and what parameters and restrictions affect that decision. Keep in mind

that this part is only summary so do not algebraically show your decision variables, objective

function or parameters. USE YOUR OWN WORDS.

2. Model Building

Model building part is the core part of your case study. You need to explain all components of

your model and build it step by step. If necessary, provide some tables, graphs or networks to

visualize your decision making process in this part.

2.1 Parameters

Start with defining your parameters. Parameters are the constant values in an LP model which

are provided in the question. Since these are constant, you have no effect on their values. As an

example, consider the following table.

In this study, an assignment problem of The Springfield School Board is analyzed. Since

one of its middle schools is going to be closed at the end of this school year, the school

board now has to reassign all students to other middle schools.

The objective is to minimize the annual cost for busing from six residential areas of the

city to the three schools under certain restrictions.

In addition to the capacity limits of the schools, the school board has put a restriction

that each grade must constitute between 30% and 36% of each schools population.

Because of the grade restriction and the possibility of partial assignment, the problem is

solved as a general LP problem instead of a transportation or an assignment problem.

2

In the second column of the table is the number of students to be transported from each area. In

the columns 3-5 are the percentages in each grade. Last three columns shows the busing cost per

student along with the capacity of each school. These values affect your decision making process

like the decision variables, but you cannot change them.

In the next part, clearly define your parameters and explain their roles. Each value in the table

represents a parameter so you need 45 parameters. Lets start with the second column and

assume that you have a definition like this:

: This is not a correct definition since it does not state what attribute of students you are talking

about. Consider another definition:

: This is not a correct definition either. You have six parameters in the second column yet you

have only one parameter and it does not state which area you refer to. The correct definition

should be:

:1 :6

This way you have 6 different parameters each of which clearly shows which value you refer.

However, for mid-scale or large scale problems defining each parameter separately is not a good

way. Instead, you should use a definition like this:

:( = 1, ,6) Index allows you to define all 6 parameters in one line. Do not forget to add which values it can take (1,, 6).

Next parameter set is related with the percentages. Again, you need indices but it is a two-way

table so one index would not be sufficient. For example consider the following definitions:

: ( = 1, ,6) !: "#$ (" = 6, , 8)

The first definition does not show the grades of the students and the second one does not show

which area they are from. The correct definition should be:

!: "#$ ( = 1, . . ,6; " = 6, . . ,8)

3

Define the remaining two definition sets similarly.

( = )* *, ( = 1,6; , = 1, ,3) ( = ./*,(, = 1, ,3)

Once you define your parameters, you need to set their values. Tables are good at representing

the parameters, so even if it is not provided in the case study you should construct one. You also

need to explain your approach if no value is set to a parameter. For example in the table no cost

is given for transporting a student from area 2 to school 1. It is an infeasible assignment. You can

assign a very large cost for this parameter (called big M).

2.2 Decision Variables

As mentioned before, decision variables are like parameters in a way that they affect your

decision making process. However, you can decide on their values considering the restrictions

and your objective. In this problem, you need to decide how many students are to be transported

from each area to each school, so your decision variables are:

0(: *, ( = 1,6; , = 1, ,3)

2.3 Objective Function

The objective function is a function of the decision variables that you would like to maximize,

minimize or set to a specific value. In this problem, our aim is to minimize the total busing cost.

You have already defined your cost parameters and decision variables. You have also provided

the values of the parameters. This part is where you can see the role of indices as well. For

example consider the following objective function:

min 4 = 3000 + 007 + 70009 +:07 + 400077 + 500079 + 60009 + 300097+ 200099 + 2000> + 5000>7 +:0>9 + 00? +:0?7 + 4000?9+ 5000 + 30007 + 009

This is an unnecessary and messy representation of the objective function. Instead, you should

use an objective function like this:

min 4 =@@ ,0,3

,=1

6

=1

4

With the two summation operators and two indices, it shows that a cost is incurred for each

student to be transported from area i to school j. It takes the summation of all costs (for each area

and for each school).

2.4 Constraints

In this part you need to algebraically show all the restrictions and explain them. For the problem

we consider, the first constraint is associated with the number of students in each area. You need

to assign all these students to schools so that no student is left unassigned.

@0(9

(A= ( = 1, ,6)

This constraint states that the sum of the number of students to be transported from area i to

school 1, 2 and 3 should be equal to the number of students residing in area i. Therefore, each

student is assigned one school (school 1, school 2 or school 3). In the parentheses it states that

this should be valid for all areas (area 1,,6). It means that this constraint set has 6 different

constraints.

The next constraint is about the school capacities. You cannot exceed the capacity of the schools

so the number of students to be transported from area 1, area 2, , area 6 to each of the schools

should be smaller than that schools capacity.

@0(

A ( (, = 1, ,3)

The summation is over i implying that we add the students from all areas together. The result has

index j so that it is valid for each school.

The last two constraints are about the percentages of the grades in each school. It is stated in the

question that each grade must constitute between 30% and 36% of each schools population.

Therefore, we need to calculate the percentages of 6th

, 7th

and 8th

grades in each school and

compare them with each schools population. 3 grades and 3 schools give us 18 different

constraints (9 for 30% and 9 for 36%).

@0(!

A 0.36@0(

A(, = 1, ,3" = 6, ,8)

In the right hand side of the constraint we see that the summation is again over i, so we add all

the students from each area. In the left hand side we multiply the number of student to be

transported from area i to school j with the percentages. For example for area 1, the number of

students to be transported to school j is 0(. However, only !% of these student are in grade k.

5

After the multiplication if we add all areas together, we get the total "#$ grade students transported to school j. Thats why the indices in parentheses are j and k.

The constraint for 30% restriction is similar:

@0(!

A 0.30@0(

A(, = 1, ,3" = 6, ,8)

In the last part, you need to consider non-negativity constraints. Since the number of students

cannot be negative, you need an additional constraint.

0( 0( = 1,6, = 1, ,3) In this part, you should also decide whether your decision variables should be integer/binary or

not. Since the number of students cannot be fractional, we actually need another constraint.

However, numbers are large and this is a linear programming problem so we are going to use LP

relaxation. If your case study presents an integer or binary programming problem, you need to

add those constraints as well.

2.5 Overall Model

Once you determine all the components of your model, present it in this part.

min 4 =@@ ,0,3

,=1

6

=1

,:

@0(9

(A= ( = 1, ,6)

@0(

A ( (, = 1, ,3)

@0(!

A 0.36@0(

A(, = 1, ,3" = 6, ,8)

@0(!

A 0.30@0(

A(, = 1, ,3" = 6, ,8)

0( 0( = 1,6, = 1, ,3)

6

This part concludes the first report. Submit the following parts in the main report along with

Section 1 (Problem Statement) and Section 2.5 (Overall Model).

3. Modeling with MS Excel

In order to work with MS Excel, you need the add-in called Solver. You can find information

in your textbooks about how to install this add-in. You can search on the web as well. The Solver

dialog box of Excel 2010 is shown in the figure below.

In Excel, all your data (parameters), decision variables, objective function and constraints (with

formulas) should be on the spreadsheet.

We are going to start with By changing variable cells part. These variable cells are your

decision variables so at the start they should be empty. Recall that in the problem that we work

on, our decision variables are

0(: *, ( = 1,6; , = 1, ,3)

Therefore, we need 18 cells. Instead of 18 rows, we can use a tabular representation to show

these variables.

7

The cells B2:D7 correspond to the decision variables 0, , 09, so you need to select these cells as variable cells. At this point you need to start naming your cells so that your formulas and

spreadsheet model become readable. You can find information on how to name ranges in your

textbook. Here are the names I have used so far:

A2:D2: StudentsInArea1, B2:B7: StudentsInSchool1

A3:D3: StudentsInArea2, C2:C7: StudentsInSchool2

A4:D4: StudentsInArea3, D2:D7: StudentsInSchool3

A5:D5: StudentsInArea4,

A6:D6: StudentsInArea5,

A7:D7: StudentsInArea6

You also need to enter your parameters in the spreadsheet. Again, representing them in tabular

form will help you transfer them easily.

8

A2:D7: NumberOfStudents

J2:L7: CostPerStudent

Now, lets calculate the objective function.

min 4 =@@ ,0,3

,=1

6

=1

There are two summations in the objective function and inside these summations there is a

multiplication of cost and number of students. In a cell named TotalCost enter the following

formula:

The SUMPRODUCT function in Excel helps you calculate the multiplications in summations. In

this example, it takes the multiplication of the number of students and the associated costs, and

after that it takes the sum of all these multiplications.

In the Solver dialog box we enter TotalCost as the objective and choose Min.

The first constraint is about the area population:

@0(9

(A= ( = 1, ,6)

Therefore, for example if we add the number of students to be transported to school 1, school 2

and school 3 from each area we get the left hand side of the constraint. In Excel terms,

9

B2 (number of students to be transported from area 1 to school 1)

C2 (number of students to be transported from area 1 to school 2)

D2 (number of students to be transported from area 1 to school 3)

should add up to 450 (number of students in area 1). As a result, we enter the following formula

in E2:

=B2+C2+D2

For the remaining areas we continue in a similar fashion.

For the second constraint, we calculate the number of students transported to each school.

Again, the sum of

B2 (number of students to be transported from area 1 to school 1)

B3 (number of students to be transported from area 2 to school 1)

B7 (number of students to be transported from area 6 to school 1)

should be smaller than School 1s capacity.

Now that we have both the left and right hand sides of the first two constraints, we can add them

in Solver.

The cells E2:E7 (named TotalTransportedFromAreas) should be equal to the cells G2:G7 (named

AreaPopulation).

Also, the cells B8:D8 (named SchoolPopulation) should be smaller than the cells B10:D10

(named SchoolCapacity).

In order to add these constraints, click add on the Solver dialog box.

10

Enter the remaining constraint in a similar way. The resulting screen should look like this:

I expect you to produce a readable model like this. Because of that, it is important that you use

range names.

After you organize your Excel file, report them in an organized way as well.

11

3.1 Variable Cells

Add a screenshot of your variable cells. State their location on the spreadsheet with their names

(e.g. A1:A10: the number of students).

3.2 Parameters

Add a screenshot of your parameters. Again, state their location on the spreadsheet with their

names.

3.3 Objective Function

Show the formula of your objective function. It should be a single cell. Name that cell as well.

3.4 Constraints

Show the formulas of your constraints. Select Show Formulas in Excel and add screenshots.

3.5 Solver

Add a screenshot of the Solver dialog box.

4. Results

After you solve the model in Excel, report your results in an organized way. Use tables, graphs,

networks and/or figures. Do not add the screenshot of Excels answer report here. Do not copy

and paste that report as well. Your results should be readable, so construct your own tables.

Make comments on the objective function. State which variables take which value. Add the

answer report of Excel to Appendix A. Refer to that report in your comments.

5. Sensitivity Analysis

If your case study does not clearly states that it is an integer or binary programming problem,

report your sensitivity analysis as well. Again, report them in an organized way. Do not copy and

paste Excels sensitivity report. Add sensitivity report of Excel to Appendix B.

In this part, your comments are very important. Comment on the shadow prices, marginal costs

and allowable ranges. State which parameters values are important in the sense that their

allowable increase/decrease values are high or low.

6. Additional Questions

Case studies may have additional questions. If you have already provided answers to them in the

previous parts, give references to those sections (e.g. The model is presented in section 2.5, or

As it was mentioned on section 4, there are no students assigned to school 3 from area 1 since it

is relatively costly.) If you need to modify your model, present your modifications as well.