CSCI 104 Spring 2016, Final Exam, Practical Portion

If you get stuck on a portion of the test, move on. Complete what you can.

All tasks should be automated whenever the tool supports such automation.

There are 10 points which can be deducted for poor, sloppy, or incorrect work.

1. Access
   • Download this database
   • The database contains information about applicants to a university. It includes the following tables
     • Majors a table containing information about the majors offered at the university.
     • Schools a table containing information about the high schools from which the applicants graduated.
     • Students a table containing information about student applicants to the university. This includes student information, the ID of the high school from which they graduated and the id of the major they are interested in earning.
   • The relationships between the tables have been established.
   • Perform the following queries
     1. [5 points] Find the number of applicants from each school.
        • Ordered by the number of applicants descending.
        • The columns should be labeled and formatted to match the screen shot.
        • Save this query as Top Schools
        • 
     2. [5 points]Find the top students by GPA
        • Produce a query which includes students with a GPA of 3.70 and higher.
        • It should include the First name, Last Name, Email, GPA, Degree and Degree Name.
        • It should be ordered by GPA
        • The columns should be labeled and formatted to match the screen shot.
        • Save this query as Top Students
        • 
     3. Save this database as Database_First_Last.accdb
2. Mail Merge
   • [10 points]Using the Top Students query from the last portion you wish to generate nametags for students visiting the campus.
     1. Start a mail merge.
     2. For the document type, select labels.
     3. For the document layout select Avery US Letter, with Product number 8395 Self Adhesive Name Badges
     4. 
     5. Select the recipients from the Top Students query in the database you just produced.
     6. If you were unable to produce this query, use the Backup Data tab from this workbook.
     7. Each name tag should contain
        • The student's first and last name, centered on the page, bold in 16 point font.
        • The degree and degree name for the student, 11 point font, centered, and in bold.
        • The student's email, centered, 11 point font, blue.
     8. 
     9. Finish the merge, and edit the nametags.
     10. Saved this as Nametags_First_Last.docx.
3. Excel
   • Download this workbook
   • Rename the workbook as Workbook_First_Last.xlsx
   • This workbook contains five worksheets.
     • Student Information where you will compute financial information for a student who is interested in attending the university.
     • Assumptions which contains information about the costs for attending the university as well as loan information.
     • School Data a large dataset which you will mine for information
     • Backup Data which can be used in place of the database query in the previous problem.
     • Grade which I will use to compute your final grade.
   • [20 points]Compute a student loan.
     1. Format the Loan Information table on the Assumptions worksheet so that lookups can be performed by Rating.
     2. Enter your name in cells B2:B2 on the Student Loan Information worksheet.
     3. In cell B7 look up the tuition based on the information in the Tuition Table on the Assumptions worksheet and the value in cell 34 of the Student Loan worksheet.
        • If there is a Y in cell b3, the student will pay in state tuition.
        • Otherwise the student will pay out of state tuition
        • This could be calculated, not copied.
     4. Perform the same task for fees in cell B8
     5. In cell B9 copy, using a formula, the cost of housing from the Tuition Table on the Assumptions worksheet.
     6. In cell B10 copy, using a formula, the cost of the meal plan from the Tuition Table on the Assumptions worksheet.
     7. Compute the total for a semester in cell B12, this should be the sum of the Tuition, Fees, Housing and Meal Plan.
     8. In cell B13, compute the cost for a full year. This should be the semester cost times two.
     9. In cell B14 compute the cost for four years. This should be the yearly cost times 4.
     10. In cell b16 compute the student's interest rate, based on their credit rating in cell B4 and the Loan Information table on the Assumptions worksheet. This is an APR.
     11. In cell b17 compute the student's interest duration, based on their credit rating in cell B4 and the Loan Information table on the Assumptions worksheet. This is in years.
     12. In cell B168 compute the student's monthly payment based on the 4 year total, the Interest Rate and the Period
     13. Change the value in B3 to "N", and the value in B4 to "Poor" and check the computation.
     14. 
     15. Create a pie chart using the data in B7:B10. It should be similar to the screenshot.
     16. 
