CIS 2010 sierra Pacific commu

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
  • Audit formulas.
  • Use SUMIF and SUMPRODUCT.

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.

  1. Set range names for the workbook.

    1. Range names are absolute references.
      Figure 2-90-Mac Define Name dialog box
  2. Enter a PMT function.

    1. The formula is =PMT(Rate/12,Loan_Term*12,-Loan_Amount)
      Figure 2-91-Mac Pv argument is negative in the PMT function
  3. Create a total interest formula.

    1. Parentheses are not necessary in the formula
      Figure 2-92 Left-to-right operations
  4. Create the total principal formula and the total loan cost.
  5. Set order of mathematical operations to build an amortization schedule.

    1. The formula is =B13*(Rate/12)
      Figure 2-93 The interest formula
  6. Fill data and copy formulas.

    1. Formulas copied to row 72 with a zero ending balance
      Figure 2-94 Formulas copied down columns
  7. Build a multiplication formula.

    1. The formula is =C7*D7*E7
      Figure 2-95 Formula to calculate total fees per course
  8. Use SUMIF to calculate fees by department.

    1. The formula is =SUMIF($B$B7:$A$18,B26,$F$7:$F$18)
      Figure 2-96 Function Arguments dialog box for SUMIF
  9. Copy a SUMIF function.

    1. The AutoFill Options button has an option to fill without formatting.
      Figure 2-97 Formula is copied without formatting
  10. Use SUMPRODUCT and trace an error.
  11. Copy and edit SUMPRODUCT.

    1. The formula is now =SUMPRODUCT(C10:C12,D10:D12)
      Figure 2-98 Replace the ArrayN arguments
  12. Insert the current date as a function.
  13. Paste range names.

  14. Excel 2-3 completed
    Figure 2-99 Excel 2-3 completed

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized