Lab 3: Due Thursday, Feb. 24, 2000

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.

You are strongly encouraged to start this lab assignment before the scheduled lab. Then you can ask questions about the parts the are causing you trouble during the lab.

Goal:

To learn how to write and format your own spreadsheet.

What to do before the lab:

Read chapter 2 of the text (Grauer & Barber) particularly pages 39-44, 50-57. and 66-67.

The exercise (what to do during the lab):

Part 1: Hands-on Exercise 1 on page 45 of the text. Modify the steps in the text as specified below.

Step 1: Replace the column heading in E3 by "Wt. Avg." to make it clear that this column is weighted average.

Step 2: Remember to save on your floppy if you are using a UC computer.

Step 3: Hint: Use the right arrow key instead of "Enter" when you are entering data row by row.

Step 6: Be sure to read the Cut, Copy, and Paste hints on page 48 for alternate ways to carry out the copy and paste operation. The hints in the colored boxes are valuable. Please make a point to read them.

Part 2: Hands-on Exercise 2,steps 1-7, on page 57 of the text

Wait until you complete Part 3 before carrying out Steps 8 and 9. For privacy reasons, you are encouraged to make up a Social Security number like 111223333 for cell A17 instead of using your real one.

Part 3: Adding some new statistical information.

Use the spreadsheet created in parts 1 and 2. In the "Class Averages" row, calculate the average of the "Wt. Avg." column. Immediately after the row containing "Class Averages" insert new rows for "Median", "Count", "Standard Deviation", "Maximum score", "Minimum score", and "Score range" (the difference between the maximum and minimum). Use formulas to calculate these values in the columns for each of the tests, "Final", and "Wt. Avg." See pages 102-103 for a discussion of the count, average, max, and min functions. You will also need the stdev and median functions. We will study these functions later in the course. Format the new values in an appropriate manner. Remember to save your workbook on your floppy.

Note: You may want to carry out Hands-on Exercise 3 beginning on page 68 for your own benefit. Do not turn in anything for the this exercise.

Part 4: Create and format a spreadsheet.

Create a spreadsheet that will show the balance if a person starts a savings account paying 2.8% APR, compounded monthly savings account with $2000 and then adds a deposit of $50 each month. What is the balance after 4 years? Also find the total of the deposits and total interest paid. Include your name following a "Prepared by" label. After printing the spreadsheet, revise the sheet for $500 initial deposit, $100 a month deposit, 3.5% interest, 4 years. You should not have to change any formulas. Print copies of the revised spreadsheet and the formulas. Hints: Include an assumptions section near the top of the spreadsheet to make it easy to change values. You will need a table with a new line for each month. Include columns for the month number, interest paid, the deposit (optional but helpful) and the current balance.

Optional group project for extra credit: (2 points) Create a spreadsheet that solves the Entrepreneur Case Study on page 79. Print the formatted sheet. Then change the data to that shown below. Note: You should only need to change numerical constants, not formulas. Hint: Changing values is easy if you have an input section near the top of your sheet.

Item	Value
First year sales Annual sales growth Cost of goods Commission Tax rate	$100,000 11% 55% 9% 27%

Include "Prepared by" and your names on the sheet. Print the revised spreadsheet and then the formulas.

What to hand in:

Turn in your output from steps 8 and 9 of part 3. Turn in two formatted spreadsheets and set of formulas for part 4. If you did the group option for part 4, also turn in the two outputs and formulas. Label each of the outputs with the part (and step) number(s).

Also answer the following question referring to the grading spreadsheet: Sometimes students skip exams. The instructor wants to count missed exams as zeros when calculating individual grades but wants to ignore them when calculating class statistics. The instructor is debating whether to indicate skipped exams by typing a zero, a dash (minus sign), or the word "skip" where the grade should go or just leaving the cell blank. What do you recommend? (Hint: Experiment with the various possibilities to see what happens.)

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

Last Updated: 2/16/2000