Reference no: EM132675357
Review Assignment
Reveries Urban Centers opened a new rental center in Midland, Michigan, on January 1, 2017. Michael Verhallen, the manager of the Midland rental center, collected the year's rental income in a workbook provided by Timothy Root at the central ofce. Before Michael can send the completed workbook to Timothy for the year-end reporting, he needs you to summarize the results and format the worksheets. Complete the following:
1. Open the Midland workbook located in the Excel6 > Review folder included with your Data Files, and then save the workbook as Midland MI in the location specied by your instructor.
2. In the Documentation worksheet, enter your name and the date, and then review the worksheets in the workbook.
3. worksheet group that contains the Quarter 1 through Quarter 4 worksheets.
4. In the worksheet group, in the range B12:D12, enter formulas that sum the values in each column, and then in the range E6:E12, enter formulas that sum the values in each row.
5. Format the quarterly worksheets as specied below:
a. In cell E5 and cell A12, enter the label Totals.
b. Increase the indent of cell A12 by one.
c. In the range A2:A3, A6:A12;B5:E5, bold the text.
d. Merge and center the range A1:E1 and the range A2:E2.
e. In the range B5:E5, center the text.
f. Add a bottom border to the range B5:E5 and the range B11:E11.
g. Format the range B6:D6,B12:D12,E6:E12 with the Accounting style and no decimal places.
h. Format the range B7:E11 with the Comma style and no decimal places.
6. Ungroup the worksheets, make a copy of the Quarter 1 worksheet, rename it as Summary, and then place it after the Documentation worksheet.
7. In the Summary worksheet, make the following changes:
a. In cell A2, change the heading to Midland Total.
b. Change cell A3 to Fiscal Year - 2017.
c. Insert a column between columns B and C.
d. In the range B5:E5, change the headings to Quarter 1, Quarter 2, Quarter 3, and Quarter 4, respectively.
e. Make sure that the text in the range B5:F5 is centered.
f. Clear the contents of the range B6:F11.
8. Complete the formulas in the Summary worksheet, as follows, using the Fill Without Formatting paste option so that you can keep the bottom border on the range B11:F11.
a. In the range B6:E11, create formulas that reference cells in other worksheets to display the quarterly totals for the rental income by type. For example, the formula in cell B6 will reference cell E6 in the Quarter 1 worksheet.
b. In the range F6:F11, create formulas that use 3-D cell references to calculate the total for each type of rental income.
c. Copy the formula from cell B12 to cell C12.
9. Change the March rental income for medical centers to 1976, and then verify that the total rental income for Medical Centers in Quarter 1 in the Summary worksheet is $5,214, the total rental income in Quarter 1 is $19,538, the total rental income for medical centers in 2017 is $22,186, and the total rental income for 2017 is $82,286.
10. Group the Quarter 1 through Quarter 4 worksheets, and then enter a formula in cell A4 that references cell A3 in the Summary worksheet.
11. In cell A8 of the Documentation worksheet, insert a hyperlink that points to the NewUCMemo located in the Module6 > Review folder included with your Data Files. Make sure the text to display is Click here to read Midland Executive Memo.
12. Edit the hyperlink to use the ScreenTip Midland Rental Center Summary for 2017.
13. Save the Midland MI workbook, and leave it open.
14. Open the NewUC workbook located in the Excel6 > Review folder included with your Data Files, and then save the workbook as New Urban Centers in the location specied by your instructor.
In the Documentation worksheet enter your name and the date. Open the JacksonMI, PetoskyMI, and FlintMI workbooks located in the Excel6 > Review folder included with your Data Files.
15. Make the New Urban Centers the active workbook, and then arrange the workbooks in a tiled layout. The New Urban Centers is the full height of the screen on the left with the remaining four taking the rest the screen. In each workbook, hide the ribbon so you can see as much data in the Summary worksheet as possible.
16. In the Summary worksheet of the New Urban Centers workbook, enter external reference formulas to create set of linked workbooks to summarize the totals for JacksonMI, PetoskyMI, Midland MI, and FlintMI. Format the Summary worksheet in the New UC Totals workbook so that the numbers are readable and the range B11:F11 has a bottom border.
17. Maximize the New Urban Centers and Midland MI worksheets, making sure that the ribbon is displayed. Save the New Urban Centers workbook, and leave it open. Close the JacksonMI, PetoskyMI, and FlintMI workbooks.
18. In the New Urban Centers workbook, break the links. Select a cell, and notice that the formula has been replaced with a value. Save the workbook as New UC Audited 2018.
19. Create headers and footers for the Summary worksheet. Display the name of the workbook and the name of the worksheet on separate lines in the right section of the header. Display your name and the date on separate lines in the right section of the footer. Save the New UC Audited 2018 workbook, and then close it.
20. Use the Midland MI workbook to create n Excel template with the lename Midland MI Template in the location specied by your instructor.
21. Create new workbook based on the Midland MI Template le, and then save the workbook as Midland MI 2018 in the location specied by your instructor. In the Documentation worksheet, enter your name and the date.
22. In the Summary worksheet, enter 2018 as the scal year in cell A3. The Center Name should be Midland in all worksheets. In the Quarter 1 worksheet, enter 500 in each cell in the range B6:D11. In the Quarter 2 worksheet, enter 1000 in each cell in the range B6:D11. Conrm that the values entered in this step are correctly totaled in the Summary worksheet.
23. Save the Midland MI 2018 workbook, and then close it.
Attachment:- Review Assignment.rar