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 available each year). The benefactor who provided the money would like to see it used yearly for new students. However, not all students accept the offers. Suppose some take offers from competing schools and wait until the admission deadline to decline the scholarship. In that case, it cannot be offered to someone else because any other talented students would already have committed to other programs. Consequently, the admissions director offers more money than is available, anticipating that a percentage of offers will be declined. If more than 20 students accept the offers, the college is committed to honoring them, and the additional amount must come out of the deans budget. Based on prior history, the percentage of applicants that accept the offer is about 70%.

SHOW EXCEL FORMULAS AND WORK FOR TWO-WAY TABLES.

2a. Scholarships Model Spreadsheet (30 pts) 2b. (15 pts) Two-way table for extra funds (\$) needed \begin{tabular}{|l|r|} \hline \multicolumn{2}{|c|}{ (Complete the model formulation) } \\ \hline Data \\ \hline Number of offers (Decisio & 40 \\ \hline Acceptance \% (estimated) & 0.70 \\ \hline Number available & 20 \\ \hline Scholarship amount & $25,000 \\ \hline Total funds available & $500,000 \\ \hline \multicolumn{2}{|l|}{} \\ \hline Model & \\ \hline Number accepted & 28 \\ \hline Extra \# needed & \\ \hline Extra funds needed & \\ \hline Unused funds & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|l|} \hline & 25 & 30 & 35 & 40 & 45 \\ \hline 50% & & & & & \\ \hline 55% & & & & & \\ \hline 60% & & & & & \\ \hline 65% & & & & & \\ \hline 70% & & & & & \\ \hline 75% & & & & & \\ \hline \end{tabular} 2c. (15 pts) Two-way table for unused funds with varying acceptance rate \& \# of offers \begin{tabular}{|c|c|c|c|c|c|} \hline & 25 & 30 & 35 & 40 & 45 \\ \hline 50% & & & & & \\ \hline 55% & & & & & \\ \hline 60% & & & & & \\ \hline 65% & & & & & \\ \hline 70% & & & & & \\ \hline 75% & & & & & \\ \hline \end{tabular} 2a. Scholarships Model Spreadsheet (30 pts) 2b. (15 pts) Two-way table for extra funds (\$) needed \begin{tabular}{|l|r|} \hline \multicolumn{2}{|c|}{ (Complete the model formulation) } \\ \hline Data \\ \hline Number of offers (Decisio & 40 \\ \hline Acceptance \% (estimated) & 0.70 \\ \hline Number available & 20 \\ \hline Scholarship amount & $25,000 \\ \hline Total funds available & $500,000 \\ \hline \multicolumn{2}{|l|}{} \\ \hline Model & \\ \hline Number accepted & 28 \\ \hline Extra \# needed & \\ \hline Extra funds needed & \\ \hline Unused funds & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|l|} \hline & 25 & 30 & 35 & 40 & 45 \\ \hline 50% & & & & & \\ \hline 55% & & & & & \\ \hline 60% & & & & & \\ \hline 65% & & & & & \\ \hline 70% & & & & & \\ \hline 75% & & & & & \\ \hline \end{tabular} 2c. (15 pts) Two-way table for unused funds with varying acceptance rate \& \# of offers \begin{tabular}{|c|c|c|c|c|c|} \hline & 25 & 30 & 35 & 40 & 45 \\ \hline 50% & & & & & \\ \hline 55% & & & & & \\ \hline 60% & & & & & \\ \hline 65% & & & & & \\ \hline 70% & & & & & \\ \hline 75% & & & & & \\ \hline \end{tabular}