Reference no: EM131422165

Park Management LLC

Project Description:

Managers at the hotel have been using a simple dashboard to keep track of revenues and room reservations. You have been asked to enhance the dashboard by adding some form controls, a Bing map, and VBA.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Instructions

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

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.

3 In cells B11:E11, calculate the total adults and children visitors as well as the adult and child admission collected.

4 Format cells D11:E11 with the Accounting Number Format. Format the number of visitors 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.

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 Quarter through Quarter4.

6 Use Fill Across Worksheets 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, 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.

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 Delete the Rates worksheet. Open the file e04ws07_grader_h1_ParkRates.xlsx. Arrange the two workbooks so you can see them side by side. On e04ws07Parks_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 e04ws7_grader_h1_ParkRates.

11 In cell D6, change the cell reference $B$6 to a relative reference to remove the absolute reference, and then copy the formula to cell E6 and cells D7:E10. Ungroup the sheets. Close e04ws07_grader_h1_ParkRates.

12 On e4ws7Parks_LastFirst, if necessary, add the Compare Merge Workbooks button to the Quick Access Toolbar. Share e4ws7Parks_LastFirst and allow changes to be made. Save e4ws7Parks_LastFirst as e4ws7Parks2_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.

13 Open e4ws7Parks_LastFirst, click Enable Content, and compare and merge the workbook with e4ws7Parks2_LastFirst. Save and close the workbooks.

14 Submit the file e04ws07Parks_LastFirst.xlsx with the following worksheets (in this order): Quarter1, Quarter2, Quarter3, Quarter4, Summary, LinkedSummary, and Documentation.

Attachment:- Data_Files.rar