EX 5-12 Review
EX 5-12a Practice: Review Assignments
Data Files needed for the Review Assignments: NP_EX_5_3.xlsx, Support_EX_5_Peoria.xlsx, Support_EX_5_Region1.xlsx, Support_EX_5_Region2.xlsx, Support_EX_5_Region3.xlsx, Support_EX_5_Region4.xlsx
Gail wants to get a monthly sales and expense report from each franchise so that the company can catch issues early and offer suggestions to franchises that are underperforming. Gail started a summary workbook that will contain the collected data. She needs you to finalize the workbook. Complete the following:
1. 1.
Open the NP_EX_5-3.xlsx workbook located in the Excel5 > Review folder included with your Data Files. Save the workbook as NP_EX_5_Report in the location specified by your instructor.
2. 2.
In the Documentation sheet, in the range B3:B4, enter your name and the date.
3. 3.
Change your name to a hyperlink pointing to your email address using the subject heading Monthly Sales and Expenses Report for the message and Email me for more info as the ScreenTip text.
4. 4.
Open the Support_EX_5_Peoria.xlsx workbook located in the Excel5 > Review folder. Copy the Grill5-08 worksheet into the NP_EX_5_Report workbook, placing the worksheet at the end of the workbook.
5. 5.
Create a worksheet group from the Grill5-01 through Grill5-08 worksheets. In the worksheet group, select the nonadjacent range A7:B7,A11:B11,A16:B16, and then create named ranges from the selection using the labels in the left column.
6. 6.
Use the Name Manager to change the TOTAL_COST_ OF_ GOODS_SOLD, TOTAL_PAYROLL_COSTS, and TOTAL_SALES named ranges for the Grill5-01 worksheet from global scope to local scope by deleting those names and recreating them, limiting them to the scope of the Grill5-01 worksheet. Verify in the Name Manager that all the defined names in the workbook have local scope.
7. 7.
In the range B5:C17 of the Grill5-01 through Grill5-08 worksheets, replace the cell references to cells B7, B11, and B16 with the TOTAL_SALES, TOTAL_COST_OF_GOODS_SOLD, and TOTAL_PAYROLL_COSTS defined names. You can either use the Apply Names command or find and replace the cell reference with the range name.
8. 8.
In the Region Report worksheet, in cell F5, use the SUM function to calculate the sum of cell B5 in the Grill5-01 through Grill5-08 worksheet group, displaying the total income from food sales.
9. 9.
Use AutoFill to extend the formula in cell F5 through the range F5:F17. Fill without formatting in the range. Delete the zeros in cells F8 and F12.
10. 10.
Open the Support_EX_5_Region1.xlsx file located in the Excel5 > Review folder. Copy the range B5:B17 of the Region 1 worksheet, and then use the Paste Link command to paste the external reference to the copied cells in the range B5:B17 of the Region Report worksheet. Delete the zeroes in cells B8 and B12.
11. 11.
Repeat Step 10 for the Region 2 through Region 4 data located in the Support_EX_5_Region2.xlsx through Support_EX_5_Region4.xlsx workbooks, pasting their external references in the ranges C5:C17, D5:D17, and E5:E17, respectively. Delete the zeroes in rows 8 and 12.
12. 12.
Copy the Grill5-01 worksheet to a new workbook so you can create a template of the Sales and Expenses worksheet with all the data removed, but the formulas and formatting retained.
13. 13.
Save the NP_EX_5_Report workbook, and then close it.
14. 14.
In the new workbook you created in Step 12 , make the following changes to the Grill5-01 worksheet:
1. Change the worksheet name to Franchise.
2. Change the text of cell A2 to [Region] Monthly Sales and Expenses.
3. Delete the data in the nonadjacent range B5:B6, B9:B10, B13:B15, F4:F9.
15. 15.
Save the workbook as a template with the file name NP_EX_5_Sales.xltx in the location specified by your instructor.
16. 16.
Save and close all the workbooks you used in the Review Assignments.
