CSCI 104 Spring 2009 Final Exam Practical Portion

• Turn in the written portion of your test now.
• You may print these instructions if you wish.
• If you get stuck on a portion, move on the the next part.
• When you are finished, make sure that you email all documents to dbennett@edinboro.edu. If you fail to do this, you will receive no credit for this part of the test.
• Make sure that all cells are properly formatted. Failure to do this will result in loss of points.

Excel

1. Exercise Data.
   1. Make a new folder for your final exam work space.
   2. Save exercise.txt to your new folder.
   3. Start excel, import this file which is tab delimited.
   4. [1 point] Sort the data alphabetically exercise. (Do not sort the Exercise row, which is a column heading.)
   5. [1 point] Center and merge the first two rows.
   6. [1 point] Make the first row bold, 16 point.
   7. [1 point] Delete the empty third row.
   8. [1 point] Make the exercise names, as well as the weights bold.
   9. [2 point] Freeze the panes above and to the right of cell B4.
2. Workout Table.
   1. Enter the following data.

      Cell	Value
      B41	Weight
      B42	Column
      B44	Exercise
      B45	Swim
      B46	Tennis
      B47	Walking
      B48	Total
      C41	146
      C44	Time
      C45	20
      C46	30
      C47	10
      D44	Calories

   2. [3 points] In Cell C42, using a function or formula, find the column number corresponding to the weight in cell C41. The weight should be properly rounded to the nearest 10. (Note, 146 should return an 8, 144 should return a 7)
   3. [3 points] Using a formula, find the calories burned for each exercise (cells D45:D47). This depends on the weight column (C42), exercise (B45:b47), Exercise Chart (A4:j38) and the time exercising (C45:C57). Find the calories in the table, divide by 60 to find calories per minute, then multiply by the minutes.
   4. [2 points] In Cells C48 and D48 calculate the total time and total calories.
   5. [3 points] Insert a pie chart showing the calories burned as a pie chart, show the percentage for each exercise in the chart.
   6. [2 points] Format C41 and B45:C47 with a yellow background to show that these values can be changed. Format All labels in this section in bold. Use a thick border under the titles in row 44.
   7. Your work should resemble this image:
3. Loan Information
   1. Start a new sheet called Loan Information
   2. Enter the following data.

      Cell	Value
      A1	Name
      A2	Amount
      B2	100,000

   3. Place your name in cell B1.
   4. [1 point] Enter the number 10 through 40 by 5 in cells B4:H4. Place a wide border UNDER these cells.
   5. [1 point] Enter 4.00% through 7.00% by .25% in cells A5:A17. Place a wide border to the Right of these cells.
   6. [5 points] Enter a formula in cell B5 which will calculate the payment required to pay off a loan for the amount in cell B2, time period in years specified in B4:H4 and interest rate in A5:A17. This formula should be such that you can copy it to cells B5:H17 without changes.
   7. [3 points] Copy the formula to B5:H17
   8. 
4. Interest Table
   1. Make a copy of the row and column labels for the previous table in cells A21:H34
   2. [5 points] Compute the total interest for each loan in this new table. To do this
      • Multiply the monthly payment (from the previous table) by the total payments (b21:h21) and subtract the amount financed (B2)
      • Copy this formula to all cells in the new table.
   3. [3 points] Use conditional formatting to highlight any cells in this table where the interest payment is higher than the amount financed.
   4. [2 points] Apply conditional formatting to display data bars in this table.
   5. 
5. Save this worksheet as "excelFinal". Make sure you save this as an excel file and not a text file.

PowerPoint

1. Start PowerPoint
2. [5 points] Title Slide
   1. Create a title slide
   2. Title: Exercise is Good
   3. Author: Put in your name.
3. [5 points] Slide 2
   1. Insert a new slide.
   2. Title: Exercise Table
   3. Body:
      • Insert a link to the table B44:D48 on the exercise sheet (Step 2 in Excel)
      • Embed the pie chart from the same worksheet.
4. [2 points] Make the table and the chart appear on different clicks.
5. [2 points] Add a common design to the slide show.
6. [1 point] Place your name in the footer of all slides.
7. 
8. Save your presentation as pptFinal

Word

1. Start Word. Header
   1. [1 point] Insert your name in the header. Center this.
   2. [1 point] Enter the date under your name, make this update automatically.
2. [1 point] Copy the text from http://www.americanheart.org/presenter.jhtml?identifier=2155
3. [1 point] Mark "Activity in Your Daily Life" as a title.
4. [2 points] Mark each of the subheadings (bold in the original text) so they will appear in the table of contents.
5. [5 points] Insert a table of contents after the title, before the text. Generate this automatically.
6. [2 points] Insert a page number in the center of the footer. Make the page numbers (i, ii, ...)
7. [2 points] Insert a footnote to the title which is a link to the web page.
8. [1 point] Place the points for the "At Play" section into two column mode.
9. [2 points] Paste the pie chart from the exercise worksheet in the middle of the "At the Office" points, wrap the text around this picture.
10. [1 point] Insert the caption "Working Out" below this pie chart.
11. [2 point] At the end of the document insert a table of figures. Place a the title "Table of Figures" on this table so it will appear in the table of contents.
12. [1 point] Insert a citation to "Go Dogs Go", by P.D. Eastman by the section heading "At Play". (You must find this information on the web).
13. [2 points] Insert a bibliography at the end of the document.
14. Make sure that your table of contents is up to date.
15. Save the document as wordFinal
16. 

Finishing the Test

1. Change cell B47 of the exercise worksheet from "Walking" to "Soccer"
2. Change cell B2 of the Loan Information worksheet from "100,000" to "250,000".
3. Save a current copy of all three documents.
4. Create an email and attach a copy of all three documents. Send the email to dbennett@edinboro.edu.