Please do the excel in the excel sheets I gave you. The grading system can detect cheating behavior Annaliese OwenProject Description: As the newest staff member for Park Management LLC, your supervisor Andrea Ramirez has asked you to consolidate information that has been collected for each of the company’s locations. Park Management LLC manages museums and parks in three states: Indiana, Ohio, and Kentucky. Each state has a workbook with quarterly data, and the management would like to see this data consolidated into one report. On each workbook there is also a sheet for Rate information, but this is not always current, so you will need to link the workbooks to the master Rate workbook that another staff member updates. Steps to Perform: Step Instructions Points Possible 1 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. 0 2 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. 0.8 3 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. 1.6 4 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. 1.6 5 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. 1.8 6 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. 0.8 7 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. 1.6 8 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. 2.6 9 Change the column width of column A to 20, hide column B, and use the AutoFit feature for columns C:F. Save the workbook. 2 10 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. 1.2 11 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. 4 12 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. 2 13 Exit Excel and submit the file e04ch07_grader_a1_Parks_LastFirst.xlsx as directed by your instructor. 0