Illustrated Excel 2019 | Module 1: SAM Project 1b Fiducia Bank

GETTING STARTED

1. Open the file IL_EX19_1b_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_1b_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

3. With the file IL_EX19_1b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

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

PROJECT STEPS

1. Matt O'Hara is an analyst for Fiducia Bank in New York City. He is analyzing the expenses of the bank's branches in Alexandria, Virginia, and Rockville, Maryland, and has created a worksheet comparing the expense data. He has asked you to help complete the analysis. Go to the Expense Analysis worksheet. Enter the text Fiducia Bank in cell A1 to provide a title for the worksheet.

2. In cell A2, enter the text Expense Analysis to provide a subtitle.

3. Clear the contents from cell G1 to remove the repeated text.

4. In cell D5, change the text "2023" to 2021 so that the cell contains the correct year.

5. In cell B15, enter the value 20435 to provide the expense amount for advertising in 2019.

6. Use the Fill Handle to fill the range E7:E10 with the formula in cell E6 to find the total for each type of expense.

7. Matt wants to calculate the total expenses for each year and for all types. Enter this information as follows:

a. In cell B11, enter a formula that uses the SUM function to total the range B6:B10 to calculate the total expenses for 2019.

b. Copy the formula in cell B11, and then paste it in the range C11:E11, pasting the formulas only.

8. In cell A13, insert the text Branch so that the cell contains the text "Rockville Branch".

9. In cell B17, enter the value 50325 as the mortgage amount. Enter the same value in cell C17 and cell D17.

10. Matt wants to know the difference in total expenses between the two branches. Determine the difference as follows:

a. In cell B24, enter a formula that subtracts the value in cell B11 from the value in cell B20.

b. Copy the formula in cell B24, and then paste it in the range C24:E24, pasting the formulas only.

11. Matt started comparing statistics on the expenses for the Alexandria and Rockville branches. Complete the comparison as follows:

a. In cell H6, enter a formula that uses the AVERAGE function to average the values in the range B6:D10 to find the average expense value at the Alexandria branch.

b. In cell I7, enter a formula that uses the MAX function to find the maximum value in the range B15:D19, which is the highest expense at the Rockville branch.

c. In cell H8, enter a formula that uses the MIN function to find the minimum value in the range B6:D10, which is the lowest expense at the Alexandria branch.

12. Matt wants to identify the highest expense at both branches. Provide this information by copying the contents of cell A8 and pasting the cell value in cell J7 with no formatting.

13. Hide the gridlines for the Expense Analysis worksheet to make it easier to read.

14. Change the orientation of the worksheet to Landscape to prepare for printing the worksheet on one page.

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 SAM website to submit your completed project.

Final Figure 1: Expense Analysis Worksheet