DAT 500N – Prescriptive Analytics Fall 2023
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
DAT 500N – Prescriptive Analytics
Fall 2023
Final Assignment
This is an individual assignment
Instructions
This final assignment consists of two problems. For full credit, you need to solve both problems using Python (use PuLP for Problem 1 and SciPy for Problem 2). If, instead, you use Excel’s Solver (or a different tool) then a 20% deduction of the full grade will be applied.
You need to submit on Canvas the following files via the link pointing to this Assignment:
1. An electronic copy of a short report, named as 'Report_YourLastName_YourFirstName_ Section_xx.pdf'. In the report, you should:
o Show the mathematical formulation of your optimization model (variables, objective function,
constraints), any assumptions you made (Legible, hand-written math formulation is acceptable).
. Provide clear definition of the decision variables
. Briefly state the purpose of each constraint
. Legible, hand-written math formulations are acceptable.
o State any additional assumptions you made if the problem statement is not 100% clear about certain aspects of the problem
o Answer all case questions
o Include screenshots of your Python (or Excel) results and present the optimal solution in a clear and understandable manner.
2. Python Jupyter notebook for Problem 1 with full output named 'P1_YourLastName_YourFirstName_ Section_xx.ipynb'.
3. HTML (or PDF) rendering of the Jupyter notebook for Problem 1 named 'P1_YourLastName_YourFirstName_ Section_xx.html' (or 'P1_YourLastName_YourFirstName_ Section_xx.pdf').
4. Python Jupyter notebook for Problem 2 with full output named 'P2_YourLastName_YourFirstName_ Section_xx.ipynb'.
5. HTML (or PDF) rendering of the Jupyter notebook for Problem 2 named 'P2_YourLastName_YourFirstName_ Section_xx.html' (or 'P2_YourLastName_YourFirstName_ Section_xx.pdf').
Notes:
. HTML rendering of a Jupyter notebook with output can be created from the File -> Download As menu in Jupyter Notebooks. For a PDF version, use the 'Print' -> 'Save as PDF' option. If you're unable to generate either an HTML or PDF file, then leave a comment with your submission indicating so (and why).
. If you use Excel instead of Python for a given problem then an Excel spreadsheet with Solver set up and run to display the optimal solution should be provided instead of the ipynb and html files. The spreadsheet files should be named 'P1_YourLastName_YourFirstName_Section_xx.xlsx' for Problem 1 or 'P2_YourLastName_YourFirstName_Section_xx.xlsx' for Problem 2.
Important reminder: This is an individual task, and you are expected to work independently.
Problem 1 [11.5 points]
As a partner in a prestigious consulting firm, you are tasked with a unique and challenging project that
requires a diverse and experienced team. Your firm prides itself on delivering top-tier consulting services while upholding the values of diversity and inclusion in its workforce.
You are currently preparing for a high-profile project that will require a dynamic approach, blending
different perspectives and skills. To this end, you must assemble a team of four associates from a pool of
eight candidates. As shown in the table below, each candidate brings a unique set of skills and experiences, represented by their years of experience in the field. Additionally, their gender and citizenship (US or Non- US) add further dimensions to the diversity of the team.
a) [7 points] You would like to maximize the total experience of the team, but in addition, for diversity
reasons, you would like to have 2 male and 2 female members, as well as 2 US and 2 non-US members. Please construct and solve a binary optimization model that selects the optimal team.
|
Associate |
Experience |
Gender |
US |
|
1 |
2 |
F |
Yes |
|
2 |
3 |
F |
No |
|
3 |
4 |
M |
Yes |
|
4 |
2 |
M |
No |
|
5 |
1 |
F |
No |
|
6 |
3 |
M |
Yes |
|
7 |
1 |
M |
Yes |
|
8 |
4 |
F |
No |
b) [4.5 points] In addition to the requirements in part (a), there are some further constraints:
i) If Associate 1 is chosen, then Associate 3 cannot be chosen.
ii) If Associate 2 is chosen, then Associates 6 and 7 need to be chosen.
iii) Associates 5 and 8 dislike each other and should not be chosen together.
Please show what additional constraints you need to add to the model of part (a) to accommodate these requirements. Add these constraints to your model and resolve to obtain an optimal solution.
Problem 2 [8.5 points]
At the beginning of each semester, university bookstores around the country face high demand for course textbooks during the short period of time consisting of the first few days of the semester. Hundreds of textbook titles are sold. Consider one textbook title sold by a given bookstore. The bookstore needs to determine the selling price of a new copy and the selling price of a used copy.
We consider the joint pricing problem of the new and used formats of a given textbook. To simplify, we assume that demand is deterministic; that is, once we set prices we know exactly what demand would be for each format of the textbook. Ignore integrality constraints in this problem.
The Multinomial Logit (MNL) demand model is a renowned and widely used tool in both marketing and operations research, primarily for its effectiveness in analyzing consumer choice data. Currently, MNL demand models remain essential for companies, serving as a fundamental instrument in comprehending and forecasting product demand.
Let us assume that the demand for the new and used copies of the textbook under consideration follows this model. In particular, the demands for the new and used copies of the textbook are given by the following expressions:
e(2 一 0.01 ´ pnew)
dnew = 250 ´ 1 + e(2 一 0.01 ´ pnew) + e(1 一 0.01 ´ pused)
e(1 一 0.01 ´ pused)
d used= 250 ´ 1 + e(2 一 0.01 ´ pnew) + e(1 一 0.01 ´ pused)
where pnew is the price set for the new book and pused is the price set for the used book. The model features the exponential function where e = 2.7183 (in particular, e0 = 1.0000, e0.5 = 1.6487, e1 = 2.7183, e2 = 7.3891, etc.).
For example, if the price of the new book is set to $200 and the price of the used book is set to $50 then the corresponding demands are:
dnew = 250 ´ 1 + e(2 一 0
20(0.)0(0)
2e(00)
1 一 0.01 ´ 50) = 250 ´ 1 + e0(e)0+ e0.5 = 69
d used= 250 ´ 1 + e(2 一 0. 0(e)
1´
0(0)
1
5e(0)
1 一 0.01 ´ 50) = 250 ´ 1 + e0(e0) .
e0.5 = 113
In this case, the “price ratio” of the used book price to the new book price is $50/$200 = 0.25, and the “average price” per book sold is (69 x 200 + 113 x 50) / (69 + 113) = $107.
The cost of procuring one copy of the new book is $75 and the cost of procuring one copy of the used book is $25. The contribution margin from this textbook title is ($200 - $75) x 69 + ($50 - $25) x 113 = $11,450.
a) [7 points] Formulate an optimization problem to maximize the bookstore’s contribution margin from this textbook. The university requires that the “price ratio” (the price of the used book divided by the price of the new book) is at most 0.5. Solve the problem using SciPy. What are the optimal prices and what is the corresponding optimal contribution margin?
b) [1.5 points] What is the corresponding “average price” in part a)? Do you think the “average price” constraint imposed by the university necessarily leads to lower prices for the new and used books? Explain.
2023-12-08