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
0.0-rc0000
Edra Kinsman is the sales manager for
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.
” in the cell.
a. Link to the email address:
b. Use washingtonmgr@example.com as the text to display.
c. Use
the statewide manager for Washington as the ScreenTip text.
6/6
In cell B9, create a hyperlink to the Narbonne Suites
s listing as follows:
a. Link to the file
Support_EX19_5a_Locations x
.
b. Use
as the text to display.
c. Use View the current list of Narbonne Suites Locations as the ScreenTip text.
6/6
Set the ScreenTip for a hyperlink.
a. Use
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.
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
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
Do not ungroup the worksheets.
6/6
a. In cell A6, change the text “In Person” to read:
b. In cell A9, change the text “Other” to read:
Do not ungroup the worksheets.
6/6
Update a value in a cell.
a. Enter a formula in cell B10 using the SUM function that totals the sales for
.
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
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
Update a value in a cell.
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 using a function.
Copy a formula into a range.
Copy a formula into a range.
6/6
6/6
Create defined names for a range.
_Sales,
_Sales, and
_Sales to the formulas in the range B10:E10 in the Consolidated Sales worksheet.
s_2021 for the range F6:F9. [Mac Hint: Delete the existing defined name “Totals” and add the new defined name.]
7/7
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.
7/7
Create a formula using a function.
Documentation
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
Grading Engine: Grading Error: |
||
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
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
$ 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
$ 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
$ – 0 | ||||||||
Consolidated Sales
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
Idaho
Montana
Oregon
mailto:oregonmgr@example.com
mailto:idahomgr@example.com
mailto:montanamgr@example.com
http://www.narbonnesuites.cengage.com/
https://bozeman.cengage.com/
Idaho
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
$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
$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
2020 Total |