Microsoft Office Tutorials and References
In Depth Information
11. In cell K16, enter the PMT function to calculate the monthly payment needed to pay
off this loan, and then edit the formula to make the value displayed in the cell
positive by placing a negative sign in front of the PMT function.
12. Sergei and Ava want to be able to view their monthly cash ﬂ ow under both mortgage
plans. The mortgage being applied to the budget will be determined by whether 1 or
2 is entered into cell E3. To switch from one mortgage to another, do the following:
a. In cell C26, enter an IF function that tests whether cell E3 equals 1. If it does,
return the value of cell K8; otherwise, return the value of cell K16. Use absolute
cell references for all references in the formula.
b. Use AutoFill to copy the formula in cell C26 into the range D26:N26.
c. Verify that the values in the range C26:N26 match the monthly payment for the
ﬁ rst mortgage condition. (Note that the worksheet will display the monthly
payment amount to the nearest dollar.)
13. In cell E3, change the value from 1 to . Verify that the monthly payment for the 2
ond mortgage appears in the range C26:N26.
14. Sergei and Ava want to maintain an average net cash ﬂ ow of at least $1,000 per
month. Is this achieved on either mortgage plan?
15. Save and close the workbook, and then submit the ﬁ nished workbook to your
instructor, either in printed or electronic form, as requested.
If you have a SAM 2010 user profile, your instructor may have assigned an
autogradable version of this assignment. If so, log into the SAM 2010 Web site at
www.cengage.com/sam2010 to download the instructions and start files.
Case Problem 1
Data File needed for this Case Problem: Chemistry.xlsx
Chemistry 303 Karen Raul is a professor of chemistry at a community college in
Shawnee, Kansas. She has started using Excel to calculate the ﬁ nal grade for students in
her Chemistry 303 course. The ﬁ nal score is a weighted average of the scores given for
three exams and the ﬁ nal exam. One way to calculate a weighted average is by
multiplying each student’s exam score by the weight given to the exam, and then totaling the
results. For example, consider the following four exam scores:
Exam 1 = 84
Exam 2 = 80
Exam 3 = 83
Final Exam = 72
If the ﬁ rst three exams are each given a weight of 20 percent and the ﬁ nal exam is given
a weight of 40 percent, the weighted average of the four scores is:
84*0.2 + 80*0.2 + 83*0.2 + 72*0.4 = 78.2
Karen has already entered the scores for her students and formatted much of the
workbook. You will enter the formulas and highlight the top 10 overall scores in her class.
Complete the following:
1. Open the Chemistry workbook located in the Excel3\Case1 folder included with
your Data Files, and then save the workbook as Chemistry 303 Final Scores .
2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. In the First Semester Scores worksheet, in cell F17, enter a formula to calculate the
weighted average of the ﬁ rst student’s four exams. Use the weights found in the
range C8:C11, matching each weight with the corresponding exam score. Use
absolute cell references for the four weights.
4. Use AutoFill to copy the formula in cell F17 into the range F18:F52.
5. In cell B5, use the COUNT function to calculate the total number of students in
6. In cell D8, calculate the median score for the ﬁ rst exam.
7. In cell E8, calculate the maximum score for the ﬁ rst exam.
8. In cell F8, calculate the minimum score for the ﬁ rst exam.