Excel 2024 In Practice - Ch 2 Guided Project 2-3

Guided Project 2-3 - SIMnet Project Steps to complete This Project

Mark the steps as checked when you complete them.

1.Open the workbook SPCCD-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.

2.Create range names for the workbook.

3.Select the Student Loan sheet and select cells B5:C8.

4.Press Ctrl and select cells E5:F7.

5.Click the Create from Selection button [Formulas tab, Defined Names group].

6.Verify that the Left column box in the Create Names from Selection dialog box is selected, and click OK.

7.Click the Name Manager button [Formulas tab, Defined Names group] to view the names in the Name Manager dialog box (Figure 2-97). Notice that the cell references are absolute and the names are scoped to the workbook.

A screenshot of Microsoft Excel shows the Name Manager dialog box.The Name Manager dialog box contains four buttons at the top: New, Edit, Delete, and Filter. Below these buttons, a table displays a list of named ranges with columns labeled Name, Value, Refers To, Scope, and Comment. The Name column includes entries such as Loan Amount, Loan Term, Payment, Rate, Total Cost, Total Interest, and Total Principal. The Refers To column lists absolute references pointing to specific cells in the Student Loan worksheet, including Equals Student Loan exclamation mark $ C $ 6, Equals Student Loan exclamation mark $ C $ 8, Equals Student Loan exclamation mark $ C $ 7, Equals Student Loan exclamation mark $ C $ 7, Equals Student Loan exclamation mark $ C $ 5, and Equals Student Loan exclamation mark $ C $ 6. The Refers To column is encircled and labeled Absolute References. At the bottom of the dialog box, a Refers To field displays the absolute reference Equals Student Loan exclamation mark $ C $ 5. A Close button is located at the bottom right corner.

Absolute references

Figure 2-97 Name Manager dialog box

8.Click Close.

Paste range names.

9.Click the New sheet button in the sheet tab area.

Name the new sheet Range Names.

10.Select cell A1 and press F3 (FN+F3) to open the Paste Name dialog box.

11.Click the Paste List button.

AutoFit columns A:B and press Ctrl+Home.

Fill a series of values.

12.Return to the Student Loan sheet.

13.Select cell A13, type 1 as the starting value, and press Ctrl+Enter.

14.Confirm that cell A13 is selected, click the Fill button [Home tab, Editing group], and choose Series.

Define a linear series in Columns with a step value of 1 and a stop value of 60.

15.Enter a PMT function.

16.Select C8.

17.Click the Financial button [Formulas tab, Function Library group] and select PMT.

18.Click the Rate box if necessary and click cell C7. The name Rate is substituted; range names are absolute references.

19.Type /12 immediately after Rate to divide by 12 for monthly payments.

20.Click the Nper box and click cell C6. The substituted range name is Loan_Term.

21.Type *12 after Loan_Term to multiply by 12.

22.Click the Pv box and type a minus sign (−) to set the argument as a negative amount.

23.Click cell C5 (Loan_Amount) for the pv argument. A negative loan amount reflects the lender’s perspective, because the money is paid out now (Figure 2-98).

A screenshot shows a Function Arguments dialog box in Microsoft Excel for the P M T function."The Function Arguments dialog box displays the text P M T at the top. It consists of five input fields labeled Rate, N per, P v, F v, and Type, with the following entries: Rate: Rate divided by 12, with a calculated value of 0.00375 displayed to its right. N per: Loan Term multiplied by 12, with a calculated value of 60 displayed to its right. Pv: Minus Loan Amount, with a calculated value of minus 10,000 displayed not its right. F v: Blank. Type: Blank. At the bottom, the Formula Result is displayed as $ 186.43. A description below the fields states: Calculates the payment for a loan based on constant payments and a constant interest rate. At the bottom left of the dialog box, a link for Help on this function is displayed. The OK and Cancel buttons are located at the bottom right of the dialog box."

Figure 2-98 Pv argument is negative in the PMT function.

Leave the Fv and Type entry boxes empty.

24.Click OK. The payment for a loan at this rate is $186.43, shown as a positive value.

25.Format cell C8 as Accounting Number Format to match cell C5.

26.Create a total interest formula.

