a$.$ Scholarships Model $(40$ pts; $10$ pts each$)$

$($Complete model formulation in cells B$13$ to B$16)$

b$.(10$ pts$)$ Two$-$way table for extra funds $($$$)$ needed

with varying acceptance rates and # of offers

The admissions director of an engineering college

has $$500,000$ in scholarships each year from an

endowment to offer to high$-$achieving applicants. The

value of each scholarship offered is $$25,000($thus$,$

$20$ scholarships are offered$).$ The benefactor who

provided the money would like to see all of it used

each year for new students. However, not all students

accept the money; some take offers from compet$-$

ing schools. If they wait until the end of the admis$-$

sion deadline to decline the scholarship, it cannot

be offered to someone else because any other good

students would already have committed to other pro$-$

grams. Consequently, the admissions director offers

more money than available in anticipation that a per$-$

centage of offers will be declined. If more than $20$

students accept the offers, the college is committed

to honoring them, and the additional amount has to

come out of the dean's budget. Based on prior his$-$

tory, the percentage of applicants that accept the offer

is about $70\%.$ Develop a spreadsheet model for this

situation to evaluate how much money must be allo$-$

cated from the dean's budget based on the number of

scholarships offered.

c$.(10$ pts$)$ Two$-$way table for Unused funds $($$$)$

with varying acceptance rates and # of offers