This Excel project will combine leanings from various Excel chapters. In this project, you will:

? Compare the cost of obtaining a bachelors degree from 4 local universities

? Analyze the financial impact of student loans for a hypothetical student.

? Create calculations similar to what I use to determine your final grade in this course.

In this project, you must justify all factual costs and other numbers you assume by explaining in words your cost rational and include hyperlinks to any webpage you obtain cost/income numbers from.

For part One and Two you must analyze the cost for a current, full time freshman undergraduate student assuming no financial aide at each of the following universities:

< >

? Southern Connecticut State University (state resident)

? University of New Haven

? Quinnipiac University.

? Yale University.

PART 1 Cost analysis of attending each university while earning a bachelors degree for one year.

Determine the total cost of attending each university for a freshman for the current year (2 semesters). Assume that the student is living on campus and include the cost of campus housing and board (room and food). If the university offers a variety of housing choices, select one priced in the middle. For all students include any additional fees the university might charge excluding health insurance. Include an estimate for textbooks but do not included any costs for transportation to and from the university (i.e. for students living out of state. Organize your data showing a breakdown of costs in a way that facilitates comparisons between the different universities.

Create a chart/graph which clearly compares the total cost of each university.

Determine the total cost per week of attending classes at each university. Assume each university

meets for 15 weeks including exams.

Determine the cost per class meeting meaning what does it cost the student every time they step foot

into their classroom. The number of class meetings per week varies depending on which course/ course section the student signs up for. Some classes meet once a week while others meet twice a week or more (if a lab class is associated with the course). Make sure you explain your rational for determining this value.

PART 2 Cost analysis of earning a bachelors degree in four years.

Based on the total cost of attending each university you computed in part one, analyze the total cost each of the four years that a student would spend earning a bachelors degree at each university. Assume that the total cost will increase by 4.5% each year above the cost of the previous year. Set up your spreadsheet so I can easily adjust this interest rate if I wish to.

Determine the final cost for earning a bachelors degree in 4 years at each university based on your calculations.

Add a chart that demonstrates the difference in cost for students attending the different universities. Use your judgement on what data to present and how best to present it.

PART 3 Total Educational debt of a typical college student earning a bachelors degree in four years graduating last May.

Research on the Internet and obtain the average total amount of student loans that a student who earned a bachelors degree in 4 years took out and graduated in 2016 or later. (Do not use your own personal numbers.) Make sure the number you determine is for students earning a bachelors degree in 4 years only. Many articles on student loans will include students earning a graduate degree (MS, PhD, MD etc), students who earn a 2 year degree, students who attend a 1 year tech school and students who drop out of school after taking on debt. Do not include these students in your analysis. Hint: Among others The Institute for College Access & Success contains publications on this data.

Using the total amount of college loan debt the student takes on and to calculate the monthly student college loan payment. Assume a 3.76% APR and a loan duration of 10 years. (Hint: PMT function)

PART 4 Impact of paying off student loans. The most accurate way of looking at the impact of student loan repayment would be to compare their student loan repayments to the persons net pay. However there

are too many variables (federal, state, city taxes to just mention a few) in determining a persons net pay so we will use gross pay in our analysis.

State what starting entry work position your hypothetical student starts their work career in. Be careful what position you chose as not all positions are available to people who just graduated from college. For example while you can find the average starting salary of a high school principal no one becomes a high school principal immediately upon graduation from college. A person must be a teacher and/or hold another administrator position for several years before they become a high school principal.

Search the Internet and find the Average Starting Salary in 2017 based on government or national organization surveys for the occupation for your hypothetical student based on their undergraduate degree. Make sure you state what your planned degree/starting job will be. Make sure you site the source and provide a hyperlink to it.

Points will be taken off if the job does not require a bachelors college degree or if the job is not one a student starting their work career would be employed in immediately after graduating in May. This means that the job title should not include works like Manager or require passing a licensing test taken after graduation.

Determine the total amount the hypothetical student will repay each year based on the monthly payment and load duration you calculated earlier. (see your part 3 number)

Assume the hypothetical student receives a 4% salary increase each year. Compare the students annual salary to the amount the student pays for their student loan for each year the student is repaying

the student loan in a talbe. The comparison should be both in terms of dollars and the percentage of the students income the student loan repayment represents. Set up your spreadsheet so I can easily adjust this interest rate if I wish to.

4. Create one or more charts showing the students salary vs yearly loan payments for 10 years both as a percentage of income and absolute dollars. Chose chart styles that clearly show the impact of the student loans. You chart(s) must be complete with titles, legends, axis information as you did in the Excel exercises.

PART 5 Reproducing my teacher grade book.

Add a new spreadsheet to your workbook and call it Daves Gradebook in blue lettering. In this spreadsheet you will create something similar to what I use to store your grades and calculate your final average for the course. Your grade book must contain a row containing appropriate column names for three fictional students. For each student create a fictitious name, attendance grade, individual assignment and project grades and most importantly a calculation of their final grade showing it as both a percentage and a letter grade.

For each student enter:

1) Paper grade and a paper PPT grade as separate grades. Combined these grades will be the grade for the Research Project. The paper is 80% of the research grade and the PPT is 20% of the research grade.

2) An Excel project grade

3) A Access project grade

4) An attendance grade

5) At least 5 Excel grades and 5 Access grades. Each assignment grade should be out of a possible

100 points.

The final grade for the course is computed by:

1) 10% attendance

2) 15% research paper (paper plus PPT)

3) 20% Excel project

4) 15% Access project

5) 40% from the average of their weekly assignments.

Each student must receive a letter grade that is determined using a vlookup table based on their numerical final average grade. The following scale assigns the letter grades.

Letter Grade

Average is equal to or above

A+ 97

A 93

A- 90

B+ 87

B+ 83

B- 80

C+ 77

C 74

C- 70

D+ 67

D 64

D- 60

F <60

Your project must:

1) Include formatting similar to what was used in each weeks assignments and discussed in class.

2) Use cell referenced formulas for all calculations as is done in each assignment. Values should not be

hard coded into formulas, instead you should be using cell references as much as possible in your

formulas as was done throughout the course.

3) Use absolute cell references or named ranges in all appropriate places to easily change calculations

based on changing interest rates and to copy formulas to other rows in a table. A single instance of an

absolute address or name ranged does not satisfy this requirement. (See Chp. 3 & 4 for examples)

4) You must cite the source or rational of all starting values.