Download this worksheet. Save it to your work drive.
Open the worksheet and convert it to excel 2007 format.
Place your name in the header of each worksheet.
The following work should be done on the Mortgage worksheet. We will be computing the monthly payment for a home mortgage, as well as the total cost of interest.
(1 pt) Compute the down payment (in cell B5) , based on the price and percentage down.
(1 pt) Compute the amount financed (in cell B5), based on the price and the down payment.
(3 pts) Compute the periodic payment in cell B10. In this case, we want to be able to change the number of payments per year, so use the value in cell B6 for payments per year.
(1 pt) Compute the total amount paid to the bank in cell B12. This should be based on the number of years, periodic payment and payments per year.
(1 pt) Compute the Total Interest Paid in cell B13. This is based on the loan total as well as the amount financed.
My worksheet looked like this.
Change to the Rent vs Buy worksheet.
(1 pt) Link the monthly payment in cell B6 to the computed monthly payment on the previous page.
(1 pt) In cells A9 through A33 place the year of the loan (1 through 25) In cells B9 through B33 place the Actual year, starting in 2008.
(3) points) In cells C9 through C33 compute the yearly amount paid for a loan. In this case, assume the landlord raises the rent by the percentage in cell B5 on Jan 1 of a given year. You should develop a formula in cell C10, which is copied to all other cells in the column without change.
(1 pt) Place the annual mortgage cost in cells D9 through D33
(1 pt) Place the difference between the cost of rent and the cost of the mortgage in cells E9 through E33.
Here is a partial shot of my work
(4 pts) Build a graph based on the data. Graph the cost of the mortgage vs the cost of renting in a line chart. Your graph should look something like this.
Change to the Gradebook tab. We will compute the grades for a set of students.
(1 point) In cell F9, compute the total number of points possible. This is based on the points available for each homework, in cells B9 through E9.
(1 point) In cells F3 through F7, compute each student's homework average, based on their homework score and the total number of homework points in cell F9.
(1 point) In cells I3 through I7, compute the student's class average. This is the average of the percentage scores for homework, and the two tests.
(2 points) In cells J3 through J7, place the word Pass in the cell if the student's final average exceeds the average in cell C11, otherwise place the work Fail
(1 point) Compute the number of students who pass in cell C13.
(1 point) Compute the number of students who fail in cell C14
(3 points) Compute the High, Low and Average scores in cells G11 through G13
Here is a shot of my final worksheet:
Word
Start Word.
(1 pt) In the header put your name, Final Exam CSCI 104, and the date.
(1 pt) Insert the page number into the footer.
(1 pt) At the top of the page, insert word art that says Your Home Loan
(2 pts) Add the following lines. Mark each as style heading 1.
The Price and Down Payment
The Terms of the Loan
The Cost of the Loan
Renting vs Buying
(1 pt) Insert the following text:
The price and down payment for the house you have selected are as follows:
(1 point) From the Mortgage worksheet, insert cells A2 through B5 so that when the worksheet is changed, the data in the word document will update. Place this under the The Price and Down Payment header.
(1 point) From the Mortgage worksheet, insert cells A7 through B10 so that when the worksheet is changed, the data in the word document will update. Place this under the The Terms of the Loan header.
(1 point) From the Mortgage worksheet, insert cells A12 through B13 so that when the worksheet is changed, the data in the word document will update. Place this under the The Cost of the Loan header.
(1 point) From the Rent vs Buy worksheet, insert cells A4 through E20 so that when the worksheet is changed, the data in the word document will not update. Place this under the Renting Vs Buying header.
(2 pts) Place text of the document it two columns, but have the word art in the heading extend over the entire page.
(2 pts) Insert the graph from the Rent vs Buy worksheet at the bottom of the document.
Make sure this fits on one page, and that the headings are above the associated chart.
Here is a shot of my final document
Print this document.
PowerPoint
(3 points) Create a PowerPoint presentation from your word document.
(2 points) Add a title slide, Purchasing A House as the title and
CSCI 104, Final Examby Your Name.
On the The Price and Down Payment slide.
(2 ponts) Insert the following bullet points.
Negotiate the price with the seller.
Negotiate the down payment with the bank.
(2 points) Animate these points so that they are displayed one at a time, when you click the mouse.
On the The Terms of the Loan slide
(2 points) Insert A7 through B10 of the Mortgage worksheet below the title in normal view (show numbers).
(3 points) Insert A7 through B10 of the Mortgage worksheet below the previous chart, but put it into formula view.
(1 pt) Delete the The Cost of the Loan slide.
(2 pts) Place the graph from the Rent vs Buy worksheet on the Renting vs Buying slide.
(2 pts) Apply a common theme.
(2 pts) Apply a common transition to all slides.
Print your slide show in handout mode, 6 slides per page.
Finishing Up
Email a copy of the worksheet and the word document to me.
Place them both in the same email message.
The subject of the message should be Final exam your name
The body of the message should contain your name and what class you are in. (MWF, TR)
Hand in your two printed items, there should be two pages.
