for Nahor

E_CH08_EXPV2_EOC_Instructions.docx

Office 2010 – myitlab:grader – Instructions Exploring Excel Ch. 08 – EOC Project

Downtown Theater Sales

Project Description: You are an accounting assistant for Downtown Theater in San Diego. Your task is to analyze the weekly and monthly ticket sales by seating type for the fourth quarter of the year. To complete this project, you will create validation rules, locate and fix invalid data, enter and format data on grouped worksheets, create 3-D formulas, and insert hyperlinks. Additionally, you will use the Error Checking feature to locate and correct errors in the formulas.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download, save, and open the Excel workbook named Exploring_e08_Grader_EOC.xlsx. Click OK to acknowledge the error. 0 2 On the Week 1 worksheet, create a validation rule for the range C3:G3 so that only whole numbers that are less than or equal to 86 are accepted. Create an input message using the text Orchestra Front as the title and Enter the number of tickets sold per day. (including the period) as the message. Create an error alert using the stop style. Title the error alert Invalid Entry and enter Please enter a whole number that is less than or equal to 86. (including the period) as the message. 8 3 Circle the invalid data on the Week 1 worksheet, and then change each invalid entry to the maximum number of applicable seats. 4 4 Group the Week 1, Week 2, Week 3, and Week 4 worksheets together. In cell C11, insert a formula that will calculate Sunday’s Orchestra Front revenue, which is based on the number of seats sold and the price per seat. Modify the price per seat reference so that the column reference is absolute. Copy the formula to the range C11:G14. 9 5 With the worksheets grouped together, in cell H11, insert a formula to calculate the weekly seating totals. Copy the formula down through cell H14. 9 6 With the worksheets still grouped together, in cell C15, insert a formula to calculate the total daily revenue. Copy the formula across through cell H15. 9 7 With the worksheets still grouped together, apply the accounting number format with zero decimal places to the Orchestra Front revenue and the total revenue rows. Apply the comma style with zero decimal places to the remaining seating revenue rows. 6 8 With the worksheets still grouped together, apply a regular underline to the data in the range containing the Balcony Level 2 revenue (just like in cells C6:H6). Apply a double underline for the total revenue values in the last row (just like in cells C7:H7). Ungroup the worksheets. 4 9 Display the Week 1 worksheet, and then create a hyperlink in cell A1 to the Documentation worksheet. 5 10 Group the Week 1, Week 2, Week 3, Week 4, and October worksheets. With the Week 1 worksheet displayed, use the Fill Across Worksheets command to copy the link and formatting in cell A1 to the other worksheets. Ungroup the worksheets and test the hyperlinks. 4 11 Display the October worksheet. In cell C11, insert a 3-D formula to calculate the total Sunday Orchestra Front revenue for all four weeks. Copy the formula for the remaining seating types, weekdays, total row, and total column. 10 12 Copy the formatting in the range C11:H15 on the Week 4 worksheet to the same range on the October worksheet. 5 13 On the October worksheet, in cell C3, insert a 3-D formula to calculate the overall percentage of total Sunday Orchestra Front tickets sold based on the total available Orchestra Front seating. Modify the total available seating reference so that the column reference is absolute. Copy the formula to the range C3:G7. 10 14 On the October worksheet, in cell H3, insert a formula to calculate the average daily percentage of seats sold. Do not use a 3-D formula. Copy the formula down through H7. 6 15 Display the November worksheet. Show precedents for cell H11, and then correct the formula so that the seat price is not included in the weekly total. 2 16 Activate the Error Checking dialog box to find the first potential error on the November worksheet. When detected, correct the error in cell H15 so that the formula calculates the total sales revenue for the month. Ignore all other potential errors on the worksheet. 2 17 Use Error Checking to identify the circular reference on the November worksheet. Display the precedents arrow for the cell, and then fix the error. 2 18 Display the Fourth Quarter worksheet, and then open the downloaded Excel file named e08theater. Tile the windows. In cell E3 on the Fourth Quarter worksheet, insert a reference to the weekly total for Orchestra Front seating in the month of December. Continue creating links to the remaining individual monthly seat revenue and the monthly total for December. Close the e08theater workbook. 5 19 Ensure that the worksheets in the Exploring_e08_Grader_EOC file are correctly named and placed in the following order in the workbook: Fourth Quarter, Documentation, Week 1, Week 2, Week 3, Week 4, October, and November. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Updated on: 5/24/2010 1 E_CH08_EXPV2_EOC_Instructions.docx