Final Exam

CSCI 104

Practical Form 2

• You may print this document if you wish.
• Please turn in the written portion now
• When you are finished, please email a copy of your workbook, word document and power point presentation in a single email message to dbennett@edinboro.edu. If you fail to do this, you will have 20 points deducted from your test.
• When you are finished, please print a copy your word document and presentation.
• You may use your book, notes and on line help.
• All values should be formatted correctly, (with $, or % when required)
• All Columns should be auto fit.
• When in doubt, see the examples.

1. Excel Portion
   1. Loan Information
      • Start Excel.
      • [1 point] Put your name in cell A1.
      • For this test we will be computing the cost of a house.
      • You must label all cells and format them appropriately, including cell width and height, or points will be taken off.
      • In cell A3, enter Price, and put 100,000 in B3
      • Enter Percent Down in A4 and put 15% in B4.
      • Enter Down Payment in cell A5.
      • [1 point] Calculate the down payment by multiplying the price by the percent down.
      • Enter Finance Price in cell A6.
      • [1 point] Compute the finance price in cell B6 by subtracting the Down Payment from the Price.
      • [1 point] Enter Terms of Loan in cell A8, merge this with cell B8.
      • Enter Interest Rate in cell A9, and 5% in cell B9
      • Enter Monthly Interest in cell A10.
      • [1 point] Compute the monthly interest in cell B10 by dividing the interest rate by 12.
      • [1 point] Format the monthly interest rate to two decimal places.
      • Enter Years in cell A11 and 30 in cell B11.
      • Enter Monthly Payment in cell A12.
      • [2 points] Compute the monthly payment in call B12.
        • Use the payment function (=pmt())
        • The first argument is the Monthly interest rate
        • The second argument is the number of years * 12
        • The third argument is the finance price times -1.
        • The value for this set of parameters should be $456.30
      • [1 point] Rename sheet 1 to be Loan Information
      • Example Image
   2. Mortgage Table
      • Move to Sheet2
      • Place your name in cell A1
      • [1 point] Rename this sheet to Mortgage Table
      • Place Extra Payment in cell A3, and $10 in cell B3.
      • Place Payment Number in cell A5
      • Place Balance in cell B5
      • Place Interest in cell C5
      • Place Principle in cell D5
      • Place Extra in cell E5
      • [2 points] Number Cells A6 through A365 sequentially 1 through 360, center these numbers.
      • [2 points] Link Cell B6 to the Finance Price on the Loan Information Sheet.
      • [2 points] Compute the monthly interest in cell C6
        • by multiplying the current balance (b6) by the Monthly Interest in on the Loan Information Sheet.
        • Make this formula so that you can copy it down without the Monthly Interest changing.
      • [2 points] Compute the monthly principle in cell D6
        • Subtract the monthly utterest from the Monthly Payment on the Loan Information sheet.
        • Make this formula so that you can copy it down without the Monthly Payment changing.
      • [1 point] Reference cell B3 in the extra field. (Cell E6). Again, you should be able to copy this formula down without the reference changing.
      • [1 point] Compute the new balance in cell B7
        • Subtract the Principle and extra payments (Cells D6 and E6) from the current balance (B6)
      • The value in B7 should be $84,887.87
      • [3 points] Copy B7 to B8:B365
      • [3 points] Copy C6:E6 to c7:E365
      • [2 points] Split the screen horizontally.
        • Position the top screen so that rows 1 through 8 are visible.
        • Position the second window so that payment 240 is visible.
      • 
      • [4 points] Use Goal Seek to determine the extra payment required to pay the mortgage off on the 240^th payment.
        • You want the value of cell B246 to be 0
        • You will change the value in cell B3.
      • 
   3. Graph
      • [4 points] Build a line graph with markers displaying interest and principle payment for the first 12 payments.
      • [1 point] Use the title The First Year
      • [1 point] Use a Y axis label Amount
      • [1 point] Use a X axis label Payment Number
      • [1 point] Show the data table in the graph.
      • 
   4. Save your workbook.
2. PowerPoint Portion
   1. Start Power Point.
   2. [3 points] Insert a title slide,
      • Title: Buying A House
      • Body:

        	   
        	CSCI 104 Final Exam
        	By 
        	Your Name
        	
        	   

   3. [ 3 points] Second Slide
      • Title: The Mortgage
      • Body: Cells A3:b12 of the loan information sheet.
   4. [3 points] Third Slide:
      • Title: The First Year
      • Body: The Graph from the worksheet.
   5. [2 points] Set the entire slide show so that it advances the slides automatically after 3 seconds for the entire show.
   6. [2 points] Set the body of the title slide so that it fades in.
   7. [1 points] Set the transitions between slides so that they uncover up.
   8. [1 point] Apply a common design to all slides.
   9. Save your slide show.
   10. Print your slide show in handout mode, 4 slides per page.
   11. 
3. Word Portion
   1. Start Word.
   2. [2 points] Place your name in the upper left hand corner of the header and Final Exam in the upper right hand corner.
   3. [1 point] Insert word art Your New House centered at the top of the page, under the header.
   4. [2 points] Insert the following in header 1 format
      • The Mortgage
      • Early Payment Information
      • The First Year
      • The Presentation.
   5. [4 points] Insert a table of contents.
   6. Under The Mortgage
      • Insert

        Here is information about your mortgage. 
        General information about mortgages can be 
        found at wikipedia.

      • [1 point] Make the word wikipedia a link to http://en.wikipedia.org/wiki/Mortgage
      • [2 points] Insert a table from Cells A3:B12 of the Loan Information sheet. This should be embedded, not linked.
   7. [1 points] Under Early Payment Information insert
      • If you pay an extra $XXX.XX each month, you will be able to pay your mortgage off in 20 years.
      • Place the amount from cell B3 of the Mortgage table in place of $XXX.XX
      • [1 points] Add a footnote to years that states Please contact me for other payoff plans
      • Insert

        More information om mortgages can be found in .
        	   

      • [2 points] Insert a citation to Mortgages for Dummies 3^rdedition, 2008, by Eric Tyson and Ray Brown, published by Wyley & Sons, New York.
   8. [2 points] Under The First Year, insert a table from the Mortgage Table of the worksheet, cells A5:E17
   9. [2 points] Insert The graph from the worksheet.
   10. [1 point] Place the chart and graph next to each other.
   11. [2 points] Embed your powerpoint presentation under The Presentation
   12. [5 points] Autonumber the pages in the footer.
       • The first page should begin with roman numeral I
       • The second page, and all pages that follow should be numbered starting at 4, using the -#- format.
   13. [4 points] At the end of the document, insert a bibliography.
   14. Update your table of contents.
   15. Make sure your document fits on two pages.
   16. Print this document.
   17. 
4. Test Submission:
   1. Create a SINGLE email message. Attach your worksheet, word document and powerpoint presentation to this message.
   2. Send this email message to dbennett@edinboro.edu.
   3. If you fail to do this, you will receive no credit for the practical portion of the test.
   4. Turn in your two page word document as well as your 1 page power point document.