Fall 2007 Excel Test

• Please follow all of the instructions on this page. You need to include all of the elements described, but your final product may have slight variations.
• If you get stuck on a portion, skip it an finish the rest of the test. Return to the portions you skipped when you have finished the portions you can do.
• If you need data from one step to do another step, but you can not do the first step, you may put in dummy data. Please make a note next to this data explaining that you did this.
• Make sure you save often. A crashing computer is not an excuse for not completing the test.
• Make sure you print out a copy of the final test and turn it in.
• Make sure you email a copy of the final test to dbennett@edinboro.edu
• You may print a copy of these instructions.
• You may use your books and notes on this portion of the test.
• Please format everything appropriately. Points will be taken off for poor formating.
• In my examples, I have colored yellow numbers which should be typed in. All other numbers should be computed.

1. Download this worksheet. You should save this and then open the file in excel.
2. (1 point) In the header (On each page, you will use three worksheets) put
   • Your Name
   • CSCI 104
   • Fall 2007 Excel Test
3. Start a new worksheet
4. (1 points) Label the worksheet A House Purchase
5. (5 points) In cells B2:C2
   • Merge the cells
   • Insert the text A House Purchase
   • Change the font to 16 point
   • Change the font style to Broadway
   • Change the text to bold.
   • Center the text horizontally and vertically in the box.
6. (3 points)

   In Cell	Place
   B4	Price of Home
   B5	Percent Down
   B6	Down Payment
   B7	Amount Financed
   B9	Points
   B10	Cost of Points
   B11	Finance Charges
   B12	Due at Closing
   B14	Interest Rate
   B15	Years Financed
   B16	Payment
   B18	Total Cost of House
   B19	Cost of Interest
   C4	$150,000
   C5	10%
   C9	3
   C11	$1,385.00
   C14	5.34%
   C15	30

7. (2 points) In cell C6 calculate the down payment based on the price of the house and the percent down.
8. (1 point) Calculate the amount financed in cell C7 by subtracting the down payment from the price.
9. (1 point) When purchasing a house, the bank charges points. Each point costs 1% of the amount financed. In cell C10, compute the price of the points.
10. (1 point) At closing, the buyers must pay the down payment, the points, and the finance charges. Compute the total due at closing and put this in cell C12
11. (3 points) In Cell C16, compute the monthly payment based on the interest rate, years financed, and amount financed.
12. (2 points) Compute the total cost of the loan in cell c18.
13. (2 points) Compute the total amount of interest paid in cell C19.
14. (1 point) Place a box around B2:C19
15. (5 points) Create a pie chart displaying the elements that contributed to closing costs. This includes points, down payment and finance charges.
16. (3 points) Using Goal Seek find the most expensive house a buyer can afford, with the given conditions, if they desire to have a $700 monthly payment.
17. Here is a shot of this work.
18. On a new worksheet, labeled Investment Value
19. (2 points)

    In Cell	Place
    B3	Price of House
    B4	Yearly Increase
    C4	2%
    B6 and D6	Year
    C6 and E6	Value
    B23	Total Percentage Increase

20. (2 points) In Cell C3, link to the price of the house on the previous sheet.
21. (1 point) In Cells B7:B21 put the number 1 to 15, in D7:D21 put the number 16 to 30.
22. (3 points) In cells C7:C21 and E7:E21 calculate the estimated value of the house, based on the starting price in C3 and the yearly increase in C4, compute the estimated value of the house at the end of each of the years of the loan.
23. (2 points) In Cell D23, compute the total percentage increase in the value of the home from the starting value in C3 to the estimated final value in E30.
24. Here is a shot of my final work.
25. Change to the worksheet labeled Grades
26. This worksheet will change values in cells C4:L21 when it recalculates. Don't worry about this
27. (2 points) In M4:M21, compute the sum of the scores for each student.
28. (2 points) In N4:N21, if the student has turned in all 10 quizzes, drop the lowest score and replace it with a score of 10
29. (2 points) In O4:O21, Put the word Pass if the score is above the minimum in cell G23, otherwise put a pass. (If you can't compute the score for column N, base this off the score in column M).
30. (2 points) In Cell G24, compute the number of people who received a "Pass", in Cell G25, compute the number of people who received a "Fail"
31. (1 point) In cells M23:M25 compute the max, min and average scores.
32. My final image

• Email the test to dbennett@edinboro.edu
• Print a copy of the test.