Posted: March 12th, 2023

NP_EX19_5a_FirstLastName

NP_EX19_5a_Smith_Report_1.xlsxNP_EX19_5a_Smith_1.xlsxInstructions_NP_EX19_5a x

 Module 5: SAM Project 1a Narbonne Suites

  

New Perspectives Excel 2019 | Module 5: SAM Project 1a

   

   

           

Graded Summary Report

New Perspectives Excel 2019 | Module 5: SAM Project 1a Jumah Saleh SUBMISSION #1 | SCORE IS: 94 OUT OF 100 GE ver. 1

5.

0.0-rc0000 1.

Edra Kinsman is the sales manager for

Narbonne Suites

hotel chain, which has locations in

Washington

,

Oregon

,

Idaho

, and

Montana

. Edra is using multiple worksheets to summarize sales data by state.
Break the external link in the worksheet, so that the formulas in the range B4:B7 of the

Manager Information

worksheet are replaced with static values. Then switch to the Manager Information worksheet.

0/6 Break links to an external workbook. The workbook should not contain a link to the workbook NarbonneDirectory.xlsx. 2. In cell C5, remove the hyperlink, leaving the unlinked text “

Bozeman, MT

” in the cell. 6/6 Remove a hyperlink. 3. In cell D7, create a hyperlink to an email address as follows:
a. Link to the email address:

washingtonmgr@example.com

b. Use washingtonmgr@example.com as the text to display.
c. Use

Email

the statewide manager for Washington as the ScreenTip text.

6/6
Hyperlink to an email address. Set the display text as a hyperlink. Set the ScreenTip for a hyperlink. 4.

In cell B9, create a hyperlink to the Narbonne Suites

Location

s listing as follows:
a. Link to the file

Support_EX19_5a_Locations x

.
b. Use

Narbonne Suites locations

as the text to display.
c. Use View the current list of Narbonne Suites Locations as the ScreenTip text.

6/6

Add a hyperlink to an external workbook. Set the display text for a hyperlink.

Set the ScreenTip for a hyperlink.

5.

Edit the hyperlink in cell B10 as follows:
a. Use

Narbonne Suites website

as the display text.
b. Use View the public-facing website for Narbonne Suites as the ScreenTip text.

6/6

Set the display text for a hyperlink.

Set the ScreenTip for a hyperlink.

6. Edra wants to apply consistent formatting to the worksheets submitted by each of the statewide managers. Group the Idaho, Montana, and Oregon worksheets together and then make the following formatting updates:
a. Change the font size in the merged range A1:F1 to 18 point.
b. Apply the 20% – Accent 6 cell style to the merged range A2:F2.
c. Bold the values in the range A6:A

10.

d. Apply the Accounting number format with zero decimal places and $ as the symbol to the range B6:F10.
e. Resize the column width of column B-F to

12.

Do not ungroup the worksheets.

6/6
Change the font size. Apply a cell style. Change the font style. Change the number format. Change the column width. 7. With the Idaho, Montana, and Oregon worksheets still grouped, update the worksheet as follows:
a. In cell A6, change the text “In Person” to read:

Walkup

b. In cell A9, change the text “Other” to read:

Other referrals

Do not ungroup the worksheets.

6/6
Update a value in a cell.

Update a value in a cell.

8. With the Idaho, Montana, and Oregon worksheets still grouped, create a formula as follows:
a. Enter a formula in cell B10 using the SUM function that totals the sales for

Q1

.
b. Copy the formula to the range C10:E10.
Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 6-8 are present in all three worksheets.

6/6
Create a formula using a function. Copy a formula into a range. 9.

Edra wants to create a copy of the formatted Oregon worksheet to use for the Washington section of the chain’s sales data. Create a copy of the Oregon worksheet between the Oregon worksheet and the

Consolidated

Sales

worksheet, and then update the worksheet as follows:
a. Change the worksheet name to Washington for the copied worksheet.
b. Edit the text to read Washington in the merged range A2:F2.
c. Clear the contents of the range B6:E9.

6/6

Copy a worksheet to a specific location. Change the name of a worksheet.

Update a value in a cell.

Clear cell contents. 10.

