Explore BrainMass

Spreadsheet building

The Willford Community College uses an Excel worksheet to compute tuition charges for incoming freshman. A sample of this file is attached. The file contains student name, major, high school GPA, and credit hours that they have registered. You are asked to complete the Excel file using formulas and function as described below.

1. Open the Willford-CC-Tuition.xls file workbook and save it as Willford-CC-Tuition on your disk.
2. Enter your name and date in cover worksheet then switch to data worksheet.
3. Insert a function to display the current date in cell F4.
4. Total tuition 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 a function to determine the correct tuition charges.
5. Scholarship will be decided based on GPA and using table 1. Set up the table1 in Sheet1 and use a lookup function to determine the amount of the scholarship.
6. 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 a function to determine tuition assistance.
7. To determine how much a student owe subtract scholarship and tuition assistance from total tuition.
8. Students should deposit one-third of what they owe or $500 whichever is smaller. Use a function to determine the amount of deposit.
9. Calculate the balance for each student.
10. Sort the students alphabetically.
11. Move to cell B6 and use a function to calculate average scholarship awards.
12. Move to cell E7 and use a function to determine how many students have received tuition assistance.
13. Include your name, Assignment #2, and date in the custom footer section.
14. Save your workbook as "Willford-CC-Tuition.xls" on your disk.

Table 1

GPA Scholarship
0.0 $0
2.0 $0
2.3 $100
2.8 $200
3.0 $300
3.5 $500


Solution Preview

see the attached file. Thanks
Adamski, Michelle POS 2.20 3 $375.00 $0.00 $- $375.00 $125.00 $250.00
Allman, Mary CSC 2.56 12 $3,050.00 $100.00 $- $2,950.00 $500.00 $2,450.00
Anderson, Robert CSC 2.77 12 $3,050.00 ...

Solution Summary

This post shows how to develop an excel sheet for simple decision making