Advanced Excel homeworks

EX16XLCH09GRADERCAPAS_-_Downtown_Theater_14_Instructions.docx

Office 2016 – myitlab:grader – Instructions Excel Project

EX16_XL_CH09_GRADER_CAP_AS - Downtown Theater 1.4

Project Description: You are an accounting assistant for Downtown Theater in San Diego. The theater hosts touring plays and musicals five days a week, including matinee and evening performances on Saturday. You want to analyze weekly and monthly ticket sales by seating type.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Download and open the file exploring_e09_grader_a1_Theater.xlsx. Acknowledge the error, and then save the file as exploring_e09_grader_a1_Theater_LastFirst, replacing LastFirst with your name. 0.000 2 On the Week 1 worksheet, select the number of daily Orchestra Front tickets sold (in the range C3:G3) and create a validation with these specifications: (1) Whole numbers between 0 and the available seating limit in cell B3. (2) Input message title Orchestra Front and input message Enter the number of tickets sold per day. (include the period). (3) Error alert Stop, alert title Invalid Data, and error message You entered an invalid value. Please enter a number between 0 and 86. (include the period). 10.000 3 Group the four weekly worksheets. Enter a formula in cell C11 to calculate Sunday’s Orchestra Front revenue, which is based on the number of seats sold and the price per seat. Use relative and mixed cell references correctly. Copy the formula for the Sunday column to complete the entire range of weekdays C11:G14. 8.000 4 In the range C15:G15, insert a function to calculate the total daily revenue. In the range H11:H15, insert a function to calculate the weekly totals for the seating categories and grand total. 8.000 5 Indent and bold the word Totals in cells A7 and A15 on the grouped worksheets. 2.000 6 In the Revenue per Day section of the grouped sheets, apply Accounting Number Format with zero decimal places to the Orchestra Front revenue (range C11:H11) and the total revenue row (C15:H15). Apply the Comma Style with zero decimal places to the remaining seating revenue rows (C12:H14). Apply the Single Accounting underline (not borders) to the range C14:H14 and apply Double Accounting underline (not borders) to the range C15:H15. 10.000 7 Use Format Painter to copy the formats from cells A2:H2 to cells A10:H10. Select the range A1:H15 and set the column width to Autofit. Ungroup the worksheets. Display the Week 4 worksheet and fill the formats of cells C1 and C9 from the Week 4 worksheet to the October worksheet without copying the content. 8.000 8 On the Documentation worksheet, create a hyperlink from the Week 1 label to cell A1 on the Week 1 worksheet. Create the hyperlinks from the remaining worksheet labels on the Documentation worksheet to the other worksheets. 7.000 9 On the Week 1 worksheet, create a hyperlink from cell A1 back to cell A1 on the Documentation worksheet. Add a ScreenTip Click to go to the Documentation sheet. (including the period). Group the weekly and October worksheets, and then use the Fill Across Worksheets command to copy the link and formatting to the other weekly and summary worksheets. Ungroup the worksheets. 7.000 10 In cell C11 in the October worksheet, insert a 3-D reference in a function using the SUM function that calculates the total Sunday Orchestra Front revenue for all four weeks. Copy the formula for the remaining seating types and weekdays. 7.000 11 In the Week 4 worksheet, select the range C11:H15 and fill the revenue number formatting to the same range in the October worksheet. 4.000 12 In cell C3 in the October worksheet, enter a 3-D reference in formula that calculates the overall percentage of total Sunday Orchestra Front tickets sold based on the total available Orchestra Front seating. The formula must sum the total Sunday Orchestra Front seats sold in cells C3 of the weekly sheets and then divide it by the sum of the available Orchestra Front seats in cell B3 of the weekly sheets. Avoid raw numbers and use an appropriate mix of relative and mixed references to derive the correct percentage. Format the result with Percent Style. Copy the formula to the range C4:C6 and then to the range D3:G6. 10.000 13 In cell H3 in the October worksheet, calculate the average daily percent for each seating type. Do not use a 3-D reference in a formula. Format the results with Percent Style with one decimal place, and then copy the formula to the range H4:H6. 10.000 14 Correct the circular reference in cell B7. 6.000 15 Create a footer on all worksheets with the sheet name code in the center and the file name code on the right side. Apply landscape orientation, and then center the worksheet horizontally on the printouts. Then ungroup the worksheets. 3.000 16 Save the workbook. Ensure that the workbooks are in the following order: Documentation, Week 1, Week 2, Week 3, Week 4, October. Close the workbook and exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000

Updated: 06/08/2017 1 Current_Instruction.docx