Computer Science and Computer Engineering 115
Solve it with the Computer!
Spring 2000

Lab 5: Due Tuesday, March 14, 2000

15 points

You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.

Goal:

Work with spreadsheets that use financial, date, if, and lookup functions. Learn to use the goal seek command.

What to do before the lab:

Read through page 122. If you do any of the hands on exercises in Chapter 3 for practice, be sure to save your work in case any of them are assigned later in the course.

Part 1: Create a spreadsheet to solve problem 7, page 130

Make the following modifications to the sheet:
Initial principal$140,000
Increment$20,000
Starting Interest 7.5%
Increment0.5%
Term (years) 30

  1. Shade cells B4:B8 to make it easy for the user to tell that they can be modified.
  2. Add "Prepared by" and "your name" to cells A20 and A21
After printing a copy of the spreadsheet using the values shown in the text, make the changes as shown on the right. You should not have to change any formulas. Print a second copy of the spreadsheet, and the formulas.

Part 2: Create a spreadsheet to solve a modified problem 2, page 126

Create a spreadsheet for problem 2, page 126 with the following modifications:

  1. You can ignore the discussion of "scenarios".
  2. It appears that many of the sales should be wholesale instead of retail. Sales tax is not charged on items that will be resold. Hence insert a new column C labeled "Taxable" that is blank for untaxable sales but contains an "X" if sales tax is to be charged. Initially mark the sales in rows 8 and 10 as being taxable.
  3. Calculate the total tax collected in J21 with an appropriate label in I21.
    Total SaleDiscount Percent
    $0.000%
    $3,0005%
    $5,00010%
    $10,00016%
    $15,00025%
      
    Sales Tax6.5%
  4. Hot Spot Software Distributors has replaced the discount policy described in the text by a multi-layered discount policy. To start implementing the new policy, replace A16:B23 by the table shown on the right. For example, the discount on a sale of $2,999 would be 0% or $0.00 but on a sale of $3,000.00, it would be 5% or $150.00.
  5. Use a function to include "today's" date in cell E18 with an appropriate label above it in E17.
  6. You can change the color scheme as desired as long as the result looks nice when printed.
  7. Print a copy of the spreadsheet.
  8. After reviewing your sheet, management determines some changes are required. The sale in row 10 is not actually taxable. The sale in row 11 was canceled. (Delete the contents of the row.) The discount percents are changed to 0%, 8%, 14% , 18%, 23%. The state raised the sales tax rate to 7%. Make the data changes. You should not have to change any formulas. Print the revised spreadsheet.
  9. Also print the formulas.
Extra credit: (2 points). In order to cancel an order, it was suggested that you should delete the contents of the row. This is undesirable since that erased formulas as well as data cells. Modify all the formulas in rows 5 to 14 so that they show a blank unless the customer name in column A is not blank. Hint: Consider the formula
=IF(ISBLANK(A1), "", 10)
If A1 contains "Word" or "15.6", the formula results in 10. However, if A1 is blank, the formula results in another blank. Delete the information in columns A-D of row 14 and print the results. Make sure your revised formulas are shown in the formula printout.

What to hand in:

For both part 2, include 2 printouts of the formatted sheet and one copy of the formulas. Make sure the formatted printouts are in landscape, fitted to one page.

For more information, contact James Brink
brinkje@plu.edu

Last Updated: 3/2/2000