FB5731 Business Analytics and Decision Modeling

Assignment 1 Decision Analysis

Problem: Real Estate Investment

Sarah and John Anderson are considering purchasing a vacation property in the town of Banff, Alberta, Canada. The asking price for the property is CA$800,000, and it has been on the market for only a day. Their real estate agent has informed them that there are multiple interested buyers who have also viewed the property.

The agent has advised Sarah and John that if they decide to make an offer, they should offer very close to the asking price of $800,000. The agent also mentioned that if there are compet-ing offers, the seller may ask the potential buyers to submit their final offers the following day.

Sarah and John have decided to construct a decision tree to help with this decision. They assumed that the “fair market value” of the property is $800,000. They assigned an “emotional value” of $20,000 if they are successful in purchasing the property. That is, whereas the fair market value is $800,000, the property is worth $820,000 to Sarah and John. Thus, if they were to be successful in purchasing the house for $780,000, the payoff of this outcome would be $40,000. Of course, if they were not successful in purchasing the property, the payoff would be simply $0. Sarah has also assigned a probability of 0.30 that they will be the only buyers on the property.

Sarah and John have decided to consider making one of three offers: $780,000, $800,000, or $810,000. They estimate that if they are the only buyers, the probability that an offer of $780,000 is accepted is 0.40, the probability that an offer of $800,000 is accepted is 0.60, and the probability that an offer of $810,000 is accepted is 0.90.

However, if there are other buyers, the seller may ask them to submit a final offer the following day. In this scenario, Sarah and John will have to decide whether to withdraw, submit the same offer, or increase the offer by $10,000. They feel that in the event of multiple bids, the probability that an offer of $780,000 is accepted is 0.20, the probability that an offer of $790,000 is accepted is 0.30, the probability that an offer of $800,000 is accepted is 0.50, the probability that an offer of $810,000 is accepted is 0.70, and the probability that an offer of $820,000 is accepted is 0.80.

(a) Structure Sarah and John’s problem as a decision tree.

(b) Solve for Sarah and John’s optimal decision strategy.

You can draw the decision tree (including the appropriate decision nodes, state-of-nature nodes, probabilities, payoffs, EMVs, etc.), and scan it to a word document. You can also use the Power-Point template to construct the decision tree.

Assignment 3 Regression Analysis

Problem: VARMAX Realty

In order to help clients determine the price at which their house is likely to sell, VARMAX Realty gathered a sample of 150 purchase transactions during a recent three-month period. Column 1 of the data shows the selling price of the home in thousands of dollars, Column 2 shows the number of square feet (in thousands), Column 3 shows the number of bathrooms, Column 4 is the lot size (the area of the land), and Column 5 is the median household income (in thousands) of the area where the home is located. Analyze the data in home prices.csv and answer the following questions. Feel free to choose your tools. You can use Excel or AI+Rstudio. If you choose AI, be careful about the hallucination issues.

1. (⋆) What are the mean and median home price in the data set?

2. (⋆) Make a histogram of the response variable Price. It should have 15 bins, with “Home Price” as the label of the x -axis and “Count” as the label of the y-axis. Set “grey” as the color of the bins.

3. (⋆) Fit a multiple regression model, using all four explanatory variables. Include the model summary, i.e., the estimated coefficients, p-value, F-test results, and adjusted R squared in your submission.

4. (⋆⋆) Does the estimated model appear to meet the conditions of multiple regression model? (Check model conditions: residual plots, normal quantile plot.)

5. (⋆) Does this model explain statistically significant variation in the prices of homes? Give your reasons.

6. (⋆) Interpret the estimated coefficient for Sq Feet. What does this coefficient mean? What does its p-value mean?

7. (⋆) A homeowner wants to sell her home with: Sq Feet=3, Bathrooms=3, Lot Size=9, Median.Income= 10. Give a 95% prediction interval for the price of her home.

8. (⋆ ⋆ ⋆) A homeowner asked the realtor if she should spend $40,000 to convert a walk-in closet into a small bathroom in order to increase the sale price of her home. What does your analysis indicate?

VARMAX Realty rates the conditions of the homes from A to E, with A being the best condition and E being the worst. The rating data are shown in Column 6. Please include the general condition rating in your model by creating dummy variables and answer the following questions. The IF function in Excel may help you create the dummy variables (ask Poe).

9. (⋆⋆) How many dummy variables do you need? Is it worth including the general con-dition rating in the regression model? Why?

10. (⋆ ⋆ ⋆) Interpret the coefficients of all the dummy variables (use α = 0.05).

Assignment 4 Linear Programming

Problem: Advertising Model Extensions

During our lecture, we formulate the advertising problem on determining how many ads to place on each social media platform. and solve it using the Solver in Excel. Gourmet Treats really has two competing objectives: (i) to obtain as many exposures as possible, and (ii) to keep the total advertising cost as low as possible. In the in-class exercise, we decided to minimize total cost and constrain the exposures to be at least as large as a required level. In this assignment, you will consider an alternative, which is to maximize the total number of excess exposures while the total cost does not exceed the budget $2 million. Here, excess exposures are those above the minimal required level.

excess exposures = actual exposures − required exposures

1. (⋆⋆) Write down the new LP formulation. Note that your objective is to maximize the total number of excess exposures. You have a new constraint on the budget, in addition to the minimum exposure requirements.

2. (⋆⋆) Modify the Excel template accordingly, and solve it using the Excel solver. Include the answer report and sensitivity report here.

3. (⋆) If the budget can be increased by $500,000, how much extra exposure will Gourmet Treats gain under the optimal solution?

In addition to the constraints already in the current advertising model, suppose Gourmet Treats also wants to obtain at least 180 million exposures to men and at least 160 million exposures to women.

4. (⋆ ⋆ ⋆) Does the current optimal solution satisfy these constraints? If not, modify the model as necessary, and rerun Solver to solve it. Include your results here.

Suppose Gourmet Treats replaced the gender-specific minimum exposure requirements (that is, 180 million exposures to men and 160 million exposures to women) by a new constraint: it shouldn’t place any more than 10 ads on any given platform.

5. (⋆⋆) Modify the advertising model appropriately to incorporate this constraint, and then reoptimize. Include the answer report and sensitivity report here.