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

off on the recruiting trail you go... ( in excel)

prepare the folloeing sheet

get this

set up the solver using data ----->

get this