Illustrated Excel 2019 | Module 1: SAM Project 1b Faith Addison 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2019 | Module 1: SAM Project 1b

Faith Addison

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

2. in cell A1 to provide a title for the worksheet.

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.