Final Exam, CSCI 104, Fall 2005

• Please save your work often. You are responsible for this test even if your computer crashes.
• You may print these instructions.
• If you get stuck on one part, put in dummy data and continue with the next portion.
• Make sure that your name is on each document that you submit. If your name is not attached, you will not receive credit.
• Make sure that you turn in all portions. If you don't turn in a portion, you will not receive credit for that portion.
• All pictures have red marks added to prevent copying. You don't need these marks in your product.
• You should format all cells appropriately. Text should be fully visible, currency and percentages should be correctly formatted. I will take points off for sloppy or incorrectly formatted work.
• In the end, you should turn in two pages, a single page document and a powerpoint hand out page.

Spread Sheet Portion

1. Download This Document
   1. You will want to save the file and then open it with excel.
2. [1 point] Rename the worksheets as follows
   • Sheet1 - Quote
   • Sheet2 - Credit
   • Sheet3 - Options
3. Change to the credit sheet.
   • This sheet contains information about the loans available for four different credit ratings. I made this information up.
   • [1 point] Format column C to contain only 1 decimal place.
   • [1 point] Format column D to contain three decimal places.
   • [2 points] Change the headings in row 1 to be Bold, 15 point font.
   • [1 point] Center these headings.
   • [1 point] Allow word wrap in the headings.
   • [1 point] Draw a 5 point box around the entire table (A1-D5)
   • [1 point] Apply a light grey background to the titles.
   • [1 point] Center the text in columns A and B
   • Here is an image of my page.
   • 
4. Change to the options sheet.
   • This data represents options for a 2006 Chevy Colorado. I copied this data from www.chevrolet.com.
   • The first column represents item number, the second a description of that item, and the third the cost of that item.
   • You need not change this sheet, but look it over so you can use it later.
5. Change to the Quote sheet.
   • [1 point] Enter Quote Prepared by in cell A1 and your name in cell A2. Make sure the cells are wide enough to fit the information.
   • [1 point] In cells A4-A7 enter the following
     • Customer Name
     • Credit Rating
     • Monthly Max
     • Down Max
   • [1 point] In cells B4-B7 enter the following:
     • Bob Smith
     • B
     • 375
     • 1000
   • [1 point] Color cells B4-B7 grey. I will use grey cells to show assumptions that you should base other computations off of.
   • [1 point] Place the credit description in cell c5. This description should change when cell b5 is changed.
   • [1 point] Insert a column before column A.
   • [1 point] Insert the following text
     • In cell A9, Num
     • In cell B9, Desc
     • In cell C9, Price
   • Color A10-A15 Grey.
   • Enter the following
     • In cell A10, BASE
     • In cell A11, A28
     • In cell A12, AJ1
     • In cell A13, EN6
     • In cell A14, RDS
     • In cell A15, ZFL
   • [5 points] Place a computation in cell b10 that will look up the number from A10 in the options table and retrieve the description. Make this formal so that you can copy it to cells B11-B15. Copy this formula to cells B11-B15.
   • [5 points] Repeat this with cells C10, and C11-C15, but look up the price.
   • [1 point] Compute the total price in cell C16
   • [1 point] Label B16 with the word Total, right justify this word.
   • [1 point] Place a line between the individual item prices and the total price in cell C16.
   • Here is a picture :
   • 
6. [5 points] Insert a pie chart
   • Show the price breakdown by item ordered.
   • Include only the items in rows 11-15
   • Label the graph Your Colorado
   • Make sure that the option names are displayed in this chart.
7. Customer Loan Computation.
   • [1 point] Insert a new worksheet between the Quote and Credit sheets, call this Loan
   • [1 point] Place the following labels
     • in A2, Required Down
     • in A3, Financed
     • in A5, Interest rate
     • in A6, Period
     • in A7, Payment
     • in B6, 5, and color this cell grey.
     • in A9, Has Down
     • in A10, Has Monthly
     • in A11, Offer Loan
   • [2 points] Compute the down payment based on the customer's credit rating (C5 of the quote page), The credit table, and the price of the truck (C16 of the quote page)
   • [1 point] Compute, in cell b3, the amount financed based on the down payment and the price of the truck.
   • [1 point] Compute the customer's interest rate, based on their credit rating. Place this in cell B5
   • [3 points] Compute the monthly payment for a loan where the amount is in cell C3, interest rate in B5, and period of years in b6.
   • [2 points] If the customer has a sufficient down payment, as indicated by cell C7 of the Quote page, place a yes in cell B9, otherwise place a no
   • [2 points] If the customer has sufficient money for the monthly payment, as indicated C6 of the the quote sheet, place a yes in cell B10, otherwise place a no
   • [2 points] This is a challenge, only do this if you have time
     • Look up how to use the and function.
     • Use the and function, and the if function to place a yes in cell B11 if there is a yes in both cells B9 and B10, place a no in cell B11 otherwise.
   • Here is a picture of this portion
   • 
8. Change the data in the following cells in the Quote sheet
   • C5, A
   • C6, 450
   • A11, K05
   • A12, V76
   • A13, VH4
   • A14, AJ1
   • A15, ZFL

Word Processing Portion

1. [1 point] In bold, 28 point font, centered place the name of your car dealership (I called mine Dan's Chevy).
2. [3 points] Under this place your name - left justified, and CSCI 104, final exam right justified, enclosed in a box under this.
3. [3 points] Add the following text, justified, with 1" indents.

   Thank you for considering purchasing a new car from us. We think that you will find our dealership outstanding, our salesmen knowledgeable and our mechanics the best in the county. If you have any questions or concerns, please contact me immediately.

4. [1 point] Add a footnote after the word immediately. That contains the following text:

   See http://www.carlemon.com/ for information about the Lemon Law.

5. [2 points] Copy the pie chart from your worksheet into this document.
6. [2 points] Copy cells A1-D5 of the Credit worksheet to your document.
7. [2 points] Copy cells A1-D7 of the Quote worksheet to your document.
8. My document looks something like:
9. 
10. Print this document, and collect it from the printer.

Presentation Software Portion

1. PLEASE NOTE: All tables must be expanded to fill the space so that they are legible in handout mode.
2. NOTE AS WELL: The first two tables are in formula view. If they are not, you will not receive credit for most of the excel work.
3. [5 points] Create a title slide, place your name, CSCI 104 and FINAL exam on this slide.
4. [5 points] Second Slide
   • Title the slide, "The Car Loan".
   • Place the Loan worksheet in formula view, and copy A1-B11 to this slide.
5. [5 points] Third Slide
   • Slide Title: The Quote
   • Place the quote worksheet in formula view, copy B10-C16 to this slide.
6. [5 points] Fourth Slide
   • Title: Chevy Colorado
   • Find a picture of a Chevy Colorado on the net and place it on this slide on the left hand side.
   • On the right hand side, place a copy of cells A9-C16 in normal view.
7. [5 points] Apply the "Crayon" template to all of the slides.
8. This is a picture of my presentation
9. 
10. Print this in handout mode, 6 slides per page.