CSCI 104, Fall 2008, Excel Test

• 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 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 of both worksheets.
• 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 example at the bottom of the page.

For this test you will be calculating the effect of interest rate on a Certificate of Deposit (CD).

1. Download and save This Worksheet
2. [1 point]
   • Place your name in cell A1 of the first and second worksheets.
   • Merge cells A1 and B1,
   • Center your name and place it in the heading 1 style.
3. [1 point] Merge cells A2:C2, center the text, place it in heading 3 style.
4. [1 point] Merge A3:B3, A4:B4, A5:B5, A6:B6. Place the text in each of these cells in heading 4 format.
5. [1 point] In Cell c6 divide the annual interest rate (cell c4) by the periods per year (c5) to compute the periodic interest rate.
6. [2 points]
   • Place CD Growth Rate in cell A8.
   • Merge cells A8:L8.
   • Center the value and place it in title style
   • Apply a thick boarder to the bottom of this cell.
7. [1 point] Place Period, Balance, and Interest in cells A9:C9
8. [1 point] Copy these to cells D9:F9, G9:I9 and J9:L9
9. [1 point] Format the text in cells A9:L9 with heading 4 style.
10. [ 2 points]
    • Place the numbers 1-16 in cells A10:A25
    • Place the numbers 17-32 in cells D10:D25
    • Place the numbers 33-48 in cells G10:G25
    • Place the numbers 49-64 in cells J10:J25
    • Center all of these values.
11. [1 point] In cell B10, place the formula =C3
12. [1 point] In cell C10, calculate the interest earned for the first period (B10 * periodic interest rate). Use a cell reference so that the formula may be copied safely to cells C11:C25)
13. [1 point] In cell B11, calculate the new balance (Previous balance plus interest)
14. [4 points] Copy the formulas to B12:B25 and C11:C25
15. [1 point] Calculate the balance in Cell E10 from the balance in B25 and the interest in C25.
16. [5 points] Complete the table (A10:L25)
17. [2 points] In cell I27, enter Final Value, Merge I27:J27, place the text in cell style accent 2.
18. [1 point] Put the sum of K25 and L25 in cell K27
19. [1 point] Center all values (B28:B32), merge cells when necessary, Place B28 in heading 1 style.
20. [1 point] In Cell D29, calculate the average of all interest values.
21. [1 point] In Cell D30, calculate the Maximum of all interest values.
22. [1 point] In Cell D31, calculate the Minimum of all interest values.
23. [1 point] In Cell D32, calculate the percentage increase from the first interest value to the last.
24. [ 2 points]
    • Rename sheet1 to be CD Growth Rate
    • Rename sheet2 to be What If Page
    • Delete sheet3
25. Change to the What If Page sheet.
26. [1 point] Link cell B3 to cell C3 on the CD Growth Rate sheet.
27. [2 points] In cells B6:H6, calculate the number of compounding periods by multiplying the number of years (B5:H5) by the periods per year on the CD Growth Rate sheet.
28. [1 point] Link cells B7:H7 to the periodic rate field on the CD Growth Rate sheet.
29. [5 points] In cells B11:H11, set the value to 30,000 by changing the value in B10:H10
30. [1 point] Set B12 by multiplying the value in B10 by the periods per year on the CD Growth Rate sheet. Copy this computation to cells C12:H12
31. [1 point] Format cells B12:H12 as percentages, show four places to the right of the decimal point.
32. [5 points] Create a 3D ribbon chart
    • Use years as the x-axis value
    • Use the APY in B12:H12 for the data.
    • Title your chart Annual Interest Rate
    • Fill the back wall, sides, and floor of the chart.
33. Save this workbook and email a copy to dbennett@edinboro.edu
34. Make sure your name is on both worksheets.
35. Print both worksheets - but read the first point below first.
    • You will need to print the first worksheet in landscape mode.
    • Collect your printouts from the graduate assistant at the printer.
    • Hand your printouts to your instructor.