Start Excel. Open the downloaded Excel file named e04ch07_grader_a1_Parks.xlsx. Save it as e04ch07_grader_a1_Parks_LastFirst using your last and first name. Enable content if necessary.

Group worksheets Quarter1 through Quarter4. Create formulas in the cell range D6:E10 to calculate the admission collected from both adult and children visitors for each location using the appropriate rates in the Rates worksheet. Widen column widths, if necessary.

With the worksheets still grouped, in cells B11:E11, calculate the total adult and children visitors as well as the adult and child admission collected.

Format cells D11:E11 with the Accounting Number Format. Format the number of visitors in B6:C11 with the Comma Style and no decimals. AutoFit columns A:E. Add a bottom border to cells B10:E10, and then type Total in cell A11.

On the Summary worksheet, enter a 3-D SUM function in cells B6:E10 to calculate the total visitors and admissions for each category and location from Quarter1 through Quarter4.

Use Fill Across Worksheets to copy the formatting for cells B6:E10 from the Quarter4 worksheet to the Summary worksheet. Adjust column widths on the Summary worksheet, if necessary.

Use Fill Across Worksheets to copy the content and formatting from cells A11:E11 on the Quarter4 worksheet to the Summary worksheet. AutoFit columns B:E in the Summary worksheet.

On the LinkedSummary worksheet, in cell A5, create a linked consolidation using cells A5:E11 from each of the quarter worksheets. Select Top row, Left column, and Create links to source data in the Consolidate dialog box.

Change the column width of column A to 20, hide column B, and use the AutoFit feature for columns C:F. Save the workbook.

In the open e04ch07Parks_grader_a1_LastFirst workbook, delete the Rates worksheet. Open the downloaded file e04ch07_grader_a1_ParkRates.xlsx. Click the Rates worksheet. Arrange the two workbooks so you can see them side by side. On e04ch07Parks_grader_a1_LastFirst, group the Quarter1 through Quarter4 worksheets. Click cell D6, and then in the Formula Bar, replace #REF!B6 with a link to cell B6 on the Rates worksheet of the e04ch07_grader_a1_ParkRates workbook.

In cell D6, change the cell reference $B$6 to a relative reference to remove the absolute reference, and then use the fill handle to copy to cell E6 and cells D7:E10. Select D10:E10 and reapply the bottom border. Ungroup the sheets. If necessary, adjust column width. Close e04ch07_grader_a1_ParkRates.

On e04ch07_grader_a1_Parks_LastFirst workbook, if necessary, add the Compare Merge Workbooks button to the Quick Access Toolbar. Share e04ch07_grader_a1_Parks_LastFirst workbook and allow changes to be made. Save e04ch07_grader_a1_Parks_LastFirst workbook as e04ch07_grader_a1_Parks2_LastFirst, using your last and first name. On the Quarter1 sheet, change the Adult visitors to Splash Park to 250 and the Children visitors to 576. Save the changes and close the workbook. Open e04ch07_grader_a1_Parks_LastFirst.xlsx, click Enable Content, and compare and merge the workbook with e04ch07_grader_a1_Parks2_LastFirst.xlsx. Save and close the workbooks. Note, Mac users, on the Review tab, click Share Workbook. Ensure that the Allow changes by more than one user at the same time option is selected, and then click OK. From the Tools menu, click Merge Workbooks.

Exit Excel and submit the file e04ch07_grader_a1_Parks_LastFirst.xlsx as directed by your instructor.

x DESO FILE HOME INSERT e04ch07_grader_al_Parks_HottSamantha (Group] - Excel REVIEW VIEW ? - A Samantha Hott PAGE LAYOUT FORMULAS DATA Calibri - 11 AA === AutoSum' Wrap Text 3 Merge & Center, B IU - A E Accounting $ - % , Number 809 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Cells B C D E F G H I J K L M N O P Q R 1 Park Management LLC 2 Indiana Visitors Admission Adults Children Adult Child 5,268 3,654 12,556 43,521 11,574 14,962 6 History Museum 7 City Zoo 8 Park Central 9 Splash Park 10 Monument 11 Total 3,368 1,254 27 Quarter1 Quarter2 Quarter3 Quarter4 Summary LinkedSummary Rates + READY @ -- + 100% x DESO FILE HOME INSERT e04ch07_grader_al_Parks_HottSamantha (Group] - Excel REVIEW VIEW ? - A Samantha Hott PAGE LAYOUT FORMULAS DATA Calibri - 11 AA === AutoSum' Wrap Text 3 Merge & Center, B IU - A E Accounting $ - % , Number 809 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Cells B C D E F G H I J K L M N O P Q R 1 Park Management LLC 2 Indiana Visitors Admission Adults Children Adult Child 5,268 3,654 12,556 43,521 11,574 14,962 6 History Museum 7 City Zoo 8 Park Central 9 Splash Park 10 Monument 11 Total 3,368 1,254 27 Quarter1 Quarter2 Quarter3 Quarter4 Summary LinkedSummary Rates + READY @ -- + 100%