Office 2013 - myitlab:grader - Instructions Exploring Series Vol. 2 Multiple-Sheet Workbook Management Project Description: You are an accounting assistant for Downtown Theater in San Diego. The theater hosts touring Broadway 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_h1.xlsx. Acknowledge the error, and then save the file as e09c1Theater10_LastFirst, replacing LastFirst with your name. 0 2 On the Week 1 worksheet, select the number of daily Orchestra Front tickets sold (in the range C3:G3). Create a validation rule to accept only whole numbers between 0 and the available seating limit in cell B3. Create an input message, using the seating type from cell A3 as the title text. As the input message, type Enter the number of tickets sold per day. (include the period). Create an error alert for the range using the Stop icon. Type Invalid Data as the title text and enter the error message as You entered an invalid value. Please enter a number between 0 and 86. (include the period). Repeat this procedure for each of the remaining rows in the range C4:G6. Change the maximum value in the error message based on the value in column B. 6 3 Circle invalid data entry. Change each invalid entry to the maximum number of applicable seats. 6 4 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. 6 5 Insert formulas to calculate the weekly seating totals and the total daily revenue. Include the grand total for the week. Indent and bold the word Totals in cells A7 and A15 on the grouped worksheets. 6 In the Revenue per Day section of the grouped sheets, apply the Accounting number format with zero decimal places to the Orchestra Front revenue and the total revenue row. Apply the Comma Style with zero decimal places to the remaining seating revenue rows. Apply bottom double borders to the range C15:G15. 6 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 auto. 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 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. 6 Updated: 08/21/2013 1 12 E_CH09_EXPV2_H1_Instructions.docx Office 2013 - myitlab:grader - Instructions Step Exploring Series Vol. 2 Points Possible Instructions 9 On the Week 1 worksheet, create a hyperlink from cell A1 back to cell A1 on the Documentation worksheet. 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. 6 10 Insert a 3-D formula that calculates the total Sunday Orchestra Front revenue for all four weeks in cell C11 in the October worksheet. Copy the formula for the remaining seating types and weekdays. 6 11 Use the Week 4 worksheet to fill the revenue number formatting to the October revenue. 6 12 Enter a 3-D formula in cell C3 on the October worksheet that calculates the overall percentage of total Sunday Orchestra Front tickets sold based on the total available Orchestra Front seating. The 3-D formula must perform several internal calculations, avoid raw numbers, and use an appropriate mix of relative and mixed references to derive the correct percentage. Format the result with Percent Style. The result is 100%, based on the sum of the Sunday Orchestra Front tickets sold divided by the total available tickets for all four weeks. Copy the formula to the remaining cells in the range C3:G6. 10 13 In cell H3, calculate the average daily percent for each seating type. Do not use a 3-D formula. Format the results with Percent Style with one decimal place, and then copy the formula down through row 6. 10 14 Correct the circular reference in cell B7. 6 15 Create a footer on the Documentation worksheet 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. 6 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 Total Points Updated: 08/21/2013 2 100 E_CH09_EXPV2_H1_Instructions.docx Home Date: Purpose: Exploring Series 8-Mar-16 Store daily ticket sales by seating group. Calculate daily and weekly revenue. Calculate monthly seating revenue. Worksheets: Week 1 Week 2 Week 3 Week 4 October Summary Worksheet Home Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Number of Seats Sold per Day Available 86 16 64 46 212 Sunday 86 16 50 32 $ 184.00 Wednesday 84 12 54 42 $ 192.00 Friday 86 16 64 44 $ 210.00 Saturday Matinee Saturday Evening Weekly Totals 86 86 428 16 16 76 64 64 296 46 46 210 $ 212.00 $ 212.00 $ 1,010.00 Revenue per Day Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Saturday Seat Price Sunday Wednesday Friday Matinee Saturday Evening Weekly Totals $ 168 $ 14,448 $ 14,112 $ 14,448 $ 14,448 $ 14,448 $ 71,904 $ 250 4,000 3,000 4,000 4,000 4,000 19,000 $ 155 7,750 8,370 9,920 9,920 9,920 45,880 $ 95 3,040 3,990 4,180 4,370 4,370 19,950 $ 29,238 $ 29,472 $ 32,548 $ 32,738 $ 32,738 $ 156,734 Home Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Number of Seats Sold per Day Available 86 16 64 46 212 Sunday 86 16 64 41 $ 207.00 Wednesday 86 8 64 41 $ 199.00 Friday 86 16 64 46 $ 212.00 Saturday Matinee 86 16 64 46 $ 212.00 Saturday Evening Weekly Totals 86 16 64 46 $ 212.00 430 72 320 220 $ 1,042.00 Revenue per Day Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Seat Price Sunday Wednesday Friday Saturday Matinee Saturday Evening Weekly Totals $ 168 $ 14,448 $ 14,448 $ 14,448 $ 14,448 $ 14,448 $ 72,240 $ 250 4,000 2,000 4,000 4,000 4,000 18,000 $ 155 9,920 9,920 9,920 9,920 9,920 49,600 $ 95 3,895 3,895 4,370 4,370 4,370 20,900 $ 32,263 $ 30,263 $ 32,738 $ 32,738 $ 32,738 $ 160,740 Home Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Number of Seats Sold per Day Available 86 16 64 46 212 Sunday 86 12 53 40 Wednesday 72 8 64 40 $ 191.00 $ 184.00 86 16 64 40 Saturday Matinee 86 16 64 40 $ 206.00 $ 206.00 Friday Saturday Evening 86 16 64 46 $ 212.00 Weekly Totals 416 68 309 206 $ 999.00 Revenue per Day Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Saturday Seat Price Sunday Wednesday Friday Matinee Saturday Evening Weekly Totals $ 168 $ 14,448 $ 12,096 $ 14,448 $ 14,448 $ 14,448 $ 69,888 $ 250 3,000 2,000 4,000 4,000 4,000 17,000 $ 155 8,215 9,920 9,920 9,920 9,920 47,895 $ 95 3,800 3,800 3,800 3,800 4,370 19,570 $ 29,463 $ 27,816 $ 32,168 $ 32,168 $ 32,738 $ 154,353 Home Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Number of Seats Sold per Day Available 86 16 64 46 212 Sunday 86 12 56 44 Wednesday 84 16 60 46 $ 198.00 $ 206.00 86 16 64 42 Saturday Matinee 84 16 62 44 $ 208.00 $ 206.00 Friday Saturday Evening 86 16 64 46 $ 212.00 Weekly Totals 426 76 306 222 $ 1,030.00 Revenue per Day Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Saturday Seat Price Sunday Wednesday Friday Matinee Saturday Evening Weekly Totals $ 168 $ 14,448 $ 14,112 $ 14,448 $ 14,112 $ 14,448 $ 71,568 $ 250 3,000 4,000 4,000 4,000 4,000 19,000 $ 155 8,680 9,300 9,920 9,610 9,920 47,430 $ 95 4,180 4,370 3,990 4,180 4,370 21,090 $ 30,308 $ 31,782 $ 32,358 $ 31,902 $ 32,738 $ 159,088 Home Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Seating Orchestra Front Box Seats Mezzanine Level 1 Balcony Level 1 Totals Number of Seats Sold per Day Available 86 16 64 46 Sunday Wednesday 100.0% 97.7% 100.0% 50.0% 82.8% 100.0% 95.7% 100.0% Friday Saturday Matinee 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 91.3% 95.7% Saturday Evening 100.0% 100.0% 100.0% 100.0% Avg Daily % 99.5% 90.0% 96.6% 96.5% 212 Revenue per Day Seat Price Sunday Wednesday Friday Saturday Matinee Saturday Evening Weekly Totals $ 168 $ 57,792 $ 54,768 $ 57,792 $ 57,456 $ 57,792 $ 285,600 $ 250 14000 11000 16000 16000 16000 73000 $ 155 34565 37510 39680 39370 39680 190805 $ 95 14915 16055 16340 16720 17480 81510 $ 121,272 $ 119,333 $ 129,812 $ 129,546 $ 130,952 $ 630,915