MSBA7021辅导、Java/Python编程辅导
- 首页 >> Database作业 Prescriptive Analytics Due December 7, 23:59 pm
Fall 2022
Assignment 1
1. La Quinta Motor Inns is a mid-sized hotel chain headquartered in San Antonio, Texas. They are looking
to expand to more locations, and know that selecting good sites is crucial to a hotel chain’s success. Of
the four major marketing considerations (price, product, promotion, and location), location has been
shown to be one of the most important considerations for multi-site firms.
Hotel chain owners who can pick good sites quickly have a distinct competitive advantage, since they
are competing against other chains for the same sites. La Quinta used data on 57 existing inn locations
to build a linear regression model to predict Profitability, computed as the operating margin, or earn-
ings before interest and taxes divided by total revenue. They tried many independent variables, such
as Number of Hotel Rooms in the Vicinity and Age of the Inn. All independent variables were normalized to
have mean zero and standard deviation one.
The final regression model is given by:
Profitability = 39.05? 5.41× State Population per Inn + 5.86× Price of the Inn
? 3.09× Square Root of the Median Income of the Area
+ 1.75× College Students in the Area
In this problem, we will use this regression model together with integer optimization to select the
most profitable sites for La Quinta.
(a) Let us start by understanding the regression equation.
(i) According to the regression equation given above, which variables positively affect Profitabil-
ity? Which variables negatively affect Profitability? Does this intuitively make sense to you?
(ii) La Quinta routinely uses this regression equation to predict Profitability, and to screen poten-
tial real estate acquisitions. Suppose that La Quinta is looking to expand their locations in
California, and has collected data for 16 different potential sites. This data is available in the
file SelectingHotels.csv. For each hotel, we have the location, the price, and the value of each
of the independent variables used in the regression equation. Using the regression equation
and the four normalized independent variables, what is the predicted Profitability of hotel 1?
(iii) Now use the regression equation and the data to compute the predicted Profitability for all
hotels. Which hotel has the highest predicted Profitability? How about the lowest?
(b) La Quinta has a budget of $10 million to spend on hotels. Suppose we used a “greedy” approach
where we selected the most profitable hotels until we ran out of budget. So we would start by
buying the hotel we predict to be the most profitable, and then if we had enough budget left, we
would buy the hotel we predict to be the second most profitable, and so on.
(i) Describe what we would do with this approach. Which hotels would we purchase?
(ii) What would our total predicted Profitability be?
(iii) If we are trying to maximize our total predicted Profitability, is this a good approach? How
about if we were trying to maximize the average predicted Profitability of the hotels we select?
Page 1 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
How about if we had a budget of $20 million instead of $10 million?
(c) Now, let us build an optimization model to select hotels. We are trying to decide whether or not a
hotel should be purchased, while making sure we spend no more than our budget of $10 million.
Our objective is to maximize the total predicted Profitability.
(i) Write out the optimization problem we are trying to solve. Make sure to describe the decision
variables, the objective, and the constraints.
(ii) Now solve your optimization model. What is the optimal solution? Report the values of the
decision variables and the optimal objective function value.
(iii) Does the optimal solution make sense intuitively? How does it compare to the greedy solu-
tion?
(d) La Quinta thinks that buying too many hotels in one city is probably not a good idea, and would
prefer to diversify across as many cities as possible. Add constraint(s) to your model to limit the
number of hotels purchased in any city to at most 2.
(i) What are the constraints that you need to add to the model? Intuitively, do you expect the
new optimal objective function value to be larger, smaller, or the same as before?
(ii) Solve the new model. What is the optimal solution? Give the values of the decision variables
and the optimal objective function value. How does this compare to the previous solution?
(e) In no more than one paragraph, describe how you would present your results to La Quinta. Do you
have any recommendations for them to improve the regression model? How about to improve the
optimization model?
Page 2 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
2. The Salanter Akiba Riverside (SAR) Academy is a coeducational, private Modern Orthodox Jewish day
school located in New York City. Every summer, the SAR Academy must create class assignments for
their elementary school students.
Each grade of 80 to 100 students must be divided into different classes. Requests for assignments
are made by parents, teachers, and school therapists. These requests include paris of students that
should be placed together, pairs of students that should not be placed together, pairs of students that
should not be placed together, and requests for students to be placed in classes that better suit their
unique or special academic needs. These requests often conflict with each other, and it falls on the
administration to prioritize which requests should be fulfilled over others.
Here, we will solve a simplified version of the problem faced by the SAR Academy with 100 students
and four classes. The parents or guardians of each of the 100 students are asked to submit preferences
for class 1 to class 4. These preferences often depend on the teaching style of the teachers, the teachers
older siblings have had in the past, and characteristics of the class. The parents give a ranking of 1 to
the class they prefer (their first choice), and a ranking of 2 to their second choice, 3 to their third choice
and 4 to their fourth choice. In addition, each student’s parents are asked to request four students. In
some cases, the data set shows fewer requests, which is usually due to administrators eliminating some
parents’ requests before we got the data. Some parents will request more than four students, which is
accepted by the school, since that makes it easier to guarantee at least one request. Some parents will
make no requests, which gives even more leeway in forming classes.
This data, as well as the gender of each of the students, is given in the spreadsheet ClassAssignment.csv.
In the following questions, students are referred by the StudentNumber.
(a) The problem faced by the SAR Academy is to decide which students should be assigned to which
classes, to satisfy as many of the parent preferences as possible. Each student must be assigned
to exactly one class, and the size of each class should be between 23 and 27.
(i) Formulate this problem as an integer optimization problem. Describe the decision variables,
objective function, and constraints.
(ii) Solve this optimization problem. What is the optimal solution? Give the values of the deci-
sion variables and the optimal objective function value.
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(b) After looking at the optimal solution, the SAR Academy decided that they would like to adjust the
formulation to better balance the boys-to-girls ratio in the classes. They would like to limit that
ratio in each class to be no less than 0.6 but no more than 0.8.
(i) What constraint(s) do you need to add to your model to incorporate this adjustment?
(ii) Add the necessary constraints and re-solve your model. What is the optimal solution now?
How does it compare to the previous solution?
Page 3 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(iv) How many boys and girls are there in each class?
(c) Now, we will add some logical constraints to capture additional preferences of parents, teachers,
and school therapists. The following sub-questions are only based on (a). Please add the corre-
sponding constraints and solve the model, and comment on how they are satisfied.
(i) Students 15 and 19, 60 and 68 are two pairs of twins, and the school has a policy that twins
must be placed in different classes. What constraint(s) needs to be added to the model to
implement this policy?
(ii) Students 4, 8, 12, 25, 30, 36, 41, 48, 55, 59, 64, 69, 73, 89, 90 and 96 are all from the same
neighborhood. They school would like to put at least 3 students from this neighborhood in
each class. What constraint(s) needs to be added to the model to implement this policy?
(iii) The school therapists strong recommend that students 1, 4 and 95 are placed in the same
class, that student 3 is placed in class 1, that student 40 are placed in class 2, and that stu-
dents 80 and 99 are placed in class 3. What constraint(s) needs to be added to the model to
implement this policy?
(d) As for friend requests, 9 students made no requests for friends, 5 made only one request, 10 made
two, 16 made three, 52 made 4 and 8 made 5 requests. The school want to guarantee that each
student will be placed with at least one child from a list of up to five children provided by that
student.
(i) What constraint(s) do you need to add to your model to incorporate this adjustment?
(ii) Add the necessary constraints and re-solve your model. What is the optimal solution now?
How does it compare to the previous solution?
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(iv) How many students are assigned to the same class with more than one friend? And with how
many friends are there?
(e) Add all the constraints involved in (a) to (d), including the constraints related to the class size,
the boys-to-girls ratio, twin students, neighborhood considerations, recommendations given by
therapists and friend requests from students, and re-solve the model.
(i) What is the optimal solution now? How does it compare to the previous solution?
(ii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
Page 4 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
3. In the second lecture note on “Portfolio Optimization”, we have implicitly assumed that the assets
available are all risky; that is, they each have a positive standard deviation. A risk-free asset has a
return that is deterministic (that is, known with certainty) and therefore has a zero standard devia-
tion. In other words, a risk-free asset is a pure interest-bearing instrument; its inclusion in a portfolio
corresponds to lending or borrowing cash at the risk-free rate. Lending (such as the purchase of a
bond) corresponds to the risk-free asset having a positive weight, whereas borrowing corresponds to
its having a negative weight.
The inclusion of a risk-free asset in the list of possible assets is necessary to obtain realism. Investors
invariably have the opportunity to borrow or lend. It turns out that inclusion of a risk-free asset greatly
simplifies the shape of the efficient frontier.
Suppose that in addition to the twenty stocks in the lecture notes, there is a risk-free asset with daily
risk-free rate 6× 10?5. Using the same data as in StockPrices.csv, answer the following questions.
(a) Consider the Markowitz model. Suppose that short selling the stocks is allowed, and that both
borrowing and lending the risk-free asset are allowed. Plot the efficient frontier and compare it
with the efficient frontier when the risk-free asset is not available.
(b) Consider the mean-absolute deviation framework. Suppose that short selling the stocks is not
allowed, and only lending the risk-free asset is allowed. Plot the efficient frontier and compare it
with the efficient frontier when the risk-free asset is not available.
Page 5 of 5
Fall 2022
Assignment 1
1. La Quinta Motor Inns is a mid-sized hotel chain headquartered in San Antonio, Texas. They are looking
to expand to more locations, and know that selecting good sites is crucial to a hotel chain’s success. Of
the four major marketing considerations (price, product, promotion, and location), location has been
shown to be one of the most important considerations for multi-site firms.
Hotel chain owners who can pick good sites quickly have a distinct competitive advantage, since they
are competing against other chains for the same sites. La Quinta used data on 57 existing inn locations
to build a linear regression model to predict Profitability, computed as the operating margin, or earn-
ings before interest and taxes divided by total revenue. They tried many independent variables, such
as Number of Hotel Rooms in the Vicinity and Age of the Inn. All independent variables were normalized to
have mean zero and standard deviation one.
The final regression model is given by:
Profitability = 39.05? 5.41× State Population per Inn + 5.86× Price of the Inn
? 3.09× Square Root of the Median Income of the Area
+ 1.75× College Students in the Area
In this problem, we will use this regression model together with integer optimization to select the
most profitable sites for La Quinta.
(a) Let us start by understanding the regression equation.
(i) According to the regression equation given above, which variables positively affect Profitabil-
ity? Which variables negatively affect Profitability? Does this intuitively make sense to you?
(ii) La Quinta routinely uses this regression equation to predict Profitability, and to screen poten-
tial real estate acquisitions. Suppose that La Quinta is looking to expand their locations in
California, and has collected data for 16 different potential sites. This data is available in the
file SelectingHotels.csv. For each hotel, we have the location, the price, and the value of each
of the independent variables used in the regression equation. Using the regression equation
and the four normalized independent variables, what is the predicted Profitability of hotel 1?
(iii) Now use the regression equation and the data to compute the predicted Profitability for all
hotels. Which hotel has the highest predicted Profitability? How about the lowest?
(b) La Quinta has a budget of $10 million to spend on hotels. Suppose we used a “greedy” approach
where we selected the most profitable hotels until we ran out of budget. So we would start by
buying the hotel we predict to be the most profitable, and then if we had enough budget left, we
would buy the hotel we predict to be the second most profitable, and so on.
(i) Describe what we would do with this approach. Which hotels would we purchase?
(ii) What would our total predicted Profitability be?
(iii) If we are trying to maximize our total predicted Profitability, is this a good approach? How
about if we were trying to maximize the average predicted Profitability of the hotels we select?
Page 1 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
How about if we had a budget of $20 million instead of $10 million?
(c) Now, let us build an optimization model to select hotels. We are trying to decide whether or not a
hotel should be purchased, while making sure we spend no more than our budget of $10 million.
Our objective is to maximize the total predicted Profitability.
(i) Write out the optimization problem we are trying to solve. Make sure to describe the decision
variables, the objective, and the constraints.
(ii) Now solve your optimization model. What is the optimal solution? Report the values of the
decision variables and the optimal objective function value.
(iii) Does the optimal solution make sense intuitively? How does it compare to the greedy solu-
tion?
(d) La Quinta thinks that buying too many hotels in one city is probably not a good idea, and would
prefer to diversify across as many cities as possible. Add constraint(s) to your model to limit the
number of hotels purchased in any city to at most 2.
(i) What are the constraints that you need to add to the model? Intuitively, do you expect the
new optimal objective function value to be larger, smaller, or the same as before?
(ii) Solve the new model. What is the optimal solution? Give the values of the decision variables
and the optimal objective function value. How does this compare to the previous solution?
(e) In no more than one paragraph, describe how you would present your results to La Quinta. Do you
have any recommendations for them to improve the regression model? How about to improve the
optimization model?
Page 2 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
2. The Salanter Akiba Riverside (SAR) Academy is a coeducational, private Modern Orthodox Jewish day
school located in New York City. Every summer, the SAR Academy must create class assignments for
their elementary school students.
Each grade of 80 to 100 students must be divided into different classes. Requests for assignments
are made by parents, teachers, and school therapists. These requests include paris of students that
should be placed together, pairs of students that should not be placed together, pairs of students that
should not be placed together, and requests for students to be placed in classes that better suit their
unique or special academic needs. These requests often conflict with each other, and it falls on the
administration to prioritize which requests should be fulfilled over others.
Here, we will solve a simplified version of the problem faced by the SAR Academy with 100 students
and four classes. The parents or guardians of each of the 100 students are asked to submit preferences
for class 1 to class 4. These preferences often depend on the teaching style of the teachers, the teachers
older siblings have had in the past, and characteristics of the class. The parents give a ranking of 1 to
the class they prefer (their first choice), and a ranking of 2 to their second choice, 3 to their third choice
and 4 to their fourth choice. In addition, each student’s parents are asked to request four students. In
some cases, the data set shows fewer requests, which is usually due to administrators eliminating some
parents’ requests before we got the data. Some parents will request more than four students, which is
accepted by the school, since that makes it easier to guarantee at least one request. Some parents will
make no requests, which gives even more leeway in forming classes.
This data, as well as the gender of each of the students, is given in the spreadsheet ClassAssignment.csv.
In the following questions, students are referred by the StudentNumber.
(a) The problem faced by the SAR Academy is to decide which students should be assigned to which
classes, to satisfy as many of the parent preferences as possible. Each student must be assigned
to exactly one class, and the size of each class should be between 23 and 27.
(i) Formulate this problem as an integer optimization problem. Describe the decision variables,
objective function, and constraints.
(ii) Solve this optimization problem. What is the optimal solution? Give the values of the deci-
sion variables and the optimal objective function value.
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(b) After looking at the optimal solution, the SAR Academy decided that they would like to adjust the
formulation to better balance the boys-to-girls ratio in the classes. They would like to limit that
ratio in each class to be no less than 0.6 but no more than 0.8.
(i) What constraint(s) do you need to add to your model to incorporate this adjustment?
(ii) Add the necessary constraints and re-solve your model. What is the optimal solution now?
How does it compare to the previous solution?
Page 3 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(iv) How many boys and girls are there in each class?
(c) Now, we will add some logical constraints to capture additional preferences of parents, teachers,
and school therapists. The following sub-questions are only based on (a). Please add the corre-
sponding constraints and solve the model, and comment on how they are satisfied.
(i) Students 15 and 19, 60 and 68 are two pairs of twins, and the school has a policy that twins
must be placed in different classes. What constraint(s) needs to be added to the model to
implement this policy?
(ii) Students 4, 8, 12, 25, 30, 36, 41, 48, 55, 59, 64, 69, 73, 89, 90 and 96 are all from the same
neighborhood. They school would like to put at least 3 students from this neighborhood in
each class. What constraint(s) needs to be added to the model to implement this policy?
(iii) The school therapists strong recommend that students 1, 4 and 95 are placed in the same
class, that student 3 is placed in class 1, that student 40 are placed in class 2, and that stu-
dents 80 and 99 are placed in class 3. What constraint(s) needs to be added to the model to
implement this policy?
(d) As for friend requests, 9 students made no requests for friends, 5 made only one request, 10 made
two, 16 made three, 52 made 4 and 8 made 5 requests. The school want to guarantee that each
student will be placed with at least one child from a list of up to five children provided by that
student.
(i) What constraint(s) do you need to add to your model to incorporate this adjustment?
(ii) Add the necessary constraints and re-solve your model. What is the optimal solution now?
How does it compare to the previous solution?
(iii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
(iv) How many students are assigned to the same class with more than one friend? And with how
many friends are there?
(e) Add all the constraints involved in (a) to (d), including the constraints related to the class size,
the boys-to-girls ratio, twin students, neighborhood considerations, recommendations given by
therapists and friend requests from students, and re-solve the model.
(i) What is the optimal solution now? How does it compare to the previous solution?
(ii) How many students will be assigned to their first choice class, according to the parent pref-
erences? What are the number of students who receive the second, third and fourth choice
class?
Page 4 of 5
MSBA7021
Prescriptive Analytics
Fall 2022
Assignment 1
3. In the second lecture note on “Portfolio Optimization”, we have implicitly assumed that the assets
available are all risky; that is, they each have a positive standard deviation. A risk-free asset has a
return that is deterministic (that is, known with certainty) and therefore has a zero standard devia-
tion. In other words, a risk-free asset is a pure interest-bearing instrument; its inclusion in a portfolio
corresponds to lending or borrowing cash at the risk-free rate. Lending (such as the purchase of a
bond) corresponds to the risk-free asset having a positive weight, whereas borrowing corresponds to
its having a negative weight.
The inclusion of a risk-free asset in the list of possible assets is necessary to obtain realism. Investors
invariably have the opportunity to borrow or lend. It turns out that inclusion of a risk-free asset greatly
simplifies the shape of the efficient frontier.
Suppose that in addition to the twenty stocks in the lecture notes, there is a risk-free asset with daily
risk-free rate 6× 10?5. Using the same data as in StockPrices.csv, answer the following questions.
(a) Consider the Markowitz model. Suppose that short selling the stocks is allowed, and that both
borrowing and lending the risk-free asset are allowed. Plot the efficient frontier and compare it
with the efficient frontier when the risk-free asset is not available.
(b) Consider the mean-absolute deviation framework. Suppose that short selling the stocks is not
allowed, and only lending the risk-free asset is allowed. Plot the efficient frontier and compare it
with the efficient frontier when the risk-free asset is not available.
Page 5 of 5