The Springfield School Board has made the decision to close one of its middle schools (sixth, seventh, and eighth grades) at the end of this school year and reassign all of next year's middle school student to the three remaining middle schools. The school district provides busing for all middle school students who must travel more than approximately a mile, so the school board wants a plan for reassigning the students that will minimize the total busing cost. The annual cost per student for busing from each of the six residential areas of the city to each of the schools is shown in the following table (along with other basic data for next year), where 0 indicates that busing is not needed and a dash indicates an infeasible assignment.

					Busing Cost per Student
Area	# of students	% in 6th grade	% in 7th grade	% in 8th grade	School 1	School 2	School 3
1	450	32	38	30	$300	$0	$700
2	600	37	28	35	-	400	500
3	550	30	32	38	600	300	200
4	350	28	40	32	200	500	-
5	500	39	34	27	0	-	400
6	450	34	28	38	500	300	0
School Capacity:	900	1,100	1,000

The school board also has imposed the restriction that each grade must constitute between 30 and 36% of each school's population. The above table shows the % of each area's middle school population for next year that falls into each of the three grades. The school attendance zone boundaries can be drawn so as to split any given area among more than one school but assume that the % shown in the table will continue to hold for any partial assignment of an area to a school.

You have been hired as a management science consultant to assist the school board in determining how many students in each area should assigned to each school.

c. Adjust your recommendation as well as you can to enable each area to be assigned to just one school. (Adding this restriction may force you to fudge on some other constraints.)

How can i make right answer? No matter what I do, only 555,555.6 comes out. (The answer for c is 420,000.) Please show me which constraint should be added in excel format

X AutoSave off case 3.5 v Search (Alt+Q) Solver Parameters X X File Home Insert Page Layout Formulas Data Review View Developer Help Easy Document Creator Power Pivot Set Objective: $1$19 Share [ From Text/CSV To Recent Sources Queries & Connections To: Min O Value Of: 0 sis O Max Get Le From Web [a Existing Connections Properties Refresh Filte Data From Table/Range Organization Stocks AJ Sort By Changing Variable Cells: All Edit Links $B$11:$D$16 Get & Transform Data Queries & Connections Data Types Sort & Subject to the Constraints: I19 vix fx $B$12 = 0 Add $B$17:$D$17 = $8$26:$D$26 Change $B$24:$D$24 = $B$26:$D$26 Delete 600 0. 37 0.28 0.35 N/A 400 500 $B$25:$D$25 = $8$26:$D$26 OUIFUN- Reset All 350 0.28 0.4 0.32 200 $C$15 = 0 500 N/A $D$14 = 0 500 0.39 0.34 0.27 0 N/A 400 $E$11:$E$16 = $G$11:$G$16 Load/Save 450 0.34 0.28 0.38 500 300 0 Make Unconstrained Variables Non-Negative # of STUD Assigned Select a Solving Simplex LP v Options Method Area School 1 School 2 School 3 Total # of STUD 0.00 450.00 0.00 450 450 Solving Method 0.0 422.22 177.78 600 600 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex 0.00 227,78 322.22 550 550 engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are OUIFUN - 350.00 0.00 0.00 350 350 non-smooth. OWNMPWOOD TOUFWNOOO NOUFUN- 366.67 0.0 133.33 500 500 83.33 0.00 366.67 450 450 Help Solve Close Total 800 1100 1000