Tuition charges
Use instructions below to complete the worksheet in next page:
Formula Instructions
1 Total tuition (column E) is $3050 for full-time students taking 12 credits or more and $125 per credit hour for part-time students (taking less than 12 credits). Use an IF function to determine the correct tuition charges.
2 Scholarship (column F) will be decided based on GPA and using table 1 in cell M10. Use a VLookup function to determine the amount of the scholarship.
3 In order to encourage students to major in business the college is providing $250 tuition assistance for business majors. If a student changes his/her major the tuition assistance will NOT be applied. Use an IF function to determine tuition assistance.(column G)
4 To determine how much a student owe (column H) subtract scholarship and tuition assistance from total tuition.
5 Students should deposit one-third of what they owe or $500 whichever is smaller. Use a MIN function to determine the amount of deposit. (Column I)
6 Calculate the balance for each student. (Column J)
7 Sort the students alphabetically.
8 Move to cell B6 and use a function to calculate average scholarship awards.
9 Move to cell E7 and use a COUNTIF function to determine how many students have received tuition assistance.
Prepared on:
Average Scholarship
Number of students receiving tuition assistance:
Name Major H.S. GPA Credit Hours Total Tuition Scholarship Tuition Assistance Owe Deposit Balance
Eliot, Marianne BUS 1.85 6 Table 1
Guisemme, Angelo EDU 1.85 9
Kellog, Ben CSC 1.85 15 GPA Scholarship
Helstein, Natalia EDU 1.85 15 2 $0
Hesse, Greta ENG 1.85 18 2.3 $100
Tutuska, Justine EDU 1.95 3 2.8 $200
Thiessen, Edvard ENG 1.95 3 3 $300
Thomas, Jacob ENG 1.95 6 3.5 $500
Friessen, Helga BUS 1.95 9
Tasha, Fari EDU 1.95 9
Tamaka, hiromi BUS 1.95 12
Ver, Jen UND 1.95 15
Whtachten, Pieter UND 1.95 15
Talty, Daniel UND 1.98 3
Lawrence, Fisher CSC 2.00 3
Norton, Jane CSC 2.00 6
Lomax, Ren EDU 2.00 6
Switzer, Inez PSY 2.00 6
Gomez, Sylvia CSC 2.00 12
Mizumo, Mari POS 2.15 3
Stachura, Linda UND 2.15 3
Russell, George POS 2.15 12
Nigro, Teresa BUS 2.15 15
Shin, Krisha PSY 2.15 18
Adamski, Michelle POS 2.20 3
Eagan, Ken POS 2.20 6
Morgan, Jason PSY 2.20 6
Wegnar, Olivia ENG 2.20 9
Podsiadlo, Paulo UND 2.20 9
Kuratsu, Takashi ENG 2.20 12
Takasi, Nagatu UND 2.20 12
Zakowicz, Allison PSY 2.20 15
Long, Helga CSC 2.40 3
Linch, Debbie PSY 2.40 3
Powley, Linda EDU 2.40 6
Hoefleschweiger, Janelle UND 2.40 6
O'Brien, Rebecca CSC 2.40 9
Rider, Nathan CSC 2.40 9
Giordano, Luigi ENG 2.40 9
Zebracki, Alice UND 2.40 15
Rosario, Renaldo ENG 2.47 6
Webster, Marriem BUS 2.47 9
Insera, Victoria CSC 2.47 12
McMahon, Hilory EDU 2.47 15
Allman, Mary CSC 2.56 12
Jones, Ted EDU 2.56 18
Louis, Pierre POS 2.58 9
Garner, James CSC 2.60 6
Perez, Bonita EDU 2.60 6
Hogan, Wendy PSY 2.60 6
Smith, Erin UND 2.60 6
Farrel, Tyler POS 2.60 9
Kamei, Yoshi POS 2.60 9
Matherly, Gregory BUS 2.60 15
Hoffman, Paul POS 2.60 15
Jackubowski, Renna CSC 2.67 9
Anderson, Robert CSC 2.77 12
Mah, Dennis ENG 2.77 18
McCaffrey, Shannon CSC 2.85 6
Barret, Oscar POS 2.85 12
Vituro, Goeff EDU 3.00 6
Demming, Lorraine POS 3.00 6
Fanc, Ethan ENG 3.00 9
Suni, Tsen ENG 3.00 9
Peters, Hilda UND 3.00 12
Delgado, Anthony EDU 3.00 15
Holburn, James ENG 3.00 15
Kastusi, Kai BUS 3.00 18
Chafro, Louis EDU 3.05 9
Lussier, Pierre ENG 3.06 6
Wagner, Betsy POS 3.06 6
Thie, Tsumu UND 3.06 12
Palanio, Maria ENG 3.06 15
Gomez, Cristobal UND 3.09 12
Laudon, Elizabeth EDU 3.09 18
Mah, Raymond BUS 3.12 6
Dennis, Mellisa POS 3.12 6
Finucane, Laura EDU 3.12 12
Constantino, Ross POS 3.12 12
Bronstein, William BUS 3.23 6
Lopez, Muriel PSY 3.23 6
Tah, Wendell EDU 3.23 9
Ghram, Issac ENG 3.23 9
Della, Jaun UND 3.23 9
George, Quinn POS 3.23 12
Sprague, Ruth EDU 3.23 15
Travis, Xavier UND 3.23 15
Delpy, Christopher CSC 3.26 9
Thill, Michael EDU 3.26 18
Piotrowski, Helen CSC 3.33 12
Bourne, Peter BUS 3.33 18
Parsons, Allan EDU 3.77 12
Quinly, Evan PSY 3.77 18
Rizzo, Andrew UND 3.81 9
McCormick, Anna CSC 3.81 18
Enright, Julio UND 3.90 9
Farroh,Henry EDU 4.00 6
Sun, Yen POS 4.00 9
Tasaka, Ayako POS 4.00 12
Rowley, Jeff PSY 4.00 12
https://brainmass.com/business/accounting/arkansas-academy-calculating-tuition-charges-272127
Attachments
Solution Summary
The solution explains the calculation of tuition charges using excel formula.