[SOLVED] DAT 500N Prescriptive Analytics Fall 2024 Python

DAT 500N – Prescriptive Analytics

Fall 2024

Final Exam

Work individually and independently on this exam

Instructions

This exam consists of two problems. The number of points allocated to each problem are indicated (summing up to 20 points). For full credit, you need to solve both problems using Python (using PuLP for Problem 1 and SciPy for Problem

2). If, instead, you choose to use Excel’s Solver then a 25% deduction of the full grade for that problem will be applied. You need to submit the following files on Canvas via the link pointing to this exam (under the “Assignments” section):

1. A short report (in .pdf format) that includes:

o The precise mathematical formulation of your optimization models (variables, objective function, constraints) for each problem (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 Answers to all case questions. Include where helpful screenshots of your Python (or Excel) results. Present the optimal solution in a clear and understandable manner.

2. Python Jupyter notebooks in .ipynb format for Problems 1 and 2, separately, with code and showing full output showing.

3. Html (or pdf) renderings of the Jupyter notebooks for Problems 1 and 2, separately, also showing full output.

A full submission thus consists of 5 files. You will likely lose points if you do not adhere to the submission instructions carefully.

Notes:

•    Html rendering of aJupyter 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. Google Colab may only provide a pdf rendering.

•    If you use the Excel option instead of Python for a given problem then an Excel spreadsheet with Solver setup and run to display the optimal solution should be provided instead of the ipynband html files.

Problem 1 (12 points)

This problem is adapted from a case presented in the textbook “Data, Models, and Decisions: The Fundamentals of Management Science”, by Dimitris Bertsimas and Robert M. Freund, published by Dynamic Ideas, 2004. The data Tables 1a-1c (see next page) are available electronically in the spreadsheet accompanying this exam.

Delmar Inc., is a manufacturer of commercial air-conditioning systems. Facing increasing inventory and transportation costs (which is projected to amount to $1.4 million in August), you’ve been asked to see if an optimization model would help.

Delmar manufactures its main product at its manufacturing plant in New Hampshire. The  plant  has the capacity to produce up to 50,000 units per month. The company divides up its national sales into three regions: the East Coast, West Coast, and Midwest areas of the United States. Each region is served by a regional sales distribution center, whose locations are Wilmington, Delaware (serving the East Coast); Salinas, California (serving the West Coast); and St.  Louis,  Missouri  (serving the  Midwest).  Delmar operates two national distribution centers, one in Ohio (that serves the Midwest and the West Coast), and one in New Jersey  (that serves the  Midwest  and the  East  Coast).  Finished  units  are  shipped from the  plant  in  New Hampshire to the two national distribution centers (in Ohio and New Jersey), where they are stored for shipment to the three regional sales distribution centers. Delmar keeps inventory both at the two national distribution centers as well as at the three regional sales distribution centers. In order to take advantage of economies of scale in shipping, shipments are scheduled for once a month. Transportation is scheduled just in time to arrive at the national distribution centers just before they leave for the regional centers and they arrive at the regional centers just before the end of the month. Transportation lead times can be assumed negligible.

The  unit shipping cost from a  national distribution center to a  regional center  is  $10,  regardless of the shipment size. The unit shipping cost from the manufacturing plant to a national distribution center is also $10. In addition, the fixed transportation costs (per month) of arranging for shipments are shown in Table 1a. If a shipment is not required on a given month then that fixed cost is avoided for that month.

The unit inventory cost per monthat the national distribution centers in Ohio and New Jersey is $5 per unit per month, while it is $10 per unit per monthat the regional sales distribution centers. That cost is incurred on inventory level left-over at the distribution center at the end of the month. Inventory costs at the New Hampshire plant are negligible.

The marketing department has provided a demand forecast for the next two months and that forecast is shown in Table  1b. This demand is withdrawn from the regional distribution centers at the end of each month.

Finally, Table 1c shows the estimated inventories at each distribution center for the end of August.

Your task is to develop an optimization model to determine, for each of the months of September and October, how many units to produce and how many units to ship to the distribution centers. Summarize your optimal solution in a single table and provide the associated optimal cost.

Table 1a: Fixed transportation costs for the various routes at Delmar

Route

From To                         Fixed Transportation Cost

[$]

Table 1b: Demand forecast for September and October

Sales Region            Month                Forecasted Demand [units]

Table 1c: Estimated inventories at the end of August

Distribution Center                Units

Problem 2 (8 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:

where pnew  is the price set for the new book and p used 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:

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.

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?