Computer Science and Computer Engineering 115
Solve It with a Computer
Fall 2006



Lab 6

Lookup and if functions

The goal for this lab is to help you develop and demonstrate your skills using Lookup and IF functions. It will also help you review some other spreadsheet topics including a financial function. Before doing this lab you are strongly urged to study pages 187-188 and work though Hands-On Exercise 3 from Chapter 4. Pages 189-192 might be helpful.

This lab is a is a continuation of Lab 2. You will develop an invoice for Value Electronics Center They have already started development of a spreadsheet that includes a page of various tables that will be needed and a page containing parts of the inventory page from Lab 2.

1. Download "lab06.xls" from the Excel download pages in the class web site. It is in the folder http://www.cs.plu.edu/courses/csce115/fall06/download/excel/
2. Familiarize yourself with the pages in the workbook. You will need to add formulas to the Invoice page in the cells with the "orange tan" color. The other two pages do not require any changes (except possibly formatting).
3. Complete the Invoice page primarily by adding formulas. The following notes were provided by your boss.
   1. To create an invoice the sales clerk will fill in the "Sold to" information. Then they will fill in the Taxable and Preferred customer blanks. They must type "No" if the item is not taxable. They must type "Yes" or "X" if the customer is a preferred customer.
   2. They must type the Item No. and Quantity for each item sold. The sheet will automatically look up the "Description" and "Price each" from the "Inventory" page and calculate the "Extended cost".
   3. On each line, the "Description", "Price each" and "Extended cost" columns should be blank until the sales clerk enters the quantity of the item. Hint: They can be filled with the empty string "" if the quantity is 0 or blank. You may find it easier to ignore this rule until after this section calculates correctly with positive quantities. Then enclose these statements in IF functions that blanks the cell until the quantity is provided.
   4. The "Discount rate" is determined by looking up "Total Purchases" in the appropriate table in the "Tables" page.
   5. The "Final cost" is "Total purchases" less "Discount".
   6. The "Sales Tax @" rate is found by looking up the city on the "Tables" page.
   7. The "Shipping and Handling" amount is found by looking up "Total Purchases" on the "Tables" page.
   8. The "Amount financed" is equal to the "Balance Due" unless it is less than or equal to $0.00. In that case "Amount financed" must read "N/A".
   9. If the "Balance Due" is less than or equal to $0.00, then the remaining blanks in the "Financing" section should be empty. Hint: They can be filled with the empty string "" in this case. You may find it easier to ignore this rule until after this section works properly with positive balance due amounts. Then enclose these formulas in an IF statement.
   10. Preferred customers get a reduced interest rate as found on the "Tables" page. (Use an IF statement for this simple table.)
4. You should format the pages in a professional manner subject to the following restrictions:
   • All three pages must have a uniform look.
   • It must 100% clear where the sales clerk can type using a distinct color for those cells.
   As a bare minimum, you will probably want to get rid of the orange tan color on the "Invoice" page that was used only to indicate where formulas are needed.

Possible extra credit

• Set the protection on the your invoice page to make it "impossible" for the user to accidently type in any cell that is not intended for input. For methods to lock and unlock cells and setting protection you might look at
  http://j-walk.com/ss/excel/faqs/protectionFAQ.htm
  or
  pages 25- 26 of Microsoft Office Excel 2003 Formulas by John Walkenbach which is on reserve in the library for CSCE 120 sections 2 and 4.
  or other references. (5 points)

  If you do this option, it is recommended that you complete the spreadsheet before trying to set the protection to avoid having to go thought the process of locking and unlocking the sheet several times. It recommended that you do NOT use a password on this exercise.

• Provide comments on the Taxable and Preferred customer cells that tell the user how to fill in the cells. (2 points)

Additional Requirement

• Remember to use formulas whenever appropriate. Assume the paper grader will change the data before grading the sheet. Your spreadsheet should correctly solve the problem with the new data.
• Add your name to the end of the Invoice page using "Implemented by xxxx". Also add a revision date as a constant "Last revision: xxxxx".

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and impression they leave the reader.

Submitting your workbook.

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce115, Fall 2006) from the drop down box.
3. Enter your submit system username and password.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook for Lab 6.
6. Select "Lab 6" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date

Monday October 23. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor and will be subject to a 20% per day. (Late assignments turned in over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)

Revised: Oct. 16, 2006