New Perspectives Excel 2019 | Module 5: End of Module Project 1 Lewellen Group GENERATE REPORTS FROM MULTIPLE WORKBOOKS GETTING STARTED ? Open the file NP_EX19_EOM5-1_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought4

Share With

New Perspectives Excel 2019 | Module 5: End of Module Project 1

Lewellen Group

GENERATE REPORTS FROM MULTIPLE WORKBOOKS

GETTING STARTED

?

Open the file

NP_EX19_EOM5-1_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

NP_EX19_EOM5-1_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

?

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.

?

To complete this SAM Project, you will also need to download and save the following data

files from the SAM website onto your computer:

?

Support_EX19_EOM5-1_2021.xlsx

?

With the file

NP_EX19_EOM5-1_

FirstLastName

_2.xlsx

still open, ensure that your first

and last name is displayed in cell B6 of the Documentation sheet.

?

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

SAM website

.

PROJECT STEPS

1.

Lonnie Holtzman is a financial analyst for the Lewellen Group, a management consulting

firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte,

North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel

workbook with multiple worksheets to create a profit and loss statement for the firm. He

asks for your help in completing the statement.

Go to the

Dashboard

worksheet. Lonnie first wants to complete the list of links in the

range H7:H10. In cell H7, create a hyperlink to the

www.lewellen.example.net

website.

2.

In cell H8, create a hyperlink to the

info@lewellen.example.net

email address. Use

Contact a Lewellen consultant

as the ScreenTip text.

3.

In cell H9, create a hyperlink to cell A1 in the

Categories

worksheet.

4.

In cell H10, create a hyperlink to the workbook

Support_EX19_EOM5-1_2021.xlsx

,

which contains Lewellen profit and loss data from 2021.

5.

Lonnie wants to compare 2021 profit and loss totals to those for 2022 and needs to add

the 2021 data to the

Dashboard

worksheet. Open the file

Support_EX19_EOM5-

1_2021.xlsx

. Copy the values in the range C6:C19. In cell C6 of the

Dashboard

worksheet in the original workbook, use the Paste Link command to create external

references to the values in the

Support_EX19_EOM5-1_2021.xlsx

workbook. Delete

the unnecessary values in cells C8, C12, and C16, and close the

Support_EX19_EOM5-

1_2021.xlsx

workbook.

6.

Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use

the name

PayrollExp

to define the range D9:D11. Use the name

OpExp

to define the

range D13:D15.

7.

In cell D18, enter a formula using the SUM function to total the expense values using the

defined range names PayrollExp and OpExp.

8.

Lonnie prefers that accountants and others contact Lewellen through the link in cell H8

rather than one that connects to his email address. In cell C21, remove the hyperlink,

leaving the unlinked text "Lonnie Holtzman" in the cell.

9.

Lonnie wants to combine the profit and loss data for 2022 from each of the three

Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and

grouped worksheets that totals the values from cell C6 in the Charlotte:Milwaukee

worksheets. Copy the formula from cell E6 to cell E7, the range E9:E11, the range

E13:E15, and the range E17:E19. In the range E17:E19, copy the formula and number

formatting only.

10.

Go to the

Charlotte

worksheet. Lonnie needs to calculate the percent of sales for each

expense item. Assign the defined name

Revenue

to cell C6.

11.

In cell D7, enter a formula without using a function that divides the Cost of sales value in

cell C7 by the revenue amount, using the defined name Revenue for the

Charlotte

worksheet. Copy the formula from cell D7 to the range D9:D11, the range D13:D15, and

the range D17:D19. In the range D17:D19, copy the formula and number formatting only.

12.

Lonnie wants to apply consistent formatting to the worksheets he collected from the

three Lewellen offices. Group the

Charlotte

,

Denver

, and

Milwaukee

worksheets, and

then apply the Heading 1 cell style to cell B2 to use the same formatting as in the

Dashboard

worksheet. Ungroup the worksheets, and then verify that the formatting is

applied to all three worksheets.

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: Dashboard Worksheet

Final Figure 2: Charlotte Worksheet

Final Figure 3: Denver Worksheet

Final Figure 4: Milwaukee Worksheet

Final Figure 5: Categories Worksheet