New Perspectives Excel 2019 | Module 5: SAM Critical Thinking Project 1c Narbonne Suites GENERATING REPORTS FROM MULTIPLE WORKBOOKS GETTING STARTED Open the file NP_EX19_CT5c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

New Perspectives Excel 2019 | Module 5: SAM Critical Thinking Project 1c

Narbonne Suites

GENERATING REPORTS FROM MULTIPLE WORKBOOKS

GETTING STARTED

• Open the file NP_EX19_CT5c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT5c_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_CT5c_2020_Sales.xlsx
  • Support_EX19_CT5c_Locations.docx
• With the file NP_EX19_CT5c_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.
• 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

1. Edra Kinsman is the sales manager for the 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 workbook, so that the formulas in the table on the Manager Information worksheet are replaced with static values. Then switch to the Manager Information worksheet.
2. Remove the hyperlink in the Location column, leaving the unlinked text "Bozeman, MT" in the cell.
3. For the Washington manager, create a hyperlink to an email address as follows:
   1. Link to the email address: washingtonmgr@example.com
   2. Use washingtonmgr@example.com as the text to display.
   3. Use Email the statewide manager for Washington as the ScreenTip text.
4. In the cell above the website link, create a hyperlink to the Narbonne Suites Locations listing as follows:
   1. Link to the file Support_EX19_CT5c_Locations.docx.
   2. Use Narbonne Suites locations as the text to display.
   3. Use View the current list of Narbonne Suites Locations as the ScreenTip text.
5. Edit the website hyperlink as follows:
   1. Use Narbonne Suites website as the display text.
   2. Use View the public-facing website for Narbonne Suites as the ScreenTip text.
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:
   1. Change the font size of the Narbonne Suites heading to 18 point.
   2. Apply the 20% - Accent 6 cell style to the heading with the state name.
   3. Bold the four sales categories as well as the label for the total row.
   4. Apply the Accounting number format with zero decimal places and the $ symbol to all of the numerical data.
   5. Resize the width of columns B-F to 12. Do not ungroup the worksheets.
7. With the Idaho, Montana, and Oregon worksheets still grouped, update the worksheet as follows:
   1. Change the text "In Person" to read: Walkup
   2. Change the text "Other" to read: Other referrals
      Do not ungroup the worksheets.
8. With the Idaho, Montana, and Oregon worksheets still grouped, create a formula as follows:
   1. Use a function to total the sales for Q1.
   2. Use the same function to fill the remaining sales totals. Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 5-8 are present in all three worksheets.
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 copied worksheet as follows:
   1. Change the worksheet name to Washington for the copied worksheet.
   2. Edit the state heading text to read Washington.
   3. Clear the quarterly numerical data for the four sales categories. Be sure to leave the totals.
10. Edra wants to consolidate the data from each of the regions. Switch to the Consolidated Sales worksheet, and then update the worksheet as follows:
    1. Without using a function, begin filling in the sales categories using a reference to cell A6 in the Washington worksheet .
    2. Without copying the formatting, use a similar reference to fill the remaining sales categories.
    3. Enter a function using 3-D references and grouped worksheets to total the Q1 walkup sales from the Idaho through Washington worksheets.
    4. Without copying the formatting, use a similar function to fill the remaining Q1 sales values.
    5. Use a similar function to fill the remaining quarterly sales values, filling the formatting.
11. Edra started to create named ranges in the worksheet and has asked you to complete the work. Create a defined name for the quarterly walkup values using Walkup as the range name.
12. In a similar way, create names for the other sales categories' quarterly values using the categories as the range names.
13. Modify the quarterly sales totals by using the existing defined names Q1_Sales, Q2_Sales, Q3_Sales and Q4_Sales
     
14. Change the existing defined name from Total to Totals_2021. [Mac Hint: Delete the existing defined name "Total" 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_CT5c_2020_Sales.xlsx. Switch back to the NP_EX19_CT5c_FirstLastName_2.xlsx workbook and go to the Consolidated Sales worksheet. Create external references as follows:
    1. Add the 2020 total walkup sales to the Consolidated Sales worksheet using a reference to the appropriate cell in the Consolidated Sales worksheet in the Support_EX19_CT5c_2020_Sales.xlsx workbook.
    2. Add the remaining sales categories totals in the same way.
       Do not break the links. Close the Support_EX19_CT5c_2020_Sales.xlsx workbook.
16. To finish, use a function to total the 2020 sales values using the existing 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.

Final Figure 1: Manager Information Worksheet

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