Assignment 4 Prescriptive Analytics

BUS 336: Data Analytics & Visualization Assignment 4 Prescriptive Analytics Total Marks: 60 Instructions: Please review the instructions carefully and make sure to follow the submission requirements. 1. This assignment is based on the data file SelectingHotels.xlsx, which has been uploaded to Canvas. The assignment focuses on Prescriptive Analytics and should be completed individually. 2. You will need to submit: a. A separate PDF files for each part of the case study on Crowdmark. For report format, please refer to the previous assignment solutions. b. Excel file with two sheets, one for each optimization model. 3. Late submissions will not be accepted. You are doing great in the course. KEEP UP THE GOOD WORK! 1 DO NOT COPY OR DISTRIBUTE Optimizing Hotel Selection 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 for multisite 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 earnings 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 1. The best-fit linear regression model is given by: Profitability = 42 + 4*(State Population per Inn) + 6*(Price of the Inn) - 3*(Square Root of the Median Income of the Area) + 2*(College Students in the Area) In this problem, we’ll use this regression model together with integer optimization to select the most profitable sites for La Quinta. Data: La Quinta created a spreadsheet model to predict profitability, and routinely uses it to screen potential 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 given in Table below and is also available on Canvas (SelectingHotels.xlsx). For each hotel, it lists the location of the hotel, the price, and the value for each of the independent variables used in the regression equation (normalized to have mean zero and standard deviation one). Figure 1: Data for Optimizing Hotel Selection Problem Part 1: Predictive Analytics [10 points] Let’s start by understanding the regression equation. 2 DO NOT COPY OR DISTRIBUTE a. According the the regression equation given above, which variables positively affect Profitability? Which variables negatively affect Profitability? Does this intuitively make sense? [4 point] b. Using the regression equation and data in the table, what is the predicted profitability of hotel 2? [2 points] c. Now compute the predicted profitability for all hotels. Show your results in a tabular format. [2 points] d. Which hotel has the highest profitability? The lowest? [2 points] Part 2: Greedy Approach to Hotel Selection Problem [10 points] La Quinta has a budget of $10,000,000 to spend on hotels. Suppose we just 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, etc. a. Which hotels would we purchase based on the greedy approach Show your working. [5 points] b. What would our total predicted profitability be? (This is the sum of the predicted profitability of all hotels we purchase) [2 points] c. If we are trying to maximize our total predicted profitability, is this a good approach? [3 points] Part 3: Optimization Approach to Hotel Selection Problem [20 points] 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 total predicted profitability. a. Write out the optimization problem. Make sure to describe the decision variables, the objective, and the constraints. [10 points] b. Now solve your optimization model using the excel solver. Add a screenshot of your excel model with optimal solution here. [5 points] c. Based on model’s results, which hotels should we purchase? What would be the total predicted profitability? [3 points] d. How does it compare to the greedy solution? Why does it perform better (or worse) than the greedy approach? [2 points] Part 4: Location Diversification [20 points] La Quinta thinks that buying too many hotels in one city is probably not a good idea, and would prefer to diversify in other cities. a. How many hotels are opened in each city? [3 points] b. La Quinta would like to limit the number of hotels purchased in any city to at most 2. Write out the additional 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? [5 points] 3 DO NOT COPY OR DISTRIBUTE c. Update your model in excel to include additional constraints in part (b). Add a screenshot of excel model with optimal solution here. [5 points] d. Based on model’s results, which hotels should we purchase? What would be the total predicted profitability? How does this compare to previous solution? [7 points] 4 DO NOT COPY OR DISTRIBUTE