Microsoft Office Tutorials and References

In Depth Information

**Case Problems**

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

the class.

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.