Question: Solve with excel please. Question 3 ) ( 2 5 points ) Before the start of a soccer tournament, you want to create a Soccer

Solve with excel please.
Question 3)(25 points) Before the start of a soccer tournament, you want to create a Soccer Fantasy (see the footnote for a general definition) team to participate in a fantasy soccer competition.
\table[[Player Name,Position,Price (Fantasy $),Predicted Performance],[Manuel Neuer,Goalkeeper,50,95],[Mendy,Goalkeeper,40,87],[Abedzadeh,Goalkeeper,10,75],[\table[[Unai Simon]],\table[[Goalkeeper]],25,87],[\table[[Hugo Loris]],Goalkeeper,35,70],[Thiago Silva,Defender,20,82],[Maguire,Defender,30,75],[\table[[John Stones]],Defender,40,92],[\table[[Van Dijk]],Defender,60,95],[\table[[Cancelo]],Defender,50,89],[Pepe,Defender,25,80],[Martinez,Defender,50,85],[Varane,Defender,30,90],[Tony Cross,Midfielder,35,92],[Modric,Midfielder,35,90],[Gavi,Midfielder,45,82],[Pedri,Midfielder,50,85],[Rice,\table[[Midfielder]],55,88],[Fermandez,\table[[Midfielder]],55,90],[Casemiro,Midfielder,40,88],[Debruyne,\table[[Midfielder]],70,95],[Rodri,Midfielder,60,90],[Neymar,Striker,70,92],[Messi,Striker,40,90],[Ronaldo,Striker,35,82],[Mbappe,Striker,90,90],[,Striker,65,79],[Muller,Striker,30,84],[Polisic,Striker,30,85],[Taremi,Striker,25,82]]
In the data that is shared with you (in Fantasy Soccer.xlsx), a set of available players is given to you. Each player has a position, a price, and a predicted performance. You have 400 fantasy dollars available. Your goal (objective) is to select a squad of players that maximizes the total predicted performance of your team.
Your decision variables are whether you select or not select each player for your team (similar to the knapsack problem). Use mathematical modeling and the Excel solver to pick your team such that the following constraints are satisfied:
You pick a player at most once (binary decision variables).
You pick exactly 11 players.
You pick exactly 1 goalkeeper.
You pick exactly 4 defenders.
You pick exactly 4 midfielders.
You pick exactly 2 strikers.
You cannot spend more than your available budget (400 fantasy $).
You do not need to present a mathematical formulation for this problem, but you MUST solve it with Excel using the Excel Solver. (Hint: Isn't this problem similar to the knapsack problem we learned in the class, but with some additional constraints?)
a) What is the total predicted performance of your selected squad?
Answer: maximum predicted performance:
b) Which defenders are selected in your team?
Answer: selected defenders:
 Solve with excel please. Question 3)(25 points) Before the start of

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!