Assignment Prompt & Instructions
– 2 years ago
**files (2)**
– ## Instructions_NP_EX365_2021_5acopy.docx
– NP_EX_5-1_MaryLynDanielakcopy.ods
**## Instructions_NP_EX365_2021_5acopy.docx**
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
| |
| — |
| **2** |
## Narbonne Suites
### GENERATING REPORTS FROM MULTIPLE WORKBOOKSvvmmm
#### GETTING STARTED
Open the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx**, available for download.
Save the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx** as
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx**
Edit the file name by changing “1” to “2”.
If you do not see the
**.xlsx** file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:
Support_EX365_2021_5a_2023_Sales.xlsx
Support_EX365_2021_5a_Locations.docx
With the file
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx** open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select
**Don't Update** in the dialog box.
#### PROJECT STEPS
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.
In cell C5, remove the hyperlink, leaving the unlinked text "Bozeman, MT" in the cell.
In cell D7, create a hyperlink to an email address as follows:
Link to the email address:
**washingtonmgr@example.com**
Use
**washingtonmgr@example.com** as the text to display.
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:
Link to the file
**Support_EX365_2021_5a_Locations.docx**.
Use
**Narbonne Suites locations** as the text to display.
Use
**View the current list of Narbonne Suites Locations** as the ScreenTip text.
Edit the hyperlink in cell B10 as follows:
Use
**Narbonne Suites website** as the display text.
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:
Change the font size in the merged range A1:F1 to
**18 point**.
Apply the
**20% – Accent 6** cell style to the merged range A2:F2.
**Bold** the values in the range A6:A10.
Apply the
**Accounting** number format with zero decimal places and
**$** as the symbol to the range B6:F10.
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:
In cell A6, change the text "In Person" to read:
**Walkup**
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:
Enter a formula in cell B10 using the
**SUM** function that totals the sales for Q1.
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:
Change the worksheet name to
**Washington** for the copied worksheet.
Edit the text to read
**Washington** in the merged range A2:F2.
Clear the contents of the range B6:E9.
Edra wants to consolidate the data from each of the regions. Switch to the
_Consolidated Sales_ worksheet, then update the worksheet as follows:
In cell A6, enter a formula without using a function that references cell A6 in the
_Washington_ worksheet.
Copy the formula from cell A6 to the range A7:A9 without copying the formatting.
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.
Copy the formula from cell B6 to the range B7:B9 without copying the formatting.
Then copy the formulas and the formatting from the range B6:B9 to the range C6:E9.
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.
Create names from the range A7:E9 using the values shown in the left column.
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.
Change the defined name to
**Totals_2024** for the range F6:F9. [Mac Hint: Delete the existing defined name "Totals" and add the new defined name.]
Edra wants to compare 2024 sales totals to the sales totals for 2023 and needs to add the 2020 data to the
_Consolidated Sales_ worksheet. Open the file
**Support_EX365_2021_5a_2023_Sales.xlsx.** Switch back to the
**NP_EX365_2021_5a_FirstLastName_2.xlsx** workbook and go to the
_Consolidated Sales_ worksheet. Create external references as follows:
Link cell G6 in the
_Consolidated Sales_ worksheet to cell F6 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G7 in the
_Consolidated Sales_ worksheet to cell F7 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G8 in the
_Consolidated Sales_ worksheet to cell F8 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G9 in the
_Consolidated Sales_ worksheet to cell F9 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Do not break the links. Close the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
In cell G10, enter a formula to total the values in the defined range
**Totals_2023**, 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 website to submit your completed project.
#### Final Figure 1: Manager Information Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.
#### Final Figure 2: Idaho Worksheet
#### Final Figure 3: Montana Worksheet
#### Final Figure 4: Oregon Worksheet
#### Final Figure 5: Washington Worksheet
#### Final Figure 6: Consolidated Sales Worksheet
## image14.png
## image15.jpeg
## image16.jpeg
## image17.jpeg
## image18.jpeg
## image19.jpeg
## image20.jpeg
## image11.png
## image12.png
## image13.png
**NP_EX_5-1_MaryLynDanielakcopy.ods**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/np-ex-5-1-marylyndanielakcopy-ods)
**NP_EX_5-1_MaryLynDanielakcopy.ods**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/np-ex-5-1-marylyndanielakcopy-ods)
**## Instructions_NP_EX365_2021_5acopy.docx**
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
| |
| — |
| **2** |
## Narbonne Suites
### GENERATING REPORTS FROM MULTIPLE WORKBOOKSvvmmm
#### GETTING STARTED
Open the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx**, available for download.
Save the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx** as
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx**
Edit the file name by changing “1” to “2”.
If you do not see the
**.xlsx** file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:
Support_EX365_2021_5a_2023_Sales.xlsx
Support_EX365_2021_5a_Locations.docx
With the file
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx** open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select
**Don't Update** in the dialog box.
#### PROJECT STEPS
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.
In cell C5, remove the hyperlink, leaving the unlinked text "Bozeman, MT" in the cell.
In cell D7, create a hyperlink to an email address as follows:
Link to the email address:
**washingtonmgr@example.com**
Use
**washingtonmgr@example.com** as the text to display.
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:
Link to the file
**Support_EX365_2021_5a_Locations.docx**.
Use
**Narbonne Suites locations** as the text to display.
Use
**View the current list of Narbonne Suites Locations** as the ScreenTip text.
Edit the hyperlink in cell B10 as follows:
Use
**Narbonne Suites website** as the display text.
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:
Change the font size in the merged range A1:F1 to
**18 point**.
Apply the
**20% – Accent 6** cell style to the merged range A2:F2.
**Bold** the values in the range A6:A10.
Apply the
**Accounting** number format with zero decimal places and
**$** as the symbol to the range B6:F10.
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:
In cell A6, change the text "In Person" to read:
**Walkup**
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:
Enter a formula in cell B10 using the
**SUM** function that totals the sales for Q1.
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:
Change the worksheet name to
**Washington** for the copied worksheet.
Edit the text to read
**Washington** in the merged range A2:F2.
Clear the contents of the range B6:E9.
Edra wants to consolidate the data from each of the regions. Switch to the
_Consolidated Sales_ worksheet, then update the worksheet as follows:
In cell A6, enter a formula without using a function that references cell A6 in the
_Washington_ worksheet.
Copy the formula from cell A6 to the range A7:A9 without copying the formatting.
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.
Copy the formula from cell B6 to the range B7:B9 without copying the formatting.
Then copy the formulas and the formatting from the range B6:B9 to the range C6:E9.
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.
Create names from the range A7:E9 using the values shown in the left column.
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.
Change the defined name to
**Totals_2024** for the range F6:F9. [Mac Hint: Delete the existing defined name "Totals" and add the new defined name.]
Edra wants to compare 2024 sales totals to the sales totals for 2023 and needs to add the 2020 data to the
_Consolidated Sales_ worksheet. Open the file
**Support_EX365_2021_5a_2023_Sales.xlsx.** Switch back to the
**NP_EX365_2021_5a_FirstLastName_2.xlsx** workbook and go to the
_Consolidated Sales_ worksheet. Create external references as follows:
Link cell G6 in the
_Consolidated Sales_ worksheet to cell F6 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G7 in the
_Consolidated Sales_ worksheet to cell F7 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G8 in the
_Consolidated Sales_ worksheet to cell F8 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G9 in the
_Consolidated Sales_ worksheet to cell F9 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Do not break the links. Close the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
In cell G10, enter a formula to total the values in the defined range
**Totals_2023**, 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 website to submit your completed project.
#### Final Figure 1: Manager Information Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.
#### Final Figure 2: Idaho Worksheet
#### Final Figure 3: Montana Worksheet
#### Final Figure 4: Oregon Worksheet
#### Final Figure 5: Washington Worksheet
#### Final Figure 6: Consolidated Sales Worksheet
## image14.png
## image15.jpeg
## image16.jpeg
## image17.jpeg
## image18.jpeg
## image19.jpeg
## image20.jpeg
## image11.png
## image12.png
## image13.png
**NP_EX_5-1_MaryLynDanielakcopy.ods**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/np-ex-5-1-marylyndanielakcopy-ods)
**## Instructions_NP_EX365_2021_5acopy.docx**
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
New Perspectives Excel 365/2021 | Module 5: SAM Project 1a
| |
| — |
| **2** |
## Narbonne Suites
### GENERATING REPORTS FROM MULTIPLE WORKBOOKSvvmmm
#### GETTING STARTED
Open the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx**, available for download.
Save the file
**NP_EX365_2021_5a_**
**_FirstLastName__1.xlsx** as
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx**
Edit the file name by changing “1” to “2”.
If you do not see the
**.xlsx** file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:
Support_EX365_2021_5a_2023_Sales.xlsx
Support_EX365_2021_5a_Locations.docx
With the file
**NP_EX365_2021_5a_**
**_FirstLastName__2.xlsx** open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select
**Don't Update** in the dialog box.
#### PROJECT STEPS
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.
In cell C5, remove the hyperlink, leaving the unlinked text "Bozeman, MT" in the cell.
In cell D7, create a hyperlink to an email address as follows:
Link to the email address:
**washingtonmgr@example.com**
Use
**washingtonmgr@example.com** as the text to display.
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:
Link to the file
**Support_EX365_2021_5a_Locations.docx**.
Use
**Narbonne Suites locations** as the text to display.
Use
**View the current list of Narbonne Suites Locations** as the ScreenTip text.
Edit the hyperlink in cell B10 as follows:
Use
**Narbonne Suites website** as the display text.
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:
Change the font size in the merged range A1:F1 to
**18 point**.
Apply the
**20% – Accent 6** cell style to the merged range A2:F2.
**Bold** the values in the range A6:A10.
Apply the
**Accounting** number format with zero decimal places and
**$** as the symbol to the range B6:F10.
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:
In cell A6, change the text "In Person" to read:
**Walkup**
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:
Enter a formula in cell B10 using the
**SUM** function that totals the sales for Q1.
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:
Change the worksheet name to
**Washington** for the copied worksheet.
Edit the text to read
**Washington** in the merged range A2:F2.
Clear the contents of the range B6:E9.
Edra wants to consolidate the data from each of the regions. Switch to the
_Consolidated Sales_ worksheet, then update the worksheet as follows:
In cell A6, enter a formula without using a function that references cell A6 in the
_Washington_ worksheet.
Copy the formula from cell A6 to the range A7:A9 without copying the formatting.
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.
Copy the formula from cell B6 to the range B7:B9 without copying the formatting.
Then copy the formulas and the formatting from the range B6:B9 to the range C6:E9.
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.
Create names from the range A7:E9 using the values shown in the left column.
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.
Change the defined name to
**Totals_2024** for the range F6:F9. [Mac Hint: Delete the existing defined name "Totals" and add the new defined name.]
Edra wants to compare 2024 sales totals to the sales totals for 2023 and needs to add the 2020 data to the
_Consolidated Sales_ worksheet. Open the file
**Support_EX365_2021_5a_2023_Sales.xlsx.** Switch back to the
**NP_EX365_2021_5a_FirstLastName_2.xlsx** workbook and go to the
_Consolidated Sales_ worksheet. Create external references as follows:
Link cell G6 in the
_Consolidated Sales_ worksheet to cell F6 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G7 in the
_Consolidated Sales_ worksheet to cell F7 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G8 in the
_Consolidated Sales_ worksheet to cell F8 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Link cell G9 in the
_Consolidated Sales_ worksheet to cell F9 in the
_Consolidated Sales_ worksheet in the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
Do not break the links. Close the
**Support_EX365_2021_5a_2023_Sales.xlsx** workbook.
In cell G10, enter a formula to total the values in the defined range
**Totals_2023**, 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 website to submit your completed project.
#### Final Figure 1: Manager Information Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.
#### Final Figure 2: Idaho Worksheet
#### Final Figure 3: Montana Worksheet
#### Final Figure 4: Oregon Worksheet
#### Final Figure 5: Washington Worksheet
#### Final Figure 6: Consolidated Sales Worksheet
## image14.png
## image15.jpeg
## image16.jpeg
## image17.jpeg
## image18.jpeg
## image19.jpeg
## image20.jpeg
## image11.png
## image12.png
## image13.png
**NP_EX_5-1_MaryLynDanielakcopy.ods**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/np-ex-5-1-marylyndanielakcopy-ods)