Grading Rubric – Excel 2003

Tutorial 2, Case Problem 3

Class:

Professor:

Notes:

Solution File(s):Sonic2.xls

Description / Pts / Your Score
TODAY() function entered to display the current date on the Documentation sheet / 1
In column C, formula entered to calculate the number of years each employee has worked at the company; for example, the formula in cell C13 is =($B$2-B13)/365 / 2
In column F, IF function entered to determine the amount of each employee’s 401(k) contribution; for example, the formula in cell F13 is =IF(C13>=1,D13*$F$4,0) / 3
In column G, IF function entered to calculate the health insurance cost of each employee; for example, the formula in cell G13 is
=IF(E13=$B$4,$C$4,$C$5) / 2
SUM function entered in range B7:B9 to calculate the total salaries, 401(k) costs, and health insurance costs / 1
Revise the Payroll worksheet under the assumption that the cost of the Premier plan is $7,000 and the cost of the Standard plan is $6,100 / 1
TOTAL POSSIBLE POINTS: / 10

YOUR SCORE: ______

Grading Rubric – Excel 2003

Tutorial 2, Case Problem 4

Class:

Professor:

Notes:

Solution File(s):Soup2.xls

Description / Pts / Your Score
Formula entered in cell B7 to calculate the total number of payment periods / 1
PMT function entered in cell B9 to calculate the monthly payment: =PMT(B8/B6,B7,B4,0,1) / 2
AutoFill used to insert the numbers 1 through 60 in range F5:F64 and the first day of each month in range G5:G64 / 1
In column H, PPMT function entered to calculate the amount of each monthly payment used for paying off the principal; for example, the formula in cell H5 is =PPMT($B$8/$B$6,F5,$B$7,$B$4,0,1) / 1
In column I, IPMT function entered to calculate the amount of each monthly payment that is used for interest; for example, the formula in cell I5 is
=-IPMT($B$8/$B$6,F5,$B$7,$B$4,0,1) / 1
In column J, formula entered to calculate the amount of the principal remaining; for example, the formula in cell J5 is
=-$B$4-SUM($H$5:H5) and the formula in J6 is
=-$B$4-SUM($H$5:H6) / 1
SUM function entered in range B12:B16 to calculate the total amount paid toward the principal after 1, 2, 3, 4, and 5 years / 1
SUM function entered in range C12:C16 to calculate the total interest paid after 1, 2, 3, 4, and 5 years / 1
Formula entered in range B19:B22 to calculate the interest saved if the loan is paid off after 1, 2, 3, or 4 years; for example, the formula in cell B19 is =$C$16-C12 / 1
TOTAL POSSIBLE POINTS: / 10

YOUR SCORE: ______