4. Excel Data Tables.
   • Continue working in the same workbook.
   • The data in the School Data worksheet represents dropout data for the schools in PA for 2008-2009.
   • [5 points]Perform the following tasks:
     1. Convert the data in A5:F625 into a table.
     2. Add a column which represents Total Dropouts which is the sum of the value from Column E and Column F.
     3. Add a column which represents Dropout Percent which is the data for each row in the Total Dropout column divided by the Enrollment
     4. Format this field as a percent, accurate to two decimal places.
     5. Add a total row to the table showing the average number of dropouts for Male, Female and Total
     6. Filter the table so that only the schools with a drop out rate greater than 30% are displayed, sorted by the dropout rate, descending.
     7. 
   • Save this document.
5. Word
   • For this portion of the test you will be formatting a report on student loans. This report was taken from Wikipedia.
   • Download this document and save it as Loans_First_Last.docx
     1. [3 points]Add a title page, include
        • Your name.
        • The title: Student Loans in the United States
        • Today's Date
        • Csci 104 Final Exam.
     2. [5 points] Table of Contents.
        • Add page numbers to the bottom of all pages other than the title page. These should be centered.
        • On a page following the title page build a table of contents.
        • Include the following items
          • Overview
          • Income-Based Repayment
            • Qualification
            • Repayment
          • Criticism
     3. [5 points]References
        • Replace all occurrences of [1] in the text with a reference to existing citation entry (Simkovic 2011).
        • Add a citation for
          • Title: Coordinating Loan Repayment Assistance Programs with New Federal Legislation
          • Authors: Philip G. Schrag and Charles W. Pruett
          • Journal Name: Journal of Legal Education
          • Year 2010
          • Pages 590-597
        • Replace all occurrences of [2] in the text with a reference to this new citation.
        • At the end of the document, on a new page, build a bibliography.
     4. [2 points] The first line of the document contains the sentence "n the United States, there are two types of student loans". Insert a footnote at this point that says "According to Wikipedia"
     5. [5 points] Image
        • On the second page of text add an image of students graduating.
        • Place this in the center of the text
        • Place an oval frame around the image.
        • Wrap the text tightly around the image.
   • 
6. PowerPoint
   • For this section, you will create a power point slide show discussing Graduation
     1. [5 points] Starting out
        • Start a PowerPoint presentation
        • Select a template.
        • Build an introduction slide
        • This should include
          • Title: Graduation Day is Soon
          • Your Name
     2. [10 points] Create a slide
        • Title: Guide to Graduation
        • Text
          • Pick a major
          • Repeat 8 times
            • Talk to your adviser
            • Take classes
            • Pass the classes
          • Graduate
        • Animate each of these lines so that they appear one at a time each time the mouse is clicked.
        • Add an image of an adviser to the right hand side of this slide
        • Make the image appear when "Talk to your adviser" appears.
        • Make the image fade when "Take classes" appears
     3. [5 points]Make a new slide
        • Title: Hopefully the Speaker Will
        • Add Text:
          • Use the 7x7 rule
        • Add points that explains the 7x7 rule.
     4. [3 points] Add a slide that explains any three points form the Death by PowerPoint exercise.
     5. [2 points] apply a single transition to the entire slide show.
     6. Save this presentation as Presentation_First_Last.pptx

• Message 1, the database
  • Subject:First Last Database
  • Please attach a copy of
    • Database_First_Last.accdb
    • Nametags_First_Last.docx.
• Message 2, The worksheet and Word Documents
  • Subject: First Last Word and Excel Documents
  • Please attach:
    • Workbook_First_Last.xlsx
    • Loans_First_Last.docx
• Message 3, The Power Point document.
  • Subject: First Last PowerPoint
  • Please attach
    • Presentation_First_Last.pptx