Edra wants to consolidate the data from each of the regions. Switch to the Consolidated Sales worksheet, then update the worksheet as follows:
a. In cell A6, enter a formula without using a function that references cell A6 in the Washington worksheet.
b. Copy the formula from cell A6 to the range A7:A9 without copying the formatting.
c. In cell B6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B6 in the Idaho:Washington worksheets.
d. Copy the formula from cell B6 to the range B7:B9 without copying the formatting.
e. Then copy the formulas and the formatting from the range B6:B9 to the range C6:E9.

6/6
Create a formula without using a function. Copy a formula into a range without formatting.

Create a formula using a function.

Copy a formula into a range.

Copy a formula into a range.

11. Edra started to create named ranges in the worksheet and has asked you to complete the work. Create a defined name for the range B6:E6 using Walkup as the range name.

6/6
Create defined names for a range. 12.

Create names from the range A7:E9 using the values shown in the left column.

6/6

Create defined names for a range.

13. Apply the defined names Q1_Sales,

Q2

_Sales,

Q3

_Sales, and

Q4

_Sales to the formulas in the range B10:E10 in the Consolidated Sales worksheet. 7/7 Use defined names in a formula. 14. Change the defined name to

Total

s_2021 for the range F6:F9. [Mac Hint: Delete the existing defined name “Totals” and add the new defined name.]

7/7
Change defined name for a range. 15. Edra wants to compare 2021 sales totals to the sales totals for 2020 and needs to add the 2020 data to the Consolidated Sales worksheet. Open the file Support_EX19_5a_2020_Sales.xlsx. Switch back to the NP_EX19_5a_FirstLastName_2.xlsx workbook and go to the Consolidated Sales worksheet. Create external references as follows:
a. Link cell G6 in the Consolidated Sales worksheet to cell F6 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.
b. Link cell G7 in the Consolidated Sales worksheet to cell F7 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.
c. Link cell G8 in the Consolidated Sales worksheet to cell F8 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.
d. Link cell G9 in the Consolidated Sales worksheet to cell F9 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.
e. Do not break the links. Close the Support_EX19_5a_2020_Sales.xlsx workbook.

7/7
Create a formula.

Create a formula.

Create a formula.

Create a formula.

16. In cell G10, enter a formula to total the values in the defined range Totals_2020, using the SUM function and the defined range name.

7/7

Create a formula using a function.

Documentation

New Perspectives Excel 2019 | Module 5: SAM Project 1a

Narbonne Suites

Jumah Saleh

GENERATING REPORTS FROM MULTIPLE WORKBOOKS

Author:

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Manager Information

Narbonne Suites

Location
Email

Idaho

Montana

Bozeman, MT

Oregon

Washington

washingtonmgr@example.com

Narbonne Suites locations

Narbonne Suites website

Grading Engine: Grading Error: Step 1: The workbook should not contain a link to the workbook NarbonneDirectory.xlsx.
State	Statewide Manager
Lenard Zimmerman	Nampa, ID	idahomgr@example.com
Doretta Burnam	montanamgr@example.com
Thomasena Bustamante	Gresham, OR	oregonmgr@example.com
Frederic Brickley	Tacoma, WA

mailto:oregonmgr@example.com

mailto:idahomgr@example.com

mailto:montanamgr@example.com

http://www.narbonnesuites.cengage.com/

mailto:washingtonmgr@example.com

Support_EX19_5a_Locations x

Idaho

Narbonne Suites

Idaho

Q1
Q2
Q3
Q4
Total

Walkup

Other referrals

Total

				2021 Sales
$ 103,021	$ 138,048	$ 207,072	$ 109,202	$ 557,343
				Online	$ 88,980	$ 148,597	$ 239,241	$ 79,192	$ 556,010
				AAA	$ 201,042	$ 281,459	$ 425,003	$ 215,115	$ 1,122,619
$ 23,522	$ 31,519	$ 46,648	$ 16,936	$ 118,625
$ 416,565	$ 599,623	$ 917,964	$ 420,445	$ 2,354,597

Montana

Narbonne Suites

Montana

2021 Sales

Q1
Q2
Q3
Q4
Total

Walkup

Online

AAA

Other referrals

Total

$ 185,438	$ 248,486	$ 372,730	$ 196,564	$ 1,003,217
$ 96,988	$ 161,971	$ 260,773	$ 86,319	$ 606,051
$ 349,813	$ 489,739	$ 739,505	$ 374,300	$ 1,953,357
$ 37,870	$ 50,746	$ 75,103	$ 27,267	$ 190,986
$ 670,110	$ 950,941	$ 1,448,111	$ 684,450	$ 3,753,612

