Lab 3 Writeup

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).

A Grade Book

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 + terms for the other projects and the final

    where 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.
• 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(cell range of assignment weights, cell range of assignment scores)

  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".
• 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.

A Challenge: The Amortization Table

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(interest rate, total # of payments, starting principal balance)

  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 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: the interest rate / 12 * the previous balance, where the interest rate is in cell B4 and the previous balance is in cell E9.
• Principal: Set the "principal" column to the amount of the principle that is being paid off: total payment - interest payment, where the total payment is in cell D10 and the interest payment is in cell C10.
• Balance: Set the "balance" column to the "new" balance: the previous balance - the principle payment, where the previous balance is in cell E9, and the principle payment is in B10.

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.

Getting More Help

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.

Submitting your Work

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.