Reynolds Community College Module 5 Narbonne Suites Excel File Download the instructions and start file. Also download support file. Complete the project.

Reynolds Community College Module 5 Narbonne Suites Excel File Download the instructions and start file. Also download support file. Complete the project. Narbonne Suites
Consolidated
2020 Sales
Walkup
Online
AAA
Other referrals
Total
Q1
$908,851
$381,311
$1,259,782
$217,645
$2,767,588
Q2
$1,073,931
$1,001,693
$2,494,369
$206,121
$4,776,114
Q3
Q4
Total
$1,212,323 $1,191,088 $4,386,193
$1,681,840
$266,916 $3,331,760
$3,081,679 $1,386,480 $8,222,309
$438,116
$161,419 $1,023,301
$6,413,958 $3,005,903 $16,963,563
Narbonne Suites Locations
Idaho
556 Lakeview Avenue
McCall, ID 83638
(208) 555-1835
237 Arbor Avenue
Eagle, ID 83616
(208) 555-5841
907 East Indianapolis Lane
Boise, ID 83703
(208) 555-2961
Montana
749 Reserve Street
Laurel, MT 59044
(406) 555-6375
70 8th Boulevard
Anaconda, MT 59711
(406) 555-8983
453 South Holland Boulevard
Whitefish, MT 59937
(406) 555-7061
888 Lake Shore Street
Bozeman, MT 59715
(406) 555-8194
Oregon
780 Foster Way
Springfield, OR 97478
(541) 555-2429
925 Redwood Court
Seaside, OR 97138
(503) 555-5742
220 West Imlay Way
Portland, OR 97219
(503) 555-5362
305 Lynch Street
Beaverton, OR 97007
(503) 555-5495
684 East Soutre Avenue
Portland, OR 97267
(503) 555-7512
Washington
262 3rd Avenue
Marysville, WA 98270
(360) 555-9667
31 Patterson Avenue
Bothell, WA 98012
(425) 555-8392
538 Anchor Court
Spokane, WA 99224
(509) 555-9777
682 Ely Way
Mukilteo, WA 98275
(425) 555-5313
968 Leader Avenue
Electric City, WA 99123
(509) 555-5856
814 Beverly Glen Street
Spokane, WA 99208
(509) 555-4668
578 Keeler Way
Seattle, WA 98115
(206) 555-8127
305 Nora Avenue
Enumclaw, WA 98022
(360) 555-3661
528 Elmdale Avenue
Port Townsend, WA 98368
(360) 555-7982
383 Front Lane
Maple Valley, WA 98038
(425) 555-6506
721 Church Way
Seattle, WA 98103
(206) 555-8007
988 School Boulevard
Pullman, WA 99163
(509) 555-2763
New Perspectives Excel 2019 | Module 5: SAM Project 1a
Narbonne Suites
GENERATING REPORTS FROM MULTIPLE WORKBOOKS
GETTING STARTED
•
Open the file NP_EX19_5a_FirstLastName_1.xlsx, available for download from the
SAM website.
•
Save the file as NP_EX19_5a_FirstLastName_2.xlsx by changing the “1” to a “2”.
o
•
•
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:
o
Support_EX19_5a_2020_Sales.xlsx
o
Support_EX19_5a_Locations.docx
With the file NP_EX19_5a_FirstLastName_2.xlsx still open, ensure that your first
and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
PROJECT STEPS
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.
2.
In cell C5, remove the hyperlink, leaving the unlinked text “Bozeman, MT” in the cell.
3.
In cell D7, create a hyperlink to an email address as follows:
4.
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.
In cell B9, create a hyperlink to the Narbonne Suites Locations listing as follows:
a.
Link to the file Support_EX19_5a_Locations.docx.
b.
Use Narbonne Suites locations as the text to display.
c.
Use View the current list of Narbonne Suites Locations as the ScreenTip text.
New Perspectives Excel 2019 | Module 5: SAM Project 1a
5.
6.
7.
8.
9.
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.
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:A10.
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.
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.
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.
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.
New Perspectives Excel 2019 | Module 5: SAM Project 1a
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.
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.
12.
Create names from the range A7:E9 using the values shown in the left column.
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.
14.
Change the defined name to Totals_2021 for the range F6:F9. [Mac Hint: Delete the
existing defined name “Totals” and add the new defined name.]
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:
16.
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.
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.
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.
New Perspectives Excel 2019 | Module 5: SAM Project 1a
Final Figure 1: Manager Information Worksheet
Final Figure 2: Idaho Worksheet
New Perspectives Excel 2019 | Module 5: SAM Project 1a
Final Figure 3: Montana Worksheet
Final Figure 4: Oregon Worksheet
New Perspectives Excel 2019 | Module 5: SAM Project 1a
Final Figure 5: Washington Worksheet
Final Figure 6: Consolidated Sales Worksheet
New Perspectives Excel 2019 | Module 5: SAM Project 1a
Narbonne Suites
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.
w copy of the
Narbonne Suites
State
Idaho
Montana
Oregon
Washington
Statewide Manager
Lenard Zimmerman
Doretta Burnam
Thomasena Bustamante
Frederic Brickley
Location
Nampa, ID
Bozeman, MT
Gresham, OR
Tacoma, WA
http://www.narbonnesuites.cengage.com/
Email
idahomgr@example.com
montanamgr@example.com
oregonmgr@example.com
Narbonne Suites
Idaho
2021 Sales
In Person
Online
AAA
Other
Total
Q1
$ 103,021.00
$ 88,980.00
$ 201,042.00
$ 23,522.00
Q2
$ 138,048.00
$ 148,597.00
$ 281,459.00
$ 31,519.00
Q3
$ 207,072.00
$ 239,241.00
$ 425,003.00
$ 46,648.00
Q4
$ 109,202.00
$ 79,192.00
$ 215,115.00
$ 16,936.00
$
$
$
$
$
Total
557,343.00
556,010.00
1,122,619.00
118,625.00
–
Narbonne Suites
Montana
2021 Sales
In Person
Online
AAA
Other
Total
Q1
Q2
$185,437.80 $248,486.40
$96,988.20 $161,970.73
$349,813.08 $489,738.66
$37,870.42 $50,745.59
Q3
Q4
Total
$372,729.60 $196,563.60 $1,003,217.40
$260,772.69 $86,319.28 $606,050.90
$739,505.22 $374,300.10 $1,953,357.06
$75,103.28 $27,266.96 $190,986.25
$0.00
Narbonne Suites
Oregon
2021 Sales
In Person
Online
AAA
Other
Total
Q1
$537,770
$242,471
$1,248,833
$102,250
Q2
$720,611
$404,927
$1,748,367
$137,013
Q3
$1,080,916
$651,932
$2,640,034
$202,779
Q4
$570,034
$215,798
$1,336,251
$73,621
Total
$2,909,330
$1,515,127
$6,973,485
$515,663
$0
Narbonne Suites
Consolidated
2021 Sales
Q1
Total
Q2
$0
Q3
$0
Q4
$0
Total
$0
2020 Total
$0
$0
$0
$0
$0

Purchase answer to see full
attachment