Business Decision Analytics under Uncertainty – Fall 2023 Assignment 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Business Decision Analytics under Uncertainty - Fall 2023
Assignment 2
Please show your entire work with brief, but sufficiently detailed explanation. You can refer to the lectures and the posted examples to support your answer. You can use a graphing calculator or computer software to visualize parts of a question when applicable. Please submit your work on Canvas as Word and/or Excel documents as appropriate; handwritten answers are not accepted. To help grading and adding comments, do not convert your work into a pdf file[s].
Question 1 (60 points)
Single Facility Location Model with Added Constraints
Q1.1 (20 points)
You assist your client to select a location x = (x1 , x2) for a service facility that will serve K customers by providing a single (by assumption, identical) service or commodity to each customer. In principle, the facility can be located anywhere within the unit square 0 < x1 < 1, 0 < x2 < 1; this square can model e.g., a 100 km by 100 km rectangular region. The customers aremodeled as points pk = (pk1 , pk2) fork = 1, … , K located within the unit square. Each customer’s total annual demand for the service is assumed to be a known positive value: this aspect is expressed by assigning weights wk fork = 1, … , K to the customers. We assume that all customer demands must be satisfied. The distance between the facility location x and
customer k (point pk) is expressed by the Euclidean distance (l2-norm) function defined by
d(x, pk) = sqrt((x1 - pk1)2 + (x2 - pk2)2) (here sqrt denotes the square root function).
We also require that the regions 2x12 – 3 x2 > 1, 4 x1 + 3 x2 > 5, x12 – 5 x22 > 0, and 3 x12 + 5 x22 < 2 be excluded from consideration for the possible location of the facility.
Formulate the mathematical model of this facility location problem.
Q1.2 (15 points)
Determine the convexity properties of your facility location model.
Q1.3 (25 points)
To develop a numerical example in Excel, choose fixed locations and weights for the K = 20 customers within the region 0 < x1 < 1, 0 < x2 < 1. Setup and solve your model.
It is expected that all students create a different numerical example. Therefore, each numerical result is expected to be unique.
Please see Question 2 on next page.
Question 2 (40 points)
Portfolio Management and Efficient Frontier
Consider the portfolio management model discussed in the topical lecture on Spreadsheet Modeling. Make a copy of the latest posted version of the Excel file titled Portfolio Management [Markowitz Model], and work on your copy. See also the original research article by Markowitz posted for your perusal. You don’t have to learn the technical details, but it will help you to understand the core ideas of the model.
Q2.1 (10 points)
Start your work by expanding your data set, adding five more data to each column of the given historical stock returns. Your data should be chosen by yourself, to approximately resemble the given data, but reasonable changes can also be added at your discretion. Every student should have her/his own data set. After adding your data, calculate the corresponding estimated expected return vector, and variance/covariance matrix. Your data will be somewhat different from the original ones, but still make sure to use overall realistic data. (To illustrate this point, don’t create more than 40% return or more than 30% loss, for any stock in any given year.)
Q2.2 (30 points)
Risk can be modeled e.g., by portfolio variance, or equivalently by the standard deviation of the portfolio. Markowitz suggested portfolio diversification based on the observation that lower covariance between portfolio securities results in lower portfolio variance. Then, for a fixed level of estimated return, one can optimize the portfolio variance.
The efficient frontier is defined by the collection of optimal portfolios that, for a given target return, offer the corresponding lowest variance, see e.g., https://www.investopedia.com/terms/e/efficientfrontier.asp. Consequently, we are interested in finding (optimized) portfolios which correspond to points on the efficient frontier.
Based on your revised data set (using all 15 annual return data for each stock), find the minimal and maximal expected return that could be achieved. You can round your expected return data: e.g., 6.86% can be rounded up to 7% and 20.25% can be rounded down to 20%. Divide the range between these minimal and maximal expected return data into steps of 0.5%: these will be your target return parameters t. In the above example, you would consider the values t = 7%, 7.5%, 8%, … , 19%, 19.5%, 20%. Next, solve the entire resulting sequence of these portfolio optimization problems, by minimizing the variance of the portfolio for all these levels of target returns.
Based on your optimization results, create a graph that summarizes the results. State your conclusion regarding the tradeoff between (estimated) portfolio return and risk in a few sentences. Which one of your calculated (return, risk) combinations would you recommend to your client as her/his financial advisor?
2023-10-25