Answer Happy

Data Files needed for the Review Assignments. Midland.xlsx, - Wellemo.docx, NewUC. xlax, JacksonM Petoskymi.xlsx Flintmi.xIsx Midland, Michugan, on January 1,301/ Machael Verinall Nanager of the Midland rental center, collected the year's rental income in a workbook provided by Timothy Root at the workbook to Timothy for the year-end reporting he need you to summarize the results and format the worksheets Complete the following 1 Open the Midland workbook located in the Excel5 > Reve foicer included with your Data Files and then save the instructor 2 in the Documentation worksheet, enter your name and the 3 Create a worksheet group that contains the Quarter through Quarter 4 worksheets 4 in the worksteet group, in the range B12 012 enter formulas that sum the values n each cokmn, and then in the 5 Format the quarterly worksheets as specified below. a In cell E5 and cell A12 enter the label Tot c. in the range A2:A3,A6:A12;B5 E5, bold the text. a 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.E17 a Format the range B6.D6, B12 D12 E6. E12 with the Accounting style and no decimal places. 7. Format the range B7:E11 with the Comma style and no docmaiplaces worksheet, rename it as Summary, and then place in after the Documentation work hieet. 7 in the Summary worksheet, make the following changes a in cell A2, change the headng to Midland Total b. Change cell A 3 to Fiscal Yeor - 2017 c. Insert a column between columns B and C dn the range B5. E5, change the headings to Quarter 1 , Quarter 2, Quarter 3 , and Quarter 4, respectively. f. Claar the contents of the range 86.F11 8 Complete the formulas in the Summary worksheet, as follows, using the Fill Wehcut Formattig 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 renta reference cell E6 n the Quarter 1 worksincet b. In the range F6. F11, create formulas that use 3-D cell c Copy the formula from coll B12 to cell C12 9 Change the March rental income for medical centers to 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,188, and the total 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 ท the 11 in cell A8 of the Documentation worksheet, n sert a Hyper link that points to the NewUCMemo located n the Noctule > Rev ew folder included with your Data Files. 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 > workbook as New Urban Centers in the location speciffed by. your nstructor In the Documentation worksheet enter your name and the date Open the JacksonML PetoskyML and FlintMI with your Data Files 15 Make the New Urban Centers the active workbook, and then arrange the workbooks irl a tiled layout The New Urban remaning four takng the rest the screen in each workbook. thide the ribbon so you can see as much data in the Summary worksheet aspossible. I6 In the Summary worksheet of the New Urban Centers of linked workbooks to summarize the totals for Jackson PetoskyMl. Midand ML and FlintM. Format the Summary werksheet in the New UC Totals workbook so that the border. 17. Maximize the New Urban Centers and Midand MI. worksheets making sure that the ribbor is displayed. Save the New Lrban Centers workbock, and leave it open Close 18 in the New Urban Centers workbook, break the links Select a cell, and notice that the formula has been replaced with a vaue. Save the workbock as New UC Audited 2018 19 Create headers and footers for the Summary workstiee worksheet on separate lines in the right section of the header. Display your name and the chate cn separate Ines in the right section of the footer Save the New UC Audited 2018 workbook and then close ri. 20 Use the Micland MI workbook to create an Excel template specified by your instructor. 21 Create a new workbook based on the Midiland MI 2018 in the location specified by your in structor. in the Documentation worksheet, enter your name and the date 22 in the Summary worksheet, enter 2018 as the fiscalyegr in cell A3. The Center Name shoud be Micland in all cell in the range B6:D11 in the Quarter 2 worksh eet, enter 1000 in each cell in the range B6011 Confirm that the values worked in this step are correctly totaled in the Summary 23 Save the Midland MI 2018 workbook, and then close it
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 office. 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 Excel > Review folder included with your Data Files, and then save the workbook as Midland MI in the location specified by your instructor. 2. In the Documentation worksheet, enter your name and the date, and then review the worksheets in the workbook. 3. Create a 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 specified below: a. In cell E5 and cell A12, enter the label Totals. h. Increase the indent of cell A12 hv nne
5. Format the quarterly worksheets as specified 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.
b. In the range F6:F11, create formulas that use 3-D cell references to calculate the fotal 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. Excel | Module 6 Managing Multiple Worksheets and Workbooks 11. In cell AB 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 specified 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.
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 specified 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.