New Perspectives Excel 365 | Module 1: SAM Project A Mentor Bank & Trust

GETTING STARTED

1. Save the file NP_EX365_1A_FirstLastName_1.xlsx as NP_EX365_1A_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_1A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Diego Espinosa is a sales manager at Mentor Bank & Trust and uses Excel to maintain data about the bank's branches. He asks you as his financial assistant to finalize the Branch Managers and Loan Income worksheets for the week. Begin on the Branch Managers worksheet by cutting the contents of the range D1:D3 and pasting it into the range A1:A3.

2. Adjust the width of column A using AutoFit.

3. In cell A3, enter the text Week of March 19, 2029 to note the week.

4. Change the width of column B to 15.00 to narrow the column.

5. Enter the values shown in Table 1 into the corresponding cells in the range D15:D17.

Table 1: Data for the Range D15-D17

| D 15 | 553,800 16 | 490,725 17 | 785,250

6. Enter malik.watson@cengage.com in cell E6. Select the range E6:E17, and then use the Flash Fill command to automatically enter email addresses into the remaining cells in the range.

7. Change the width of column E to 35.00 to display the full text of each email address in the cell.

8. Enter the word Total in cell B18 to identify the results of calculations you are about to make.

9. Diego needs to determine the total amount of new loans and deposits made by each branch in the West region of Mentor Bank & Trust. In cell C18, create a formula using the SUM function to total the new loan amounts (the range C6:C17). Copy the formula you created in cell C18 to cell D18 to total the deposits.

10. Diego also wants you to complete the summary statistics at the bottom of the worksheet. Enter the text Average Deposit in cell A22 to identify the data in cell B22.

11. In cell B20, create a formula using the COUNTA function to determine the number of managers in the West region by counting the manager names (the range A6:A17).

12. Add an Outside Border to the range A20:B22 to set the cells apart from other cells in the worksheet.

13. Switch to the Loan Income worksheet. Diego wants to make this worksheet easier to read and print. Change the orientation to Landscape.

14. Change the Zoom level to 130% to better read the worksheet contents.

15. In cell A2, edit the text so that it reads Loans: Week of March 19, 2029 to include the correct date.

16. Select the column headings (the range B5:F5) and the cell A10, and then increase the font size of the cells to 11 point to make the text more prominent.

17. Diego also needs you to complete the calculations on the worksheet. In cell B8, enter a formula without using a function to determine the income earned in the East region from loan interest by subtracting the credit loss amount (cell B7) from the interest amount (cell B6). Copy the formula you created in cell B8 to the range C8:E8 to display the income for the other regions.

18. Next, calculate the total interest, credit loss amount, and income for all four regions. Select the range F6:F8, and then apply AutoSum to calculate the totals.

19. Apply the Wrap Text formatting to cell A10 to fit the contents within the cell borders.

20. Adjust the height of row 10 using AutoFit to display all the text in cell A10.

21. In cell B11, enter 3/19/2029 as the forecast date of the last update.

22. Since you just calculated the income and totals, you no longer need the blue tip instructions in cell D11. Delete the contents of cell D11.

23. In preparation for tracking employee data, insert a new worksheet in the workbook, rename the worksheet Employees, and if necessary, move the new worksheet after the Loan Income worksheet.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Branch Managers Worksheet

Final Figure 2: Loan Income Worksheet

Final Figure 3: Employees Worksheet