New Perspectives Excel 365 | Module 5: End of Module Project 1 Westwood Restaurant Group
GENERATING REPORTS FROM MULTIPLE WORKBOOKS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_EOM5-1_FirstLastName_1.xlsx as NP_EX365_EOM5-1_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. To complete this Project, you will also need the following files:
Support_EX365_EOM5-1_2027.xlsx
3. With the file NP_EX365_EOM5-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Daniela Ramos is the finance manager of Westwood Restaurant Group, a local chain of restaurants in Seattle, Washington. The restaurant has locations in Downtown, Capitol Hill, and the University District. Daniela is using an Excel workbook with multiple worksheets to create a profit and loss statement for the restaurant group. She asks for your help in completing the statement. Go to the Dashboard worksheet. Daniela first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.westwoodrestaurants.cengage.com website.
2. In cell H8, create a hyperlink to the info@westwoodrestaurants.cengage.com email address. Use Contact Westwood Restaurant Group as the ScreenTip text.
3. In cell H9, create a hyperlink to cell A1 in the Categories worksheet.
4. In cell H10, create a hyperlink to the workbook Support_EX365_EOM5-1_2027.xlsx, which contains the Westwood Restaurant Group's profit and loss data from 2027.
5. Daniela wants to compare 2027 profit and loss totals to those for 2028 and needs to add the 2027 data to the Dashboard worksheet. Open the file Support_EX365_EOM5-1_2027.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboard worksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX365_EOM5-1_2027.xlsx workbook. Delete the unnecessary values in cells C8, C12, and C16, and close the Support_EX365_EOM5-1_2027.xlsx workbook.
6. Daniela needs a quick way to refer to the budgeted payroll and operating expenses. Use the name Payroll to define the range D9:D11. Use the name Operating to define the range D13:D15.
7. In cell D18, enter a formula using the SUM function to total the expense values using the defined range names Payroll and Operating.
8. Daniela prefers that accountants and others contact Westwood Restaurant Group through the link in cell H8 rather than one that connects to her email address. In cell C21, remove the hyperlink, leaving the unlinked text "Daniela Ramos" in the cell.
9. Daniela wants to combine the profit and loss data for 2028 from each of the three Westwood restaurant locations. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Downtown:UniversityDistrict worksheets. Copy the formula from cell E6 to cell E7, the range E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, copy the formula and number formatting only.
10. Go to the Downtown worksheet. Daniela needs to calculate the percent of sales for each expense item. Assign the defined name Revenue to cell C6.
11. In cell D7, enter a formula without using a function that divides the Cost of Sales value in cell C7 by the revenue amount, using the defined name Revenue for the Downtown worksheet. Copy the formula from cell D7 to the range D9:D11, the range D13:D15, and the range D17:D19. In the range D17:D19, copy the formula and number formatting only.
12. Daniela wants to apply consistent formatting to the worksheets she collected from the three Westwood restaurants. Group the Downtown, CapitolHill, and UniversityDistrict worksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard worksheet. Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets.
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: Dashboard Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Downtown Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: CapitolHill Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: UniversityDistrict Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Categories Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.