Sierra Pacific Community College District (SPCCD) consists of four individual community colleges. The workbook for this project includes an amortization schedule for student loans and a fee and credit hour summary for several departments.

[Student Learning Outcomes 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7]

File Needed:SierraPacific-02.xlsx(Available from theStart Filelink.)

Completed Project File Name:[your name]-SierraPacific-02.xlsx

Skills Covered in This Project

• Name cell ranges.
• construct and copy formulas.
• Set mathematical order of operations.
• Use absolute references in formulas.
• Insert the current date as a function.
• Use thePMT
• Audit formulas.
• UseSUMIFandSUMPRODUCT.

1. Open theSierraPacific-02.xlsxstart file. If the workbook opens inProtected View, click theEnable Editingbutton so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Set range names for the workbook.
3. Select theStudent Loansheet, and select cellsB5:C8.
4. Click theCreate from Selectionbutton [Formulastab,Defined Namesgroup].
5. Verify that theLeft columnbox in theCreate Names from Selectiondialog box is selected.
6. Deselect theTop rowbox if it is checked and clickOK.
7. Select cellsE5:F7. Repeat steps ad to create range names.
8. Click theName Managerbutton [Formulastab,Defined Namesgroup] to view the names in theName Managerdialog box (Figure 2-90). Notice that the cell references are absolute.
9. Figure 2-90Name Managerdialog box
10. ClickClose.
11. Enter aPMTfunction.
12. SelectC8.
13. Click theFinancialbutton [Formulastab,Function Librarygroup] and selectPMT.
14. Click theRatebox and click cellC7. The range nameRateis substituted and is an absolute reference.
15. Type/12immediately afterRateto divide by 12 for monthly payments.
16. Click theNperbox and click cellC6. The substituted range name isLoan_Term.
17. Type*12afterLoan_Termto multiply by 12.
18. Click thePvbox and put a minus sign (-) to set the argument as a negative amount.
19. Click cellC5(Loan_Amount) for thepvargument. A negative loan amount reflects the lender's perspective, since the money is paid out now (Figure 2-91).
20. Figure 2-91Pvargument is negative in thePMTfunction
21. Leave theFvandTypeboxes empty.
22. ClickOK. The payment for a loan at this rate is $186.43, shown as a positive value.
23. Verify or format cellC8asAccounting Number Formatto match cellC5.
24. Conztruct a total interest formula.
25. Click cellF5(Total_Interest). This value is calculated by multiplying the monthly payment by the total number of payments to determine total outlay. From this amount, you subtract the loan amount.
26. Type=and click cellC8(thePayment).
27. Type*to multiply and click cellC6(Loan_Term).
28. Type*12to multiply by 12 for monthly payments. Values typed in a formula are constants and are absolute references.
29. Type-immediately after*12to subtract.
30. Click cellC5(theLoan_Amount). The formula isPayment * Loan_Term * 12 - Loan_Amount. Parentheses are not required, because the multiplications are done from left to right, followed by the subtraction (Figure 2-92).
31. Figure 2-92 Left-to-right operations
32. PressEnter. The result is $1,185.81.
33. Create the total principal formula and the total loan cost.
34. Select cellF6(Total_Principal). This value is calculated by multiplying the monthly payment by the total number of payments. From this amount, subtract the total interest.
35. Type=and click cellC8(thePayment).
36. Type*to multiply and click cellC6(Loan_Term).
37. Type*12to multiply by 12 for monthly payments.
38. Type-immediately after*12to subtract.
39. Click cellF5(theTotal_Interest). The formula isPayment * Loan_Term * 12 - Total_Interest.
40. PressEnter. Total principal is the amount of the loan.
41. Click cellF7, theTotal_Costof the loan. This is the total principal plus the total interest.
42. Type=, click cellF5, type+, click cellF6,and then pressEnter.
43. Set order of mathematical operations to build an amortization schedule.
44. Click cellB13. The beginning balance is the loan amount.
45. Type=, click cellC5, and pressEnter.
46. Format the value asAccounting Number Format.
47. Select cellC13. The interest for each payment is calculated by multiplying the balance in column B by the rate divided by 12.
48. Type=and click cellB13.
49. Type*(and click cellC7.
50. Type/12). Parentheses are necessary so that the division is done first (Figure 2-93).
51. Figure 2-93 The interest formula
52. PressEnterand format the results (37.5) asAccounting Number Format.
53. Select cellD13. The portion of the payment that is applied to the principal is calculated by subtracting the interest portion from the payment.
54. Type=, click cellC8(thePayment).
55. Type-, click cellC13, and pressEnter. From the first month's payment, $148.93 is applied to the principal and $37.50 is interest.
56. Click cellE13. The total payment is the interest portion plus the principal portion.
57. Type=, click cellC13, type+, click cellD13, and then pressEnter. The value matches the amount in cellC8.
58. Select cellF13. The ending balance is the beginning balance minus the principal payment. The interest is part of the cost of the loan.
59. Type=, click cellB13, type-, click cellD13, and then pressEnter. The ending balance is $9,851.07.
61. The image includes rows 13 through 28 and then rows 60 through72 after you complete Step 7g.
62. Formulas in cells B13:F13
63. B13
64. =Loan_Amount
65. C13
66. =B13*(Rate/12)
67. D13
68. =Payment-C13
69. E13
70. =C13+D13
71. F13
72. =B13-D13
73. Fill data and copy formulas.
74. Select cellsA13:A14. This is a series with an increment of 1.
75. Drag theFillpointer to reach cellA72. This sets 60 payments for a five-year loan term.
76. Select cellB14. The beginning balance for the second payment is the ending balance for the first payment.
77. Type=, click cellF13, and pressEnter.
78. Double-click theFillpointer for cellB14to fill the formula down to row72. The results are zero (displayed as a hyphen in Accounting Number Format) until the rest of the schedule is complete.
79. Select cellsC13:F13.
80. Double-click theFillpointer at cellF13. All of the formulas are filled (copied) to row72(Figure 2-94).
81. Figure 2-94 Formulas copied down columns
82. Scroll to see the values in row72. The loan balance reaches 0.
83. PressCtrl+Home.
84. Build a multiplication formula.
85. Click theFees & Creditsheet tab and select cellF7. Credit hours times number of sections times the fee calculates the total fees from a course.
86. Type=, click cellC7, type*, click cellD7, type*, click cellE7, and then pressEnter. No parentheses are necessary because multiplication is done in left to right order (Figure 2-95).
87. Figure 2-95 Formula to calculate total fees per course
88. Double-click theFillpointer for cellF7to copy the formula.
89. Verify that cellsF7:F18areCurrencyformat. Set a single bottom border for cellF18.
90. UseSUMIFto calculate fees by department.
91. Select cellC26.
92. Click theMath & Trigbutton [Formulastab,Function Librarygroup] and selectSUMIF.
93. Click theRangebox and select cellsB7:B18. This range will be matched against the criteria.
94. PressF4(FN+F4) to make the reference absolute.
95. Click theCriteriabox and select cellB26.
96. Click theSum_rangebox, select cellsF7:F18, and pressF4(FN+F4).
97. ClickOK. Total fees for the Biology department are 13350 (Figure 2-96).
98. Figure 2-96Function Argumentsdialog box forSUMIF
99. Copy aSUMIFfunction.
100. Click cellC26and drag itsFillpointer to copy the formula to cellsC27:C29without formatting to preserve the borders (Figure 2-97).
101. Figure 2-97 Formula is copied without formatting
102. Format cellsC26:C29asCurrency.
103. UseSUMPRODUCTand trace an error.
104. Select cellD26and click theFormulastab.
105. Click theMath & Trigbutton in theFunction Librarygroup and selectSUMPRODUCT.
106. Click theArray1box and select cellsC7:C9, credit hours for courses in the Biology Department.
107. Click theArray2box and select cellsD7:D9, the number of sections for the Biology Department.
108. ClickOK. The Biology Department offered 98 total credit hours.
109. Click cellD26and point to itsTrace Errorbutton. The formula omits adjacent cells in the worksheet but it is correct.
110. Click theTrace Errorbutton and selectIgnore Error.
111. Copy and editSUMPRODUCT.
112. Click cellD26and drag itsFillpointer to copy the formula to cellsD27:D29without formatting to preserve the borders.
113. Click cellD27and click theInsert Functionbutton in theFormulabar.
114. Select and highlight the range in theArray1box and select cellsC10:C12. The range you select replaces the range in the dialog box (Figure 2-98).
115. Figure 2-98 Replace theArrayNarguments
116. Select the range in theArray2box and select cellsD10:D12.
117. ClickOK.
118. Edit and complete the formulas in cellsD28:D29and ignore errors.
119. Insert the current date as a function.
120. Select cellF20.
121. Type=toand pressTabto select the function.
122. PressEnter.
123. PressCtrl+Home.
124. Paste range names.
125. Click theNew sheetbutton in the sheet tab area.
126. Name the new sheetRange Names.
127. PressF3(FN+F3) to open thePaste Namedialog box.
128. Click thePaste Listbutton.
129. AutoFitcolumnsA:B.
130. Save and close the workbook (Figure 2-99).
131. Figure 2-99 Excel 2-3 completed
132. Upload and save your project file.
133. Submit project for grading.