CSCI 104 Fall 2016, Final Exam, Practical Portion

Unless otherwise specified the exact appearance of the document does not have to match the example below, but all of the elements must be in place.

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

Please be aware that the dropbox folder will close at 12:15. There are substantial penalties for late submission and failure to submit your work will result in a 0 on that portion of the test.

There are 10 points which can be deducted for poor, sloppy, or incorrect work. This includes unformatted or inappropriately formatted data.

You may ask for help, but I may not provide that help. You may use your books, notes and the internet to do this portion of the test. You may not communicate with another person while working on this portion of the test.

For this test, we will be working with real-estate transfers in the city of Lake Geneva Wisconsin. This information is from a public data repository located here. I have reduced the data in this worksheet.

1. Start by downloading this workbook. Save it now and save it frequently as you work on the test.
2. Go to the worksheet labeled Basic Work Sheet
   • On this worksheet you will compute mortgage payments for ten different loans.
   • [1 point] Place your name in cell C1
   • [2 points] D4:D14, find the required percent down for each loan based on the Credit rating in C4:C14 and the loan table in cells A18:C23.
   • [1 point] In E4:E14 compute the down payment based on the price of the home and the required percent down.
   • [1 point] In F4:F14 compute the amount financed based on the price of the home and the down payment.
   • [2 points] In G4:G14 find the interest rate for the loan based on the credit rating and the loan table.
   • [4 points] In I4:I14 compute the monthly payment for each loan based on the amount of the loan, the period and the interest rate.
   • If you wish you may add another column and compute 28% of the monthly income.
   • [5 points] In cells K4:K14 create a formula which will place an "Yes" in the cell if the monthly payment is less than 28% of the monthly income. If this is not the case, the formula should place a "No" in the cell.
   • 
3. Go to the next worksheet, labeled Report Data.
   • The data in this worksheet represents real estate transactions. On this first sheet you will summarize the data in this table. The goal is to summarize the data by type of property and then by year.
   • 
   • [1 point] Add a column and compute the year of the purchase based on the date of purchase. (year function)
   • [8 points] Use the proper tools in Excel to summarize this data as described and shown in the picture. Count the entries in the Energy Exclusion column.
   • [1 point] Hide all columns except for Year, Primary Residence, Type and Energy Exclusion.
4. Pivot Table
   • The worksheet Energy Exclusion Codes provides further information about the energy exclusions.
   • The worksheet Report Data(2) is a copy of the worksheet from the last problem.
   • [10 points] Build a pivot table from the data in these two worksheets. Rows are based on energy exclusion, columns based by the type of building. The average property value should be displayed in the table.
   • 
5. Save this workbook.
6. Start PowerPoint
   • [5 points] Slide 1:
     • Create a new slide show using any template of your choice, note the template name.
     • Make a title slide which includes
       • Your name
       • CSCI 104 Final Exam
       • The name of the template you used.
   • [5 points] Slide 2:
     • Add a two content slide.
     • Title: Home Loans
     • On the left side insert a picture of a home.
     • On the right side insert a list of at least three basic principles to keep in mind when preparing a PowerPoint presentation.
   • [10 points] Slide 3:
     • Add a slide
     • Title: Animation
     • Add the following text on the right side.

       		* Line 1
       		* Line 2, a smile appears
       		* Line 3,
       		     * the smile disappears
       		     * a triangle appears
       		* Line 4, the triangle disappears
       		

     • Add a smile and triangle on the left side.
     • Animate the text so that
       • Each line appears on click.
       • Each objects appears/disappears with the appropriate line.
     • Apply a single translation to all slides in the show.
     • 
7. Save this PowerPoint presentation.
8. Upload your final results.
   • Upload Location:
     • Most should upload their work to the Final Exam folder on dropbox.
     • Students taking the test at OSD please upload to the OSD Final Exam folder. Anyone not taking the test at OSD who places a test in this folder will suffer a 10% penalty in addition to a 10% penalty for each half hour the test is submitted after the folder for their class closes.
     • Students who are taking the test outside of their normal exam time should upload their test to the OSD Final Exam dropbox folder. The 10% penalty does not apply in this case.
     • If you miss the folder close time, you may email the test to dbennett@edinboro.edu. There is a 10% per 10 minute penalty for this based on the time stamp on the email.
   • You should upload the following:
     • The workbook from steps 1-4.
     • The PowerPoint presentation from step 6.
   • Work from missing files will be assigned a 0 for that portion of the test.
   • Make sure that your files are submitted. You will not be contacted for missing work.