CSCI 104 Spring 2017, Excel, Practical Portion

For this test you will be working with data related to students attending colleges.

You should perform all computations using excel.

Unless otherwise specified the exact appearance of the document does not have to match the example below, but all of the elements must be in place.

You may ask for help, but I may not provide that help. You may use your notes, book and my on line notes if you wish.

If you get stuck on a portion of the test, move on. Complete what you can.

Please be aware that the dropbox folder will close at 1:50 for the 12:30 class and 4:50 for the 3:30 am class. There are penalties associated with late or incorrectly submitted tests.

There are 10 points which can be deducted for poor, sloppy, or incorrect work. This includes unformated or inappropriately formatted data.

You may use your books, notes and the internet to do this portion of the test. You may not communicate with another person while working on this portion of the test.

  1. Download a copy of this document.
    1. Save the document as LAST_FIRST_EXCEL_TEST
    2. Make sure you can locate the document later.
    3. Open this document.
  2. Go to the Start Work workbook.
    1. Place your name in cell B1.
    2. For this worksheet, you will calculate an amortization table for purchasing a house. The terms of the loan are in the worksheet.
    3. [2 points] In cell B4 compute the Down Payment based on the Cost in cell B2 and the Percent Down in B3.
    4. [2 points] In cell B5 compute the Amount Financed based on the Cost in cell B3 and
    5. [7 points] In cell B8 compute the Monthly Payment for this loan based on the information provided.
    7. [10 points] Build an amortization table for this loan in cells a12:e371
      • Compute the starting Balance in cell B12. This is just the Amount Financed.
      • Using the simple interest formula (i=prt) compute the Interest due in C12
      • Using the Monthly Payment and the Interest compute the amount applied To Balance in cell D12
      • Using the Balance and the amount applied To Balance compute the New Balance in cell E2.
      • Complete the table.
  3. Go to the New Students workbook. The data on this page represents students who are interested in attending college. For all students do the following:
    1. [3 points] Using the Major Code from column B, lookup the Major based on the table on the College Degrees worksheet. Place the major in column C.
    2. [3 points] Using the Major Code from column B, lookup the College based on the table on the College Degrees worksheet. Place the college in column D.
    3. [3 points] Using the College in column D, look up the Dean using the table on the Data worksheet. Place the dean's name in column E.
    4. [5 points] Decide if the student should be admitted.
      • Using the GPA data stored in column F,
      • And the Admit GPA on the Data worksheet,
      • place a Yes in column G if the student's GPA is greater than or equal to the Admit GPA.
      • Place a blank in column G otherwise.
    6. [3 points] Format the table to match the picture above.
      • Column headings in bold.
      • Word Wrap
      • Column widths set.
      • Column headings underlined.
      • GPA to two decimal points.
    7. [5 points] Count admitted and rejected students. Place the results in J2 and J3.
  4. Go to the State System Data workbook. All data on this page is in the form of a percentage.
    1. [5 points] Compute statistics on the table.
      • In row 16, compute the minimum of each column.
      • In row 17, compute the maximum of each column.
      • In row 18, compute the average of each column.
      • Label each of these appropriately.
    2. [5 points] On this worksheet, insert a column chart that compares the four and six year graduation rate between all schools.
      • The title should be "Four and Six Year Graduation Rates by School"
      • Label the y axis as "Percent"
      • Do not have an x axis label.
    3. [5 points] Using the Four Year Graduation Rate and the Percent Receiving Pell Grants draw a line chart comparing these two rates.
      • The title should be "Comparison of Pell Grants and Graduation Rates"
      • Label the y axis as "Percent"
      • Add data labels to centered on the data points.
  5. Move to the College Degrees worksheet
    1. [5 points] On this worksheet, make a pie chart showing the Degree and Enrolled for degrees in the Professional College.
      • This should have callouts for data labels.
      • And should have an appropriate title.
  6. Save your workbook and upload it to the appropriate drop box folder