Be lazy—IT people make the computer do the work because IT work is too hard to do manually.

In this lab exercise, we'll work with spreadsheet formulas, functions and relative/absolute addresses using Microsoft Excel.

To begin, download this
sample Excel workbook onto your `F:`

drive in your FIT folder.
The **workbook** includes two **worksheets** named
"amortization" and "grading" (see the worksheet tabs at the bottom of the
page).

In this exercise, produce a gradebook worksheet that looks like this sample gradebook. Start by studying this sample. Make sure you think about where you're going before you do anything else. When you're ready:

- Add the appropriate
*weights*to the "weights" row, and sum them up in cell G2 to ensure that they add up to 1.0. To do this sum, use the function call:=SUM(B2:F2)

where`B2:F2`

is the range of cells for all the weights. - Format the weights and their sum as percentages.
- Add the "assignment averages" row that computes the
*project average*for the scores in that column. For this, use the function call:=AVERAGE(B5:B12)

where`B5:B12`

is the range of cells for all the "proj1" scores. Fill this in to the first average cell and then copy it across the averages row using the "fill handle" discussed in class. Remember:Be lazy, don't do the math yourself, and don't hand-type the formula again and again.

- Compute the
*weighted score*for the first student's row using an appropriate formula.- This formula should take the assignment weights as
follows:
=B5*$B$2 +

where*terms for the other projects and the final*`B5*$B$2`

computes the weighted contribution of proj1. (Use the F4 key on your keyboard to turn`B2`

into`$B$2`

.) Note the use of the absolute reference`$B$2`

to refer to the weight of proj1. That weight value should be the*same*for*all*students, whereas`B5`

should*adjust*as the formula is copied). - When this is right, copy the formula down to the other students' rows using the fill handle.

- This formula should take the assignment weights as
follows:
- The weighted score computation you just entered has a bit too much
typing and won't automatically upgrade itself if you add a new column—it's not lazy enough! As a
contrast, enter in a
*second*formula for the first student's weighted score, and use the`SUMPRODUCT`

function, as follows:=SUMPRODUCT(

Don't forget to use relative and absolute references as appropriate. Copy the formula down the column using the fill handle. As a general rule, you can expect Excel to have functions specialized to deal cleanly with situations like this, so be sure to look for them when you feel that you're not being "lazy enough".*cell range of assignment weights*,*cell range of assignment scores*) - Finally,
*sort the student rows*by selecting all rows 5-12, including the students' names and weighted scores, choosing "Data"-"Sort" and selecting "Column G", "Descending". (Troubleshooting hint: you'll get interesting results if you didn't use absolute addressing correctly.) - Change the grader's name to yours.

Be sure to save your modified workbook on your `F:`

drive.

If you have extra time and would like to get some extra credit for this lab, try to solve the following challenge.

You'll produce an amortization table that looks like this sample amortization table. Start by filling out the amortization worksheet (on the 2nd worksheet of the sample spreadsheet you downloaded above) as follows:

- Fill in the monthly "payment" cell (B6) with the appropriate PMT
function:
=PMT(

where the rate, number of payments and starting principal are as specified at the top of the grading spreadsheet you just downloaded. Hints: the specified interest rate is per year, but you need the rate per month; similarly, the number of payments is the number of*interest rate*,*total # of payments*,*starting principal balance*)*monthly*payments; finally, the starting balance should be negative because it is money that you owe. - Consider the first balance (cell E9). You need a reference to the starting principal here since that's the first balance. Use a formula that just refers to the starting principal (cell B3); it'll be odd because there's
*no*computation, just the reference! - Fill in the following columns of row 1:
*Payment*: Set the "payment" column to the computed monthly payment (an absolute reference to cell B6).*Interest*: Set the "interest" column to the appropriate interest charge:

, where the interest rate is in cell B4 and the previous balance is in cell E9.*the interest rate*/ 12 **the previous balance**Principal*: Set the "principal" column to the amount of the principle that is being paid off:

, where the total payment is in cell D10 and the interest payment is in cell C10.*total payment*-*interest payment**Balance*: Set the "balance" column to the "new" balance:

, where the previous balance is in cell E9, and the principle payment is in B10.*the previous balance*-*the principle payment*

When you've entered all of these formulas and functions, make sure that you've used absolute and relative references in the correct places, and then select all of row 1 and drag the row down using the fill handle. The resulting amortization table should look like the sample given above.

Now, use your sheet to compute estimated payments for some major capital item that you might buy (e.g., an expensive car from DPFox here in Grand Rapids or a cheaper used car from Cars.com). Assume a 7% interest rate and then choose a balance and term that would work for you. Include your name, course section and the nature of the item you're working with somewhere on your worksheet and then save it so you an submit it later.

If you are new to Excel or would like more help with it, you can make use of any of the following resources:

- The FIT Excel tutorial page provides basic help for Excel.
- Microsoft also provides tutorial materials for Excel.
- You may also come ask questions in person during either my office hours or the graders' lab hours, as listed in our course syllabus.

Submit your modified gradebook spreadsheet in KnightVision as an attachment
to Lab 3. *If you did the extra credit, say so in the comments section of
your KV submission.*