Question

THIS IS THE ENTIRE PROBLEM GIVEN -

National signing day for rugby recruiting season 2020 has been completed – now, as the recruiting coordinator for the San Diego State University Aztec rugby team, it is time to analyze the results and plan for 2021.

You’ve developed complex analytics and data collection processes and applied them for the past few recruiting seasons to help you develop a plan for 2021. Basically, you have divided the area in which you actively recruit rugby players into 8 different regions. Each region has a per target cost, a “star rating” (average recruit ‘star’ ranking, from 0 to 5, similar to what Rivals uses for football), a yield or acceptance rate percentage (the percentage of targeted recruits who come to SDSU), and a visibility measure, which represents a measure of how much publicity SDSU gets for recruiting in that region, measured per target (increased visibility will enhance future recruiting efforts).

	cos/target	avg star rating	acceptance rt %	visibility per target
Region1	125	3	40	0
Region2	89	2.5	42	0
region 3	234	3.25	25	2
R4	148	3.1	30	3
r5	321	3.5	22	7
r6	274	3.45	20	4
r7	412	3.76	17	5
r8	326	3.2	18	5.5

Your goal is to create a LINEAR mathematical model that determines the number of target recruits you should pursue in each region in order to have an estimated yield (expected number ) of at least 25 rugby recruits for next year while minimizing cost. (Region 1 with yield of 40% - if we target 10 people, the expected number that will come is .4*10 = 4.)

In determining the optimal number of targets in each region (which, not surprisingly, should be integer values), you must also satisfy the following conditions.

1) No more than 20% of the total targets (not the expected number of recruits) should be from any one region.

2) Each region should have at least 4% of the total targets (again, not the expected number of recruits, but the number of targets).

3) The average star rating of the targets must be at least equal to 3.3.

4) The average visibility value of the targets must be at least equal to 3.5.

Off on the recruiting trail you go!! (IN EXCEL)

Answer 1

Let be the number of targets in regions 1 to 8 respectively. These are the decision variables.

The total cost of targeting these many recruits is

We want to minimize this total cost, and hence this is the objective function

Now the constraints.

0) ...have an estimated yield (expected number ) of at least 25 rugby recruits

The expected yield with of targets is

This needs to be at least 25, and hence

1) No more than 20% of the total targets (not the expected number of recruits) should be from any one region.

2) Each region should have at least 4% of the total targets (again, not the expected number of recruits, but the number of targets).

3) The average star rating of the targets must be at least equal to 3.3.

4) The average visibility value of the targets must be at least equal to 3.5.

The LP model is

Minimize

s.t.

Off on the recruiting trail you go!! (IN EXCEL)

Prepare the following sheet

get this

set up the solver using data-->solver

get this

ans: the number of target recruits you should pursue in each region is

	Number of targets
Region 1	17
Region 2	4
Region 3	4
Region 4	18
Region 5	15
Region 6	18
Region 7	15
Region 8	4

The optimum cost is  $23,312.00