Assume a university begins operations in 2015, which is the first year of its undergraduate programs. The university offers only undergraduate programs, which normally take four years to complete. The university is planning its operations for the ten years ending in 2024. It wants to forecast student population in each year of operation using the numbers of new students being admitted as inputs. The university has also estimated the dropout and graduation rates of students in each cohort. Of all newly admitted students in the first year, 20% drop out in the second year. Of those remaining in the second year, 10% drop out in the third year. Of those remaining in the third year, 5% drop out in the fourth year. Of those remaining in the fourth year, 100% graduate after the fourth year. These dropout and graduation rates are expected to remain unchanged over time. The planning model is provided in the Excel screenshot below: D E F H 1 K L B 1 STUDENT POPULATION 2 3 Year of Operation 2015 4 5 New students 6,300 6 2016 2017 2018 2019 2020 2021 2022 2023 2024 6,800 6,250 7,180 6,670 7,410 7,120 6,940 7,380 7,050 2024 5,440 8 STUDENTS No of Years After Junior Year 9 1 2 3 4 10 Dropout/Graduation rate 20.00% 10.00% 5.00% 100.00% 11 12 Entered New Student Population by Cohorts in a Given Year of University's Operations 13 in Year Students 2015 2016 2017 2018 2019 2020 2021 2022 2023 14 2015 6,300 6,300 5,040 4,536 4,309 15 2016 6,800 6,800 4,896 4,651 16 2017 6,250 6,250 5,000 4,500 4,275 17 2018 7,180 7,180 5,744 5,170 4,912 18 2019 6,670 6,670 5,336 4,802 4,562 19 2020 7,410 7,410 5,928 5,335 5,068 20 2021 7,120 7,120 5,696 5,126 21 2022 6,940 6,940 5,552 22 2023 7,380 7,380 23 2024 7,050 24 Total Students 6,300 11,840 16,226 21,385 21,565 22,191 22,762 22,533 23,126 4,870 4,997 5,904 7,050 22,821 The only inputs provided by the user are in cells (C3], [C5] to (L5), and [F10] to [110]. Here are some additional specifications of this model. Row 5 provides the new student inputs - i.e. numbers of new students that have entered or will enter in a given year of the university's operations. Row 10 provides the dropout and graduation rates as discussed above. For years, only cell [C3] is filled in by the user. All other years are incremented using formulas. Cells (C13] and [A14] refer to the value in cell [C3]. Cells (B14] to (B23] use the same formula that is dragged down from rows 14 to 23 to update references, if needed. Cells (C14] to (L23] also use the same formula that is dragged down from rows 14 to 23 and across from columns C to L to update references, if needed. Row 24 calculates the total student population in a given year of operations across all cohorts. Question 8 (1 point) Saved Provide the formula being used in cell [B19) to reflect the number of newly admitted students (which has already been entered by the user in row 5). Remember cells [B14] to (B23] use the same formula that is dragged down from rows 14 to 23 to update references, if needed. =VLOOKUP($A19, $C$3:$L$5, 3, FALSE) =HLOOKUP($A19, $C$3:$L$5, 5, FALSE) =VLOOKUP($A19, $C$3:$L$5, 5, FALSE) =HLOOKUP($A19, $C$3:$L$5, 3, FALSE) Question 9 (1 point) Provide the formula being used in cell (G18] to calculate the number of students remaining in that cohort using the dropout/graduation rates. Remember cells C14 to L23 use the same formula that is dragged down from rows 14 to 23 to update references, if needed. =ROUND(IF(($A18-G$13)=0, $B18, IF(OR(($A18-G$13)>0, (G$13-$A18)>=MAX($F$9:$L$9)), O, F18*(1-VLOOKUP((G$13-$A18), $F$9:$L$10, 2, FALSE)))), o) =ROUND(IF(($A18-G$13)=0, $B18, IF(OR(($A18-G$13)>0, (G$13-$A18)>=MIN($F$9:$L$9)), O, F18*(1-VLOOKUP((G$13-$A18), $F$9:$L$10, 2, FALSE)))), o) =ROUND(IF($A18-G$13)=0, $B18, IF(OR(($A18-G$13)>0, (G$13-$A18)>=MAX($F$9:$L$9)), O, F18*(1-HLOOKUP((G$13-$A18), $F$9:$L$10, 2, FALSE)))), O) =ROUND(IF(($A18-G$13)=0, $B18, IF(OR(($A18-G$13)>0, (G$13-$A18)>=MIN($F$9:$L$9)), O, F18*(1-HLOOKUP((G$13-$A18), $F$9:$L$10, 2, FALSE)))), o)