27.Click cell F5 (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.

28.Type = and click cell C8 (the Payment).

29.Type * to multiply and click cell C6 (Loan_Term).

30.Type *12 to multiply by 12 for monthly payments. Values typed in a formula are constants and are absolute references.

31.Type – immediately after *12 to subtract.

32.Click cell C5 (the Loan_Amount). The formula is Payment *times Loan_Term *times 12 − Loan_Amount. Parentheses are not required, because the multiplications are done from left to right, followed by the subtraction (Figure 2-99).

A screenshot of Microsoft Excel shows a spreadsheet displaying two tables."Two tables are shown. The table on the left consists of two columns and four rows, containing the following entries: Row 1: Loan Amount and $ 10,000.00. Row 2: Loan Term and 5. Row 3: Rate and 4.50 percent. Row 4: Payment and $ 186.43. The table on the right also consists of two columns and four rows, with the following entries: Row 1: Total Interest, Equals Payment times Loan Term times 12-Loan Amount. Row 2: Total Principal, Blank. Row 3: Total Cost, Blank. Row 4: Blank. The cells containing entries $ 10,000.00, 5, and $ 186.43 are highlighted."

Figure 2-99 Left-to-right operations

33.Press Enter. The result is $1,185.81.

Calculate the total loan cost.

34.Select cell F6 (Total_Principal). This is the loan amount in cell C5.

35.Type = and click cell C5 (the Loan_Amount). Press Enter.

36.Click cell F7, the Total_Cost of the loan. This is the total principal plus the total interest.

37.Type =, click cell F5, type +, click cell F6, and then press Enter. The result is $11,185.81.

38.Set order of mathematical operations to build an amortization schedule.

39.Click cell B13. The beginning balance is the loan amount.

40.Type =, click cell C5, and press Enter.

41.Format the value as Accounting Number.

42.Select cell C13. The interest for each payment is calculated by multiplying the balance in column B by the rate divided by 12.

43.Type = and click cell B13.

44.Type *( and click cell C7. Ignore the Formula AutoComplete list and note that the Rate range name is substituted.

45.Type /12). Parentheses are necessary so that the division is done first (Figure 2-100).

A screenshot of Microsoft Excel shows a spreadsheet with the Interest Formula."The spreadsheet displays two tables, one below the other. The top table consists of two columns and four rows, containing the following entries: Row 1: Loan Amount and $ 10,000.00. Row 2: Loan Term and 5. Row 3: Rate and 4.50 percent. Row 4: Payment and $ 185.73. The table below consists of four columns labeled P m t Number, Beginning Balance, Interest, and Principal. Row 1 contains the following entries: P m t Number: 1. Beginning Balance: $ 10,000.00. Interest: Equals B 13 times (Rate divided by 12). Principal: Blank. The cells B 13 (containing $10,000.00) and C 7 (containing 4.50 percent) are highlighted."

Figure 2-100 The interest formula

46.Press Enter. The result is $37.50.

47.Verify that the result is formatted as Accounting Number.

48.Select cell D13. The portion of the payment that is applied to the principal is calculated by subtracting the interest portion from the payment.

49.Type = and click cell C8 (the Payment).

50.Type −, click cell C13, and press Enter. From the first month’s payment, $148.93 is applied to the principal and $37.50 is interest.

51.Click cell E13. The total payment is the interest portion plus the principal portion.

52.Type =, click cell C13, type +, click cell D13, and then press Enter. The value matches the payment in cell C8.

53.Select cell F13. The ending balance is the beginning balance minus the principal payment. The interest is part of the cost of the loan and does not reduce the money still owed.

54.Type =, click cell B13, type −, click cell D13, and then press Enter. The ending balance is $9,851.07.

This table lists cell references and the formulas in each cell

B13 =Loan_Amount

C13 =B13*times(Rate/12)

D13 =Payment-C13

E13 =C13+D13

F13 =B13-D13

55.Copy formulas.

56.Select cell B14. The beginning balance for the second payment is the ending balance for the first payment.

57.Type =, click cell F13, and press Enter.

58.Double-click the Fill pointer for cell B14 to fill the formula down to row 72. The results are zero (displayed as a hyphen in Accounting Number Format) until the rest of the schedule is complete.

59.Select cells C13:F13.

60.Double-click the Fill pointer at cell F13. All formulas are filled (copied) to row 72 (Figure 2-101).

A screenshot of Microsoft Excel shows two spreadsheets."The spreadsheet contains a table divided into two sections: The first section spans from row 12 to row 30. The second section spans from row 60 to row 72. The table consists of six columns titled P m t Number, Beginning Balance, Interest, Principal, Total Payment, and Ending Balance. Row 1 contains the following entries: P m t Number: 1. Beginning Balance: $ 10,000.00. Interest: $ 37.50. Principal: $ 148.93. Total Payment: $ 186.43. Ending Balance: $ 9,851.07."

Figure 2-101 Formulas copied down columns

61.Scroll to see the values in row 72. The loan balance reaches 0.

62.Press Ctrl+Home.

Preview the document and insert print titles.

63.Select cell A12. This row of labels should repeat on the second printed page.

64.Click the Page Layout command tab and click the Print Titles button [Page Setup group].

65.Click the Rows to repeat at top box and click A12. Only the row number displays.

66.Click Print Preview in the dialog box.

67.Click the Next Page button in the Backstage view to see the print titles.

68.Click the Page Setup link at the bottom of the Settings group and center the page horizontally (Margins tab).

69.Return to the worksheet.

Build a multiplication formula.

70.Click the Fees & Credits sheet tab and select cell F7. Total fees from a course equal the number of students times the fee.

71.Type =, click cell D7, type *, click cell E7, and press Enter.

72.Double-click the Fill pointer for cell F7 to copy the formula.

73.Verify that cells F7:F18 are Currency format.

74.Set a single bottom border for cell F18.

75.Use SUMIF to calculate statistics by department.

76.Select cell C26. Course names are in alphabetical order, but the department names are ordered in rows 26:29.

77.Click the Math & Trig button [Formulas tab, Function Library group] and select SUMIF.

78.Click the Range box and select cells B7:B18. This range will be matched against the criteria.

79.Press F4 (FN+F4) to make the reference absolute.

80.Click the Criteria box and select cell B26.

81.Press F4 (FN+F4) three times to make the mixed reference $B26.

82.Click the Sum_range box, select cells D7:D18.

83.Press F4 (FN+F4) two times to make the mixed reference D$7:D$18. The row number is absolute so that you can copy the formula down to row 29.

84.Click OK. Total number of students for the Building Industries department is 35 (Figure 2-102).

A screenshot shows a Function Arguments dialog box for the SUM IF function."The Function Arguments dialog box displays the SUM IF function at the top. It contains three labeled input fields: Range: $ B $ 7 to $ B $ 10. Criteria: $ B 26. Sum range: $ D $ 7 to $ D $ 10. A function description below the input fields states: Adds the cells specified by a given condition or criteria. Below this, the Formula result is displayed as 37. At the bottom left of the dialog box a link Help on this function is displayed. The OK and Cancel buttons are located at the bottom right of the dialog box."

Figure 2-102 Function Arguments dialog box for SUMIF

85.Copy a SUMIF function.

86.Click cell C26 and drag its Fill pointer to copy the formula to cells C27:C29 without formatting to preserve the borders.

87.Copy the formula in cell C26 to cells D26:E26.

Troubleshoot an error and edit a SUMIF formula.

88.Select cell D26. This is an error that has not been flagged.

89.Click the Trace Precedents button [Formulas tab, Formula Auditing group]. The reference to column E, the course fee, is incorrect in the copied formula.

90.Click the Insert Function button in the Formula bar.

91.Select the entry in the Sum_range box, select cells F$7:F$18, and click OK (Figure 2-103).

A screenshot shows a spreadsheet and a Function Argument dialog box."The spreadsheet consists of a Formula Bar at the top, displaying a formula, followed by a spreadsheet area containing two tables. Table 1: This table contains columns labeled Department, Credit Hours, Number of Students, Course Fee, and Total Fees. Each column has corresponding data in its rows. Table 2: This table contains columns labeled Department, Number of Students, Total Fees, and Total Credit Hours. The cell D 26 under the Total Fees column contains the formula: $ F $ 18. An arrow is pointing from the formula to cells B 7 and D 7. The Function Arguments dialog box displays the SUM IF function at the top. It consists of three labeled input fields: Range: $ B $ 7 to $ B $ 16. Criteria: B 26. Sum range: $ D $ 7 to $ D $ 16. At the bottom of the dialog box, the Formula Result is displayed. At the bottom left of the dialog box, a link for Help on this function is displayed. The OK and Cancel buttons are located at the bottom right of the dialog box."

Figure 2-103 Precedent arrows help identify the error

92.Copy cell D26 to cells D27:D29 without formatting.

93.Format cells D26:D29 as Currency from the Number Format drop-down list.

Order mathematical operations to calculate total credit hours by department.

94.Click cell E26. The SUMIF formula will not work for this calculation because you first need the product of credit hours and number of students.

95.Type =( to start the formula.

96.Click cell C8 for the Building Industries department, type * to multiply, and click cell D8.

97.Type ) as the closing parenthesis for the first multiplication.

98.Type + to add the next course.

99.Type ( and click cell C11.

100.Type * to multiply and click cell D11, and type ) for the closing parenthesis.

101.Type + to add the next course.

102.Complete the multiplication for (C17*D17) and press Enter. The result is 105 (Figure 2-104).

An Excel spreadsheet calculating total credit hours for a college department using a formula that multiplies student numbers by course credit hours.The image displays a Microsoft Excel worksheet titled �Sierra Pacific Community College District Credit Hours and Fees by Department.� The main table contains columns for: Course Name Department Code (e.g., THE, BI, IT, ESS) Credit Hours (ranging from 1 to 4) # of Students (ranging from 3 to 15) Course Fee (ranging from $75.00 to $150.00) Total Fees (calculated per course, e.g., $1,875.00, $975.00, $2,250.00). Below the table, there is a summary section that aggregates totals by department: BI (Biology) ? 35 students, $2,975.00 fees ESS (Environmental Sciences & Studies) ? 37 students, $5,100.00 fees IT (Information Technology) ? 36 students, $2,700.00 fees THE (Theatre) ? 42 students, $5,625.00 fees Next to these totals, there is a formula bar showing the calculation for Total Credit Hours: =(C8*D8)+(C11*D11)+(C17*D17) This formula multiplies credit hours by the number of students for specific courses (rows 8, 11, and 17) and adds them together. The sheet visually organizes student credit hours, course fees, and department totals to provide a clear financial and enrollment overview.

Figure 2-104 Addition formula with multiplication

103.Complete the formulas for cells E27:E29. You can insert missing parentheses or operators in a formula where needed.

104.Insert the current date.

105.Select cell E31.

106.Type =tod and select TODAY.

107.Press Ctrl+Home.

108.Save and close the workbook (Figure 2-105).

Two screenshots show a spreadsheet divided into two sections."The spreadsheet area displays the title Sierra Pacific Community College District Financial Aid Office and consists of three tables. The table on the top contains two columns and four rows. The rows include the following entries: Row 1: Loan Amount and $ 10,000.00. Row 2: Loan Term and 5. Row 3: Rate and 4.50 percent. Row 4: Payment and $ 186.43. On the right, a table contains two columns and four rows. The rows include the following entries: Row 1: Total Interest and $ 1,185.85. Row 2: Total Principal and $ 10,000.00. Row 3: Total Cost and $ 11,185.81. Row 4: Blank and Blank. Table 3 is divided into two sections. The first part of the spreadsheet is shown in the first screenshot, from rows 12 to 40. The second section is shown in the second screenshot, from rows 41 to 72. The table consists of six columns labeled P m t number, Beginning Balance, Interest, Principal, Total Payment, and Ending Balance. Row 1 consists of the following entries: 1, $ 10,000.00, $ 37.50, $ 148.93, $ 186.43, and $ 9,851.07. "

Figure 2-105 Excel 2-3 completed

A screenshot shows a workbook with two tables."The title, Sierra Pacific Community College District Credit Hours and Fees by Department, is displayed at the top. Two tables are shown below: The first table consists of six columns for Course Names, Departments, Credit Hours, Number of Students, Course Fees, and Total Fees. This table contains corresponding data for various courses. The second table, positioned below the first, has four columns labeled Department, Number of Students, Total Fees, and Total Credit Hours. This table summarizes the data by department. Additionally, in the bottom-right corner, a smaller portion of another table is visible. It displays loan-related data with the following labels: Loan Amount, Loan Term, Payment, Rate, Total Cost, Total Interest, and Total Principal."

Figure 2-105 Excel 2-3 completed

109.Upload and save your project file.

110.Submit file for grading.