Oregon

Narbonne Suites

Oregon

2021 Sales

Q1
Q2
Q3
Q4
Total

Walkup

Online

AAA

Other referrals

Total

$ 537,770	$ 720,611	$ 1,080,916	$ 570,034	$ 2,909,330
$ 242,471	$ 404,927	$ 651,932	$ 215,798	$ 1,515,127
$ 1,248,833	$ 1,748,367	$ 2,640,034	$ 1,336,251	$ 6,973,485
$ 102,250	$ 137,013	$ 202,779	$ 73,621	$ 515,663
$ 2,131,323	$ 3,010,917	$ 4,575,660	$ 2,195,705	$ 11,913,605

Washington

Narbonne Suites

Washington

2021 Sales

Q1
Q2
Q3
Q4
Total

Walkup

Online

$ – 0

AAA

$ – 0

Other referrals

$ – 0

Total
$ – 0
$ – 0
$ – 0
$ – 0
$ – 0

								$ – 0

Consolidated Sales

Narbonne Suites

Consolidated

2021 Sales

Q1
Q2
Q3
Q4
Total

Walkup

Online

AAA

Other referrals

Total

2020 Total
$826,228	$1,107,145	$1,660,717	$875,800	$4,469,891	$4,386,193
$428,439	$715,495	$1,151,945	$381,309	$2,677,188	$3,331,760
$1,799,688	$2,519,565	$3,804,542	$1,925,666	$10,049,461	$8,222,309
$163,643	$219,278	$324,530	$117,824	$825,274	$1,023,301
$3,217,997	$4,561,482	$6,941,735	$3,300,600	$18,021,814	$16,963,563

image2

image3

image4

image1

Documentation

New Perspectives Excel 2019 | Module 5: SAM Project 1a

Narbonne Suites

GENERATING REPORTS FROM MULTIPLE WORKBOOKS

Author:

Smith

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Manager Information

Narbonne Suites

State Statewide Manager Location Email

Idaho

Lenard Zimmerman Nampa, ID idahomgr@example.com

Montana

Doretta Burnam Bozeman, MT montanamgr@example.com

Oregon

Thomasena Bustamante Gresham, OR oregonmgr@example.com Washington Frederic Brickley Tacoma, WA

http://www.narbonnesuites.cengage.com/

mailto:oregonmgr@example.com

mailto:idahomgr@example.com

mailto:montanamgr@example.com

http://www.narbonnesuites.cengage.com/

https://bozeman.cengage.com/

Idaho

Narbonne Suites

Idaho
Total

			2021 Sales
			Q1				Q2				Q3				Q4								Total
		In Person	$ 103,021.00	$ 138,048.00	$ 207,072.00	$ 109,202.00	$ 557,343.00
		Online	$ 88,980.00	$ 148,597.00	$ 239,241.00	$ 79,192.00	$ 556,010.00
		AAA	$ 201,042.00	$ 281,459.00	$ 425,003.00	$ 215,115.00	$ 1,122,619.00
		Other	$ 23,522.00	$ 31,519.00	$ 46,648.00	$ 16,936.00	$ 118,625.00
$ –

Montana

Narbonne Suites

Montana

2021 Sales

Q1
Q2
Q3
Q4
Total

In Person

Online

AAA

Other

Total

.00

$185,437.80	$248,486.40	$372,729.60	$196,563.60	$1,003,217.40
$96,988.20	$161,970.73	$260,772.69	$86,319.28	$606,050.90
$349,813.08	$489,738.66	$739,505.22	$374,300.10	$1,953,357.06
$37,870.42	$50,745.59	$75,103.28	$27,266.96	$190,986.25
										$0

Oregon

Narbonne Suites

Oregon

2021 Sales

Q1
Q2
Q3
Q4
Total

In Person

Online

AAA

Other

Total

$0

$537,770	$720,611	$1,080,916	$570,034	$2,909,330
$242,471	$404,927	$651,932	$215,798	$1,515,127
$1,248,833	$1,748,367	$2,640,034	$1,336,251	$6,973,485
$102,250	$137,013	$202,779	$73,621	$515,663

Consolidated

Sales

Narbonne Suites

Consolidated

2021 Sales

Q1
Q2
Q3
Q4
Total

$0

$0

$0

$0

Total
$0
$0
$0
$0
$0

2020 Total

image1