Reference no: EM132684224
YO19_Excel_Ch07_PS1_Parks
Project 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: Minnesota, Iowa, and Wisconsin. 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. Downloaded and open the Excel file named Excel_CH07_PS1_Parks.xlsx. Grader has automatically added your last name to the beginning of the file name. Save the file to a location where you are storing your files.
2 When worksheets are grouped, what you do to one worksheet happens to the other worksheets in the group. To save yourself time and ensure consistency, you will group the Quarter 1-4 worksheets and then create formulas and complete formatting.
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 referencing to the appropriate rates in the Rates worksheet.
Leave the worksheets grouped.
3 With the worksheets still grouped, in cells B11:E11, calculate the totals of the columns.
4 With the worksheets still grouped, format cells B6:C11 with the Comma style and no decimals.
Format cells D11:E11 with the Accounting Number Format.
AutoFit columns A:E.
Apply the Total cell style to cells B11:E11.
Type Total in cell A11.
Ungroup the worksheets.
5 3-D references allow formulas and functions to use data from cells and cell ranges across worksheets.
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.
6 To save yourself time in formatting the Summary worksheet, use the Fill Across Worksheets feature to copy the formatting for cells B6:E10 from the Quarter4 worksheet to the Summary worksheet.
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.
8 On the LinkedSummary worksheet, you want to consolidate data and include the cell references from other worksheets that contribute to the consolidated data result.
On the LinkedSummary worksheet, in cell A5, create a linked consolidation using cells A5:E11 from the Quarter1, Quarter2, Quarter3, and Quarter4 worksheets. Select Top row, Left column, and Create links to source data in the Consolidate dialog box.
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.
10 Save the Excel_CH07_PS1_Parks.xlsx open workbook as Excel_CH07_PS1_ParksLink_LastFirst
Delete the Rates worksheet, and then click the Quarter1 worksheet tab.
Open the Excel_CH07_PS1_Parks.xlsx workbook.
Arrange the two workbooks so you can view them side by side.
11 Next, you want to create a link between two workbooks to ensure the rates in the ParksLink workbook are always always up to date by linking to the rates in the Parks workbook.
On Excel_CH07_PS1_ParksLink_LastFirst, group the Quarter1 through Quarter4 worksheets.
Click cell D6, and then in the formula bar replace the #REF!B6 with a reference to cell B6 on the Rates worksheet on Excel_CH07_PS1_Parks.xlsx.
Change the cell reference $B$6 to a relative reference - to remove the absolute reference - and then copy the formula to cell D6 to cells D7:E10.
Ungroup the sheets.
12 Save, and then exit Excel. Submit the file Excel_CH07_PS1_ParksLink_LastFirst.xlsx as directed.
Attachment:- Project Description.rar