Excel 2019 In Practice - Ch 2 Guided Project 2-3 - Sierra Pacific Community College District (2025)

Guided Project 2-3
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 the Start File link.)
Completed Project File Name:[your name]-SierraPacific-02.xlsx
Skills Covered in This Project
Name cell ranges.
Create and copy formulas.
Set mathematical order of operations.
Use absolute references in formulas.
Insert the current date as a function.
Use the PMT function.
Audit formulas.
Use SUMIF and SUMPRODUCT.
Steps to complete This Project
Mark the steps as checked when you complete them.
1. Open the SierraPacific-02.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button 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.
NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open
the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show.
Close the Excel Preferences dialog box.
2. Set range names for the workbook.
a.
b.
c.
d.
e.
f.
g.
Select the Student Loan sheet, and select cells B5:C8.
Click the Create from Selection button [Formulas tab, Defined Names group].
Verify that the Left column box in the Create Names from Selection dialog box is selected.
Deselect the Top row box if it is checked and click OK.
Select cells E5:F7. Repeat steps a−d to create range names.
Click the Name Manager button [Formulas tab, Defined Names group] to view the names in the Name Manager
dialog box (Figure 2-90-Mac). Notice that the cell references are absolute.
Click Close.
3. Enter a PMT function.
a.
b.
c.
Select C8.
Click the Financial button [Formulas tab, Function Library group] and select PMT.
Click the Rate box and click cell C7. The range name Rate is substituted and is an absolute reference.
1/5
https://tulsacc.simnetonline.com/sp/assignments/projects/details/8633868
2/5/24, 11:28 AM Excel 2019 In Practice - Ch 2 Guided Project 2-3 - SIMnet
https://tulsacc.simnetonline.com/sp/assignments/projects/details/8633868 2/5
Figure 2-90-Mac Name Manager dialog box
Figure 2-91-Mac Pv argument is negative in the PMT function
Figure 2-92 Left-to-right operations
d. Type /12 immediately after Rate to divide by 12 for monthly
payments.
e. Click the Nper box and click cell C6. The substituted range
name is Loan_Term.
f. Type *12 after Loan_Term to multiply by 12.
g. Click the Pv box and type a minus sign (-) to set the
argument as a negative amount.
h. Click cell C5 (Loan_Amount) for the pv argument. A
negative loan amount reflects the lender’s perspective, since
the money is paid out now (Figure 2-91-Mac).
i. Leave the Fv and Type boxes empty.
j. Click Done. The payment for a loan at this rate is $186.43,
shown as a positive value.
k. Verify or format cell C8 as Accounting Number Format to
match cell C5.