Excel - Optimization Models

Background

The Denver Nuggets (away team) were playing at the Los Angeles Lakers (home team) on October 25, 2018. I have collected data and built a model to project how many fantasy points each player is going to score during this game. We are going use optimization methods to select a collection of six players (referred to as a “fantasy team”) in order to maximize the expected points that the fantasy team will earn during this game. Each part of the problem is subject to a unique set of constraints. The general problem is a variant of a fantasy sports problem.

Data

You need to download your individual dataset (.csv) on canvas in order to solve these problem. Your file is given as a link under your name on the Project 2 assignment page. The data file has the following data fields:

1. name: The name of the player. 2. team: The player’s team:

• LAL is the Los Angeles Lakers • DEN is the Denver Nuggets.

3. position: The player’s position: • C is Center • PF is Power Forward • SF is Small Forward • PG is Point Guard • SG is Shooting Guard

4. home_indicator: An indicator variable showing if the team was playing at home (1) or away (0). 5. salary: The player’s salary in the game. 6. expected_points: The model’s projection for how many fantasy points the player will earn during

this game.

Instructions

Solve the following optimization problems to the best of your ability. You must work individually on your solutions and may only ask me (Ryan) for help. Each person has a unique problem and your answers will not match any other student’s solutions. If there is evidence that you are submitting the solution to your friend’s problem and not your own, you will receive a zero for the project.

Problem 1

Find the optimal set of six players in order to maximize expected points subject to the following constraints. First, your fantasy team of six players may not exceed more than $50,000 in total salary. In addition, your fantasy team must have at least one player from each team, i.e. at least one player from the Lakers and at least one from the Nuggets.

1. Report the expected total points for the optimal team. 2. How much salary did you use to construct the team? 3. Report the players’ name, position, team, salary, and expected points for the optimal lineup.

Problem 2

Suppose we want at least five Lakers on our proposed team of six players. Use this constraint in addition to the constraints in Problem 1.

1

1. Report the expected total points for the optimal team. 2. How much salary did you use to construct the team? 3. Report the players’ name, position, team, salary, and expected points for the optimal lineup.

Problem 3

On the other hand, the Nuggets are a better team. Remove the constraint from part 2 and add a constraint saying that you want at least four players from the Nuggets. What is the optimial team in terms of maximizing expected points?

1. Report the expected total points for the optimal team. 2. How much salary did you use to construct the team? 3. Report the players’ name, position, team, salary, and expected points for the optimal lineup.

Problem 4

Suppose that we want at least one player from each of the five positions: PG, SG, SF, PF, and C. Remove the constraints from Problems 2 and 3 and find the optimal team in terms of maximizing expected points.

1. Report the expected total points for the optimal team. 2. How much salary did you use to construct the team? 3. Report the players’ name, position, team, salary, and expected points for the optimal lineup.

Problem 5

Which team would you choose and why? Summarize your results in the Executive Summary.

2